[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;
/