10203升級到10205後個別SQL效能下降

yangtingkun發表於2011-02-22

客戶的資料庫從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.

這個SQLCOST已經達到了233M,難怪幾個小時也出不來結果。

做了這個SQLAWR報告,找到升級以前的執行計劃:

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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章