[20221130]优化备库dg遇到的问题2.txt
--//生产系统一些语句执行慢,开发或者同事移动到备库执行,我发现实际这些语句大部分都是由于选择错误的索引导致的情况.
--//我想通过sql profile在主库来稳定执行计划,这样备库也可以使用sql profile选择合理的执行计划.但是我遇到一些问题,做一些记
--//录.
1.环境:
SYS@192.168.100.237:1521/orcldg> @ pr
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 19.0.0.0.0
BANNER : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
BANNER_FULL : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
BANNER_LEGACY : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.
2.问题语句:
SYS@192.168.100.237:1521/orcldg> @ dpc bu48z014njcg4 ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID bu48z014njcg4, child number 0
-------------------------------------
select case a.hosp_id when 141 then
1 when 181 then 3 when 201 then 2 end hosp_code,a.id
test_id,''GROUP_ID,a.AUDIT_TIME test_date ,''SAMPLE_NUMBER,a.barcode
a.pat_barcode pat_id,''INPATIENT_ID,''
CHARGE_TYPE,a.pat_name ,a.pat_sex ,''AGE_TYPE,a.report_age
,''AGE_SAVE,''PATIENT_NATION,''PATIENT_NATION_NAME,''BLOODTYPE_ABO,''BLO
a.depart_name ,a.area_code ,a.area_name
,a.BED ,''ESPECIAL_CONDITION,a.diagnosis_name , '' SAMPLE_CLASS,
a.sample_type_name ,'' INFECT_STATUS,'' SAMPLE_STATUS,''
''_STATUS_NAME,'' SAMPLING_POSITION,
'' SAMPLE_CHARGE,'''' TEST_ORDER,
_COUNT,''WORKLOAD, a.test_user_name, a.print_user_name,
a.collect_time ,a.collect_user_name ,a.order_time ,a.order_user_name
,a.order_item_name ,'' INCEPT_TIME,''INCEPT_PERSON,a.sign_time ,
a.audit_user_name ,
to_char( a.audit_time,'yyyy-mm-dd hh
Plan hash value: 86349049
-----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 13 (100)| | | | |
| 1 | SORT ORDER BY | | 1 | 368 | | | 52224 | 52224 |47104 (0)|
| 2 | HASH UNIQUE | | 1 | 368 | 13 (8)| 00:00:01 | 714K| 714K| 1390K (0)|
|* 3 | FILTER | | | | | | | | |
| 4 | NESTED LOOPS | | 1 | 368 | 12 (0)| 00:00:01 | | | |
| 5 | NESTED LOOPS OUTER | | 1 | 362 | 9 (0)| 00:00:01 | | | |
|* 6 | TABLE ACCESS BY INDEX ROWID BATCHED| LIS_TEST | 1 | 281 | 6 (0)| 00:00:01 | | | |
|* 7 | INDEX RANGE SCAN | IX_LIS_TEST_ORDER_TIME | 3 | | 3 (0)| 00:00:01 | | | |
| 8 | TABLE ACCESS BY INDEX ROWID BATCHED| LIS_PROMPT | 1 | 81 | 3 (0)| 00:00:01 | | | |
|* 9 | INDEX RANGE SCAN | IX_LIS_PROMPT_TEST_ID | 1 | | 2 (0)| 00:00:01 | | | |
|* 10 | INDEX RANGE SCAN | PK_LIS_RESULT | 10 | 60 | 3 (0)| 00:00:01 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$4439918E
6 - SEL$4439918E / A@SEL$2
7 - SEL$4439918E / A@SEL$2
8 - SEL$4439918E / C@SEL$3
9 - SEL$4439918E / C@SEL$3
10 - SEL$4439918E / B@SEL$2
Peeked Binds (identified by position):
--------------------------------------
1 - :1 (CHAR(30), CSID=852): '91237433'
2 - :2 (CHAR(30), CSID=852): '452127198107122110'
3 - (CHAR(30), CSID=852): '2022-11-18'
4 - (CHAR(30), CSID=852): '2022-11-24'
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(TO_DATE(:STR_DTTO,'yyyy-MM-dd')>=TO_DATE(:STR_DTFROM,'yyyy-MM-dd'))
6 - filter((("A"."IDENTITY_ID"=SYS_OP_C2C(:STR_SFZH) OR "A"."PAT_ID"=SYS_OP_C2C(:STR_MZHM)) AND "A"."STATE">=60))
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
7 - access("A"."ORDER_TIME">=TO_DATE(:STR_DTFROM,'yyyy-MM-dd') AND "A"."ORDER_TIME"<=TO_DATE(:STR_DTTO,'yyyy-MM-dd'))
9 - access("A"."ID"="C"."TEST_ID")
10 - access("A"."ID"="B"."ID")
--//选择错误的索引IX_LIS_TEST_ORDER_TIME,应该选择IDENTITY_ID以及PAT_ID索引,首先这些索引是存在的.
--//但是我首先遇到奇怪的问题.
3.生成执行脚本:
SYS@192.168.100.237:1521/orcldg> @ b9 bu48z014njcg4 0
variable STR_MZHM VARCHAR2(32)
variable STR_SFZH VARCHAR2(128)
variable STR_DTFROM VARCHAR2(32)
variable STR_DTTO VARCHAR2(32)
begin
:STR_MZHM := '91209282';
:STR_SFZH := '450802198909083618';
:STR_DTFROM := '2022-11-07';
:STR_DTTO := '2022-11-29';
null;
end;
/
set termout off
set sqlblanklines on
alter session set current_schema=FINDREPORT;
alter session set statistics_level=all;
select distinct re.*
from (
select case a.hosp_id when 141 then 1 when 181 then 3 when 201 then 2 end hosp_code,a.id test_id,''GROUP_ID,a.AUDIT_TIME test_date ,''SAMPLE_NUMBER,a.barcode ,a.pat_type_name ,
a.pat_barcode pat_id,''INPATIENT_ID,'' CHARGE_TYPE,a.pat_name ,a.pat_sex ,''AGE_TYPE,a.report_age ,''AGE_SAVE,''PATIENT_NATION,''PATIENT_NATION_NAME,''BLOODTYPE_ABO,''BLOODTYPE_RH,a.depart_code ,
a.depart_name ,a.area_code ,a.area_name ,a.BED ,''ESPECIAL_CONDITION,a.diagnosis_name , '' SAMPLE_CLASS,
a.sample_type_name ,'' INFECT_STATUS,'' SAMPLE_STATUS,'' SAMPLE_STATUS_NAME,'' SAMPLING_POSITION,
'' SAMPLING_POSITION_NAME,'' TEST_ORDER,
'' SAMPLE_CHARGE,'' ITEM_COUNT,''WORKLOAD, a.test_user_name, a.print_user_name,
a.collect_time ,a.collect_user_name ,a.order_time ,a.order_user_name ,a.order_item_name ,'' INCEPT_TIME,''INCEPT_PERSON,a.sign_time ,
a.sign_user_name ,
''INSPECTION_PERSON,
a.audit_user_name , to_char( a.audit_time,'yyyy-mm-dd hh24:mi:ss')audit_time ,''PRINT_PERSON,
a.print_time ,''PRINT_COUNT,''EXPERT_RULE,''CHARGE_STATE,''INSPECTION_STATE,''RERUN_STATE,''READ_STATE,
dbms_lob.substr(VERDICT) order_remark,
''REMARK_NAME,''ID_CARD,''CONFIRM_PERSON,''CONFIRM_TIME,''PATIENT_TYPE_OLD,''INSTRUMENT,''MOBILENO,''SEND_STATE,''SEND_DATE,''PATIENT_BIRTHDAY,a.report_type ,a.id RESID,
case when a.state=90 then '已报告' when a.state=60 then '正在检验' when a.state=100 then '已打印' else '' end INSPECTIONSTATE,''YCBS,''runow
from lis_test a
inner join lis_result b on a.id=b.id
left join lis_prompt c on a.id=c.test_id
where ( a.pat_id= :str_mzhm or a.identity_id= :str_sfzh )
and a.ORDER_TIME>=to_date( :str_dtfrom,'yyyy-MM-dd')
and a.ORDER_TIME<=to_date( :str_dtto,'yyyy-MM-dd')
and a.state >=60
)re order by re.ORDER_TIME;
set termout on
set sqlblanklines off
--@zws '' ''
--@dpc '' ''
@dpc '' outline
rollback;
alter session set current_schema=SYS ;
--//而当我执行生成的脚本时,我发现里面记录的sql_id不是bu48z014njcg4,而是76wprdhujxj54,怎么可能不同呢?
--//首先我不可能在备库执行spsw.sql脚本来交换执行计划来稳定提高性能,因为要在read write模式,我必须在主库执行.
--//而sql_id=bu48z014njcg4的语句并不存在主库,主库执行后仅仅存在sql_id=76wprdhujxj54的语句.
--//这样我在主库执行
--//@ spsw good_sql_id 0 76wprdhujxj54 0 '' true 是否到备库sql_id=bu48z014njcg4可以应用.
--//注:最后我的测试可以.只要最后一个参数(第6个参数)为true.
--//我执行的脚本b9.sql在这个语句中仅仅过滤掉里面\r字符,使用replace替换chr(13)=>''.看来以后给修改脚本代码改成替换为空格!!
SYS@192.168.100.237:1521/orcldg> select lengthb(to_char(sql_fulltext)),length(SQL_FULLTEXT), ora_hash(sql_fulltext),sql_id from v$sqlarea where sql_id in ('bu48z014njcg4','76wprdhujxj54');
LENGTHB(TO_CHAR(SQL_FULLTEXT)) LENGTH(SQL_FULLTEXT) ORA_HASH(SQL_FULLTEXT) SQL_ID
------------------------------ -------------------- ---------------------- -------------
1816 1806 3132263513 bu48z014njcg4
1777 1767 2094332972 76wprdhujxj54
--//注:应该使用lengthb函数查看长度,我开始在这里犯错.因为语句里面有汉字.10个汉字多了10个长度.
--//1816-1777 = 39.两组存在39个字符的差距.
--//在toad下执行:
select length(SQL_FULLTEXT), ora_hash(sql_fulltext),sql_id ,sql_fulltext from v$sqlarea where sql_id in ('bu48z014njcg4','76wprdhujxj54');
--//在toad下分别取出sql_fulltext输出,分别对应的保存为d1.bu48z014njcg4,d2.76wprdhujxj54文件.
$ ls -l d1.bu48z014njcg4 d2.76wprdhujxj54
-rw-r--r-- 1 oracle oinstall 1790 2022-11-30 10:16:32 d1.bu48z014njcg4
-rw-r--r-- 1 oracle oinstall 1778 2022-11-30 10:16:52 d2.76wprdhujxj54
--//d1.bu48z014njcg4,d2.76wprdhujxj54有12个字符上的差异.
--//1816-1790 = 26,这26个字符应该是\r字符.
$ diff d1.bu48z014njcg4 d2.76wprdhujxj54 | cat -Ev
8,9c8,9$
< $
< '' SAMPLING_POSITION_NAME,'' TEST_ORDER, $
---$
> $
> '' SAMPLING_POSITION_NAME,'' TEST_ORDER,$
11,12c11,12$
< $
< a.collect_time ,a.collect_user_name ,a.order_time ,a.order_user_name ,a.order_item_name ,'' INCEPT_TIME,''INCEPT_PERSON,a.sign_time , $
---$
> $
> a.collect_time ,a.collect_user_name ,a.order_time ,a.order_user_name ,a.order_item_name ,'' INCEPT_TIME,''INCEPT_PERSON,a.sign_time ,$
14,15c14,15$
< ''INSPECTION_PERSON, $
< a.audit_user_name , to_char( a.audit_time,'yyyy-mm-dd hh24:mi:ss')audit_time ,''PRINT_PERSON, $
---$
> ''INSPECTION_PERSON,$
> a.audit_user_name , to_char( a.audit_time,'yyyy-mm-dd hh24:mi:ss')audit_time ,''PRINT_PERSON,$
21,22c21,22$
< from lis_test a $
< inner join lis_result b on a.id=b.id $
---$
> from lis_test a$
> inner join lis_result b on a.id=b.id$
25,26c25,26$
< and a.ORDER_TIME>=to_date( :str_dtfrom,'yyyy-MM-dd') $
< and a.ORDER_TIME<=to_date( :str_dtto,'yyyy-MM-dd') $
---$
> and a.ORDER_TIME>=to_date( :str_dtfrom,'yyyy-MM-dd')$
> and a.ORDER_TIME<=to_date( :str_dtto,'yyyy-MM-dd')$
--//看上去一样的,cat -E可以在结尾加入$,实际上有一些行结尾处有一些行包含空格.也就是差异的12个字符实际上空格.换一句话将
--//b9.sql脚本的输出时结尾的空格丢失了.
--//我的缺省设置:
--//set trimout on; -- 去除标准输出每行的行尾空格(缺省为off)
--//set trimspool on; -- 去除spool输出结果中每行的结尾空格(缺省为off)
SYS@192.168.100.237:1521/orcldg> show trimspool
trimspool ON
SYS@192.168.100.237:1521/orcldg> show trimout
trimout ON
--//我测试了set trimout off好像也不行呢?.另外写blog说明问题.
--//如果加入-b参数,两者一致.
--// -b Ignore changes in amount of white space
--// -w Ignore white space when comparing lines.
$ diff -b d1.bu48z014njcg4 d2.76wprdhujxj54
$ diff -w d1.bu48z014njcg4 d2.76wprdhujxj54
$ wc d1.bu48z014njcg4 d2.76wprdhujxj54
28 133 1790 d1.bu48z014njcg4
28 133 1778 d2.76wprdhujxj54
56 266 3568 total
--//我仔细在toad下看了sql_fulltext的dump发现bu48z014njcg4每行结尾有\r字符.这样
--//1816-1790 = 26,应该多27个才对啊(最好1行不算).怎么差1个字符呢?
--//仔细看76wprdhujxj54,lengthb输出的长度是1777,d2.76wprdhujxj54的文件大小1778,也是差1,这样就很好理解了,因为保存文件
--//时最后一行结尾存在\n字符,多1个是正常的.
--//这样就完成对上了,我的b9脚本输出过滤chr(13)字符(\r),并且因为sqlplus的输出少了12个空格.
--//27 chr(13) + 12 空格 = 39.
--//1816-1777 = 39.这样正好能对上了.
--//不过即使我修改生成的脚本加入\r字符在sqlplus结尾,在sqlplus执行的sql_id还是76wprdhujxj54.
--//顺便做一个记录vim下在结尾加入^M的方法,打入:'a,'bs/$/\^M/
--//前面的\不能缺少,不然文本仅仅全部多一个空行.
--//^M 输入 ctrl+q ctrl+m (for windwos) 或者 ctrl+v ctrl+m ( for linux)
--//不过最后我发现我自己多虑了,实际上在主库执行如下:
--//先运行bu48z014njcg4.sql9_0 => sql_id=76wprdhujxj54
--//加入提示: BITMAP_TREE(@"SEL$4439918E" "A"@"SEL$2" OR(1 2 "IX_LIS_TEST_IDENTITY_ID" 1 ("LIS_TEST"."PAT_ID")))
--//再运行bu48z014njcg4.sql9_0 -> sql_id=86uxv50rvtb3x
SYS@192.168.100.235:1521/orcl> @ spsw 86uxv50rvtb3x 0 76wprdhujxj54 0 '' true
PL/SQL procedure successfully completed.
--//主要第6个参数等于true,就没有问题.
SYS@192.168.100.235:1521/orcl> update (select * from sqlobj$auxdata where created<>to_char(created,'yyyy-mm-dd hh24:mi:ss')) set created=to_char(created,'yyyy-mm-dd hh24:mi:ss');
1 row updated.
SYS@192.168.100.235:1521/orcl> commit ;
Commit complete.
--//为了在toad下维护sql profile.
--//然后在备库执行或者查看sql_id=bu48z014njcg4的执行计划,会使用对应的sql profile.
SYS@192.168.100.237:1521/orcldg> @ dpc bu48z014njcg4 ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID bu48z014njcg4, child number 1
-------------------------------------
select case a.hosp_id when 141 then
1 when 181 then 3 when 201 then 2 end hosp_code,a.id
test_id,''GROUP_ID,a.AUDIT_TIME test_date ,''SAMPLE_NUMBER,a.barcode
a.pat_barcode pat_id,''INPATIENT_ID,''
CHARGE_TYPE,a.pat_name ,a.pat_sex ,''AGE_TYPE,a.report_age
,''AGE_SAVE,''PATIENT_NATION,''PATIENT_NATION_NAME,''BLOODTYPE_ABO,''BLO
a.depart_name ,a.area_code ,a.area_name
,a.BED ,''ESPECIAL_CONDITION,a.diagnosis_name , '' SAMPLE_CLASS,
a.sample_type_name ,'' INFECT_STATUS,'' SAMPLE_STATUS,''
''_STATUS_NAME,'' SAMPLING_POSITION,
'' SAMPLE_CHARGE,'''' TEST_ORDER,
_COUNT,''WORKLOAD, a.test_user_name, a.print_user_name,
a.collect_time ,a.collect_user_name ,a.order_time ,a.order_user_name
,a.order_item_name ,'' INCEPT_TIME,''INCEPT_PERSON,a.sign_time ,
a.audit_user_name ,
to_char( a.audit_time,'yyyy-mm-dd hh
Plan hash value: 518087822
------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 21 (100)| | | | |
| 1 | SORT ORDER BY | | 1 | 368 | 21 (15)| 00:00:01 | 74752 | 74752 |65536 (0)|
| 2 | HASH UNIQUE | | 1 | 368 | 20 (10)| 00:00:01 | 715K| 715K| 1388K (0)|
|* 3 | FILTER | | | | | | | | |
| 4 | NESTED LOOPS | | 1 | 368 | 19 (6)| 00:00:01 | | | |
| 5 | NESTED LOOPS OUTER | | 1 | 362 | 16 (7)| 00:00:01 | | | |
|* 6 | TABLE ACCESS BY INDEX ROWID BATCHED| LIS_TEST | 1 | 281 | 13 (8)| 00:00:01 | | | |
| 7 | BITMAP CONVERSION TO ROWIDS | | | | | | | | |
| 8 | BITMAP OR | | | | | | | | |
| 9 | BITMAP CONVERSION FROM ROWIDS | | | | | | | | |
| 10 | SORT ORDER BY | | | | | | 73728 | 73728 | |
|* 11 | INDEX RANGE SCAN | IX_LIS_TEST_IDENTITY_ID | | | 4 (0)| 00:00:01 | | | |
| 12 | BITMAP CONVERSION FROM ROWIDS | | | | | | | | |
|* 13 | INDEX RANGE SCAN | IX_LIS_TEST_PAT_ID | | | 3 (0)| 00:00:01 | | | |
| 14 | TABLE ACCESS BY INDEX ROWID BATCHED| LIS_PROMPT | 1 | 81 | 3 (0)| 00:00:01 | | | |
|* 15 | INDEX RANGE SCAN | IX_LIS_PROMPT_TEST_ID | 1 | | 2 (0)| 00:00:01 | | | |
|* 16 | INDEX RANGE SCAN | PK_LIS_RESULT | 10 | 60 | 3 (0)| 00:00:01 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$4439918E
6 - SEL$4439918E / A@SEL$2
14 - SEL$4439918E / C@SEL$3
15 - SEL$4439918E / C@SEL$3
16 - SEL$4439918E / B@SEL$2
Peeked Binds (identified by position):
--------------------------------------
1 - :1 (CHAR(30), CSID=852): '91240481'
2 - :2 (CHAR(30), CSID=852): '452128198512081523'
3 - (CHAR(30), CSID=852): '2022-11-19'
4 - (CHAR(30), CSID=852): '2022-12-01'
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(TO_DATE(:STR_DTTO,'yyyy-MM-dd')>=TO_DATE(:STR_DTFROM,'yyyy-MM-dd'))
6 - filter(("A"."ORDER_TIME">=TO_DATE(:STR_DTFROM,'yyyy-MM-dd') AND "A"."ORDER_TIME"<=TO_DATE(:STR_DTTO,'yyyy-MM-dd') AND
"A"."STATE">=60))
11 - access("A"."IDENTITY_ID"=SYS_OP_C2C(:STR_SFZH))
filter("A"."IDENTITY_ID"=SYS_OP_C2C(:STR_SFZH))
13 - access("A"."PAT_ID"=SYS_OP_C2C(:STR_MZHM))
15 - access("A"."ID"="C"."TEST_ID")
16 - access("A"."ID"="B"."ID")
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 17 (U - Unused (1))
---------------------------------------------------------------------------
0 - STATEMENT
- ALL_ROWS
- DB_VERSION('19.1.0')
- IGNORE_OPTIM_EMBEDDED_HINTS
- OPTIMIZER_FEATURES_ENABLE('19.1.0')
0 - SEL$2
- MERGE(@"SEL$2" >"SEL$006708EA")
0 - SEL$98196233
- ANSI_REARCH(@"SEL$3")
- MERGE(@"SEL$98196233" >"SEL$1")
0 - SEL$C97233AF
- MERGE(@"SEL$C97233AF" >"SEL$E6E74641")
1 - SEL$4439918E
- ANSI_REARCH(@"SEL$4")
- LEADING(@"SEL$4439918E" "A"@"SEL$2" "C"@"SEL$3" "B"@"SEL$2")
6 - SEL$4439918E / A@SEL$2
U - BITMAP_TREE(@"SEL$4439918E" "A"@"SEL$2" OR(1 2 "IX_LIS_TEST_IDENTITY_ID" 1 ("LIS_TEST"."PAT_ID")))
- BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$4439918E" "A"@"SEL$2")
14 - SEL$4439918E / C@SEL$3
- BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$4439918E" "C"@"SEL$3")
- INDEX_RS_ASC(@"SEL$4439918E" "C"@"SEL$3" ("LIS_PROMPT"."TEST_ID"))
- USE_NL(@"SEL$4439918E" "C"@"SEL$3")
16 - SEL$4439918E / B@SEL$2
- INDEX(@"SEL$4439918E" "B"@"SEL$2" ("LIS_RESULT"."ID" "LIS_RESULT"."ITEM_ID"))
- USE_NL(@"SEL$4439918E" "B"@"SEL$2")
Note
-----
- SQL profile switch tuning 76wprdhujxj54 used for this statement
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
116 rows selected.
3.附上spsw.sql脚本:
$ 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'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 => &&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;
/
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2926312/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
