DBMS_SQLTUNE的使用方法

網(wǎng)站建設(shè)哪家好，找創(chuàng)新互聯(lián)！專注于網(wǎng)頁(yè)設(shè)計(jì)、網(wǎng)站建設(shè)、微信開發(fā)、成都小程序開發(fā)、集團(tuán)企業(yè)網(wǎng)站建設(shè)等服務(wù)項(xiàng)目。為回饋新老客戶創(chuàng)新互聯(lián)還提供了永仁免費(fèi)建站歡迎大家使用！

所需權(quán)限

grant advisor to user;

grant select_catalog_role to user;  --通過(guò)OEM管理必不可少

grant execute on dbms_sqltune to user;

 

獲取綁定變量的方法

    此時(shí)分兩種情況，一種是在v$sql中存在的SQL，一種是在v$sql中不存在的SQL

轉(zhuǎn)化v$sql中對(duì)應(yīng)的綁定變量值

   跟據(jù)SQL_ID取v$sql中bind_data的值

select position, value_string

 from table(dbms_sqltune.extract_binds('beda0a200500521ffd700'));

如果v$sql中不存在，可以用此方法獲取綁定變量值

select dbms_sqltune.extract_bind(bind_data,1).value_string || '-' ||

      dbms_sqltune.extract_bind(bind_data, 2).value_string || '-' ||

      dbms_sqltune.extract_bind(bind_data, 3).value_string || '-' ||

      dbms_sqltune.extract_bind(bind_data, 4).value_string || '-' ||

      dbms_sqltune.extract_bind(bind_data, 5).value_string || '-' ||

      dbms_sqltune.extract_bind(bind_data, 6).value_string

from sys.wrh$_sqlstat

 where sql_id = '1nnrufx6sw8sd'

 

1、創(chuàng)建調(diào)優(yōu)任務(wù)

1.1 通過(guò)sql_text創(chuàng)建（綁定變量SQL）

DECLARE

 my_task_name VARCHAR2(30);

 my_sqltext  CLOB;

BEGIN

 my_sqltext := 'select * from emp where ename=:name and DEPTNO= :deptno';

 my_task_name :=DBMS_SQLTUNE.CREATE_TUNING_TASK(

        sql_text    => my_sqltext,

bind_list  => sql_binds(anydata.convertvarchar2(10),anydata.convertnumber(2)),

        user_name   => 'TEST',

        scope       => 'COMPREHENSIVE',

        time_limit  => 60,

        task_name   =>'test_sql_tuning',

        description => 'Task to tune a query on emp');

END;

/

1.2 無(wú)綁定變量SQL的優(yōu)化

declare

 my_task_name VARCHAR2(30);

 my_sqltext   CLOB;

BEGIN

 my_sqltext   := 'select * fromorabpel.cube_scope';

 my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text    => my_sqltext,

                                                 user_name   => 'orabpel',

                  scope => 'COMPREHENSIVE',

                  time_limit => 60,  --優(yōu)化限時(shí)60s

                  task_name   => 'wxw_sql_tuning_task',

                  description => 'tune thebad sql');

 dbms_sqltune.Execute_tuning_task(task_name =>'TEST_sql_tuning_task');

END;

1.3 通過(guò)SQL_ID進(jìn)行優(yōu)化

    這種情況通常常用于v$sql中的異常SQL優(yōu)化

DECLARE 

    my_task_name VARCHAR2(40); 

BEGIN 

    my_task_name :=  DBMS_SQLTUNE.create_tuning_task(

        sql_id         => '1hudpukz651tt',

        plan_hash_value    => NULL,

        scope           =>  dbms_sqltune.SCOPE_COMPREHENSIVE,

        time_limit   =>  dbms_sqltune.TIME_LIMIT_DEFAULT,

        task_name    => 'test_sql_tuning_1hudpukz651tt',

        description   => '1hudpukz651tt'); 

    DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'test_sql_tuning_1hudpukz651tt');

    dbms_output.put_line(my_task_name); 

END; 

1.4 通過(guò)指定AWR中的Snap_id進(jìn)行優(yōu)化

declare

    my_task_name varchar2(30);

begin

    my_task_name := dbms_sqltune.create_tuning_task(

         begin_snap      => 24365,

         end_snap        => 24366,

         sql_id          =>'d40kghyfbg8sj',

         plan_hash_value => null,

         scope           =>'comprehensive',

         time_limit      => 60,

         task_name       =>'wxw_sql_tuning_task',

         description     => 'tune thebad sql'

    );

    dbms_sqltune.execute_tuning_task (task_name =>'wxw_sql_tuning_task');

 end;

 

2、執(zhí)行調(diào)優(yōu)任務(wù)

---execute

execdbms_sqltune.execute_tuning_task('TEST_tuning_task');

3、查看執(zhí)行情況

---checek

SELECT status FROM USER_ADVISOR_TASKS WHERElower(task_name) ='test_tuning_task';

select * from user_advisor_tasks;

---report

SET LONG 999999

set serveroutput on size 999999

SET LINESIZE 1000

SELECTDBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_tuning_task') FROM DUAL;

4、刪除調(diào)優(yōu)任務(wù)

---drop

execdbms_sqltune.drop_tuning_task('TEST_tuning_task');

分享標(biāo)題：DBM_SQLTUNE使用簡(jiǎn)介
本文路徑：http://jinyejixie.com/article0/ggchio.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站策劃、動(dòng)態(tài)網(wǎng)站、網(wǎng)站建設(shè)、關(guān)鍵詞優(yōu)化、面包屑導(dǎo)航、商城網(wǎng)站

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)