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