[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
--//以前写的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
