10203升級到10205後個別SQL效能下降
客戶的資料庫從10203升級到10205後，出現個別SQL效能下降的情況。
原來的SQL只需要十幾秒，而現在幾個小時也得不到結果。
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.5.0 - Productio
NLSRTL Version 10.2.0.5.0 - Production
在當前版本中執行計劃為：
SQL> explain plan for
2 select distinct islcode,gtcode,idx,rmb2,qty
3 from storeslistg g
4 where vtype=10
5 and SLSTATUS='70'
6 and iodate>=to_date('2008-01-01','YYYY-MM-DD')
7 and exists
8 (
9 select ISLCODE,gticode
10 from storeslistg h
11 where vtype=4
12 and iodate>=to_date('2008-01-01','YYYY-MM-DD')
13 and SLSTATUS='70'
14 and gticode=g.gticode
15 and idx=g.idx
16 and gtidx=g.gtidx
17 and (g.rmb2<>h.rmb2
18 or g.qty<>h.qty
19 or g.upric2<>h.upric2)
20 );
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
Plan hash value: 3399640810
------------------------------------------------------------------------------------------
| Id| Operation | Name |Rows|Bytes|Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 1 | 62| 233M (1)|779:29:27 |
|* 1| FILTER | | | | | |
| 2| TABLE ACCESS BY INDEX ROWID| STORESLISTG |176K| 10M|74961 (1)| 00:15:00 |
|* 3| INDEX SKIP SCAN | STORESLISTG_IODATE |176K| | 2016 (3)| 00:00:25 |
|* 4| TABLE ACCESS BY INDEX ROWID| STORESLISTG | 1 | 36 | 1528 (1)| 00:00:19 |
|* 5| INDEX RANGE SCAN | GTICODE_STORESLISTG |3150| | 16 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT 0 FROM "STORESLISTG" "H" WHERE "GTICODE"=:B1 AND "IDX"=:B2
AND "GTIDX"=:B3 AND "VTYPE"=4 AND "IODATE">=TO_DATE(' 2008-01-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "SLSTATUS"='70' AND ("H"."RMB2"<>:B4 OR "H"."QTY"<>:B5 OR
"H"."UPRIC2"<>:B6)))
3 - access("IODATE">=TO_DATE(' 2008-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"SLSTATUS"='70' AND "VTYPE"=10 AND "IODATE" IS NOT NULL)
filter("VTYPE"=10 AND "SLSTATUS"='70')
4 - filter("IDX"=:B1 AND "GTIDX"=:B2 AND "VTYPE"=4 AND "IODATE">=TO_DATE(' 2008-01-01
00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "SLSTATUS"='70' AND ("H"."RMB2"<>:B3 OR
"H"."QTY"<>:B4 OR "H"."UPRIC2"<>:B5))
5 - access("GTICODE"=:B1)
27 rows selected.
這個SQL的COST已經達到了233M，難怪幾個小時也出不來結果。
做了這個SQL的AWR報告，找到升級以前的執行計劃：
WORKLOAD REPOSITORY SQL Report
Snapshot Period Summary
DB Name DB Id Instance Inst Num Release RAC Host
------------ ----------- ------------ -------- ----------- --- ------------
CNDERPDB 2400249746 cnderpdb1 1 10.2.0.3.0 YES p5a1
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 47142 14-Feb-11 01:00:04 70 8.6
End Snap: 47252 18-Feb-11 15:00:29 474 17.1
Elapsed: 6,600.41 (mins)
DB Time: 8,654.33 (mins)
SQL Summary DB/Inst: CNDERPDB/cnderpdb1 Snaps: 47142-47252
Elapsed
SQL Id Time (ms)
------------- ----------
4850c9w6y3a44 162,272
Module: oracledevdb@erpdevdb (TNS V1-V3)
SELECT DISTINCT "A1"."ISLCODE","A1"."GTCODE","A1"."IDX","A1"."RMB2","A1"."QTY" F
ROM "STORESLISTG" "A1" WHERE "A1"."VTYPE"=10 AND "A1"."SLSTATUS"='70' AND "A1"."
IODATE">=TO_DATE('2008-01-01','YYYY-MM-DD') AND EXISTS (SELECT "A2"."ISLCODE","
A2"."GTICODE" FROM "STORESLISTG" "A2" WHERE "A2"."VTYPE"=4 AND "A2"."IODATE">=TO
-------------------------------------------------------------
SQL ID: 4850c9w6y3a44 DB/Inst: CNDERPDB/cnderpdb1 Snaps: 47142-47252
-> 1st Capture and Last Capture Snap IDs
refer to Snapshot IDs witin the snapshot range
-> SELECT DISTINCT "A1"."ISLCODE","A1"."GTCODE","A1"."IDX","A1"."RMB2","A...
Plan Hash Total Elapsed 1st Capture Last Capture
# Value Time(ms) Executions Snap ID Snap ID
--- ---------------- ---------------- ------------- ------------- --------------
1 3362936455 162,272 5 47145 47241
-------------------------------------------------------------
Plan 1(PHV: 3362936455)
-----------------------
Plan Statistics DB/Inst: CNDERPDB/cnderpdb1 Snaps: 47142-47252
-> % Total DB Time is the Elapsed Time of the SQL statement divided
into the Total Database Time multiplied by 100
Stat Name Statement Per Execution % Snap
---------------------------------------- ---------- -------------- -------
Elapsed Time (ms) 162,272 32,454.4 0.0
CPU Time (ms) 91,551 18,310.2 0.0
Executions 5 N/A N/A
Buffer Gets 3,043,251 608,650.2 0.0
Disk Reads 51,304 10,260.8 0.1
Parse Calls 5 1.0 0.0
Rows 45 9.0 N/A
User I/O Wait Time (ms) 65,389 N/A N/A
Cluster Wait Time (ms) 11,514 N/A N/A
Application Wait Time (ms) 0 N/A N/A
Concurrency Wait Time (ms) 3 N/A N/A
Invalidations 0 N/A N/A
Version Count 5 N/A N/A
Sharable Mem(KB) 252 N/A N/A
-------------------------------------------------------------
Execution Plan
--------------------------------------------------------------------------------------------
|Id|Operation |Name |Rows |Bytes|TempSpc|Cost(%CPU)|Time |
--------------------------------------------------------------------------------------------
| 0|SELECT STATEMENT | | | | | 124K(100)| |
| 1| HASH UNIQUE | | 834|81732| | 124K (1)| 0:24:56|
| 2| HASH JOIN | | 834|81732| 2984K| 124K (1)|00:24:56|
| 3| TABLE ACCESS BY INDEX ROWID|STORESLISTG |63561|2234K| |62124 (1)|00:12:26|
| 4| INDEX SKIP SCAN |STORESLISTG_IODATE| 138K| | | 2158 (2)|00:00:26|
| 5| TABLE ACCESS BY INDEX ROWID|STORESLISTG |63561|3848K| |62124 (1)|00:12:26|
| 6| INDEX SKIP SCAN |STORESLISTG_IODATE| 138K| | | 2158 (2)|00:00:26|
--------------------------------------------------------------------------------------------
Full SQL Text
SQL ID SQL Text
------------ -----------------------------------------------------------------
4850c9w6y3a4 SELECT DISTINCT "A1"."ISLCODE", "A1"."GTCODE", "A1"."IDX", "A1"."
"RMB2", "A1"."QTY" FROM "STORESLISTG" "A1" WHERE "A1"."VTYPE"=10
AND "A1"."SLSTATUS"='70' AND "A1"."IODATE">=TO_DATE('2008-01-01',
'YYYY-MM-DD') AND EXISTS (SELECT "A2"."ISLCODE", "A2"."GTICODE"
FROM "STORESLISTG" "A2" WHERE "A2"."VTYPE"=4 AND "A2"."IODATE">=T
O_DATE('2008-01-01', 'YYYY-MM-DD') AND "A2"."SLSTATUS"='70' AND "
A2"."GTICODE"="A1"."GTICODE" AND "A2"."IDX"="A1"."IDX" AND "A2"."
GTIDX"="A1"."GTIDX" AND ("A1"."RMB2"<>"A2"."RMB2" OR "A1"."QTY"<>
"A2"."QTY" OR "A1"."UPRIC2"<>"A2"."UPRIC2"))
Report written to awrsqlrpt_1_47142_47252.txt
升級以前是HASH JOIN，顯然效率要高得多。可是由於這種SQL的寫法，導致新增HINT沒有辦法是的這個查詢使用HASH JOIN，為了獲取到10203中的執行計劃，最終使用了提示OPTIMIZER_FEATURES_ENABLE：
SQL> explain plan for
2 select /*+ optimizer_features_enable('10.2.0.3') */
3 distinct islcode,gtcode,idx,rmb2,qty
4 from storeslistg g
5 where vtype=10
6 and SLSTATUS='70'
7 and iodate>=to_date('2008-01-01','YYYY-MM-DD')
8 and exists
9 (
10 select ISLCODE,gticode
11 from storeslistg h
12 where vtype=4
13 and iodate>=to_date('2008-01-01','YYYY-MM-DD')
14 and SLSTATUS='70'
15 and gticode=g.gticode
16 and idx=g.idx
17 and gtidx=g.gtidx
18 and (g.rmb2<>h.rmb2
19 or g.qty<>h.qty
20 or g.upric2<>h.upric2)
21 );
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------
Plan hash value: 3362936455
--------------------------------------------------------------------------------------------------------
|Id| Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 1139 | 109K| | 150K (1)| 00:30:05 |
| 1| HASH UNIQUE | | 1139 | 109K| | 150K (1)| 00:30:05 |
|*2| HASH JOIN | | 1139 | 109K| 3728K| 150K (1)| 00:30:05 |
|*3| TABLE ACCESS BY INDEX ROWID| STORESLISTG | 79468 | 2793K| | 74961 (1)| 00:15:00 |
|*4| INDEX SKIP SCAN | STORESLISTG_IODATE | 176K| | | 2016 (3)| 00:00:25 |
|*5| TABLE ACCESS BY INDEX ROWID| STORESLISTG | 79468 | 4811K| | 74961 (1)| 00:15:00 |
|*6| INDEX SKIP SCAN | STORESLISTG_IODATE | 176K| | | 2016 (3)| 00:00:25 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("GTICODE"="G"."GTICODE" AND "IDX"="G"."IDX" AND "GTIDX"="G"."GTIDX")
filter("G"."RMB2"<>"H"."RMB2" OR "G"."QTY"<>"H"."QTY" OR "G"."UPRIC2"<>"H"."UPRIC2")
3 - filter("GTIDX" IS NOT NULL AND "GTICODE" IS NOT NULL)
4 - access("IODATE">=TO_DATE(' 2008-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"SLSTATUS"='70' AND "VTYPE"=4 AND "IODATE" IS NOT NULL)
filter("VTYPE"=4 AND "SLSTATUS"='70')
5 - filter("G"."GTIDX" IS NOT NULL AND "G"."GTICODE" IS NOT NULL)
6 - access("IODATE">=TO_DATE(' 2008-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"SLSTATUS"='70' AND "VTYPE"=10 AND "IODATE" IS NOT NULL)
filter("VTYPE"=10 AND "SLSTATUS"='70')
27 rows selected.
最終SQL恢復了原始的執行計劃，大概10多秒的時間就可以得到結果。而OPTIMIZER_FEATURES_ENABLE提示有同名的初始化引數，可以在系統和會話級進行設定，Oracle正是為了升級後保證執行計劃的穩定性而推出了這個功能的。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/4227/viewspace-687734/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 10204升級到10205後同一sql報告ora-01719SQL
- 升級win10 1903系統後效能下降如何解決Win10
- Delete大量資料後SQL Server效能下降deleteSQLServer
- 9.2.0.4 升級到10.2.0.5升級後 Oracle Ultra Search 元件NO SCRIPTOracle元件
- [轉帖]使用perf解決JDK8U小版本升級後效能下降的問題JDK
- 10G遷移升級到11G使用SPA 分析SQL效能例項SQL
- Mac升級到high sierra後cocoapods失效Mac
- ORACLE EXADATA升級—從11.2.3.1.0到11.2.3.3.0–（9）升級後的檢查Oracle
- 資料倉儲環境下謹慎升級10205補丁
- SQL效能的度量 - 語句級別的SQL跟蹤autotraceSQL
- SQL效能的度量 - 會話級別的SQL跟蹤sql_traceSQL會話
- spring升級到3.1.1 hibernate升級到4備忘Spring
- Oracle9i 升級到10g 後，大多數SQL變慢的問題OracleSQL
- mongodb單機從3.2升級到4.0.4升級MongoDB
- 升級到 Pulsar3.0 後深入瞭解 JWT 鑑權JWT
- 升級Spring Cloud到Finchley後的一點坑SpringCloud
- oracle 10 rac 升級 10.2.0.1升級到10.2.0.5Oracle
- 升級check SQL01SQL
- PR效能測試工具升級到全鏈路效能測試與分析平臺
- CentOS升級MySQL到5.5CentOSMySql
- yum升級php到5.3PHP
- oracle 升級到 11.2.0.2Oracle
- MySQL 5.7 升級到 8.0MySql
- [轉帖]JDK 從8升級到11，使用 G1 GC，HBase 效能下降近20%。JDK 到底幹了什麼原創JDKGC
- SQL Server升級和遷移的三個技巧GZSQLServer
- 升級到PHP5.4.3遇到的一個錯誤PHP
- Linux環境下CRS升級到10.2.0.4.2 Database升級到10.2.0.4.3LinuxDatabase
- mysql 5.6 升級 到 5.7 的二進位制升級方法 另一個簡便思路.MySql
- MacOS升級到Monterey後python SSL握手失敗問題MacPython
- iOS一定要升級到最新的背後真相大揭秘iOS
- Windows 系統安裝Oracle升級到9.2.0.8 後，exp問題WindowsOracle
- ArchLinux/Manjaro升級到6.9核心後的問題解決LinuxJAR
- ABP Framework 手動升級指南：從6.0.1升級到7.0.0Framework
- oracle資料庫升級11.2.0.3升級到11.2.0.4Oracle資料庫
- [網路資料] Oracle9i 升級到10g後的效能問題 - 引數調整Oracle
- 單機升級11.2.0.4到12.1.0.2的實戰__catupgrd.sqlSQL
- Mac 升級 PHP 到 7.4 版本MacPHP
- ccproject升級到1135Project