[20221130]最佳化備庫dg遇到的問題2.txt
[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部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2926312/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20221128]dg資料庫最佳化問題.txt資料庫
- ASM REBLANCE引起的DG備庫停止同步問題ORA-16055ASM
- ORACLE RAC TO RAC DG搭建過程中可能遇到的問題Oracle
- Oracle-DG最大保護模式下,dg備庫出現問題對主庫有什麼影響？Oracle模式
- [20180423]關於rman備份的問題2.txt
- [20180413]熱備模式相關問題2.txt模式
- ORACLE DG之備庫角色Oracle
- [20190124]bbed恢復資料遇到延遲塊清除的問題2.txt
- 【DG】備庫RMAN還原方式搭建DG（不使用duplicate命令）
- 【DG】Data Guard主備庫Failove切換AI
- 【DG】Data Guard主備庫Switchover切換
- ORACLE DG從庫 Rman備份恢復Oracle
- [20190116]詭異的問題2.txt
- JackJson遇到的問題JSON
- mysql 遇到的問題MySql
- WangEditor遇到的問題
- Homestead 遇到的問題
- sudo 遇到的問題
- linux遇到的問題Linux
- Vagrant 遇到的問題
- 11g dg 備庫搭建多種方式
- dg主庫建立檔案備庫未同步解決方法
- 本週遇到的問題
- 部署Jenkins + 遇到的問題Jenkins
- fastadmin自己遇到的問題AST
- 使用git遇到的問題Git
- SpringBoot遇到的某些問題Spring Boot
- 我遇到的小白問題
- React Native遇到的問題React Native
- 遇到奇葩問題
- DG備庫手動管理 新增資料檔案
- [20231023]共享伺服器的問題2.txt伺服器
- DG使用中遇到的幾個錯誤
- 遇到的編碼問題、時區問題整理
- [20230427]bbed sum apply問題2.txtAPP
- 資料庫伺服器的NUMA最佳化問題資料庫伺服器
- Oracle資料庫備份、災備的23個常見問題Oracle資料庫
- 初學javaweb遇到的問題JavaWeb