[20230516]完善spsw.sql脚本.txt

[20230516]完善spsw.sql脚本.txt --//以前写的spsw.sql脚本通过加入提示,产生好的执行计划(sql_id=good_sql_id),替换有问题的sql语句(bad_sql_id). --//现在遇到一个问题,就是现在的dg可以做只读查询,里面的sql语句没有在主库执行过,我抽取的脚本在sqlplus执行时里面的\r字符给 --//过滤掉了.即使加入\r也没有用,你可以测试windows写的sql语句(带\r)字符,linux写的sql语句(没带\r)字符,在sqlplus下执行时可以 --//发现生成的sql_id是一样的. --//这样我执行生成的sql_id与原来不一致.而且在spsw.sql在备库无法执行,因为是只读模式. --//我的做法现在在主库执行一次,获得sql_id,相当于bad_sql_id. --//然后加入提示在主库执行一次,获得sql_id,相当于good_sql_id. --//然后执行如下 @spsw good_sql_id 0 bad_sql_id 0 '' true --//这样带来一个问题我使用spext.sql(sql profile extrace)脚本无法查询,因为我无法知道good_sql_id, bad_sql_id值. --//仅仅知道真实的sql_id,除非我看执行计划后面的note部分. --//而现在参数4=0现在脚本是没有使用的(我已经修改通过v$sqlarea获得完整sql文本).我使用它设置orig_sql_id. --//dbms_sqltune.execute_tuning_task执行支持database_link_to参数,看看DBMS_SQLTUNE.import_sql_profile发现不支持该参数. --//还有一个细节问题,就是获取执行计划的来源可以是v$sql_plan或者dba_hist_sql_plan. --//获取完整sql文本的来源可以是v$sqlarea或者dba_hist_sqltext. --//还有参数2指定child_number,有时候也许指定Plan_hash_value更加合理，特别是查询dba_hist_sql_plan视图时。 --//重新改写看看,加入参数7,8表示如下:： --//参数7 sga|awr 定义 执行计划的来源可以是v$sql_plan或者dba_hist_sql_plan. --//参数8 sga|awr 定义 完整sql文本的来源可以是v$sqlarea或者dba_hist_sqltext. --//这样的好处因为备库可以建立awr报表，对应sql语句保存在dba_hist_sqltext视图里面。我可以指定bad_sql_id不在主库运行过. --//例子: @ undefparm.sql ---//tpt 脚步用于清除参数1..25定义. --//不指定参数7,8 相当于sga. @ spsw good_sql_id [child_number|plan_hash_value] bad_sql_id [0|orig_sql_id] '' true --//利用执行计划好的plan_hash_value作为sql profile. @ spsw sql_id [child_number|plan_hash_value] sql_id [0|orig_sql_id] '' true awr awr --//备库有问题的sql语句: @ spsw good_sql_id [child_number|plan_hash_value] sql_id [0|orig_sql_id] '' true  sga awr --//我并没有完整的测试,或者讲测试不全面.更新脚本如下: $ cat spsw.sql prompt prompt @spsw good_sql_id [child_number|plan_hash_value] bad_sql_id [0|orig_sql_id] '' true [sga|awr] [sga|awr] prompt col orig_sql_id new_value v3 define noprint='noprint' col tpt_comment1 &noprint new_value _tpt_comment1 col tpt_comment2 &noprint new_value _tpt_comment2 col tpt_comment3 &noprint new_value _tpt_comment3 col tpt_comment4 &noprint new_value _tpt_comment4 col tpt_noprint  &noprint new_value _tpt_noprint set term off select decode('&&4','0','&&3','&&4') orig_sql_id       ,decode(lower('&&7'),'sga','',NULL,'','--') tpt_comment1       ,decode(lower('&&7'),'awr','','--')         tpt_comment2       ,decode(lower('&&8'),'sga','',NULL,'','--') tpt_comment3       ,decode(lower('&&8'),'awr','','--')         tpt_comment4 from dual; set term on prompt orig_sql_id=&&v3 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 &&_tpt_comment1       FROM v$sql_plan &&_tpt_comment2       FROM dba_hist_sql_plan                        WHERE     sql_id = '&&1' &&_tpt_comment1              AND (child_number = &&2 or plan_hash_value= &&2) &&_tpt_comment2              AND (plan_hash_value= &&2)                              AND other_xml IS NOT NULL)) d;    SELECT &&_tpt_comment3 SQL_FULLTEXT &&_tpt_comment4 SQL_TEXT      INTO cl_sql_text      FROM -- replace with dba_hist_sqltext           -- if required for AWR based           -- execution &&_tpt_comment3 v$sqlarea &&_tpt_comment4 sys.dba_hist_sqltext     WHERE sql_id = '&&3'and rownum=1;    -- plan_hash_value = &&2;    DBMS_SQLTUNE.import_sql_profile (sql_text      => cl_sql_text,                                     profile       => ar_profile_hints,                                     category      => '&&5',                                     DESCRIPTION   => 'switch &&1 => &&v3',                                     name          => 'switch tuning &&v3' -- use force_match => true                                                                          -- to use CURSOR_SHARING=SIMILAR                                                                          -- behaviour, i.e. match even with                                                                          -- differing literals                                     ,                                     force_match   => &&6); END; / prompt ================================================================================================================================================= prompt if drop or alter sql profile ,run : prompt execute dbms_sqltune.drop_sql_profile(name => 'switch tuning &&v3') prompt execute dbms_sqltune.alter_sql_profile(name => 'switch tuning &&v3',attribute_name=>'STATUS',value=>'DISABLED') prompt ================================================================================================================================================= prompt prompt