[20221012]完善spsw.sql指令碼.txt

[20221012]完善spsw.sql指令碼.txt

--//前幾天在最佳化sql profile時,使用我自己寫spsw.sql(sql profile switch)時,遇到的問題.
--//我開始執行如下:
@ spsw 6svzht02nz53p 0 3rhg88u6qnt7h 0 '' true
--//提示沒有找到sql_id=3rhg88u6qnt7h的sql語句,明顯不存在child_number=0的sql語句.檢視發現存在child_number=1的語句.
@ spsw 6svzht02nz53p 0 3rhg88u6qnt7h 1 '' true
--//執行正常!!
--//很明顯我並不需要穩定語句對應的child_number的值,我僅僅知道在共享池存在該語句就ok了.
--//將原來的語句如下內容修改child_number = &&4 換成 rownum=1.或者使用v$sqlarea檢視.
   SELECT SQL_FULLTEXT
     INTO cl_sql_text
     FROM -- replace with dba_hist_sqltext
          -- if required for AWR based
          -- execution
          v$sql
    -- sys.dba_hist_sqltext
    WHERE sql_id = '&&3' AND child_number = &&4;
--//替換如下:
   SELECT SQL_FULLTEXT
     INTO cl_sql_text
     FROM -- replace with dba_hist_sqltext
          -- if required for AWR based
          -- execution
          v$sqlarea
    -- sys.dba_hist_sqltext
    WHERE sql_id = '&&3' ;

$ cat spsw.sql
-- @create_profile_from_shared_pool c2trqja6wh561 0 TEST true
-- @spsw good_sql_id 0 bad_sql_id 0 test true
-- @spsw good_sql_id 0 bad_sql_id 0 '' true
DECLARE
   ar_profile_hints   SYS.sqlprof_attr;
   cl_sql_text        CLOB;
BEGIN
   SELECT EXTRACTVALUE (VALUE (d), '/hint') AS outline_hints
     BULK COLLECT INTO ar_profile_hints
     FROM XMLTABLE (
             '/*/outline_data/hint'
             PASSING (SELECT xmltype (other_xml) AS xmlval
                        FROM v$sql_plan
                       WHERE     sql_id = '&&1'
                             AND child_number = &&2
                             AND other_xml IS NOT NULL)) d;

   SELECT SQL_FULLTEXT
     INTO cl_sql_text
     FROM -- replace with dba_hist_sqltext
          -- if required for AWR based
          -- execution
          v$sqlarea
    -- sys.dba_hist_sqltext
    WHERE sql_id = '&&3' ;

   -- plan_hash_value = &&2;

   DBMS_SQLTUNE.import_sql_profile (sql_text      => cl_sql_text,
                                    profile       => ar_profile_hints,
                                    category      => '&&5',
                                    DESCRIPTION   => 'switch &&1 => &&3',
                                    name          => 'switch tuning &&3' -- use force_match => true
                                                                         -- to use CURSOR_SHARING=SIMILAR
                                                                         -- behaviour, i.e. match even with
                                                                         -- differing literals
                                    ,
                                    force_match   => &&6);
END;
/