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博客 ” ，链接：https://blog.itpub.net/4227/viewspace-687734/ ，如需转载，请注明出处，否则将追究法律责任。
