cursor_sharing=force導致sql profile部分hint失效
SQL文字
（部分表名欄位名做了替換）
select *
from (select prop1 ,
prop2,
(select codename
from code d1
where '1418892021000' = '1418892021000'
and d1.codetype = 'edornotype'
and d1.code = Rpad(prop3, 20, ' ' )),
prop11,
(case ( select count (1)
from t_pol
where polno = mainpolno
and contno = rpad(prop2, 20, ' ' ))
when 1 then
(select paytodate
from t_pol c
where polno = mainpolno
and contno = rpad(prop2, 20, ' ' )
And Rownum = 1)
else
(select max(paytodate)
from t_pol c
where polno = mainpolno
and payintv > 0
and contno = rpad(prop2, 20, ' ' )
and appflag = '1' )
end) paytodate ,
(select codename
from code d2
where d2.codetype = 'edorapptype'
and d2.code = Rpad(prop5, 20, ' ' )),
(SELECT EDORNAME
FROM item
WHERE APPOBJ <> 'G'
AND EDORCODE IN
(SELECT EDORTYPE
FROM pitem
WHERE EDORACCEPTNO = rpad(prop1, 20, ' '))
AND ROWNUM = 1),
nvl((SELECT SUM(getmoney)
FROM pitem
WHERE EDORACCEPTNO = rpad(prop1, 20, ' ')
and edorstate = '2' ),
0),
(Select Edorpopedom
From user
Where Usercode = Rpad(Createoperator, 20, ' ' )
And Usertype = '1'
And Userstate = '0' ),
prop7,
Createoperator,
makedate,
missionid,
submissionid,
activityid,
(select nvl(urgenttype, '1')
from app
where edoracceptno = rpad(prop1, 20)) as urgenttype,
(case
when prop5 = '2' then
(select codename
from code
where codetype = 'agentlevel'
and code =
(select rpad(agentstar, 20)
from app
where edoracceptno = rpad(prop1, 20)))
else
''
end),
(select codename
from code
where codetype = 'CustomCheckResult'
and code =
(select customcheckresult
from app
where edoracceptno = rpad(prop1, 20))),
greatest(makedate,
nvl((select max(makedate)
from doc_main
where doccode = mission.prop1),
date '1900-01-01' )) as greatdate,
greatest(to_char( makedate, 'yyyymmdd') || maketime,
(select nvl(max(to_char( makedate, 'yyyymmdd') ||
maketime),
'1900010100:00:00')
from doc_main
where doccode = prop1)) as greattime,
(case
when prop5 = '2' then
(select agentstar
from app
where edoracceptno = rpad(prop1, 20))
else
''
end) as agentstar
from mission
where 1 = 1
and activityid = '0000000007'
and processid = '0000000000'
and exists
(select 1
from app
where (Preinputflag Is Null Or Preinputflag = '0')
and edoracceptno = rpad(mission.prop1, 20, ' '))
and defaultoperator is null
and exists ( select 1
from app
where applyfortype <> '2'
and edoracceptno = rpad(prop1, 20))
and exists
(select 1
from pitem
where EdorAcceptNo = rpad(prop1, 20, ' ')
and edortype in
(select edorcode
from popedom
where AppDateModifyFlag >=
(select min(outdate)
from bmission
where activityid = '0000000003'
and bmission.prop1 =
mission.prop1) -
(select distinct edorappdate
from pitem
where edorstate = '2'
and edoracceptno =
rpad(mission.prop1, 20 , ' '))
and LowerAppDate <=
(select min(outdate)
from bmission
where activityid = '0000000003'
and bmission.prop1 =
mission.prop1) -
(select distinct edorappdate
from pitem
where edorstate = '2'
and edoracceptno =
rpad(mission.prop1, 20 , ' '))
and LimitGetMoney +
nvl((SELECT sum(getmoney)
FROM pitem
WHERE EDORACCEPTNO =
rpad(prop1, 20 , ' ')
and edorstate = '2' ),
0) >= 0
and LowerLimitGetMoney +
nvl((SELECT sum(getmoney)
FROM pitem
WHERE EDORACCEPTNO =
rpad(prop1, 20 , ' ')
and edorstate = '2' ),
0) < 0
and edorcode in ('AG',
'CT',
'PT',
'CG',
'LO',
'PG',
'LN',
'RL',
'XT')
and ApproveFlag = '1'
and Exists
(Select 1
From user
Where Usercode = 'zy1zgs'
And Usertype = '1'
And Userstate = '0'
And Edorpopedom = popedom.Edorpopedom )
union
select edorcode
from popedom
where AppDateModifyFlag >=
(select min(outdate)
from bmission
where activityid = '0000000003'
and bmission.prop1 =
mission.prop1) -
(select distinct edorappdate
from pitem
where edorstate = '2'
and edoracceptno =
rpad(mission.prop1, 20 , ' '))
and LowerAppDate <=
(select min(outdate)
from bmission
where activityid = '0000000003'
and bmission.prop1 =
mission.prop1) -
(select distinct edorappdate
from pitem
where edorstate = '2'
and edoracceptno =
rpad(mission.prop1, 20 , ' '))
and edorcode not in ( 'AG',
'CT',
'PT',
'CG',
'LO',
'PG',
'LN',
'RL',
'XT')
and ApproveFlag = '1'
and Exists
(Select 1
From user
Where Usercode = 'zy1zgs'
And Usertype = '1'
And Userstate = '0'
and Edorpopedom = popedom.Edorpopedom )))
And Exists
(Select 1
From rrela a, mrela b
Where a.Areaid = b.Areaid
And b.Comcode in
(Rpad (Substr(prop7, 1, 4), 10 ),
Rpad(Substr (prop7 , 1, 6 ), 10))
And a.Usercode = 'zy1zgs'
and not exists
(select 1
from ldcom
where comcode = rpad(prop7, 10)
and substr (comcode , 1, 6 ) in
(select trim(code)
from code
where rpad (codealias , 10) = b.Comcode
and codetype = 'RiseCom' )))
and (CreateOperator <> 'zy1zgs' or
(select edortype
from pitem
where edoracceptno = rpad(prop1, 20, ' ')
and rownum = 1) in ('AC' , 'BB', 'CD', 'PC' ))
And Exists
(Select 1
From doc_Pages
Where Exists ( Select 1
From doc_Main
Where Docid = doc_Pages.Docid
And Doccode = prop1))
And Exists
(Select 1
From Lccont
Where Contno =
(Select Contno
From pitem
Where Edoracceptno = Rpad(prop1, 20, ' ')
And Rownum = 1)
and Conttype != 2 )
order by urgenttype desc ,
greatdate,
agentstar desc nulls last ,
greattime)
where rownum <= 10
故事從最早說起，這個SQL有一條執行突然變得很慢。以前幾秒，現在幾十秒。
把生產的統計資訊匯出，放在審計庫（和生產差一個月資料）上研究了一下，問題應該是出在柱狀圖上
對比審計庫本身的 10053和將生產統計資訊匯入到審計庫後的 10053
索引方面
審計庫統計資訊
Index: IDX_pitem_EDORACCEPTNO Col#: 1
LVLS: 3 #LB: 533158 #DK: 62078976 LB/K: 1.00 DB/K: 1.00 CLUF: 46340376.00
Index: pitem_INDEX_3 Col#: 13
LVLS: 2 #LB: 235330 #DK: 12 LB/K: 19610.00 DB/K: 318001.00 CLUF: 3816015.00
生產統計資訊
Index: IDX_pitem_EDORACCEPTNO Col#: 1
LVLS: 3 #LB: 530957 #DK: 63950848 LB/K: 1.00 DB/K: 1.00 CLUF: 45405526.00
Index: pitem_INDEX_3 Col#: 13
LVLS: 3 #LB: 248069 #DK: 12 LB/K: 20672.00 DB/K: 310030.00 CLUF: 3720364.00
審計庫柱狀圖資訊
Column (#1): EDORACCEPTNO(
AvgLen: 21 NDV: 62078976 Nulls: 0 Density: 0.000005
Histogram: Freq #Bkts: 255 UncompBkts: 254 EndPtVals: 255
Column (#13): EDORSTATE(
AvgLen: 2 NDV: 12 Nulls: 0 Density: 0.000000
Histogram: Freq #Bkts: 10 UncompBkts: 20023 EndPtVals: 10
生產柱狀圖資訊
Column (#1): EDORACCEPTNO(
AvgLen: 21 NDV: 63950848 Nulls: 0 Density: 0.000005
Histogram: Freq #Bkts: 255 UncompBkts: 254 EndPtVals: 255
Column (#13): EDORSTATE(
AvgLen: 2 NDV: 12 Nulls: 0 Density: 0.000000
Histogram: Freq #Bkts: 10 UncompBkts: 20632 EndPtVals: 10
上面看到統計資訊表明看起來沒什麼區別，但是成本估算差距很大。
注意下面兩個索引的成本對比
審計庫成本估算
Access Path: index (AllEqRange)
Index: IDX_pitem_EDORACCEPTNO
resc_io: 221.00 resc_cpu: 0
ix_sel: 0.000005 ix_sel_with_filters: 0.000005
Cost: 221.00 Resp: 221.00 Degree: 1
Access Path: index (AllEqRange)
Index: pitem_INDEX_3
resc_io: 813.00 resc_cpu: 0
ix_sel: 0.000200 ix_sel_with_filters: 0.000200
Cost: 813.00 Resp: 813.00 Degree: 1
生產成本估算
Access Path: index (AllEqRange)
Index: IDX_pitem_EDORACCEPTNO
resc_io: 245.00 resc_cpu: 0
ix_sel: 0.000005 ix_sel_with_filters: 0.000005
Cost: 245.00 Resp: 245.00 Degree: 1
Access Path: index (AllEqRange)
Index: pitem_INDEX_3
resc_io: 5.00 resc_cpu: 0
ix_sel: 0.000000 ix_sel_with_filters: 0.000000
Cost: 5.00 Resp: 5.00 Degree: 1
生產的執行計劃裡所有pitem表都錯誤地選擇了pitem_INDEX_3。可以發現對 IDX_pitem_EDORACCEPTNO的估算比較真實，但是對 pitem_INDEX_3，也就是 EDORSTATE（流程節點狀態）的估算差距非常大。
oracle認為EDORSTATE=’2’ 的值非常非常少。這個 10053沒有報謂詞越界，問題應該出在柱狀圖上
檢視柱狀圖
CREATE OR REPLACE FUNCTION hexstr (p_number IN NUMBER)
RETURN VARCHAR2
AS
l_str LONG := TO_CHAR (p_number, 'fm' || RPAD ('x', 50, 'x'));
l_return VARCHAR2 (4000);
BEGIN
WHILE (l_str IS NOT NULL)
LOOP
l_return := l_return || CHR (TO_NUMBER (SUBSTR (l_str, 1, 2), 'xx'));
l_str := SUBSTR (l_str, 3);
END LOOP;
RETURN (SUBSTR (l_return, 1, 6));
END;
/
檢視這張表第 13列的柱狀圖。
這裡只能顯示每個 bucket末端的值。可以看到兩個 bucket都是 ’1’，第三個是 ’2’。最大的可能是第三個 bucket裡的大多數也是 ’1’
SQL> select BUCKET, HEXSTR(ENDPOINT) value from histgrm$ where obj# = 94494 and COL# = 13;
BUCKET VALUE
---------- ----------------
20392 0
20446 1
20447 1
20448 2
20536 3
20537 6
20579 7
20621 8
20630 `
20632 a
柱狀圖資訊和真實值差距過大的結果就是對取值返回的結果集大小估算有偏差
在兩個庫上執行下面語句
explain plan for
select * from pitem where EDORSTATE = '2';
select * from table(dbms_xplan.display(null, null, 'OUTLINE'));
昨天生產的統計資訊
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 2906569252
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 337 | 5 |
| 1 | TABLE ACCESS BY INDEX ROWID| pitem | 1 | 337 | 5 |
|* 2 | INDEX RANGE SCAN | pitem_INDEX_3 | 1 | | 4 |
----------------------------------------------------------------------------------
今天生產的統計資訊
Plan hash value: 2906569252
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10433 | 3433K| 572 |
| 1 | TABLE ACCESS BY INDEX ROWID| pitem | 10433 | 3433K| 572 |
|* 2 | INDEX RANGE SCAN | pitem_INDEX_3 | 10433 | | 39 |
----------------------------------------------------------------------------------
考慮到這個 SQL時查詢流程節點出在某一狀態的，並且採用搶佔的方式處理流程。
可以做如下分析：
1 SQL中的'0000000000' 號流程的'0000000007'號節點處於 ’2’狀態的在週末基本都已經被處理完了。
2 30號收集統計資訊時發現幾乎沒有狀態為 ’2’的這個節點。
3 星期一早上，這個表的狀態欄位發生了大量的修改，節點狀態發生了變化：
Segments by DB Blocks Changes DB/Inst: PRODB/PRODB2 Snaps: 47177-47179
-> % of Capture shows % of DB Block Changes for each top segment compared
-> with total DB Block Changes for all segments captured by the Snapshot
Tablespace Subobject Obj. DB Block % of
Owner Name Object Name Name Type Changes Capture
---------- ---------- -------------------- ---------- ----- ------------ -------
LIS LIS pitem TABLE 256,322,560 55.21
LIS LISINDX pitem_INDEX_3 INDEX 205,673,696 44.30
LIS LIS LDMAXNO TABLE 156,144 .03
LIS LIS app TABLE 135,376 .03
LIS LIS mission TABLE 132,912 .03
-------------------------------------------------------------
但是oracle採用了週末收集的統計資訊，走了 pitem_INDEX_3
4 隨著大家又開始處理流程了，因此 EDORSTATE=’2’ 的資料開始減少。掃描pitem_INDEX_3的成本逐漸提高，因此就有了現在生產上後來的預設執行計劃：
走兩個索引，做 AND-EQUAL。說明兩個索引的成正在逐漸接近。
後來決定用sql profile固定執行計劃
declare
sql_txt clob;
h sys.sqlprof_attr;
begin
h := sys.sqlprof_attr (
'BEGIN_OUTLINE_DATA',
'INDEX(@"SEL$44" "pitem"@"SEL$44" ("pitem"."EDORACCEPTNO" "pitem"."EDORNO"',
' "pitem"."POLNO" "pitem"."CONTNO" "pitem"."INSUREDNO" "pitem"."EDORTYPE"))',
'USE_NL(@"SEL$245B400D" "code"@"SEL$39")' ,
'LEADING(@"SEL$245B400D" "LDCOM"@"SEL$38" "code"@"SEL$39")' ,
'INDEX_RS_ASC(@"SEL$245B400D" "code"@"SEL$39" ("code"."CODETYPE" "code"."CODE"))',
'INDEX(@"SEL$245B400D" "LDCOM"@"SEL$38" ("LDCOM"."COMCODE"))' ,
'INDEX_RS_ASC(@"SEL$32" "bmission"@"SEL$32" ("bmission"."prop1"))' ,
'USE_HASH_AGGREGATION(@"SEL$33")' ,
'INDEX_RS_ASC(@"SEL$33" "pitem"@"SEL$33" ("pitem"."EDORACCEPTNO"))' ,
'INDEX_RS_ASC(@"SEL$34" "bmission"@"SEL$34" ("bmission"."prop1"))' ,
'USE_HASH_AGGREGATION(@"SEL$35")' ,
'INDEX_RS_ASC(@"SEL$35" "pitem"@"SEL$35" ("pitem"."EDORACCEPTNO"))' ,
'INDEX_RS_ASC(@"SEL$24" "bmission"@"SEL$24" ("bmission"."prop1"))' ,
'USE_HASH_AGGREGATION(@"SEL$25")' ,
'INDEX_RS_ASC(@"SEL$25" "pitem"@"SEL$25" ("pitem"."EDORACCEPTNO"))' ,
'INDEX_RS_ASC(@"SEL$26" "bmission"@"SEL$26" ("bmission"."prop1"))' ,
'USE_HASH_AGGREGATION(@"SEL$27")' ,
'INDEX_RS_ASC(@"SEL$27" "pitem"@"SEL$27" ("pitem"."EDORACCEPTNO"))' ,
'INDEX_RS_ASC(@"SEL$28" "pitem"@"SEL$28" ("pitem"."EDORACCEPTNO"))' ,
'INDEX_RS_ASC(@"SEL$29" "pitem"@"SEL$29" ("pitem"."EDORACCEPTNO"))' ,
'USE_NL(@"SEL$EE91C113" "popedom"@"SEL$23")' ,
'LEADING(@"SEL$EE91C113" "user"@"SEL$30" "popedom"@"SEL$23")' ,
'INDEX_RS_ASC(@"SEL$EE91C113" "popedom"@"SEL$23" ("popedom"."EDORPOPEDOM"',
' "popedom"."EDORCODE" "popedom"."MANAGECOM" "popedom"."RISKPROP"',
' "popedom"."RISKPERIOD"))' ,
'INDEX_RS_ASC(@"SEL$EE91C113" "user"@"SEL$30" ("user"."USERCODE" "user"."USERTYPE"))',
'USE_NL(@"SEL$69C7062B" "popedom"@"SEL$31")' ,
'LEADING(@"SEL$69C7062B" "user"@"SEL$36" "popedom"@"SEL$31")' ,
'INDEX_RS_ASC(@"SEL$69C7062B" "popedom"@"SEL$31" ("popedom"."EDORPOPEDOM"',
' "popedom"."EDORCODE" "popedom"."MANAGECOM" "popedom"."RISKPROP"',
' "popedom"."RISKPERIOD"))' ,
'INDEX_RS_ASC(@"SEL$69C7062B" "user"@"SEL$36" ("user"."USERCODE" "user"."USERTYPE"))',
'INDEX_RS_ASC(@"SEL$16" "app"@"SEL$16" ("app"."EDORACCEPTNO"))' ,
'INDEX_RS_ASC(@"SEL$14" "app"@"SEL$14" ("app"."EDORACCEPTNO"))' ,
'INDEX(@"SEL$9" "pitem"@"SEL$9" ("pitem"."EDORACCEPTNO" "pitem"."EDORNO" "pitem"."POLNO"',
' "pitem"."CONTNO" "pitem"."INSUREDNO" "pitem"."EDORTYPE"))',
'INDEX_RS_ASC(@"SEL$3" "D1"@"SEL$3" ("code"."CODETYPE" "code"."CODE"))' ,
'INDEX_RS_ASC(@"SEL$4" "t_pol"@"SEL$4" ("t_pol"."CONTNO"))' ,
'INDEX_RS_ASC(@"SEL$5" "C"@"SEL$5" ("t_pol"."CONTNO"))' ,
'INDEX_RS_ASC(@"SEL$6" "C"@"SEL$6" ("t_pol"."CONTNO"))' ,
'INDEX_RS_ASC(@"SEL$7" "D2"@"SEL$7" ("code"."CODETYPE" "code"."CODE"))' ,
'FULL(@"SEL$8" "item"@"SEL$8")' ,
'INDEX_RS_ASC(@"SEL$10" "pitem"@"SEL$10" ("pitem"."EDORACCEPTNO"))' ,
'INDEX_RS_ASC(@"SEL$11" "user"@"SEL$11" ("user"."USERCODE" "user"."USERTYPE"))',
'INDEX_RS_ASC(@"SEL$12" "app"@"SEL$12" ("app"."EDORACCEPTNO"))' ,
'PUSH_SUBQ(@"SEL$14")' ,
'INDEX_RS_ASC(@"SEL$13" "code"@"SEL$13" ("code"."CODETYPE" "code"."CODE"))' ,
'PUSH_SUBQ(@"SEL$16")' ,
'INDEX_RS_ASC(@"SEL$15" "code"@"SEL$15" ("code"."CODETYPE" "code"."CODE"))' ,
'INDEX_RS_ASC(@"SEL$17" "doc_MAIN"@"SEL$17" ("doc_MAIN"."DOCCODE" "doc_MAIN"."BUSSTYPE"',
' "doc_MAIN"."SUBTYPE"))' ,
'INDEX_RS_ASC(@"SEL$18" "doc_MAIN"@"SEL$18" ("doc_MAIN"."DOCCODE" "doc_MAIN"."BUSSTYPE"',
' "doc_MAIN"."SUBTYPE"))' ,
'INDEX_RS_ASC(@"SEL$19" "app"@"SEL$19" ("app"."EDORACCEPTNO"))' ,
'PUSH_SUBQ(@"SET$1")' ,
'INDEX(@"SEL$22" "pitem"@"SEL$22" ("pitem"."EDORACCEPTNO" "pitem"."EDORNO"',
' "pitem"."POLNO" "pitem"."CONTNO" "pitem"."INSUREDNO" "pitem"."EDORTYPE"))',
'PUSH_SUBQ(@"SEL$245B400D")' ,
'USE_NL(@"SEL$37" "B"@"SEL$37")' ,
'LEADING(@"SEL$37" "A"@"SEL$37" "B"@"SEL$37")' ,
'INDEX(@"SEL$37" "B"@"SEL$37" ("mrela"."COMCODE" "mrela"."AREAID"))',
'INDEX(@"SEL$37" "A"@"SEL$37" ("rrela"."USERCODE" "rrela"."AREAID"',
' "rrela"."COMCODE"))' ,
'INDEX(@"SEL$40" "pitem"@"SEL$40" ("pitem"."EDORACCEPTNO" "pitem"."EDORNO"',
' "pitem"."POLNO" "pitem"."CONTNO" "pitem"."INSUREDNO" "pitem"."EDORTYPE"))',
'PUSH_SUBQ(@"SEL$44")' ,
'INDEX_RS_ASC(@"SEL$43" "LCCONT"@"SEL$43" ("LCCONT"."CONTNO"))' ,
'USE_NL(@"SEL$700B301E" "doc_PAGES"@"SEL$41")' ,
'LEADING(@"SEL$700B301E" "doc_MAIN"@"SEL$42" "doc_PAGES"@"SEL$41")' ,
'INDEX(@"SEL$700B301E" "doc_PAGES"@"SEL$41" ("doc_PAGES"."DOCID"))' ,
'INDEX_RS_ASC(@"SEL$700B301E" "doc_MAIN"@"SEL$42" ("doc_MAIN"."DOCCODE" "doc_MAIN"."BUSSTYPE"',
' "doc_MAIN"."SUBTYPE"))' ,
'USE_NL(@"SEL$D8F4EB61" "VW_SQ_1"@"SEL$CEAA7324")' ,
'USE_NL(@"SEL$D8F4EB61" "app"@"SEL$20")' ,
'USE_NL(@"SEL$D8F4EB61" "app"@"SEL$21")' ,
'LEADING(@"SEL$D8F4EB61" "mission"@"SEL$2" "app"@"SEL$21" "app"@"SEL$20"',
' "VW_SQ_1"@"SEL$CEAA7324")' ,
'NO_ACCESS(@"SEL$D8F4EB61" "VW_SQ_1"@"SEL$CEAA7324")' ,
'INDEX_RS_ASC(@"SEL$D8F4EB61" "app"@"SEL$20" ("app"."EDORACCEPTNO"))' ,
'INDEX_RS_ASC(@"SEL$D8F4EB61" "app"@"SEL$21" ("app"."EDORACCEPTNO"))' ,
'INDEX_RS_ASC(@"SEL$D8F4EB61" "mission"@"SEL$2" ("mission"."ACTIVITYID"))' ,
'NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")' ,
'OUTLINE(@"SEL$42")' ,
'OUTLINE(@"SEL$41")' ,
'OUTLINE(@"SEL$2")' ,
'UNNEST(@"SEL$42")' ,
'OUTLINE(@"SEL$28583BE2")' ,
'OUTLINE(@"SEL$21")' ,
'OUTLINE(@"SEL$20")' ,
'OUTLINE(@"SEL$CEAA7324")' ,
'UNNEST(@"SEL$28583BE2")' ,
'UNNEST(@"SEL$21")' ,
'UNNEST(@"SEL$20")' ,
'OUTLINE(@"SEL$D8F4EB61")' ,
'OUTLINE(@"SEL$1B0ECD80")' ,
'OUTLINE(@"SEL$39")' ,
'OUTLINE(@"SEL$38")' ,
'OUTLINE(@"SEL$36")' ,
'OUTLINE(@"SEL$31")' ,
'OUTLINE(@"SEL$30")' ,
'OUTLINE(@"SEL$23")' ,
'OUTLINE_LEAF(@"SEL$1")' ,
'UNNEST(@"SEL$28583BE2")' ,
'UNNEST(@"SEL$21")' ,
'UNNEST(@"SEL$20")' ,
'OUTLINE_LEAF(@"SEL$D8F4EB61")' ,
'PUSH_PRED(@"SEL$D8F4EB61" "VW_SQ_1"@"SEL$CEAA7324" 7)' ,
'OUTLINE_LEAF(@"SEL$700B301E")' ,
'OUTLINE_LEAF(@"SEL$43")' ,
'OUTLINE_LEAF(@"SEL$44")' ,
'OUTLINE_LEAF(@"SEL$40")' ,
'OUTLINE_LEAF(@"SEL$37")' ,
'UNNEST(@"SEL$39")' ,
'OUTLINE_LEAF(@"SEL$245B400D")' ,
'OUTLINE_LEAF(@"SEL$22")' ,
'OUTLINE_LEAF(@"SET$1")' ,
'UNNEST(@"SEL$36")' ,
'OUTLINE_LEAF(@"SEL$69C7062B")' ,
'OUTLINE_LEAF(@"SEL$35")' ,
'OUTLINE_LEAF(@"SEL$34")' ,
'OUTLINE_LEAF(@"SEL$33")' ,
'OUTLINE_LEAF(@"SEL$32")' ,
'UNNEST(@"SEL$30")' ,
'OUTLINE_LEAF(@"SEL$EE91C113")' ,
'OUTLINE_LEAF(@"SEL$29")' ,
'OUTLINE_LEAF(@"SEL$28")' ,
'OUTLINE_LEAF(@"SEL$27")' ,
'OUTLINE_LEAF(@"SEL$26")' ,
'OUTLINE_LEAF(@"SEL$25")' ,
'OUTLINE_LEAF(@"SEL$24")' ,
'OUTLINE_LEAF(@"SEL$19")' ,
'OUTLINE_LEAF(@"SEL$18")' ,
'OUTLINE_LEAF(@"SEL$17")' ,
'OUTLINE_LEAF(@"SEL$15")' ,
'OUTLINE_LEAF(@"SEL$16")' ,
'OUTLINE_LEAF(@"SEL$13")' ,
'OUTLINE_LEAF(@"SEL$14")' ,
'OUTLINE_LEAF(@"SEL$12")' ,
'OUTLINE_LEAF(@"SEL$11")' ,
'OUTLINE_LEAF(@"SEL$10")' ,
'OUTLINE_LEAF(@"SEL$8")' ,
'OUTLINE_LEAF(@"SEL$9")' ,
'OUTLINE_LEAF(@"SEL$7")' ,
'OUTLINE_LEAF(@"SEL$6")' ,
'OUTLINE_LEAF(@"SEL$5")' ,
'OUTLINE_LEAF(@"SEL$4")' ,
'OUTLINE_LEAF(@"SEL$3")' ,
'ALL_ROWS',
'DB_VERSION(''11.2.0.3'')' ,
'OPTIMIZER_FEATURES_ENABLE(''11.2.0.3'')' ,
'OPT_PARAM(''_optimizer_use_feedback'' ''false'')' ,
'OPT_PARAM(''_optimizer_adaptive_cursor_sharing'' ''false'')' ,
'OPT_PARAM(''_optimizer_extended_cursor_sharing_rel'' ''none'')' ,
'OPT_PARAM(''_optimizer_extended_cursor_sharing'' ''none'')' ,
'OPT_PARAM(''_b_tree_bitmap_plans'' ''false'')' ,
'IGNORE_OPTIM_EMBEDDED_HINTS' ,
'END_OUTLINE_DATA'
);
dbms_sqltune.import_sql_profile (
sql_text => 'select * from (select prop1, prop2, (select codename from code d1 where ''1418892021000''=''1418892021000'' and d1.codetype = ''edornotype'' and d1.code = Rpad(prop3, 20, '' '') ), prop11, (case (select count(1) from t_pol where polno =mainpolno and contno = rpad(prop2,20,'' '')) when 1 then (select paytodate from t_pol c where polno = mainpolno and contno = rpad(prop2,20,'' '') And Rownum = 1) else (select max(paytodate) from t_pol c where polno = mainpolno and payintv>0 and contno = rpad(prop2,20,'' '') and appflag =''1'') end) paytodate, (select codename from code d2 where d2.codetype = ''edorapptype'' and d2.code = Rpad(prop5, 20, '' '') ), (SELECT EDORNAME FROM item WHERE APPOBJ<>''G'' AND EDORCODE IN (SELECT EDORTYPE FROM pitem WHERE EDORACCEPTNO=rpad(prop1,20,'' '')) AND ROWNUM=1), nvl((SELECT SUM(getmoney) FROM pitem WHERE EDORACCEPTNO=rpad(prop1,20,'' '') and edorstate=''2''),0), (Select Edorpopedom From user Where Usercode = Rpad(Createoperator, 20, '' '') And Usertype = ''1'' And Userstate = ''0''), prop7, Createoperator, makedate, missionid, submissionid, activityid, (select nvl(urgenttype, ''1'') from app where edoracceptno = rpad(prop1, 20)) as urgenttype, (case when prop5 = ''2'' then (select codename from code where codetype = ''agentlevel'' and code = (select rpad(agentstar, 20) from app where edoracceptno=rpad(prop1, 20))) else '''' end),(select codename from code where codetype= ''CustomCheckResult'' and code = (select customcheckresult from app where edoracceptno = rpad(prop1, 20))), greatest(makedate, nvl((select max(makedate) from doc_main where doccode = mission.prop1), date ''1900-01-01'')) as greatdate, greatest(to_char(makedate, ''yyyymmdd'') || maketime, (select nvl(max(to_char(makedate, ''yyyymmdd'') || maketime), ''1900010100:00:00'') from doc_main where doccode = prop1)) as greattime,(case when prop5 = ''2'' then (select agentstar from app where edoracceptno = rpad(prop1, 20)) else '''' end) as agentstar from mission where 1=1 and activityid = ''0000000007'' and processid = ''0000000000'' and exists (select 1 from app where (Preinputflag Is Null Or Preinputflag=''0'') and edoracceptno=rpad(mission.prop1,20,'' '')) and defaultoperator is null and exists (select 1 from app where applyfortype <> ''2'' and edoracceptno = rpad(prop1, 20)) and exists (select 1 from pitem where EdorAcceptNo = rpad(prop1, 20, '' '') and edortype in (select edorcode from popedom where AppDateModifyFlag >= (select min(outdate) from bmission where activityid = ''0000000003'' and bmission.prop1 = mission.prop1) - (select distinct edorappdate from pitem where edorstate = ''2'' and edoracceptno = rpad(mission.prop1, 20, '' '')) and LowerAppDate <= (select min(outdate) from bmission where activityid = ''0000000003'' and bmission.prop1 = mission.prop1) - (select distinct edorappdate from pitem where edorstate = ''2'' and edoracceptno = rpad(mission.prop1, 20, '' '')) and LimitGetMoney + nvl((SELECT sum(getmoney) FROM pitem WHERE EDORACCEPTNO = rpad(prop1, 20, '' '') and edorstate = ''2''), 0) >= 0 and LowerLimitGetMoney + nvl((SELECT sum(getmoney) FROM pitem WHERE EDORACCEPTNO = rpad(prop1, 20, '' '') and edorstate = ''2''), 0) < 0 and edorcode in (''AG'', ''CT'', ''PT'', ''CG'', ''LO'', ''PG'', ''LN'', ''RL'', ''XT'') and ApproveFlag = ''1'' and Exists (Select 1 From user Where Usercode = ''zy1zgs'' And Usertype = ''1'' And Userstate = ''0'' And Edorpopedom = popedom.Edorpopedom) union select edorcode from popedom where AppDateModifyFlag >= (select min(outdate) from bmission where activityid = ''0000000003'' and bmission.prop1 = mission.prop1) - (select distinct edorappdate from pitem where edorstate = ''2'' and edoracceptno = rpad(mission.prop1, 20, '' '')) and LowerAppDate <= (select min(outdate) from bmission where activityid = ''0000000003'' and bmission.prop1 = mission.prop1) - (select distinct edorappdate from pitem where edorstate = ''2'' and edoracceptno = rpad(mission.prop1, 20, '' '')) and edorcode not in (''AG'', ''CT'', ''PT'', ''CG'', ''LO'', ''PG'', ''LN'', ''RL'', ''XT'') and ApproveFlag = ''1'' and Exists (Select 1 From user Where Usercode = ''zy1zgs'' And Usertype = ''1'' And Userstate = ''0'' and Edorpopedom = popedom.Edorpopedom))) And Exists (Select 1 From rrela a, mrela b Where a.Areaid = b.Areaid And b.Comcode in (Rpad(Substr(prop7, 1, 4), 10), Rpad(Substr(prop7, 1, 6), 10)) And a.Usercode =''zy1zgs'' and not exists (select 1 from ldcom where comcode = rpad(prop7, 10) and substr(comcode, 1, 6) in (select trim(code) from code where rpad(codealias,10) = b.Comcode and codetype = ''RiseCom''))) and (CreateOperator <> ''zy1zgs'' or (select edortype from pitem where edoracceptno = rpad(prop1, 20, '' '') and rownum =1 ) in (''AC'', ''BB'', ''CD'',''PC'')) And Exists (Select 1 From doc_Pages Where Exists (Select 1 From doc_Main Where Docid = doc_Pages.Docid And Doccode = prop1)) And Exists (Select 1 From Lccont Where Contno = (Select Contno From pitem Where Edoracceptno = Rpad(prop1, 20, '' '') And Rownum = 1) and Conttype != 2) order by urgenttype desc, greatdate, agentstar desc nulls last, greattime) where rownum<=10',
profile => h,
name => '59zyu0jtuz0v7',
description => '59zyu0jtuz0v7',
category => 'default',
validate => true,
replace => true,
force_match => true );
end;
/
正確的執行計劃如下
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5880 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID | code | 1 | 59 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_code | 1 | | 1 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 63 | | |
|* 4 | TABLE ACCESS BY INDEX ROWID | t_pol | 1 | 63 | 5 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | t_pol_INDEX_7 | 2 | | 4 (0)| 00:00:01 |
|* 6 | COUNT STOPKEY | | | | | |
|* 7 | TABLE ACCESS BY INDEX ROWID | t_pol | 1 | 71 | 5 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | t_pol_INDEX_7 | 2 | | 4 (0)| 00:00:01 |
| 9 | SORT AGGREGATE | | 1 | 76 | | |
|* 10 | TABLE ACCESS BY INDEX ROWID | t_pol | 1 | 76 | 5 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | t_pol_INDEX_7 | 2 | | 4 (0)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID | code | 1 | 59 | 2 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | PK_code | 1 | | 1 (0)| 00:00:01 |
|* 14 | COUNT STOPKEY | | | | | |
|* 15 | FILTER | | | | | |
|* 16 | TABLE ACCESS FULL | item | 83 | 1660 | 3 (0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | PK_pitem | 1 | 24 | 4 (0)| 00:00:01 |
| 18 | SORT AGGREGATE | | 1 | 27 | | |
|* 19 | TABLE ACCESS BY INDEX ROWID | pitem | 1 | 27 | 5 (0)| 00:00:01 |
|* 20 | INDEX RANGE SCAN | PK_pitem | 1 | | 4 (0)| 00:00:01 |
|* 21 | TABLE ACCESS BY INDEX ROWID | user | 1 | 28 | 2 (0)| 00:00:01 |
|* 22 | INDEX UNIQUE SCAN | P_KEY_2 | 1 | | 1 (0)| 00:00:01 |
| 23 | TABLE ACCESS BY INDEX ROWID | app | 1 | 23 | 4 (0)| 00:00:01 |
|* 24 | INDEX UNIQUE SCAN | PK_app | 1 | | 3 (0)| 00:00:01 |
| 25 | TABLE ACCESS BY INDEX ROWID | code | 1 | 59 | 2 (0)| 00:00:01 |
|* 26 | INDEX UNIQUE SCAN | PK_code | 1 | | 1 (0)| 00:00:01 |
| 27 | TABLE ACCESS BY INDEX ROWID | app | 1 | 23 | 4 (0)| 00:00:01 |
|* 28 | INDEX UNIQUE SCAN | PK_app | 1 | | 3 (0)| 00:00:01 |
| 29 | TABLE ACCESS BY INDEX ROWID | code | 1 | 59 | 2 (0)| 00:00:01 |
|* 30 | INDEX UNIQUE SCAN | PK_code | 1 | | 1 (0)| 00:00:01 |
| 31 | TABLE ACCESS BY INDEX ROWID | app | 1 | 23 | 4 (0)| 00:00:01 |
|* 32 | INDEX UNIQUE SCAN | PK_app | 1 | | 3 (0)| 00:00:01 |
| 33 | SORT AGGREGATE | | 1 | 23 | | |
| 34 | TABLE ACCESS BY INDEX ROWID | doc_MAIN | 2 | 46 | 6 (0)| 00:00:01 |
|* 35 | INDEX RANGE SCAN | IDX_doc_MAIN_DOCCD | 2 | | 4 (0)| 00:00:01 |
| 36 | SORT AGGREGATE | | 1 | 32 | | |
| 37 | TABLE ACCESS BY INDEX ROWID | doc_MAIN | 2 | 64 | 6 (0)| 00:00:01 |
|* 38 | INDEX RANGE SCAN | IDX_doc_MAIN_DOCCD | 2 | | 4 (0)| 00:00:01 |
| 39 | TABLE ACCESS BY INDEX ROWID | app | 1 | 23 | 4 (0)| 00:00:01 |
|* 40 | INDEX UNIQUE SCAN | PK_app | 1 | | 3 (0)| 00:00:01 |
|* 41 | COUNT STOPKEY | | | | | |
| 42 | VIEW | | 1 | 589 | 5880 (1)| 00:01:11 |
|* 43 | SORT ORDER BY STOPKEY | | 1 | 178 | 5880 (1)| 00:01:11 |
|* 44 | FILTER | | | | | |
| 45 | NESTED LOOPS SEMI | | 1 | 178 | 5809 (1)| 00:01:10 |
| 46 | NESTED LOOPS SEMI | | 1 | 176 | 5800 (1)| 00:01:10 |
| 47 | NESTED LOOPS SEMI | | 1 | 153 | 5797 (1)| 00:01:10 |
|* 48 | TABLE ACCESS BY INDEX ROWID | mission | 1265 | 160K| 1999 (1)| 00:00:24 |
|* 49 | INDEX RANGE SCAN | IDX_mission_ACTID | 3006 | | 17 (0)| 00:00:01 |
|* 50 | TABLE ACCESS BY INDEX ROWID | app | 1 | 23 | 3 (0)| 00:00:01 |
|* 51 | INDEX UNIQUE SCAN | PK_app | 1 | | 2 (0)| 00:00:01 |
|* 52 | TABLE ACCESS BY INDEX ROWID | app | 1 | 23 | 3 (0)| 00:00:01 |
|* 53 | INDEX UNIQUE SCAN | PK_app | 1 | | 2 (0)| 00:00:01 |
| 54 | VIEW PUSHED PREDICATE | VW_SQ_1 | 1 | 2 | 9 (0)| 00:00:01 |
| 55 | NESTED LOOPS | | 1 | 29 | 9 (0)| 00:00:01 |
| 56 | TABLE ACCESS BY INDEX ROWID | doc_MAIN | 1 | 22 | 6 (0)| 00:00:01 |
|* 57 | INDEX RANGE SCAN | IDX_doc_MAIN_DOCCD | 2 | | 4 (0)| 00:00:01 |
|* 58 | INDEX RANGE SCAN | IDX_doc_PAGdoc | 1 | 7 | 3 (0)| 00:00:01 |
|* 59 | INDEX RANGE SCAN | PK_pitem | 1 | 24 | 4 (0)| 00:00:01 |
| 60 | SORT UNIQUE | | 2 | 90 | 52 (43)| 00:00:01 |
| 61 | UNION-ALL | | | | | |
|* 62 | FILTER | | | | | |
|* 63 | FILTER | | | | | |
| 64 | NESTED LOOPS | | 1 | 49 | 4 (0)| 00:00:01 |
|* 65 | TABLE ACCESS BY INDEX ROWID| user | 1 | 28 | 2 (0)| 00:00:01 |
|* 66 | INDEX UNIQUE SCAN | P_KEY_2 | 1 | | 1 (0)| 00:00:01 |
|* 67 | TABLE ACCESS BY INDEX ROWID| popedom | 1 | 21 | 2 (0)| 00:00:01 |
|* 68 | INDEX RANGE SCAN | P_KEY_1 | 1 | | 1 (0)| 00:00:01 |
| 69 | SORT AGGREGATE | | 1 | 27 | | |
|* 70 | TABLE ACCESS BY INDEX ROWID | pitem | 1 | 27 | 5 (0)| 00:00:01 |
|* 71 | INDEX RANGE SCAN | IDX_pitem_EDORACCEPTNO | 1 | | 4 (0)| 00:00:01 |
| 72 | SORT AGGREGATE | | 1 | 27 | | |
|* 73 | TABLE ACCESS BY INDEX ROWID | pitem | 1 | 27 | 5 (0)| 00:00:01 |
|* 74 | INDEX RANGE SCAN | IDX_pitem_EDORACCEPTNO | 1 | | 4 (0)| 00:00:01 |
| 75 | SORT AGGREGATE | | 1 | 36 | | |
|* 76 | TABLE ACCESS BY INDEX ROWID | bmission | 1 | 36 | 8 (0)| 00:00:01 |
|* 77 | INDEX RANGE SCAN | IDX_bmission_MP1 | 6 | | 4 (0)| 00:00:01 |
| 78 | HASH UNIQUE | | 1 | 31 | 6 (17)| 00:00:01 |
|* 79 | TABLE ACCESS BY INDEX ROWID | pitem | 1 | 31 | 5 (0)| 00:00:01 |
|* 80 | INDEX RANGE SCAN | IDX_pitem_EDORACCEPTNO | 1 | | 4 (0)| 00:00:01 |
| 81 | SORT AGGREGATE | | 1 | 36 | | |
|* 82 | TABLE ACCESS BY INDEX ROWID | bmission | 1 | 36 | 8 (0)| 00:00:01 |
|* 83 | INDEX RANGE SCAN | IDX_bmission_MP1 | 6 | | 4 (0)| 00:00:01 |
| 84 | HASH UNIQUE | | 1 | 31 | 6 (17)| 00:00:01 |
|* 85 | TABLE ACCESS BY INDEX ROWID | pitem | 1 | 31 | 5 (0)| 00:00:01 |
|* 86 | INDEX RANGE SCAN | IDX_pitem_EDORACCEPTNO | 1 | | 4 (0)| 00:00:01 |
|* 87 | FILTER | | | | | |
|* 88 | FILTER | | | | | |
| 89 | NESTED LOOPS | | 1 | 41 | 4 (0)| 00:00:01 |
|* 90 | TABLE ACCESS BY INDEX ROWID| user | 1 | 28 | 2 (0)| 00:00:01 |
|* 91 | INDEX UNIQUE SCAN | P_KEY_2 | 1 | | 1 (0)| 00:00:01 |
|* 92 | TABLE ACCESS BY INDEX ROWID| popedom | 1 | 13 | 2 (0)| 00:00:01 |
|* 93 | INDEX RANGE SCAN | P_KEY_1 | 1 | | 1 (0)| 00:00:01 |
| 94 | SORT AGGREGATE | | 1 | 36 | | |
|* 95 | TABLE ACCESS BY INDEX ROWID | bmission | 1 | 36 | 8 (0)| 00:00:01 |
|* 96 | INDEX RANGE SCAN | IDX_bmission_MP1 | 6 | | 4 (0)| 00:00:01 |
| 97 | HASH UNIQUE | | 1 | 31 | 6 (17)| 00:00:01 |
|* 98 | TABLE ACCESS BY INDEX ROWID | pitem | 1 | 31 | 5 (0)| 00:00:01 |
|* 99 | INDEX RANGE SCAN | IDX_pitem_EDORACCEPTNO | 1 | | 4 (0)| 00:00:01 |
| 100 | SORT AGGREGATE | | 1 | 36 | | |
|*101 | TABLE ACCESS BY INDEX ROWID | bmission | 1 | 36 | 8 (0)| 00:00:01 |
|*102 | INDEX RANGE SCAN | IDX_bmission_MP1 | 6 | | 4 (0)| 00:00:01 |
| 103 | HASH UNIQUE | | 1 | 31 | 6 (17)| 00:00:01 |
|*104 | TABLE ACCESS BY INDEX ROWID | pitem | 1 | 31 | 5 (0)| 00:00:01 |
|*105 | INDEX RANGE SCAN | IDX_pitem_EDORACCEPTNO | 1 | | 4 (0)| 00:00:01 |
| 106 | NESTED LOOPS | | 1 | 54 | 3 (0)| 00:00:01 |
|*107 | INDEX RANGE SCAN | rrela_KEY_1 | 2 | 64 | 1 (0)| 00:00:01 |
| 108 | INLIST ITERATOR | | | | | |
|*109 | INDEX UNIQUE SCAN | mrela_KEY_1 | 1 | 22 | 1 (0)| 00:00:01 |
| 110 | NESTED LOOPS | | 1 | 58 | 3 (0)| 00:00:01 |
|*111 | INDEX UNIQUE SCAN | PK_LDCOM | 1 | 11 | 1 (0)| 00:00:01 |
|*112 | TABLE ACCESS BY INDEX ROWID | code | 1 | 47 | 2 (0)| 00:00:01 |
|*113 | INDEX RANGE SCAN | PK_code | 1 | | 1 (0)| 00:00:01 |
|*114 | TABLE ACCESS BY INDEX ROWID | LCCONT | 1 | 23 | 4 (0)| 00:00:01 |
|*115 | INDEX UNIQUE SCAN | PK_LCCONT | 1 | | 3 (0)| 00:00:01 |
|*116 | COUNT STOPKEY | | | | | |
|*117 | INDEX RANGE SCAN | PK_pitem | 1 | 42 | 4 (0)| 00:00:01 |
|*118 | COUNT STOPKEY | | | | | |
|*119 | INDEX RANGE SCAN | PK_pitem | 1 | 24 | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------
前面都是鋪墊，主角來了
後來有一天，需要在登陸觸發器裡為部分客戶機的session設定cursor_sharing=force。
結果這條SQL就有了兩個版本，一個是之前的非繫結變了的SQL，一個是oracle改寫的強制繫結變數版本。
被oracle改寫為繫結變數版本的SQL統統執行不出來了。
執行計劃如下
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 2292K(100)| |
|* 1 | FILTER | | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | code | 1 | 59 | | 2 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | PK_code | 1 | | | 1 (0)| 00:00:01 |
| 4 | SORT AGGREGATE | | 1 | 63 | | | |
|* 5 | TABLE ACCESS BY INDEX ROWID | t_pol | 1 | 63 | | 5 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | t_pol_INDEX_7 | 2 | | | 4 (0)| 00:00:01 |
|* 7 | COUNT STOPKEY | | | | | | |
|* 8 | TABLE ACCESS BY INDEX ROWID | t_pol | 1 | 71 | | 5 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | t_pol_INDEX_7 | 2 | | | 4 (0)| 00:00:01 |
| 10 | SORT AGGREGATE | | 1 | 76 | | | |
|* 11 | TABLE ACCESS BY INDEX ROWID | t_pol | 1 | 76 | | 5 (0)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | t_pol_INDEX_7 | 2 | | | 4 (0)| 00:00:01 |
| 13 | TABLE ACCESS BY INDEX ROWID | code | 1 | 59 | | 2 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | PK_code | 1 | | | 1 (0)| 00:00:01 |
|* 15 | COUNT STOPKEY | | | | | | |
|* 16 | FILTER | | | | | | |
|* 17 | TABLE ACCESS FULL | item | 83 | 1660 | | 3 (0)| 00:00:01 |
|* 18 | INDEX RANGE SCAN | PK_pitem | 1 | 24 | | 4 (0)| 00:00:01 |
| 19 | SORT AGGREGATE | | 1 | 27 | | | |
|* 20 | TABLE ACCESS BY INDEX ROWID | pitem | 1 | 27 | | 5 (0)| 00:00:01 |
|* 21 | INDEX RANGE SCAN | PK_pitem | 1 | | | 4 (0)| 00:00:01 |
|* 22 | TABLE ACCESS BY INDEX ROWID | user | 1 | 28 | | 2 (0)| 00:00:01 |
|* 23 | INDEX UNIQUE SCAN | P_KEY_2 | 1 | | | 1 (0)| 00:00:01 |
| 24 | TABLE ACCESS BY INDEX ROWID | app | 1 | 23 | | 4 (0)| 00:00:01 |
|* 25 | INDEX UNIQUE SCAN | PK_app | 1 | | | 3 (0)| 00:00:01 |
| 26 | TABLE ACCESS BY INDEX ROWID | code | 1 | 59 | | 2 (0)| 00:00:01 |
|* 27 | INDEX UNIQUE SCAN | PK_code | 1 | | | 1 (0)| 00:00:01 |
| 28 | TABLE ACCESS BY INDEX ROWID | app | 1 | 23 | | 4 (0)| 00:00:01 |
|* 29 | INDEX UNIQUE SCAN | PK_app | 1 | | | 3 (0)| 00:00:01 |
| 30 | TABLE ACCESS BY INDEX ROWID | code | 1 | 59 | | 2 (0)| 00:00:01 |
|* 31 | INDEX UNIQUE SCAN | PK_code | 1 | | | 1 (0)| 00:00:01 |
| 32 | TABLE ACCESS BY INDEX ROWID | app | 1 | 23 | | 4 (0)| 00:00:01 |
|* 33 | INDEX UNIQUE SCAN | PK_app | 1 | | | 3 (0)| 00:00:01 |
| 34 | SORT AGGREGATE | | 1 | 23 | | | |
| 35 | TABLE ACCESS BY INDEX ROWID | doc_MAIN | 2 | 46 | | 6 (0)| 00:00:01 |
|* 36 | INDEX RANGE SCAN | IDX_doc_MAIN_DOCCD | 2 | | | 4 (0)| 00:00:01 |
| 37 | SORT AGGREGATE | | 1 | 32 | | | |
| 38 | TABLE ACCESS BY INDEX ROWID | doc_MAIN | 2 | 64 | | 6 (0)| 00:00:01 |
|* 39 | INDEX RANGE SCAN | IDX_doc_MAIN_DOCCD | 2 | | | 4 (0)| 00:00:01 |
| 40 | TABLE ACCESS BY INDEX ROWID | app | 1 | 23 | | 4 (0)| 00:00:01 |
|* 41 | INDEX UNIQUE SCAN | PK_app | 1 | | | 3 (0)| 00:00:01 |
|* 42 | COUNT STOPKEY | | | | | | |
| 43 | VIEW | | 1 | 638 | | 2292K (1)| 07:38:26 |
|* 44 | SORT ORDER BY STOPKEY | | 1 | 191 | | 2292K (1)| 07:38:26 |
|* 45 | FILTER | | | | | | |
| 46 | NESTED LOOPS SEMI | | 1 | 191 | | 2292K (1)| 07:38:25 |
| 47 | NESTED LOOPS SEMI | | 1 | 176 | | 5070 (1)| 00:01:01 |
| 48 | NESTED LOOPS SEMI | | 1 | 153 | | 5067 (1)| 00:01:01 |
|* 49 | TABLE ACCESS BY INDEX ROWID | mission | 1022 | 129K| | 1999 (1)| 00:00:24 |
|* 50 | INDEX RANGE SCAN | IDX_mission_ACTID | 3006 | | | 17 (0)| 00:00:01 |
|* 51 | TABLE ACCESS BY INDEX ROWID | app | 1 | 23 | | 3 (0)| 00:00:01 |
|* 52 | INDEX UNIQUE SCAN | PK_app | 1 | | | 2 (0)| 00:00:01 |
|* 53 | TABLE ACCESS BY INDEX ROWID | app | 1 | 23 | | 3 (0)| 00:00:01 |
|* 54 | INDEX UNIQUE SCAN | PK_app | 1 | | | 2 (0)| 00:00:01 |
|* 55 | VIEW | VW_SQ_1 | 378M| 5411M| | 2286K (1)| 07:37:24 |
|* 56 | FILTER | | | | | | |
|* 57 | HASH JOIN | | 460M| 12G| 5377M| 2286K (1)| 07:37:24 |
| 58 | TABLE ACCESS FULL | doc_MAIN | 165M| 3479M| | 827K (1)| 02:45:33 |
| 59 | INDEX FAST FULL SCAN | IDX_doc_PAGdoc | 474M| 3167M| | 757K (1)| 02:31:32 |
|* 60 | INDEX RANGE SCAN | PK_pitem | 1 | 24 | | 4 (0)| 00:00:01 |
| 61 | SORT UNIQUE | | 2 | 90 | | 52 (43)| 00:00:01 |
| 62 | UNION-ALL | | | | | | |
|* 63 | FILTER | | | | | | |
|* 64 | FILTER | | | | | | |
| 65 | NESTED LOOPS | | 1 | 49 | | 4 (0)| 00:00:01 |
|* 66 | TABLE ACCESS BY INDEX ROWID| user | 1 | 28 | | 2 (0)| 00:00:01 |
|* 67 | INDEX UNIQUE SCAN | P_KEY_2 | 1 | | | 1 (0)| 00:00:01 |
|* 68 | TABLE ACCESS BY INDEX ROWID| popedom | 1 | 21 | | 2 (0)| 00:00:01 |
|* 69 | INDEX RANGE SCAN | P_KEY_1 | 1 | | | 1 (0)| 00:00:01 |
| 70 | SORT AGGREGATE | | 1 | 27 | | | |
|* 71 | TABLE ACCESS BY INDEX ROWID | pitem | 1 | 27 | | 5 (0)| 00:00:01 |
|* 72 | INDEX RANGE SCAN | IDX_pitem_EDORACCEPTNO | 1 | | | 4 (0)| 00:00:01 |
| 73 | SORT AGGREGATE | | 1 | 27 | | | |
|* 74 | TABLE ACCESS BY INDEX ROWID | pitem | 1 | 27 | | 5 (0)| 00:00:01 |
|* 75 | INDEX RANGE SCAN | IDX_pitem_EDORACCEPTNO | 1 | | | 4 (0)| 00:00:01 |
| 76 | SORT AGGREGATE | | 1 | 36 | | | |
|* 77 | TABLE ACCESS BY INDEX ROWID | bmission | 1 | 36 | | 8 (0)| 00:00:01 |
|* 78 | INDEX RANGE SCAN | IDX_bmission_MP1 | 6 | | | 4 (0)| 00:00:01 |
| 79 | HASH UNIQUE | | 1 | 31 | | 6 (17)| 00:00:01 |
|* 80 | TABLE ACCESS BY INDEX ROWID | pitem | 1 | 31 | | 5 (0)| 00:00:01 |
|* 81 | INDEX RANGE SCAN | IDX_pitem_EDORACCEPTNO | 1 | | | 4 (0)| 00:00:01 |
| 82 | SORT AGGREGATE | | 1 | 36 | | | |
|* 83 | TABLE ACCESS BY INDEX ROWID | bmission | 1 | 36 | | 8 (0)| 00:00:01 |
|* 84 | INDEX RANGE SCAN | IDX_bmission_MP1 | 6 | | | 4 (0)| 00:00:01 |
| 85 | HASH UNIQUE | | 1 | 31 | | 6 (17)| 00:00:01 |
|* 86 | TABLE ACCESS BY INDEX ROWID | pitem | 1 | 31 | | 5 (0)| 00:00:01 |
|* 87 | INDEX RANGE SCAN | IDX_pitem_EDORACCEPTNO | 1 | | | 4 (0)| 00:00:01 |
|* 88 | FILTER | | | | | | |
|* 89 | FILTER | | | | | | |
| 90 | NESTED LOOPS | | 1 | 41 | | 4 (0)| 00:00:01 |
|* 91 | TABLE ACCESS BY INDEX ROWID| user | 1 | 28 | | 2 (0)| 00:00:01 |
|* 92 | INDEX UNIQUE SCAN | P_KEY_2 | 1 | | | 1 (0)| 00:00:01 |
|* 93 | TABLE ACCESS BY INDEX ROWID| popedom | 1 | 13 | | 2 (0)| 00:00:01 |
|* 94 | INDEX RANGE SCAN | P_KEY_1 | 1 | | | 1 (0)| 00:00:01 |
| 95 | SORT AGGREGATE | | 1 | 36 | | | |
|* 96 | TABLE ACCESS BY INDEX ROWID | bmission | 1 | 36 | | 8 (0)| 00:00:01 |
|* 97 | INDEX RANGE SCAN | IDX_bmission_MP1 | 6 | | | 4 (0)| 00:00:01 |
| 98 | HASH UNIQUE | | 1 | 31 | | 6 (17)| 00:00:01 |
|* 99 | TABLE ACCESS BY INDEX ROWID | pitem | 1 | 31 | | 5 (0)| 00:00:01 |
|*100 | INDEX RANGE SCAN | IDX_pitem_EDORACCEPTNO | 1 | | | 4 (0)| 00:00:01 |
| 101 | SORT AGGREGATE | | 1 | 36 | | | |
|*102 | TABLE ACCESS BY INDEX ROWID | bmission | 1 | 36 | | 8 (0)| 00:00:01 |
|*103 | INDEX RANGE SCAN | IDX_bmission_MP1 | 6 | | | 4 (0)| 00:00:01 |
| 104 | HASH UNIQUE | | 1 | 31 | | 6 (17)| 00:00:01 |
|*105 | TABLE ACCESS BY INDEX ROWID | pitem | 1 | 31 | | 5 (0)| 00:00:01 |
|*106 | INDEX RANGE SCAN | IDX_pitem_EDORACCEPTNO | 1 | | | 4 (0)| 00:00:01 |
| 107 | NESTED LOOPS | | 1 | 54 | | 3 (0)| 00:00:01 |
|*108 | INDEX RANGE SCAN | rrela_KEY_1 | 2 | 64 | | 1 (0)| 00:00:01 |
| 109 | INLIST ITERATOR | | | | | | |
|*110 | INDEX UNIQUE SCAN | mrela_KEY_1 | 1 | 22 | | 1 (0)| 00:00:01 |
| 111 | NESTED LOOPS | | 1 | 58 | | 3 (0)| 00:00:01 |
|*112 | INDEX UNIQUE SCAN | PK_LDCOM | 1 | 11 | | 1 (0)| 00:00:01 |
|*113 | TABLE ACCESS BY INDEX ROWID | code | 1 | 47 | | 2 (0)| 00:00:01 |
|*114 | INDEX RANGE SCAN | PK_code | 1 | | | 1 (0)| 00:00:01 |
|*115 | TABLE ACCESS BY INDEX ROWID | LCCONT | 1 | 23 | | 4 (0)| 00:00:01 |
|*116 | INDEX UNIQUE SCAN | PK_LCCONT | 1 | | | 3 (0)| 00:00:01 |
|*117 | COUNT STOPKEY | | | | | | |
|*118 | INDEX RANGE SCAN | PK_pitem | 1 | 42 | | 4 (0)| 00:00:01 |
|*119 | COUNT STOPKEY | | | | | | |
|*120 | INDEX RANGE SCAN | PK_pitem | 1 | 24 | | 4 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------------
......
......
......
Note
-----
- SQL profile 59zyu0jtuz0v7 used for this statement
看來還是用了sql profile的，但問題是儲存照片影像資訊的兩張大表走的是全表掃描和索引FFS，而且是在迴圈裡做這件事。
不但跑不出來，而且大大消耗臨時表空間，致使資料庫一些SQL直接報臨時表空間不足。
執行計劃主要區別
正常的執行計劃（cursor_sharing=exact）
| 54 | VIEW PUSHED PREDICATE | VW_SQ_1 | 1 | 2 | 9 (0)| 00:00:01 |
| 55 | NESTED LOOPS | | 1 | 29 | 9 (0)| 00:00:01 |
| 56 | TABLE ACCESS BY INDEX ROWID | doc_MAIN | 1 | 22 | 6 (0)| 00:00:01 |
|* 57 | INDEX RANGE SCAN | IDX_doc_MAIN_DOCCD | 2 | | 4 (0)| 00:00:01 |
|* 58 | INDEX RANGE SCAN | IDX_doc_PAGdoc | 1 | 7 | 3 (0)| 00:00:01 |
異常的執行計劃（cursor_sharing=force）
|* 55 | VIEW | VW_SQ_1 | 378M| 5411M| | 2286K (1)| 07:37:24 |
|* 56 | FILTER | | | | | | |
|* 57 | HASH JOIN | | 460M| 12G| 5377M| 2286K (1)| 07:37:24 |
| 58 | TABLE ACCESS FULL | doc_MAIN | 165M| 3479M| | 827K (1)| 02:45:33 |
| 59 | INDEX FAST FULL SCAN | IDX_doc_PAGdoc | 474M| 3167M| | 757K (1)| 02:31:32 |
很奇怪為什麼profile裡的hint被忽略了。
總之就是為非繫結變數SQL建立的sql profile，對oracle自己改寫的SQL支援有問題。
於是plan A來了
建立一個針對oracle強制繫結變數改寫的SQL的sql profile，設定force_match為false。這樣兩個SQL兩個sql profile不就好了麼。
結果，強制繫結變數的SQL按新的sql profile執行，非常爽。
但是由於兩種SQL的force_matching_signature一樣，因此當建立第二個sql profile的時候，之前那個force_match為true的sql profile消失了。
既然一個force_matching_signature只能有一個sql profile，而且強制繫結變數的SQL已經有一個理想的執行計劃在共享池了，乾脆把它做成sql baseline把。
於是有了plan B：
1 將強制繫結變數的SQL的理想執行計劃做成sql baseline
2 重新建立早先給非繫結變數版本用的sql profile
結果，現實太殘酷了，sql profile的優先順序高於sql baseline。強制繫結變數的SQL還是走了sql profile，然後跑不出結果了。問題依舊。
於是痛定思痛，仔細對比outline發現區別
正常執行計劃的outline
PUSH_PRED(@"SEL$D8F4EB61" "VW_SQ_1"@"SEL$CEAA7324" 7)
INDEX_RS_ASC(@"SEL$700B301E" "doc_MAIN"@"SEL$42" ("doc_MAIN"."DOCCODE" "doc_MAIN"."BUSSTYPE" "doc_MAIN"."SUBTYPE"))
INDEX(@"SEL$700B301E" "doc_PAGES"@"SEL$41" ("doc_PAGES"."DOCID"))
LEADING(@"SEL$700B301E" "doc_MAIN"@"SEL$42" "doc_PAGES"@"SEL$41")
USE_NL(@"SEL$700B301E" "doc_PAGES"@"SEL$41")
異常執行計劃的outline
FULL(@"SEL$1B0ECD80" "doc_MAIN"@"SEL$42")
INDEX_FFS(@"SEL$1B0ECD80" "doc_PAGES"@"SEL$41" ("doc_PAGES"."DOCID"))
LEADING(@"SEL$1B0ECD80" "doc_MAIN"@"SEL$42" "doc_PAGES"@"SEL$41")
USE_HASH(@"SEL$1B0ECD80" "doc_PAGES"@"SEL$41")
原來hint裡程式碼塊的別名在設定cursor_sharing=force後發生了變化。從700B301E變成1B0ECD80了，最重要的還是異常執行計劃裡沒有謂詞推入PUSH_PRED。
經過在審計庫（與生產庫只差一個月資料）反覆測試，就算用700B301E和1B0ECD80兩個別名寫兩段正確的OUTLINE放到sql profile裡，還是不行，根本不起作用。
最後的解決方案是直接讓這個子查詢no_unnest，倒是把問題解決了。。。
順便把有時候走and-equal的那個索引也給禁了，保證它只走一個最好的索引，並且禁用了索引轉點陣圖。
最終在sql profile里加上這段，繫結變數和非繫結變數的兩個SQL終於可以適用一個sql profile了。
'OPT_PARAM(''_b_tree_bitmap_plans'' ''false'')',
'no_unnest(@sel$41)',
'no_index(@"SEL$8" "pitem"@"SEL$8" pitem_INDEX_3)',
'no_index(@"SEL$9" "pitem"@"SEL$9" pitem_INDEX_3)',
'no_index(@"SEL$25" "pitem"@"SEL$25" pitem_INDEX_3)',
'no_index(@"SEL$27" "pitem"@"SEL$27" pitem_INDEX_3)',
'no_index(@"SEL$28" "pitem"@"SEL$28" pitem_INDEX_3)',
'no_index(@"SEL$29" "pitem"@"SEL$29" pitem_INDEX_3)',
'no_index(@"SEL$33" "pitem"@"SEL$33" pitem_INDEX_3)',
'no_index(@"SEL$35" "pitem"@"SEL$35" pitem_INDEX_3)',
'no_index(@"SEL$44" "pitem"@"SEL$44" pitem_INDEX_3)',
'no_index(@"SEL$40" "pitem"@"SEL$40" pitem_INDEX_3)',
'no_index(@"SEL$22" "pitem"@"SEL$22" pitem_INDEX_3)',
'no_index(@"SEL$10" "pitem"@"SEL$10" pitem_INDEX_3)',
（部分表名欄位名做了替換）
select *
from (select prop1 ,
prop2,
(select codename
from code d1
where '1418892021000' = '1418892021000'
and d1.codetype = 'edornotype'
and d1.code = Rpad(prop3, 20, ' ' )),
prop11,
(case ( select count (1)
from t_pol
where polno = mainpolno
and contno = rpad(prop2, 20, ' ' ))
when 1 then
(select paytodate
from t_pol c
where polno = mainpolno
and contno = rpad(prop2, 20, ' ' )
And Rownum = 1)
else
(select max(paytodate)
from t_pol c
where polno = mainpolno
and payintv > 0
and contno = rpad(prop2, 20, ' ' )
and appflag = '1' )
end) paytodate ,
(select codename
from code d2
where d2.codetype = 'edorapptype'
and d2.code = Rpad(prop5, 20, ' ' )),
(SELECT EDORNAME
FROM item
WHERE APPOBJ <> 'G'
AND EDORCODE IN
(SELECT EDORTYPE
FROM pitem
WHERE EDORACCEPTNO = rpad(prop1, 20, ' '))
AND ROWNUM = 1),
nvl((SELECT SUM(getmoney)
FROM pitem
WHERE EDORACCEPTNO = rpad(prop1, 20, ' ')
and edorstate = '2' ),
0),
(Select Edorpopedom
From user
Where Usercode = Rpad(Createoperator, 20, ' ' )
And Usertype = '1'
And Userstate = '0' ),
prop7,
Createoperator,
makedate,
missionid,
submissionid,
activityid,
(select nvl(urgenttype, '1')
from app
where edoracceptno = rpad(prop1, 20)) as urgenttype,
(case
when prop5 = '2' then
(select codename
from code
where codetype = 'agentlevel'
and code =
(select rpad(agentstar, 20)
from app
where edoracceptno = rpad(prop1, 20)))
else
''
end),
(select codename
from code
where codetype = 'CustomCheckResult'
and code =
(select customcheckresult
from app
where edoracceptno = rpad(prop1, 20))),
greatest(makedate,
nvl((select max(makedate)
from doc_main
where doccode = mission.prop1),
date '1900-01-01' )) as greatdate,
greatest(to_char( makedate, 'yyyymmdd') || maketime,
(select nvl(max(to_char( makedate, 'yyyymmdd') ||
maketime),
'1900010100:00:00')
from doc_main
where doccode = prop1)) as greattime,
(case
when prop5 = '2' then
(select agentstar
from app
where edoracceptno = rpad(prop1, 20))
else
''
end) as agentstar
from mission
where 1 = 1
and activityid = '0000000007'
and processid = '0000000000'
and exists
(select 1
from app
where (Preinputflag Is Null Or Preinputflag = '0')
and edoracceptno = rpad(mission.prop1, 20, ' '))
and defaultoperator is null
and exists ( select 1
from app
where applyfortype <> '2'
and edoracceptno = rpad(prop1, 20))
and exists
(select 1
from pitem
where EdorAcceptNo = rpad(prop1, 20, ' ')
and edortype in
(select edorcode
from popedom
where AppDateModifyFlag >=
(select min(outdate)
from bmission
where activityid = '0000000003'
and bmission.prop1 =
mission.prop1) -
(select distinct edorappdate
from pitem
where edorstate = '2'
and edoracceptno =
rpad(mission.prop1, 20 , ' '))
and LowerAppDate <=
(select min(outdate)
from bmission
where activityid = '0000000003'
and bmission.prop1 =
mission.prop1) -
(select distinct edorappdate
from pitem
where edorstate = '2'
and edoracceptno =
rpad(mission.prop1, 20 , ' '))
and LimitGetMoney +
nvl((SELECT sum(getmoney)
FROM pitem
WHERE EDORACCEPTNO =
rpad(prop1, 20 , ' ')
and edorstate = '2' ),
0) >= 0
and LowerLimitGetMoney +
nvl((SELECT sum(getmoney)
FROM pitem
WHERE EDORACCEPTNO =
rpad(prop1, 20 , ' ')
and edorstate = '2' ),
0) < 0
and edorcode in ('AG',
'CT',
'PT',
'CG',
'LO',
'PG',
'LN',
'RL',
'XT')
and ApproveFlag = '1'
and Exists
(Select 1
From user
Where Usercode = 'zy1zgs'
And Usertype = '1'
And Userstate = '0'
And Edorpopedom = popedom.Edorpopedom )
union
select edorcode
from popedom
where AppDateModifyFlag >=
(select min(outdate)
from bmission
where activityid = '0000000003'
and bmission.prop1 =
mission.prop1) -
(select distinct edorappdate
from pitem
where edorstate = '2'
and edoracceptno =
rpad(mission.prop1, 20 , ' '))
and LowerAppDate <=
(select min(outdate)
from bmission
where activityid = '0000000003'
and bmission.prop1 =
mission.prop1) -
(select distinct edorappdate
from pitem
where edorstate = '2'
and edoracceptno =
rpad(mission.prop1, 20 , ' '))
and edorcode not in ( 'AG',
'CT',
'PT',
'CG',
'LO',
'PG',
'LN',
'RL',
'XT')
and ApproveFlag = '1'
and Exists
(Select 1
From user
Where Usercode = 'zy1zgs'
And Usertype = '1'
And Userstate = '0'
and Edorpopedom = popedom.Edorpopedom )))
And Exists
(Select 1
From rrela a, mrela b
Where a.Areaid = b.Areaid
And b.Comcode in
(Rpad (Substr(prop7, 1, 4), 10 ),
Rpad(Substr (prop7 , 1, 6 ), 10))
And a.Usercode = 'zy1zgs'
and not exists
(select 1
from ldcom
where comcode = rpad(prop7, 10)
and substr (comcode , 1, 6 ) in
(select trim(code)
from code
where rpad (codealias , 10) = b.Comcode
and codetype = 'RiseCom' )))
and (CreateOperator <> 'zy1zgs' or
(select edortype
from pitem
where edoracceptno = rpad(prop1, 20, ' ')
and rownum = 1) in ('AC' , 'BB', 'CD', 'PC' ))
And Exists
(Select 1
From doc_Pages
Where Exists ( Select 1
From doc_Main
Where Docid = doc_Pages.Docid
And Doccode = prop1))
And Exists
(Select 1
From Lccont
Where Contno =
(Select Contno
From pitem
Where Edoracceptno = Rpad(prop1, 20, ' ')
And Rownum = 1)
and Conttype != 2 )
order by urgenttype desc ,
greatdate,
agentstar desc nulls last ,
greattime)
where rownum <= 10
故事從最早說起，這個SQL有一條執行突然變得很慢。以前幾秒，現在幾十秒。
把生產的統計資訊匯出，放在審計庫（和生產差一個月資料）上研究了一下，問題應該是出在柱狀圖上
對比審計庫本身的 10053和將生產統計資訊匯入到審計庫後的 10053
索引方面
審計庫統計資訊
Index: IDX_pitem_EDORACCEPTNO Col#: 1
LVLS: 3 #LB: 533158 #DK: 62078976 LB/K: 1.00 DB/K: 1.00 CLUF: 46340376.00
Index: pitem_INDEX_3 Col#: 13
LVLS: 2 #LB: 235330 #DK: 12 LB/K: 19610.00 DB/K: 318001.00 CLUF: 3816015.00
生產統計資訊
Index: IDX_pitem_EDORACCEPTNO Col#: 1
LVLS: 3 #LB: 530957 #DK: 63950848 LB/K: 1.00 DB/K: 1.00 CLUF: 45405526.00
Index: pitem_INDEX_3 Col#: 13
LVLS: 3 #LB: 248069 #DK: 12 LB/K: 20672.00 DB/K: 310030.00 CLUF: 3720364.00
審計庫柱狀圖資訊
Column (#1): EDORACCEPTNO(
AvgLen: 21 NDV: 62078976 Nulls: 0 Density: 0.000005
Histogram: Freq #Bkts: 255 UncompBkts: 254 EndPtVals: 255
Column (#13): EDORSTATE(
AvgLen: 2 NDV: 12 Nulls: 0 Density: 0.000000
Histogram: Freq #Bkts: 10 UncompBkts: 20023 EndPtVals: 10
生產柱狀圖資訊
Column (#1): EDORACCEPTNO(
AvgLen: 21 NDV: 63950848 Nulls: 0 Density: 0.000005
Histogram: Freq #Bkts: 255 UncompBkts: 254 EndPtVals: 255
Column (#13): EDORSTATE(
AvgLen: 2 NDV: 12 Nulls: 0 Density: 0.000000
Histogram: Freq #Bkts: 10 UncompBkts: 20632 EndPtVals: 10
上面看到統計資訊表明看起來沒什麼區別，但是成本估算差距很大。
注意下面兩個索引的成本對比
審計庫成本估算
Access Path: index (AllEqRange)
Index: IDX_pitem_EDORACCEPTNO
resc_io: 221.00 resc_cpu: 0
ix_sel: 0.000005 ix_sel_with_filters: 0.000005
Cost: 221.00 Resp: 221.00 Degree: 1
Access Path: index (AllEqRange)
Index: pitem_INDEX_3
resc_io: 813.00 resc_cpu: 0
ix_sel: 0.000200 ix_sel_with_filters: 0.000200
Cost: 813.00 Resp: 813.00 Degree: 1
生產成本估算
Access Path: index (AllEqRange)
Index: IDX_pitem_EDORACCEPTNO
resc_io: 245.00 resc_cpu: 0
ix_sel: 0.000005 ix_sel_with_filters: 0.000005
Cost: 245.00 Resp: 245.00 Degree: 1
Access Path: index (AllEqRange)
Index: pitem_INDEX_3
resc_io: 5.00 resc_cpu: 0
ix_sel: 0.000000 ix_sel_with_filters: 0.000000
Cost: 5.00 Resp: 5.00 Degree: 1
生產的執行計劃裡所有pitem表都錯誤地選擇了pitem_INDEX_3。可以發現對 IDX_pitem_EDORACCEPTNO的估算比較真實，但是對 pitem_INDEX_3，也就是 EDORSTATE（流程節點狀態）的估算差距非常大。
oracle認為EDORSTATE=’2’ 的值非常非常少。這個 10053沒有報謂詞越界，問題應該出在柱狀圖上
檢視柱狀圖
CREATE OR REPLACE FUNCTION hexstr (p_number IN NUMBER)
RETURN VARCHAR2
AS
l_str LONG := TO_CHAR (p_number, 'fm' || RPAD ('x', 50, 'x'));
l_return VARCHAR2 (4000);
BEGIN
WHILE (l_str IS NOT NULL)
LOOP
l_return := l_return || CHR (TO_NUMBER (SUBSTR (l_str, 1, 2), 'xx'));
l_str := SUBSTR (l_str, 3);
END LOOP;
RETURN (SUBSTR (l_return, 1, 6));
END;
/
檢視這張表第 13列的柱狀圖。
這裡只能顯示每個 bucket末端的值。可以看到兩個 bucket都是 ’1’，第三個是 ’2’。最大的可能是第三個 bucket裡的大多數也是 ’1’
SQL> select BUCKET, HEXSTR(ENDPOINT) value from histgrm$ where obj# = 94494 and COL# = 13;
BUCKET VALUE
---------- ----------------
20392 0
20446 1
20447 1
20448 2
20536 3
20537 6
20579 7
20621 8
20630 `
20632 a
柱狀圖資訊和真實值差距過大的結果就是對取值返回的結果集大小估算有偏差
在兩個庫上執行下面語句
explain plan for
select * from pitem where EDORSTATE = '2';
select * from table(dbms_xplan.display(null, null, 'OUTLINE'));
昨天生產的統計資訊
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 2906569252
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 337 | 5 |
| 1 | TABLE ACCESS BY INDEX ROWID| pitem | 1 | 337 | 5 |
|* 2 | INDEX RANGE SCAN | pitem_INDEX_3 | 1 | | 4 |
----------------------------------------------------------------------------------
今天生產的統計資訊
Plan hash value: 2906569252
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10433 | 3433K| 572 |
| 1 | TABLE ACCESS BY INDEX ROWID| pitem | 10433 | 3433K| 572 |
|* 2 | INDEX RANGE SCAN | pitem_INDEX_3 | 10433 | | 39 |
----------------------------------------------------------------------------------
考慮到這個 SQL時查詢流程節點出在某一狀態的，並且採用搶佔的方式處理流程。
可以做如下分析：
1 SQL中的'0000000000' 號流程的'0000000007'號節點處於 ’2’狀態的在週末基本都已經被處理完了。
2 30號收集統計資訊時發現幾乎沒有狀態為 ’2’的這個節點。
3 星期一早上，這個表的狀態欄位發生了大量的修改，節點狀態發生了變化：
Segments by DB Blocks Changes DB/Inst: PRODB/PRODB2 Snaps: 47177-47179
-> % of Capture shows % of DB Block Changes for each top segment compared
-> with total DB Block Changes for all segments captured by the Snapshot
Tablespace Subobject Obj. DB Block % of
Owner Name Object Name Name Type Changes Capture
---------- ---------- -------------------- ---------- ----- ------------ -------
LIS LIS pitem TABLE 256,322,560 55.21
LIS LISINDX pitem_INDEX_3 INDEX 205,673,696 44.30
LIS LIS LDMAXNO TABLE 156,144 .03
LIS LIS app TABLE 135,376 .03
LIS LIS mission TABLE 132,912 .03
-------------------------------------------------------------
但是oracle採用了週末收集的統計資訊，走了 pitem_INDEX_3
4 隨著大家又開始處理流程了，因此 EDORSTATE=’2’ 的資料開始減少。掃描pitem_INDEX_3的成本逐漸提高，因此就有了現在生產上後來的預設執行計劃：
走兩個索引，做 AND-EQUAL。說明兩個索引的成正在逐漸接近。
後來決定用sql profile固定執行計劃
declare
sql_txt clob;
h sys.sqlprof_attr;
begin
h := sys.sqlprof_attr (
'BEGIN_OUTLINE_DATA',
'INDEX(@"SEL$44" "pitem"@"SEL$44" ("pitem"."EDORACCEPTNO" "pitem"."EDORNO"',
' "pitem"."POLNO" "pitem"."CONTNO" "pitem"."INSUREDNO" "pitem"."EDORTYPE"))',
'USE_NL(@"SEL$245B400D" "code"@"SEL$39")' ,
'LEADING(@"SEL$245B400D" "LDCOM"@"SEL$38" "code"@"SEL$39")' ,
'INDEX_RS_ASC(@"SEL$245B400D" "code"@"SEL$39" ("code"."CODETYPE" "code"."CODE"))',
'INDEX(@"SEL$245B400D" "LDCOM"@"SEL$38" ("LDCOM"."COMCODE"))' ,
'INDEX_RS_ASC(@"SEL$32" "bmission"@"SEL$32" ("bmission"."prop1"))' ,
'USE_HASH_AGGREGATION(@"SEL$33")' ,
'INDEX_RS_ASC(@"SEL$33" "pitem"@"SEL$33" ("pitem"."EDORACCEPTNO"))' ,
'INDEX_RS_ASC(@"SEL$34" "bmission"@"SEL$34" ("bmission"."prop1"))' ,
'USE_HASH_AGGREGATION(@"SEL$35")' ,
'INDEX_RS_ASC(@"SEL$35" "pitem"@"SEL$35" ("pitem"."EDORACCEPTNO"))' ,
'INDEX_RS_ASC(@"SEL$24" "bmission"@"SEL$24" ("bmission"."prop1"))' ,
'USE_HASH_AGGREGATION(@"SEL$25")' ,
'INDEX_RS_ASC(@"SEL$25" "pitem"@"SEL$25" ("pitem"."EDORACCEPTNO"))' ,
'INDEX_RS_ASC(@"SEL$26" "bmission"@"SEL$26" ("bmission"."prop1"))' ,
'USE_HASH_AGGREGATION(@"SEL$27")' ,
'INDEX_RS_ASC(@"SEL$27" "pitem"@"SEL$27" ("pitem"."EDORACCEPTNO"))' ,
'INDEX_RS_ASC(@"SEL$28" "pitem"@"SEL$28" ("pitem"."EDORACCEPTNO"))' ,
'INDEX_RS_ASC(@"SEL$29" "pitem"@"SEL$29" ("pitem"."EDORACCEPTNO"))' ,
'USE_NL(@"SEL$EE91C113" "popedom"@"SEL$23")' ,
'LEADING(@"SEL$EE91C113" "user"@"SEL$30" "popedom"@"SEL$23")' ,
'INDEX_RS_ASC(@"SEL$EE91C113" "popedom"@"SEL$23" ("popedom"."EDORPOPEDOM"',
' "popedom"."EDORCODE" "popedom"."MANAGECOM" "popedom"."RISKPROP"',
' "popedom"."RISKPERIOD"))' ,
'INDEX_RS_ASC(@"SEL$EE91C113" "user"@"SEL$30" ("user"."USERCODE" "user"."USERTYPE"))',
'USE_NL(@"SEL$69C7062B" "popedom"@"SEL$31")' ,
'LEADING(@"SEL$69C7062B" "user"@"SEL$36" "popedom"@"SEL$31")' ,
'INDEX_RS_ASC(@"SEL$69C7062B" "popedom"@"SEL$31" ("popedom"."EDORPOPEDOM"',
' "popedom"."EDORCODE" "popedom"."MANAGECOM" "popedom"."RISKPROP"',
' "popedom"."RISKPERIOD"))' ,
'INDEX_RS_ASC(@"SEL$69C7062B" "user"@"SEL$36" ("user"."USERCODE" "user"."USERTYPE"))',
'INDEX_RS_ASC(@"SEL$16" "app"@"SEL$16" ("app"."EDORACCEPTNO"))' ,
'INDEX_RS_ASC(@"SEL$14" "app"@"SEL$14" ("app"."EDORACCEPTNO"))' ,
'INDEX(@"SEL$9" "pitem"@"SEL$9" ("pitem"."EDORACCEPTNO" "pitem"."EDORNO" "pitem"."POLNO"',
' "pitem"."CONTNO" "pitem"."INSUREDNO" "pitem"."EDORTYPE"))',
'INDEX_RS_ASC(@"SEL$3" "D1"@"SEL$3" ("code"."CODETYPE" "code"."CODE"))' ,
'INDEX_RS_ASC(@"SEL$4" "t_pol"@"SEL$4" ("t_pol"."CONTNO"))' ,
'INDEX_RS_ASC(@"SEL$5" "C"@"SEL$5" ("t_pol"."CONTNO"))' ,
'INDEX_RS_ASC(@"SEL$6" "C"@"SEL$6" ("t_pol"."CONTNO"))' ,
'INDEX_RS_ASC(@"SEL$7" "D2"@"SEL$7" ("code"."CODETYPE" "code"."CODE"))' ,
'FULL(@"SEL$8" "item"@"SEL$8")' ,
'INDEX_RS_ASC(@"SEL$10" "pitem"@"SEL$10" ("pitem"."EDORACCEPTNO"))' ,
'INDEX_RS_ASC(@"SEL$11" "user"@"SEL$11" ("user"."USERCODE" "user"."USERTYPE"))',
'INDEX_RS_ASC(@"SEL$12" "app"@"SEL$12" ("app"."EDORACCEPTNO"))' ,
'PUSH_SUBQ(@"SEL$14")' ,
'INDEX_RS_ASC(@"SEL$13" "code"@"SEL$13" ("code"."CODETYPE" "code"."CODE"))' ,
'PUSH_SUBQ(@"SEL$16")' ,
'INDEX_RS_ASC(@"SEL$15" "code"@"SEL$15" ("code"."CODETYPE" "code"."CODE"))' ,
'INDEX_RS_ASC(@"SEL$17" "doc_MAIN"@"SEL$17" ("doc_MAIN"."DOCCODE" "doc_MAIN"."BUSSTYPE"',
' "doc_MAIN"."SUBTYPE"))' ,
'INDEX_RS_ASC(@"SEL$18" "doc_MAIN"@"SEL$18" ("doc_MAIN"."DOCCODE" "doc_MAIN"."BUSSTYPE"',
' "doc_MAIN"."SUBTYPE"))' ,
'INDEX_RS_ASC(@"SEL$19" "app"@"SEL$19" ("app"."EDORACCEPTNO"))' ,
'PUSH_SUBQ(@"SET$1")' ,
'INDEX(@"SEL$22" "pitem"@"SEL$22" ("pitem"."EDORACCEPTNO" "pitem"."EDORNO"',
' "pitem"."POLNO" "pitem"."CONTNO" "pitem"."INSUREDNO" "pitem"."EDORTYPE"))',
'PUSH_SUBQ(@"SEL$245B400D")' ,
'USE_NL(@"SEL$37" "B"@"SEL$37")' ,
'LEADING(@"SEL$37" "A"@"SEL$37" "B"@"SEL$37")' ,
'INDEX(@"SEL$37" "B"@"SEL$37" ("mrela"."COMCODE" "mrela"."AREAID"))',
'INDEX(@"SEL$37" "A"@"SEL$37" ("rrela"."USERCODE" "rrela"."AREAID"',
' "rrela"."COMCODE"))' ,
'INDEX(@"SEL$40" "pitem"@"SEL$40" ("pitem"."EDORACCEPTNO" "pitem"."EDORNO"',
' "pitem"."POLNO" "pitem"."CONTNO" "pitem"."INSUREDNO" "pitem"."EDORTYPE"))',
'PUSH_SUBQ(@"SEL$44")' ,
'INDEX_RS_ASC(@"SEL$43" "LCCONT"@"SEL$43" ("LCCONT"."CONTNO"))' ,
'USE_NL(@"SEL$700B301E" "doc_PAGES"@"SEL$41")' ,
'LEADING(@"SEL$700B301E" "doc_MAIN"@"SEL$42" "doc_PAGES"@"SEL$41")' ,
'INDEX(@"SEL$700B301E" "doc_PAGES"@"SEL$41" ("doc_PAGES"."DOCID"))' ,
'INDEX_RS_ASC(@"SEL$700B301E" "doc_MAIN"@"SEL$42" ("doc_MAIN"."DOCCODE" "doc_MAIN"."BUSSTYPE"',
' "doc_MAIN"."SUBTYPE"))' ,
'USE_NL(@"SEL$D8F4EB61" "VW_SQ_1"@"SEL$CEAA7324")' ,
'USE_NL(@"SEL$D8F4EB61" "app"@"SEL$20")' ,
'USE_NL(@"SEL$D8F4EB61" "app"@"SEL$21")' ,
'LEADING(@"SEL$D8F4EB61" "mission"@"SEL$2" "app"@"SEL$21" "app"@"SEL$20"',
' "VW_SQ_1"@"SEL$CEAA7324")' ,
'NO_ACCESS(@"SEL$D8F4EB61" "VW_SQ_1"@"SEL$CEAA7324")' ,
'INDEX_RS_ASC(@"SEL$D8F4EB61" "app"@"SEL$20" ("app"."EDORACCEPTNO"))' ,
'INDEX_RS_ASC(@"SEL$D8F4EB61" "app"@"SEL$21" ("app"."EDORACCEPTNO"))' ,
'INDEX_RS_ASC(@"SEL$D8F4EB61" "mission"@"SEL$2" ("mission"."ACTIVITYID"))' ,
'NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")' ,
'OUTLINE(@"SEL$42")' ,
'OUTLINE(@"SEL$41")' ,
'OUTLINE(@"SEL$2")' ,
'UNNEST(@"SEL$42")' ,
'OUTLINE(@"SEL$28583BE2")' ,
'OUTLINE(@"SEL$21")' ,
'OUTLINE(@"SEL$20")' ,
'OUTLINE(@"SEL$CEAA7324")' ,
'UNNEST(@"SEL$28583BE2")' ,
'UNNEST(@"SEL$21")' ,
'UNNEST(@"SEL$20")' ,
'OUTLINE(@"SEL$D8F4EB61")' ,
'OUTLINE(@"SEL$1B0ECD80")' ,
'OUTLINE(@"SEL$39")' ,
'OUTLINE(@"SEL$38")' ,
'OUTLINE(@"SEL$36")' ,
'OUTLINE(@"SEL$31")' ,
'OUTLINE(@"SEL$30")' ,
'OUTLINE(@"SEL$23")' ,
'OUTLINE_LEAF(@"SEL$1")' ,
'UNNEST(@"SEL$28583BE2")' ,
'UNNEST(@"SEL$21")' ,
'UNNEST(@"SEL$20")' ,
'OUTLINE_LEAF(@"SEL$D8F4EB61")' ,
'PUSH_PRED(@"SEL$D8F4EB61" "VW_SQ_1"@"SEL$CEAA7324" 7)' ,
'OUTLINE_LEAF(@"SEL$700B301E")' ,
'OUTLINE_LEAF(@"SEL$43")' ,
'OUTLINE_LEAF(@"SEL$44")' ,
'OUTLINE_LEAF(@"SEL$40")' ,
'OUTLINE_LEAF(@"SEL$37")' ,
'UNNEST(@"SEL$39")' ,
'OUTLINE_LEAF(@"SEL$245B400D")' ,
'OUTLINE_LEAF(@"SEL$22")' ,
'OUTLINE_LEAF(@"SET$1")' ,
'UNNEST(@"SEL$36")' ,
'OUTLINE_LEAF(@"SEL$69C7062B")' ,
'OUTLINE_LEAF(@"SEL$35")' ,
'OUTLINE_LEAF(@"SEL$34")' ,
'OUTLINE_LEAF(@"SEL$33")' ,
'OUTLINE_LEAF(@"SEL$32")' ,
'UNNEST(@"SEL$30")' ,
'OUTLINE_LEAF(@"SEL$EE91C113")' ,
'OUTLINE_LEAF(@"SEL$29")' ,
'OUTLINE_LEAF(@"SEL$28")' ,
'OUTLINE_LEAF(@"SEL$27")' ,
'OUTLINE_LEAF(@"SEL$26")' ,
'OUTLINE_LEAF(@"SEL$25")' ,
'OUTLINE_LEAF(@"SEL$24")' ,
'OUTLINE_LEAF(@"SEL$19")' ,
'OUTLINE_LEAF(@"SEL$18")' ,
'OUTLINE_LEAF(@"SEL$17")' ,
'OUTLINE_LEAF(@"SEL$15")' ,
'OUTLINE_LEAF(@"SEL$16")' ,
'OUTLINE_LEAF(@"SEL$13")' ,
'OUTLINE_LEAF(@"SEL$14")' ,
'OUTLINE_LEAF(@"SEL$12")' ,
'OUTLINE_LEAF(@"SEL$11")' ,
'OUTLINE_LEAF(@"SEL$10")' ,
'OUTLINE_LEAF(@"SEL$8")' ,
'OUTLINE_LEAF(@"SEL$9")' ,
'OUTLINE_LEAF(@"SEL$7")' ,
'OUTLINE_LEAF(@"SEL$6")' ,
'OUTLINE_LEAF(@"SEL$5")' ,
'OUTLINE_LEAF(@"SEL$4")' ,
'OUTLINE_LEAF(@"SEL$3")' ,
'ALL_ROWS',
'DB_VERSION(''11.2.0.3'')' ,
'OPTIMIZER_FEATURES_ENABLE(''11.2.0.3'')' ,
'OPT_PARAM(''_optimizer_use_feedback'' ''false'')' ,
'OPT_PARAM(''_optimizer_adaptive_cursor_sharing'' ''false'')' ,
'OPT_PARAM(''_optimizer_extended_cursor_sharing_rel'' ''none'')' ,
'OPT_PARAM(''_optimizer_extended_cursor_sharing'' ''none'')' ,
'OPT_PARAM(''_b_tree_bitmap_plans'' ''false'')' ,
'IGNORE_OPTIM_EMBEDDED_HINTS' ,
'END_OUTLINE_DATA'
);
dbms_sqltune.import_sql_profile (
sql_text => 'select * from (select prop1, prop2, (select codename from code d1 where ''1418892021000''=''1418892021000'' and d1.codetype = ''edornotype'' and d1.code = Rpad(prop3, 20, '' '') ), prop11, (case (select count(1) from t_pol where polno =mainpolno and contno = rpad(prop2,20,'' '')) when 1 then (select paytodate from t_pol c where polno = mainpolno and contno = rpad(prop2,20,'' '') And Rownum = 1) else (select max(paytodate) from t_pol c where polno = mainpolno and payintv>0 and contno = rpad(prop2,20,'' '') and appflag =''1'') end) paytodate, (select codename from code d2 where d2.codetype = ''edorapptype'' and d2.code = Rpad(prop5, 20, '' '') ), (SELECT EDORNAME FROM item WHERE APPOBJ<>''G'' AND EDORCODE IN (SELECT EDORTYPE FROM pitem WHERE EDORACCEPTNO=rpad(prop1,20,'' '')) AND ROWNUM=1), nvl((SELECT SUM(getmoney) FROM pitem WHERE EDORACCEPTNO=rpad(prop1,20,'' '') and edorstate=''2''),0), (Select Edorpopedom From user Where Usercode = Rpad(Createoperator, 20, '' '') And Usertype = ''1'' And Userstate = ''0''), prop7, Createoperator, makedate, missionid, submissionid, activityid, (select nvl(urgenttype, ''1'') from app where edoracceptno = rpad(prop1, 20)) as urgenttype, (case when prop5 = ''2'' then (select codename from code where codetype = ''agentlevel'' and code = (select rpad(agentstar, 20) from app where edoracceptno=rpad(prop1, 20))) else '''' end),(select codename from code where codetype= ''CustomCheckResult'' and code = (select customcheckresult from app where edoracceptno = rpad(prop1, 20))), greatest(makedate, nvl((select max(makedate) from doc_main where doccode = mission.prop1), date ''1900-01-01'')) as greatdate, greatest(to_char(makedate, ''yyyymmdd'') || maketime, (select nvl(max(to_char(makedate, ''yyyymmdd'') || maketime), ''1900010100:00:00'') from doc_main where doccode = prop1)) as greattime,(case when prop5 = ''2'' then (select agentstar from app where edoracceptno = rpad(prop1, 20)) else '''' end) as agentstar from mission where 1=1 and activityid = ''0000000007'' and processid = ''0000000000'' and exists (select 1 from app where (Preinputflag Is Null Or Preinputflag=''0'') and edoracceptno=rpad(mission.prop1,20,'' '')) and defaultoperator is null and exists (select 1 from app where applyfortype <> ''2'' and edoracceptno = rpad(prop1, 20)) and exists (select 1 from pitem where EdorAcceptNo = rpad(prop1, 20, '' '') and edortype in (select edorcode from popedom where AppDateModifyFlag >= (select min(outdate) from bmission where activityid = ''0000000003'' and bmission.prop1 = mission.prop1) - (select distinct edorappdate from pitem where edorstate = ''2'' and edoracceptno = rpad(mission.prop1, 20, '' '')) and LowerAppDate <= (select min(outdate) from bmission where activityid = ''0000000003'' and bmission.prop1 = mission.prop1) - (select distinct edorappdate from pitem where edorstate = ''2'' and edoracceptno = rpad(mission.prop1, 20, '' '')) and LimitGetMoney + nvl((SELECT sum(getmoney) FROM pitem WHERE EDORACCEPTNO = rpad(prop1, 20, '' '') and edorstate = ''2''), 0) >= 0 and LowerLimitGetMoney + nvl((SELECT sum(getmoney) FROM pitem WHERE EDORACCEPTNO = rpad(prop1, 20, '' '') and edorstate = ''2''), 0) < 0 and edorcode in (''AG'', ''CT'', ''PT'', ''CG'', ''LO'', ''PG'', ''LN'', ''RL'', ''XT'') and ApproveFlag = ''1'' and Exists (Select 1 From user Where Usercode = ''zy1zgs'' And Usertype = ''1'' And Userstate = ''0'' And Edorpopedom = popedom.Edorpopedom) union select edorcode from popedom where AppDateModifyFlag >= (select min(outdate) from bmission where activityid = ''0000000003'' and bmission.prop1 = mission.prop1) - (select distinct edorappdate from pitem where edorstate = ''2'' and edoracceptno = rpad(mission.prop1, 20, '' '')) and LowerAppDate <= (select min(outdate) from bmission where activityid = ''0000000003'' and bmission.prop1 = mission.prop1) - (select distinct edorappdate from pitem where edorstate = ''2'' and edoracceptno = rpad(mission.prop1, 20, '' '')) and edorcode not in (''AG'', ''CT'', ''PT'', ''CG'', ''LO'', ''PG'', ''LN'', ''RL'', ''XT'') and ApproveFlag = ''1'' and Exists (Select 1 From user Where Usercode = ''zy1zgs'' And Usertype = ''1'' And Userstate = ''0'' and Edorpopedom = popedom.Edorpopedom))) And Exists (Select 1 From rrela a, mrela b Where a.Areaid = b.Areaid And b.Comcode in (Rpad(Substr(prop7, 1, 4), 10), Rpad(Substr(prop7, 1, 6), 10)) And a.Usercode =''zy1zgs'' and not exists (select 1 from ldcom where comcode = rpad(prop7, 10) and substr(comcode, 1, 6) in (select trim(code) from code where rpad(codealias,10) = b.Comcode and codetype = ''RiseCom''))) and (CreateOperator <> ''zy1zgs'' or (select edortype from pitem where edoracceptno = rpad(prop1, 20, '' '') and rownum =1 ) in (''AC'', ''BB'', ''CD'',''PC'')) And Exists (Select 1 From doc_Pages Where Exists (Select 1 From doc_Main Where Docid = doc_Pages.Docid And Doccode = prop1)) And Exists (Select 1 From Lccont Where Contno = (Select Contno From pitem Where Edoracceptno = Rpad(prop1, 20, '' '') And Rownum = 1) and Conttype != 2) order by urgenttype desc, greatdate, agentstar desc nulls last, greattime) where rownum<=10',
profile => h,
name => '59zyu0jtuz0v7',
description => '59zyu0jtuz0v7',
category => 'default',
validate => true,
replace => true,
force_match => true );
end;
/
正確的執行計劃如下
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5880 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID | code | 1 | 59 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_code | 1 | | 1 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 63 | | |
|* 4 | TABLE ACCESS BY INDEX ROWID | t_pol | 1 | 63 | 5 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | t_pol_INDEX_7 | 2 | | 4 (0)| 00:00:01 |
|* 6 | COUNT STOPKEY | | | | | |
|* 7 | TABLE ACCESS BY INDEX ROWID | t_pol | 1 | 71 | 5 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | t_pol_INDEX_7 | 2 | | 4 (0)| 00:00:01 |
| 9 | SORT AGGREGATE | | 1 | 76 | | |
|* 10 | TABLE ACCESS BY INDEX ROWID | t_pol | 1 | 76 | 5 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | t_pol_INDEX_7 | 2 | | 4 (0)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID | code | 1 | 59 | 2 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | PK_code | 1 | | 1 (0)| 00:00:01 |
|* 14 | COUNT STOPKEY | | | | | |
|* 15 | FILTER | | | | | |
|* 16 | TABLE ACCESS FULL | item | 83 | 1660 | 3 (0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | PK_pitem | 1 | 24 | 4 (0)| 00:00:01 |
| 18 | SORT AGGREGATE | | 1 | 27 | | |
|* 19 | TABLE ACCESS BY INDEX ROWID | pitem | 1 | 27 | 5 (0)| 00:00:01 |
|* 20 | INDEX RANGE SCAN | PK_pitem | 1 | | 4 (0)| 00:00:01 |
|* 21 | TABLE ACCESS BY INDEX ROWID | user | 1 | 28 | 2 (0)| 00:00:01 |
|* 22 | INDEX UNIQUE SCAN | P_KEY_2 | 1 | | 1 (0)| 00:00:01 |
| 23 | TABLE ACCESS BY INDEX ROWID | app | 1 | 23 | 4 (0)| 00:00:01 |
|* 24 | INDEX UNIQUE SCAN | PK_app | 1 | | 3 (0)| 00:00:01 |
| 25 | TABLE ACCESS BY INDEX ROWID | code | 1 | 59 | 2 (0)| 00:00:01 |
|* 26 | INDEX UNIQUE SCAN | PK_code | 1 | | 1 (0)| 00:00:01 |
| 27 | TABLE ACCESS BY INDEX ROWID | app | 1 | 23 | 4 (0)| 00:00:01 |
|* 28 | INDEX UNIQUE SCAN | PK_app | 1 | | 3 (0)| 00:00:01 |
| 29 | TABLE ACCESS BY INDEX ROWID | code | 1 | 59 | 2 (0)| 00:00:01 |
|* 30 | INDEX UNIQUE SCAN | PK_code | 1 | | 1 (0)| 00:00:01 |
| 31 | TABLE ACCESS BY INDEX ROWID | app | 1 | 23 | 4 (0)| 00:00:01 |
|* 32 | INDEX UNIQUE SCAN | PK_app | 1 | | 3 (0)| 00:00:01 |
| 33 | SORT AGGREGATE | | 1 | 23 | | |
| 34 | TABLE ACCESS BY INDEX ROWID | doc_MAIN | 2 | 46 | 6 (0)| 00:00:01 |
|* 35 | INDEX RANGE SCAN | IDX_doc_MAIN_DOCCD | 2 | | 4 (0)| 00:00:01 |
| 36 | SORT AGGREGATE | | 1 | 32 | | |
| 37 | TABLE ACCESS BY INDEX ROWID | doc_MAIN | 2 | 64 | 6 (0)| 00:00:01 |
|* 38 | INDEX RANGE SCAN | IDX_doc_MAIN_DOCCD | 2 | | 4 (0)| 00:00:01 |
| 39 | TABLE ACCESS BY INDEX ROWID | app | 1 | 23 | 4 (0)| 00:00:01 |
|* 40 | INDEX UNIQUE SCAN | PK_app | 1 | | 3 (0)| 00:00:01 |
|* 41 | COUNT STOPKEY | | | | | |
| 42 | VIEW | | 1 | 589 | 5880 (1)| 00:01:11 |
|* 43 | SORT ORDER BY STOPKEY | | 1 | 178 | 5880 (1)| 00:01:11 |
|* 44 | FILTER | | | | | |
| 45 | NESTED LOOPS SEMI | | 1 | 178 | 5809 (1)| 00:01:10 |
| 46 | NESTED LOOPS SEMI | | 1 | 176 | 5800 (1)| 00:01:10 |
| 47 | NESTED LOOPS SEMI | | 1 | 153 | 5797 (1)| 00:01:10 |
|* 48 | TABLE ACCESS BY INDEX ROWID | mission | 1265 | 160K| 1999 (1)| 00:00:24 |
|* 49 | INDEX RANGE SCAN | IDX_mission_ACTID | 3006 | | 17 (0)| 00:00:01 |
|* 50 | TABLE ACCESS BY INDEX ROWID | app | 1 | 23 | 3 (0)| 00:00:01 |
|* 51 | INDEX UNIQUE SCAN | PK_app | 1 | | 2 (0)| 00:00:01 |
|* 52 | TABLE ACCESS BY INDEX ROWID | app | 1 | 23 | 3 (0)| 00:00:01 |
|* 53 | INDEX UNIQUE SCAN | PK_app | 1 | | 2 (0)| 00:00:01 |
| 54 | VIEW PUSHED PREDICATE | VW_SQ_1 | 1 | 2 | 9 (0)| 00:00:01 |
| 55 | NESTED LOOPS | | 1 | 29 | 9 (0)| 00:00:01 |
| 56 | TABLE ACCESS BY INDEX ROWID | doc_MAIN | 1 | 22 | 6 (0)| 00:00:01 |
|* 57 | INDEX RANGE SCAN | IDX_doc_MAIN_DOCCD | 2 | | 4 (0)| 00:00:01 |
|* 58 | INDEX RANGE SCAN | IDX_doc_PAGdoc | 1 | 7 | 3 (0)| 00:00:01 |
|* 59 | INDEX RANGE SCAN | PK_pitem | 1 | 24 | 4 (0)| 00:00:01 |
| 60 | SORT UNIQUE | | 2 | 90 | 52 (43)| 00:00:01 |
| 61 | UNION-ALL | | | | | |
|* 62 | FILTER | | | | | |
|* 63 | FILTER | | | | | |
| 64 | NESTED LOOPS | | 1 | 49 | 4 (0)| 00:00:01 |
|* 65 | TABLE ACCESS BY INDEX ROWID| user | 1 | 28 | 2 (0)| 00:00:01 |
|* 66 | INDEX UNIQUE SCAN | P_KEY_2 | 1 | | 1 (0)| 00:00:01 |
|* 67 | TABLE ACCESS BY INDEX ROWID| popedom | 1 | 21 | 2 (0)| 00:00:01 |
|* 68 | INDEX RANGE SCAN | P_KEY_1 | 1 | | 1 (0)| 00:00:01 |
| 69 | SORT AGGREGATE | | 1 | 27 | | |
|* 70 | TABLE ACCESS BY INDEX ROWID | pitem | 1 | 27 | 5 (0)| 00:00:01 |
|* 71 | INDEX RANGE SCAN | IDX_pitem_EDORACCEPTNO | 1 | | 4 (0)| 00:00:01 |
| 72 | SORT AGGREGATE | | 1 | 27 | | |
|* 73 | TABLE ACCESS BY INDEX ROWID | pitem | 1 | 27 | 5 (0)| 00:00:01 |
|* 74 | INDEX RANGE SCAN | IDX_pitem_EDORACCEPTNO | 1 | | 4 (0)| 00:00:01 |
| 75 | SORT AGGREGATE | | 1 | 36 | | |
|* 76 | TABLE ACCESS BY INDEX ROWID | bmission | 1 | 36 | 8 (0)| 00:00:01 |
|* 77 | INDEX RANGE SCAN | IDX_bmission_MP1 | 6 | | 4 (0)| 00:00:01 |
| 78 | HASH UNIQUE | | 1 | 31 | 6 (17)| 00:00:01 |
|* 79 | TABLE ACCESS BY INDEX ROWID | pitem | 1 | 31 | 5 (0)| 00:00:01 |
|* 80 | INDEX RANGE SCAN | IDX_pitem_EDORACCEPTNO | 1 | | 4 (0)| 00:00:01 |
| 81 | SORT AGGREGATE | | 1 | 36 | | |
|* 82 | TABLE ACCESS BY INDEX ROWID | bmission | 1 | 36 | 8 (0)| 00:00:01 |
|* 83 | INDEX RANGE SCAN | IDX_bmission_MP1 | 6 | | 4 (0)| 00:00:01 |
| 84 | HASH UNIQUE | | 1 | 31 | 6 (17)| 00:00:01 |
|* 85 | TABLE ACCESS BY INDEX ROWID | pitem | 1 | 31 | 5 (0)| 00:00:01 |
|* 86 | INDEX RANGE SCAN | IDX_pitem_EDORACCEPTNO | 1 | | 4 (0)| 00:00:01 |
|* 87 | FILTER | | | | | |
|* 88 | FILTER | | | | | |
| 89 | NESTED LOOPS | | 1 | 41 | 4 (0)| 00:00:01 |
|* 90 | TABLE ACCESS BY INDEX ROWID| user | 1 | 28 | 2 (0)| 00:00:01 |
|* 91 | INDEX UNIQUE SCAN | P_KEY_2 | 1 | | 1 (0)| 00:00:01 |
|* 92 | TABLE ACCESS BY INDEX ROWID| popedom | 1 | 13 | 2 (0)| 00:00:01 |
|* 93 | INDEX RANGE SCAN | P_KEY_1 | 1 | | 1 (0)| 00:00:01 |
| 94 | SORT AGGREGATE | | 1 | 36 | | |
|* 95 | TABLE ACCESS BY INDEX ROWID | bmission | 1 | 36 | 8 (0)| 00:00:01 |
|* 96 | INDEX RANGE SCAN | IDX_bmission_MP1 | 6 | | 4 (0)| 00:00:01 |
| 97 | HASH UNIQUE | | 1 | 31 | 6 (17)| 00:00:01 |
|* 98 | TABLE ACCESS BY INDEX ROWID | pitem | 1 | 31 | 5 (0)| 00:00:01 |
|* 99 | INDEX RANGE SCAN | IDX_pitem_EDORACCEPTNO | 1 | | 4 (0)| 00:00:01 |
| 100 | SORT AGGREGATE | | 1 | 36 | | |
|*101 | TABLE ACCESS BY INDEX ROWID | bmission | 1 | 36 | 8 (0)| 00:00:01 |
|*102 | INDEX RANGE SCAN | IDX_bmission_MP1 | 6 | | 4 (0)| 00:00:01 |
| 103 | HASH UNIQUE | | 1 | 31 | 6 (17)| 00:00:01 |
|*104 | TABLE ACCESS BY INDEX ROWID | pitem | 1 | 31 | 5 (0)| 00:00:01 |
|*105 | INDEX RANGE SCAN | IDX_pitem_EDORACCEPTNO | 1 | | 4 (0)| 00:00:01 |
| 106 | NESTED LOOPS | | 1 | 54 | 3 (0)| 00:00:01 |
|*107 | INDEX RANGE SCAN | rrela_KEY_1 | 2 | 64 | 1 (0)| 00:00:01 |
| 108 | INLIST ITERATOR | | | | | |
|*109 | INDEX UNIQUE SCAN | mrela_KEY_1 | 1 | 22 | 1 (0)| 00:00:01 |
| 110 | NESTED LOOPS | | 1 | 58 | 3 (0)| 00:00:01 |
|*111 | INDEX UNIQUE SCAN | PK_LDCOM | 1 | 11 | 1 (0)| 00:00:01 |
|*112 | TABLE ACCESS BY INDEX ROWID | code | 1 | 47 | 2 (0)| 00:00:01 |
|*113 | INDEX RANGE SCAN | PK_code | 1 | | 1 (0)| 00:00:01 |
|*114 | TABLE ACCESS BY INDEX ROWID | LCCONT | 1 | 23 | 4 (0)| 00:00:01 |
|*115 | INDEX UNIQUE SCAN | PK_LCCONT | 1 | | 3 (0)| 00:00:01 |
|*116 | COUNT STOPKEY | | | | | |
|*117 | INDEX RANGE SCAN | PK_pitem | 1 | 42 | 4 (0)| 00:00:01 |
|*118 | COUNT STOPKEY | | | | | |
|*119 | INDEX RANGE SCAN | PK_pitem | 1 | 24 | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------
前面都是鋪墊，主角來了
後來有一天，需要在登陸觸發器裡為部分客戶機的session設定cursor_sharing=force。
結果這條SQL就有了兩個版本，一個是之前的非繫結變了的SQL，一個是oracle改寫的強制繫結變數版本。
被oracle改寫為繫結變數版本的SQL統統執行不出來了。
執行計劃如下
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 2292K(100)| |
|* 1 | FILTER | | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | code | 1 | 59 | | 2 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | PK_code | 1 | | | 1 (0)| 00:00:01 |
| 4 | SORT AGGREGATE | | 1 | 63 | | | |
|* 5 | TABLE ACCESS BY INDEX ROWID | t_pol | 1 | 63 | | 5 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | t_pol_INDEX_7 | 2 | | | 4 (0)| 00:00:01 |
|* 7 | COUNT STOPKEY | | | | | | |
|* 8 | TABLE ACCESS BY INDEX ROWID | t_pol | 1 | 71 | | 5 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | t_pol_INDEX_7 | 2 | | | 4 (0)| 00:00:01 |
| 10 | SORT AGGREGATE | | 1 | 76 | | | |
|* 11 | TABLE ACCESS BY INDEX ROWID | t_pol | 1 | 76 | | 5 (0)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | t_pol_INDEX_7 | 2 | | | 4 (0)| 00:00:01 |
| 13 | TABLE ACCESS BY INDEX ROWID | code | 1 | 59 | | 2 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | PK_code | 1 | | | 1 (0)| 00:00:01 |
|* 15 | COUNT STOPKEY | | | | | | |
|* 16 | FILTER | | | | | | |
|* 17 | TABLE ACCESS FULL | item | 83 | 1660 | | 3 (0)| 00:00:01 |
|* 18 | INDEX RANGE SCAN | PK_pitem | 1 | 24 | | 4 (0)| 00:00:01 |
| 19 | SORT AGGREGATE | | 1 | 27 | | | |
|* 20 | TABLE ACCESS BY INDEX ROWID | pitem | 1 | 27 | | 5 (0)| 00:00:01 |
|* 21 | INDEX RANGE SCAN | PK_pitem | 1 | | | 4 (0)| 00:00:01 |
|* 22 | TABLE ACCESS BY INDEX ROWID | user | 1 | 28 | | 2 (0)| 00:00:01 |
|* 23 | INDEX UNIQUE SCAN | P_KEY_2 | 1 | | | 1 (0)| 00:00:01 |
| 24 | TABLE ACCESS BY INDEX ROWID | app | 1 | 23 | | 4 (0)| 00:00:01 |
|* 25 | INDEX UNIQUE SCAN | PK_app | 1 | | | 3 (0)| 00:00:01 |
| 26 | TABLE ACCESS BY INDEX ROWID | code | 1 | 59 | | 2 (0)| 00:00:01 |
|* 27 | INDEX UNIQUE SCAN | PK_code | 1 | | | 1 (0)| 00:00:01 |
| 28 | TABLE ACCESS BY INDEX ROWID | app | 1 | 23 | | 4 (0)| 00:00:01 |
|* 29 | INDEX UNIQUE SCAN | PK_app | 1 | | | 3 (0)| 00:00:01 |
| 30 | TABLE ACCESS BY INDEX ROWID | code | 1 | 59 | | 2 (0)| 00:00:01 |
|* 31 | INDEX UNIQUE SCAN | PK_code | 1 | | | 1 (0)| 00:00:01 |
| 32 | TABLE ACCESS BY INDEX ROWID | app | 1 | 23 | | 4 (0)| 00:00:01 |
|* 33 | INDEX UNIQUE SCAN | PK_app | 1 | | | 3 (0)| 00:00:01 |
| 34 | SORT AGGREGATE | | 1 | 23 | | | |
| 35 | TABLE ACCESS BY INDEX ROWID | doc_MAIN | 2 | 46 | | 6 (0)| 00:00:01 |
|* 36 | INDEX RANGE SCAN | IDX_doc_MAIN_DOCCD | 2 | | | 4 (0)| 00:00:01 |
| 37 | SORT AGGREGATE | | 1 | 32 | | | |
| 38 | TABLE ACCESS BY INDEX ROWID | doc_MAIN | 2 | 64 | | 6 (0)| 00:00:01 |
|* 39 | INDEX RANGE SCAN | IDX_doc_MAIN_DOCCD | 2 | | | 4 (0)| 00:00:01 |
| 40 | TABLE ACCESS BY INDEX ROWID | app | 1 | 23 | | 4 (0)| 00:00:01 |
|* 41 | INDEX UNIQUE SCAN | PK_app | 1 | | | 3 (0)| 00:00:01 |
|* 42 | COUNT STOPKEY | | | | | | |
| 43 | VIEW | | 1 | 638 | | 2292K (1)| 07:38:26 |
|* 44 | SORT ORDER BY STOPKEY | | 1 | 191 | | 2292K (1)| 07:38:26 |
|* 45 | FILTER | | | | | | |
| 46 | NESTED LOOPS SEMI | | 1 | 191 | | 2292K (1)| 07:38:25 |
| 47 | NESTED LOOPS SEMI | | 1 | 176 | | 5070 (1)| 00:01:01 |
| 48 | NESTED LOOPS SEMI | | 1 | 153 | | 5067 (1)| 00:01:01 |
|* 49 | TABLE ACCESS BY INDEX ROWID | mission | 1022 | 129K| | 1999 (1)| 00:00:24 |
|* 50 | INDEX RANGE SCAN | IDX_mission_ACTID | 3006 | | | 17 (0)| 00:00:01 |
|* 51 | TABLE ACCESS BY INDEX ROWID | app | 1 | 23 | | 3 (0)| 00:00:01 |
|* 52 | INDEX UNIQUE SCAN | PK_app | 1 | | | 2 (0)| 00:00:01 |
|* 53 | TABLE ACCESS BY INDEX ROWID | app | 1 | 23 | | 3 (0)| 00:00:01 |
|* 54 | INDEX UNIQUE SCAN | PK_app | 1 | | | 2 (0)| 00:00:01 |
|* 55 | VIEW | VW_SQ_1 | 378M| 5411M| | 2286K (1)| 07:37:24 |
|* 56 | FILTER | | | | | | |
|* 57 | HASH JOIN | | 460M| 12G| 5377M| 2286K (1)| 07:37:24 |
| 58 | TABLE ACCESS FULL | doc_MAIN | 165M| 3479M| | 827K (1)| 02:45:33 |
| 59 | INDEX FAST FULL SCAN | IDX_doc_PAGdoc | 474M| 3167M| | 757K (1)| 02:31:32 |
|* 60 | INDEX RANGE SCAN | PK_pitem | 1 | 24 | | 4 (0)| 00:00:01 |
| 61 | SORT UNIQUE | | 2 | 90 | | 52 (43)| 00:00:01 |
| 62 | UNION-ALL | | | | | | |
|* 63 | FILTER | | | | | | |
|* 64 | FILTER | | | | | | |
| 65 | NESTED LOOPS | | 1 | 49 | | 4 (0)| 00:00:01 |
|* 66 | TABLE ACCESS BY INDEX ROWID| user | 1 | 28 | | 2 (0)| 00:00:01 |
|* 67 | INDEX UNIQUE SCAN | P_KEY_2 | 1 | | | 1 (0)| 00:00:01 |
|* 68 | TABLE ACCESS BY INDEX ROWID| popedom | 1 | 21 | | 2 (0)| 00:00:01 |
|* 69 | INDEX RANGE SCAN | P_KEY_1 | 1 | | | 1 (0)| 00:00:01 |
| 70 | SORT AGGREGATE | | 1 | 27 | | | |
|* 71 | TABLE ACCESS BY INDEX ROWID | pitem | 1 | 27 | | 5 (0)| 00:00:01 |
|* 72 | INDEX RANGE SCAN | IDX_pitem_EDORACCEPTNO | 1 | | | 4 (0)| 00:00:01 |
| 73 | SORT AGGREGATE | | 1 | 27 | | | |
|* 74 | TABLE ACCESS BY INDEX ROWID | pitem | 1 | 27 | | 5 (0)| 00:00:01 |
|* 75 | INDEX RANGE SCAN | IDX_pitem_EDORACCEPTNO | 1 | | | 4 (0)| 00:00:01 |
| 76 | SORT AGGREGATE | | 1 | 36 | | | |
|* 77 | TABLE ACCESS BY INDEX ROWID | bmission | 1 | 36 | | 8 (0)| 00:00:01 |
|* 78 | INDEX RANGE SCAN | IDX_bmission_MP1 | 6 | | | 4 (0)| 00:00:01 |
| 79 | HASH UNIQUE | | 1 | 31 | | 6 (17)| 00:00:01 |
|* 80 | TABLE ACCESS BY INDEX ROWID | pitem | 1 | 31 | | 5 (0)| 00:00:01 |
|* 81 | INDEX RANGE SCAN | IDX_pitem_EDORACCEPTNO | 1 | | | 4 (0)| 00:00:01 |
| 82 | SORT AGGREGATE | | 1 | 36 | | | |
|* 83 | TABLE ACCESS BY INDEX ROWID | bmission | 1 | 36 | | 8 (0)| 00:00:01 |
|* 84 | INDEX RANGE SCAN | IDX_bmission_MP1 | 6 | | | 4 (0)| 00:00:01 |
| 85 | HASH UNIQUE | | 1 | 31 | | 6 (17)| 00:00:01 |
|* 86 | TABLE ACCESS BY INDEX ROWID | pitem | 1 | 31 | | 5 (0)| 00:00:01 |
|* 87 | INDEX RANGE SCAN | IDX_pitem_EDORACCEPTNO | 1 | | | 4 (0)| 00:00:01 |
|* 88 | FILTER | | | | | | |
|* 89 | FILTER | | | | | | |
| 90 | NESTED LOOPS | | 1 | 41 | | 4 (0)| 00:00:01 |
|* 91 | TABLE ACCESS BY INDEX ROWID| user | 1 | 28 | | 2 (0)| 00:00:01 |
|* 92 | INDEX UNIQUE SCAN | P_KEY_2 | 1 | | | 1 (0)| 00:00:01 |
|* 93 | TABLE ACCESS BY INDEX ROWID| popedom | 1 | 13 | | 2 (0)| 00:00:01 |
|* 94 | INDEX RANGE SCAN | P_KEY_1 | 1 | | | 1 (0)| 00:00:01 |
| 95 | SORT AGGREGATE | | 1 | 36 | | | |
|* 96 | TABLE ACCESS BY INDEX ROWID | bmission | 1 | 36 | | 8 (0)| 00:00:01 |
|* 97 | INDEX RANGE SCAN | IDX_bmission_MP1 | 6 | | | 4 (0)| 00:00:01 |
| 98 | HASH UNIQUE | | 1 | 31 | | 6 (17)| 00:00:01 |
|* 99 | TABLE ACCESS BY INDEX ROWID | pitem | 1 | 31 | | 5 (0)| 00:00:01 |
|*100 | INDEX RANGE SCAN | IDX_pitem_EDORACCEPTNO | 1 | | | 4 (0)| 00:00:01 |
| 101 | SORT AGGREGATE | | 1 | 36 | | | |
|*102 | TABLE ACCESS BY INDEX ROWID | bmission | 1 | 36 | | 8 (0)| 00:00:01 |
|*103 | INDEX RANGE SCAN | IDX_bmission_MP1 | 6 | | | 4 (0)| 00:00:01 |
| 104 | HASH UNIQUE | | 1 | 31 | | 6 (17)| 00:00:01 |
|*105 | TABLE ACCESS BY INDEX ROWID | pitem | 1 | 31 | | 5 (0)| 00:00:01 |
|*106 | INDEX RANGE SCAN | IDX_pitem_EDORACCEPTNO | 1 | | | 4 (0)| 00:00:01 |
| 107 | NESTED LOOPS | | 1 | 54 | | 3 (0)| 00:00:01 |
|*108 | INDEX RANGE SCAN | rrela_KEY_1 | 2 | 64 | | 1 (0)| 00:00:01 |
| 109 | INLIST ITERATOR | | | | | | |
|*110 | INDEX UNIQUE SCAN | mrela_KEY_1 | 1 | 22 | | 1 (0)| 00:00:01 |
| 111 | NESTED LOOPS | | 1 | 58 | | 3 (0)| 00:00:01 |
|*112 | INDEX UNIQUE SCAN | PK_LDCOM | 1 | 11 | | 1 (0)| 00:00:01 |
|*113 | TABLE ACCESS BY INDEX ROWID | code | 1 | 47 | | 2 (0)| 00:00:01 |
|*114 | INDEX RANGE SCAN | PK_code | 1 | | | 1 (0)| 00:00:01 |
|*115 | TABLE ACCESS BY INDEX ROWID | LCCONT | 1 | 23 | | 4 (0)| 00:00:01 |
|*116 | INDEX UNIQUE SCAN | PK_LCCONT | 1 | | | 3 (0)| 00:00:01 |
|*117 | COUNT STOPKEY | | | | | | |
|*118 | INDEX RANGE SCAN | PK_pitem | 1 | 42 | | 4 (0)| 00:00:01 |
|*119 | COUNT STOPKEY | | | | | | |
|*120 | INDEX RANGE SCAN | PK_pitem | 1 | 24 | | 4 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------------
......
......
......
Note
-----
- SQL profile 59zyu0jtuz0v7 used for this statement
看來還是用了sql profile的，但問題是儲存照片影像資訊的兩張大表走的是全表掃描和索引FFS，而且是在迴圈裡做這件事。
不但跑不出來，而且大大消耗臨時表空間，致使資料庫一些SQL直接報臨時表空間不足。
執行計劃主要區別
正常的執行計劃（cursor_sharing=exact）
| 54 | VIEW PUSHED PREDICATE | VW_SQ_1 | 1 | 2 | 9 (0)| 00:00:01 |
| 55 | NESTED LOOPS | | 1 | 29 | 9 (0)| 00:00:01 |
| 56 | TABLE ACCESS BY INDEX ROWID | doc_MAIN | 1 | 22 | 6 (0)| 00:00:01 |
|* 57 | INDEX RANGE SCAN | IDX_doc_MAIN_DOCCD | 2 | | 4 (0)| 00:00:01 |
|* 58 | INDEX RANGE SCAN | IDX_doc_PAGdoc | 1 | 7 | 3 (0)| 00:00:01 |
異常的執行計劃（cursor_sharing=force）
|* 55 | VIEW | VW_SQ_1 | 378M| 5411M| | 2286K (1)| 07:37:24 |
|* 56 | FILTER | | | | | | |
|* 57 | HASH JOIN | | 460M| 12G| 5377M| 2286K (1)| 07:37:24 |
| 58 | TABLE ACCESS FULL | doc_MAIN | 165M| 3479M| | 827K (1)| 02:45:33 |
| 59 | INDEX FAST FULL SCAN | IDX_doc_PAGdoc | 474M| 3167M| | 757K (1)| 02:31:32 |
很奇怪為什麼profile裡的hint被忽略了。
總之就是為非繫結變數SQL建立的sql profile，對oracle自己改寫的SQL支援有問題。
於是plan A來了
建立一個針對oracle強制繫結變數改寫的SQL的sql profile，設定force_match為false。這樣兩個SQL兩個sql profile不就好了麼。
結果，強制繫結變數的SQL按新的sql profile執行，非常爽。
但是由於兩種SQL的force_matching_signature一樣，因此當建立第二個sql profile的時候，之前那個force_match為true的sql profile消失了。
既然一個force_matching_signature只能有一個sql profile，而且強制繫結變數的SQL已經有一個理想的執行計劃在共享池了，乾脆把它做成sql baseline把。
於是有了plan B：
1 將強制繫結變數的SQL的理想執行計劃做成sql baseline
2 重新建立早先給非繫結變數版本用的sql profile
結果，現實太殘酷了，sql profile的優先順序高於sql baseline。強制繫結變數的SQL還是走了sql profile，然後跑不出結果了。問題依舊。
於是痛定思痛，仔細對比outline發現區別
正常執行計劃的outline
PUSH_PRED(@"SEL$D8F4EB61" "VW_SQ_1"@"SEL$CEAA7324" 7)
INDEX_RS_ASC(@"SEL$700B301E" "doc_MAIN"@"SEL$42" ("doc_MAIN"."DOCCODE" "doc_MAIN"."BUSSTYPE" "doc_MAIN"."SUBTYPE"))
INDEX(@"SEL$700B301E" "doc_PAGES"@"SEL$41" ("doc_PAGES"."DOCID"))
LEADING(@"SEL$700B301E" "doc_MAIN"@"SEL$42" "doc_PAGES"@"SEL$41")
USE_NL(@"SEL$700B301E" "doc_PAGES"@"SEL$41")
異常執行計劃的outline
FULL(@"SEL$1B0ECD80" "doc_MAIN"@"SEL$42")
INDEX_FFS(@"SEL$1B0ECD80" "doc_PAGES"@"SEL$41" ("doc_PAGES"."DOCID"))
LEADING(@"SEL$1B0ECD80" "doc_MAIN"@"SEL$42" "doc_PAGES"@"SEL$41")
USE_HASH(@"SEL$1B0ECD80" "doc_PAGES"@"SEL$41")
原來hint裡程式碼塊的別名在設定cursor_sharing=force後發生了變化。從700B301E變成1B0ECD80了，最重要的還是異常執行計劃裡沒有謂詞推入PUSH_PRED。
經過在審計庫（與生產庫只差一個月資料）反覆測試，就算用700B301E和1B0ECD80兩個別名寫兩段正確的OUTLINE放到sql profile裡，還是不行，根本不起作用。
最後的解決方案是直接讓這個子查詢no_unnest，倒是把問題解決了。。。
順便把有時候走and-equal的那個索引也給禁了，保證它只走一個最好的索引，並且禁用了索引轉點陣圖。
最終在sql profile里加上這段，繫結變數和非繫結變數的兩個SQL終於可以適用一個sql profile了。
'OPT_PARAM(''_b_tree_bitmap_plans'' ''false'')',
'no_unnest(@sel$41)',
'no_index(@"SEL$8" "pitem"@"SEL$8" pitem_INDEX_3)',
'no_index(@"SEL$9" "pitem"@"SEL$9" pitem_INDEX_3)',
'no_index(@"SEL$25" "pitem"@"SEL$25" pitem_INDEX_3)',
'no_index(@"SEL$27" "pitem"@"SEL$27" pitem_INDEX_3)',
'no_index(@"SEL$28" "pitem"@"SEL$28" pitem_INDEX_3)',
'no_index(@"SEL$29" "pitem"@"SEL$29" pitem_INDEX_3)',
'no_index(@"SEL$33" "pitem"@"SEL$33" pitem_INDEX_3)',
'no_index(@"SEL$35" "pitem"@"SEL$35" pitem_INDEX_3)',
'no_index(@"SEL$44" "pitem"@"SEL$44" pitem_INDEX_3)',
'no_index(@"SEL$40" "pitem"@"SEL$40" pitem_INDEX_3)',
'no_index(@"SEL$22" "pitem"@"SEL$22" pitem_INDEX_3)',
'no_index(@"SEL$10" "pitem"@"SEL$10" pitem_INDEX_3)',
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26239116/viewspace-1392005/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 檢視SQL PROFILE使用的HINTSQL
- oracle 通過sql profile為sql語句加hintOracleSQL
- cursor_sharing : exact , force , similarMILA
- ORACLE 部分HINTOracle
- 小心設定cursor_sharing=force引數
- Mysql 會導致索引失效的情況MySql索引
- sql最佳化-錯誤強制型別轉換導致索引失效SQL型別索引
- 補充：小心設定cursor_sharing=force引數
- 一次oracle sql調優的經歷（隱士轉換導致索引失效）OracleSQL索引
- 關於 Laravel mix 導致 Bootstrap 失效的問題Laravelboot
- Grant許可權導致執行計劃失效
- Oracle隱式型別轉換導致索引失效Oracle型別索引
- 【SQL Profile】coe_xfr_sql_profile.sql內容SQL
- oracle實驗記錄 （cursor_sharing(1)exact&force）Oracle
- sql profileSQL
- 【sql hint 提示】SQL
- sql hint articleSQL
- Oralce SQL hintSQL
- sqlldr 匯入重複資料導致PK INDEX失效SQLIndex
- WebMagic多執行緒導致註解失效問題Web執行緒
- @Transactional 中使用執行緒鎖導致了鎖失效執行緒
- [20150513]函式索引與CURSOR_SHARING=FORCE函式索引
- 自定義導航欄返回按鈕導致返回手勢失效問題
- sql profile使用SQL
- MySQL SQL hint 提示MySql
- WPF 已知問題 監聽 WMI 事件導致觸控失效事件
- [20221008]sql profile最佳化失效問題.txtSQL
- Oracle 11g 中 cursor_sharing 設定為SIMILAR 導致的問題OracleMILA
- 低效sql導致DB負載很高SQL負載
- Oracle中的sql hintOracleSQL
- ORACLE SQL PROFILE使用OracleSQL
- SQL PROFILE 測試SQL
- sql profile的使用SQL
- 【HINT】使用“NO_INDEX ”Hint提示避免SQL使用特定索引IndexSQL索引
- 案例：DG主庫未設定force logging導致備庫壞塊
- [20241012]cursor_sharing=force與函式索引.txt函式索引
- sql中使用函式導致cost高SQL函式
- 探究 CSS 混合模式\濾鏡導致 CSS 3D 失效問題CSS模式3D