11G訪問DBA_OBJECTS和V$LOCK檢視時HANG住

yangtingkun發表於2008-08-27

11g也存在訪問資料字典出現長時間等待的問題。

 

 

今天在檢查11g被鎖物件時,發現了這個問題。資料庫版本Oracle rac 11.1.0.6 for Solaris sparc64

SQL> SELECT * FROM V$VERSION;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for Solaris: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

下面看看造成問題的SQL

SQL> SET TIMING ON
SQL> SET AUTOT ON
SQL> SELECT OWNER, OBJECT_NAME FROM DBA_OBJECTS
  2  WHERE OBJECT_ID IN (SELECT ID1 FROM V$LOCK WHERE SID = 305);

OWNER                          OBJECT_NAME
------------------------------ ------------------------------
SYS                            ORA$BASE
TEST                           T_PARALLEL

已用時間:  00: 26: 49.82

執行計劃
----------------------------------------------------------
Plan hash value: 444070136

---------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                 |   660 | 63360 |   181   (5)| 00:00:03 |
|*  1 |  FILTER                         |                 |       |       |            |          |
|   2 |   VIEW                          | DBA_OBJECTS     | 65980 |  6185K|   181   (5)| 00:00:03 |
|   3 |    UNION-ALL                    |                 |       |       |            |          |
|*  4 |     FILTER                      |                 |       |       |            |          |
|*  5 |      HASH JOIN                  |                 | 71138 |  8475K|   178   (5)| 00:00:03 |
|   6 |       TABLE ACCESS FULL         | USER$           |    87 |  1479 |     3   (0)| 00:00:01 |
|*  7 |       HASH JOIN                 |                 | 71138 |  7294K|   174   (5)| 00:00:03 |
|   8 |        INDEX FULL SCAN          | I_USER2         |    87 |  2001 |     1   (0)| 00:00:01 |
|*  9 |        TABLE ACCESS FULL        | OBJ$            | 71138 |  5696K|   172   (4)| 00:00:03 |
|* 10 |      TABLE ACCESS BY INDEX ROWID| IND$            |     1 |     8 |     2   (0)| 00:00:01 |
|* 11 |       INDEX UNIQUE SCAN         | I_IND1          |     1 |       |     1   (0)| 00:00:01 |
|  12 |      NESTED LOOPS               |                 |     1 |    28 |     2   (0)| 00:00:01 |
|* 13 |       INDEX FULL SCAN           | I_USER2         |     1 |    20 |     1   (0)| 00:00:01 |
|* 14 |       INDEX RANGE SCAN          | I_OBJ4          |     1 |     8 |     1   (0)| 00:00:01 |
|  15 |     NESTED LOOPS                |                 |     2 |    86 |     3   (0)| 00:00:01 |
|  16 |      INDEX FULL SCAN            | I_LINK1         |     2 |    52 |     1   (0)| 00:00:01 |
|  17 |      TABLE ACCESS CLUSTER       | USER$           |     1 |    17 |     1   (0)| 00:00:01 |
|* 18 |       INDEX UNIQUE SCAN         | I_USER#         |     1 |       |     0   (0)| 00:00:01 |
|  19 |   NESTED LOOPS                  |                 |     1 |    76 |     1 (100)| 00:00:01 |
|* 20 |    HASH JOIN                    |                 |     1 |    57 |     1 (100)| 00:00:01 |
|* 21 |     FIXED TABLE FULL            | X$KSUSE         |     1 |    32 |     0   (0)| 00:00:01 |
|  22 |     VIEW                        | GV$_LOCK        |    10 |   250 |     0   (0)| 00:00:01 |
|  23 |      UNION-ALL                  |                 |       |       |            |          |
|* 24 |       FILTER                    |                 |       |       |            |          |
|  25 |        VIEW                     | GV$_LOCK1       |     2 |   178 |     0   (0)| 00:00:01 |
|  26 |         UNION-ALL               |                 |       |       |            |          |
|* 27 |          FIXED TABLE FULL       | X$KDNSSF        |     1 |   102 |     0   (0)| 00:00:01 |
|* 28 |          FIXED TABLE FULL       | X$KSQEQ         |     1 |   102 |     0   (0)| 00:00:01 |
|* 29 |       FIXED TABLE FULL          | X$KTADM         |     1 |   102 |     0   (0)| 00:00:01 |
|* 30 |       FIXED TABLE FULL          | X$KTATRFIL      |     1 |   102 |     0   (0)| 00:00:01 |
|* 31 |       FIXED TABLE FULL          | X$KTATRFSL      |     1 |   102 |     0   (0)| 00:00:01 |
|* 32 |       FIXED TABLE FULL          | X$KTATL         |     1 |   102 |     0   (0)| 00:00:01 |
|* 33 |       FIXED TABLE FULL          | X$KTSTUSC       |     1 |   102 |     0   (0)| 00:00:01 |
|* 34 |       FIXED TABLE FULL          | X$KTSTUSS       |     1 |   102 |     0   (0)| 00:00:01 |
|* 35 |       FIXED TABLE FULL          | X$KTSTUSG       |     1 |   102 |     0   (0)| 00:00:01 |
|* 36 |       FIXED TABLE FULL          | X$KTCXB         |     1 |   102 |     0   (0)| 00:00:01 |
|* 37 |    FIXED TABLE FIXED INDEX      | X$KSQRS (ind:1) |     1 |    19 |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter( EXISTS (SELECT 0 FROM SYS."X$KSQRS" "R",SYS."X$KSUSE" "S", ( (SELECT
              USERENV('INSTANCE') "INST_ID","LADDR" "LADDR","KADDR" "KADDR","SADDR" "SADDR","RADDR"
              "RADDR","LMODE" "LMODE","REQUEST" "REQUEST","CTIME" "CTIME","BLOCK" "BLOCK" FROM  (
              (SELECT "INST_ID" "INST_ID","ADDR" "LADDR","KSQLKADR" "KADDR","KSQLKSES"
              "SADDR","KSQLKRES" "RADDR","KSQLKMOD" "LMODE","KSQLKREQ" "REQUEST","KSQLKCTIM"
              "CTIME","KSQLKLBLK" "BLOCK" FROM SYS."X$KDNSSF" "X$KDNSSF" WHERE ("KSQLKMOD"<>0 OR
              "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0) UNION ALL
              (SELECT "INST_ID" "INST_ID","ADDR" "LADDR","KSQLKADR" "KADDR","KSQLKSES"
              "SADDR","KSQLKRES" "RADDR","KSQLKMOD" "LMODE","KSQLKREQ" "REQUEST","KSQLKCTIM"
              "CTIME","KSQLKLBLK" "BLOCK" FROM SYS."X$KSQEQ" "X$KSQEQ" WHERE ("KSQLKMOD"<>0 OR
              "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)) "GV$_LOCK1"
              WHERE USERENV('INSTANCE') IS NOT NULL) UNION ALL  (SELECT "INST_ID" "INST_ID","ADDR"
              "LADDR","KSQLKADR" "KADDR","KSQLKSES" "SADDR","KSQLKRES" "RADDR","KSQLKMOD"
              "LMODE","KSQLKREQ" "REQUEST","KSQLKCTIM" "CTIME","KSQLKLBLK" "BLOCK" FROM SYS."X$KTADM"
              "X$KTADM" WHERE ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
              BITAND("KSSOBFLG",1)<>0) UNION ALL  (SELECT "INST_ID" "INST_ID","ADDR" "LADDR","KSQLKADR"
              "KADDR","KSQLKSES" "SADDR","KSQLKRES" "RADDR","KSQLKMOD" "LMODE","KSQLKREQ"
              "REQUEST","KSQLKCTIM" "CTIME","KSQLKLBLK" "BLOCK" FROM SYS."X$KTATRFIL" "X$KTATRFIL" WHERE
              ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
              BITAND("KSSOBFLG",1)<>0) UNION ALL  (SELECT "INST_ID" "INST_ID","ADDR" "LADDR","KSQLKADR"
              "KADDR","KSQLKSES" "SADDR","KSQLKRES" "RADDR","KSQLKMOD" "LMODE","KSQLKREQ"
              "REQUEST","KSQLKCTIM" "CTIME","KSQLKLBLK" "BLOCK" FROM SYS."X$KTATRFSL" "X$KTATRFSL" WHERE
              ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
              BITAND("KSSOBFLG",1)<>0) UNION ALL  (SELECT "INST_ID" "INST_ID","ADDR" "LADDR","KSQLKADR"
              "KADDR","KSQLKSES" "SADDR","KSQLKRES" "RADDR","KSQLKMOD" "LMODE","KSQLKREQ"
              "REQUEST","KSQLKCTIM" "CTIME","KSQLKLBLK" "BLOCK" FROM SYS."X$KTATL" "X$KTATL" WHERE
              ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
              BITAND("KSSOBFLG",1)<>0) UNION ALL  (SELECT "INST_ID" "INST_ID","ADDR" "LADDR","KSQLKADR"
              "KADDR","KSQLKSES" "SADDR","KSQLKRES" "RADDR","KSQLKMOD" "LMODE","KSQLKREQ"
              "REQUEST","KSQLKCTIM" "CTIME","KSQLKLBLK" "BLOCK" FROM SYS."X$KTSTUSC" "X$KTSTUSC" WHERE
              ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
              BITAND("KSSOBFLG",1)<>0) UNION ALL  (SELECT "INST_ID" "INST_ID","ADDR" "LADDR","KSQLKADR"
              "KADDR","KSQLKSES" "SADDR","KSQLKRES" "RADDR","KSQLKMOD" "LMODE","KSQLKREQ"
              "REQUEST","KSQLKCTIM" "CTIME","KSQLKLBLK" "BLOCK" FROM SYS."X$KTSTUSS" "X$KTSTUSS" WHERE
              ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
              BITAND("KSSOBFLG",1)<>0) UNION ALL  (SELECT "INST_ID" "INST_ID","ADDR" "LADDR","KSQLKADR"
              "KADDR","KSQLKSES" "SADDR","KSQLKRES" "RADDR","KSQLKMOD" "LMODE","KSQLKREQ"
              "REQUEST","KSQLKCTIM" "CTIME","KSQLKLBLK" "BLOCK" FROM SYS."X$KTSTUSG" "X$KTSTUSG" WHERE
              ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
              BITAND("KSSOBFLG",1)<>0) UNION ALL  (SELECT "INST_ID" "INST_ID","KTCXBXBA"
              "LADDR","KTCXBLKP" "KADDR","KSQLKSES" "SADDR","KSQLKRES" "RADDR","KSQLKMOD"
              "LMODE","KSQLKREQ" "REQUEST","KSQLKCTIM" "CTIME","KSQLKLBLK" "BLOCK" FROM SYS."X$KTCXB"
              "X$KTCXB" WHERE ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
              BITAND("KSSPAFLG",1)<>0)) "GV$_LOCK" WHERE "SADDR"="S"."ADDR" AND "S"."KSUSENUM"=305 AND
              "S"."INST_ID"=USERENV('INSTANCE') AND "R"."KSQRSID1"=:B1 AND "RADDR"="R"."ADDR"))
   4 - filter(("O"."TYPE#"<>1 AND "O"."TYPE#"<>10 OR "O"."TYPE#"=1 AND  (SELECT 1 FROM
              "SYS"."IND$" "I" WHERE "I"."OBJ#"=:B1 AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3
              OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))=1) AND
              ("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND
              "O"."TYPE#"<>9 AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND
              "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND
              "O"."TYPE#"<>88 OR BITAND("U"."SPARE1",16)=0 OR ("O"."TYPE#"=4 OR "O"."TYPE#"=5 OR
              "O"."TYPE#"=7 OR "O"."TYPE#"=8 OR "O"."TYPE#"=9 OR "O"."TYPE#"=10 OR "O"."TYPE#"=11 OR
              "O"."TYPE#"=12 OR "O"."TYPE#"=13 OR "O"."TYPE#"=14 OR "O"."TYPE#"=22 OR "O"."TYPE#"=87)
              AND (SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2 OR
              "U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR
              EXISTS (SELECT 0 FROM SYS."USER$" "U2",SYS."OBJ$" "O2" WHERE "O2"."OWNER#"="U2"."USER#"
              AND "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"=:B2 AND "U2"."TYPE#"=2 AND
              "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))))
   5 - access("O"."SPARE3"="U"."USER#")
   7 - access("O"."OWNER#"="U"."USER#")
   9 - filter("O"."NAME"<>'_NEXT_OBJECT' AND "O"."NAME"<>'_default_auditing_options_' AND
              "O"."LINKNAME" IS NULL AND BITAND("O"."FLAGS",128)=0)
  10 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR
              "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9)
  11 - access("I"."OBJ#"=:B1)
  13 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edi
              tion_id')))
       filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edi
              tion_id')))
  14 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
  18 - access("L"."OWNER#"="U"."USER#")
  20 - access("SADDR"="S"."ADDR")
  21 - filter("S"."KSUSENUM"=305 AND "S"."INST_ID"=USERENV('INSTANCE'))
  24 - filter(USERENV('INSTANCE') IS NOT NULL)
  27 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
              BITAND("KSSOBFLG",1)<>0)
  28 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
              BITAND("KSSOBFLG",1)<>0)
  29 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
              BITAND("KSSOBFLG",1)<>0)
  30 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
              BITAND("KSSOBFLG",1)<>0)
  31 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
              BITAND("KSSOBFLG",1)<>0)
  32 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
              BITAND("KSSOBFLG",1)<>0)
  33 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
              BITAND("KSSOBFLG",1)<>0)
  34 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
              BITAND("KSSOBFLG",1)<>0)
  35 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
              BITAND("KSSOBFLG",1)<>0)
  36 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
              BITAND("KSSPAFLG",1)<>0)
  37 - filter("R"."KSQRSID1"=:B1 AND "RADDR"="R"."ADDR")


統計資訊
----------------------------------------------------------
         15  recursive calls
      70632  db block gets
        982  consistent gets
          3  physical reads
          0  redo size
        667  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

感覺似乎並不是常見的OracleVIEW進行MERGE導致的執行計劃效率低的問題,從現有的執行計劃上看,兩個檢視並沒有被MERGE

不過從統計資訊上看,就存在很大問題了,這麼簡單的一個查詢怎麼會導致了7萬多的db block gets呢。

而且在這個會話的執行過程中,檢查了會話的等待時間,發現也比較有意思,前後出現了gc cr requestlatch freedb file sequential read等多種等待事件。

而如果改變一下SQL語句的寫法,直接寫成關聯的方式:

SQL> SELECT OWNER, OBJECT_NAME FROM DBA_OBJECTS, V$LOCK
  2  WHERE OBJECT_ID = ID1
  3  AND SID = 305;

OWNER                          OBJECT_NAME
------------------------------ ------------------------------
SYS                            ORA$BASE
TEST                           T_PARALLEL

已用時間:  00: 00: 00.44

執行計劃
----------------------------------------------------------
Plan hash value: 3416262628

---------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                 |     1 |   159 |   182   (5)| 00:00:03 |
|*  1 |  HASH JOIN                      |                 |     1 |   159 |   182   (5)| 00:00:03 |
|   2 |   NESTED LOOPS                  |                 |     1 |    63 |     1 (100)| 00:00:01 |
|*  3 |    HASH JOIN                    |                 |     1 |    44 |     1 (100)| 00:00:01 |
|*  4 |     FIXED TABLE FULL            | X$KSUSE         |     1 |    32 |     0   (0)| 00:00:01 |
|   5 |     VIEW                        | GV$_LOCK        |    10 |   120 |     0   (0)| 00:00:01 |
|   6 |      UNION-ALL                  |                 |       |       |            |          |
|*  7 |       FILTER                    |                 |       |       |            |          |
|   8 |        VIEW                     | GV$_LOCK1       |     2 |   152 |     0   (0)| 00:00:01 |
|   9 |         UNION-ALL               |                 |       |       |            |          |
|* 10 |          FIXED TABLE FULL       | X$KDNSSF        |     1 |   102 |     0   (0)| 00:00:01 |
|* 11 |          FIXED TABLE FULL       | X$KSQEQ         |     1 |   102 |     0   (0)| 00:00:01 |
|* 12 |       FIXED TABLE FULL          | X$KTADM         |     1 |   102 |     0   (0)| 00:00:01 |
|* 13 |       FIXED TABLE FULL          | X$KTATRFIL      |     1 |   102 |     0   (0)| 00:00:01 |
|* 14 |       FIXED TABLE FULL          | X$KTATRFSL      |     1 |   102 |     0   (0)| 00:00:01 |
|* 15 |       FIXED TABLE FULL          | X$KTATL         |     1 |   102 |     0   (0)| 00:00:01 |
|* 16 |       FIXED TABLE FULL          | X$KTSTUSC       |     1 |   102 |     0   (0)| 00:00:01 |
|* 17 |       FIXED TABLE FULL          | X$KTSTUSS       |     1 |   102 |     0   (0)| 00:00:01 |
|* 18 |       FIXED TABLE FULL          | X$KTSTUSG       |     1 |   102 |     0   (0)| 00:00:01 |
|* 19 |       FIXED TABLE FULL          | X$KTCXB         |     1 |   102 |     0   (0)| 00:00:01 |
|* 20 |    FIXED TABLE FIXED INDEX      | X$KSQRS (ind:1) |     1 |    19 |     0   (0)| 00:00:01 |
|  21 |   VIEW                          | DBA_OBJECTS     | 65980 |  6185K|   181   (5)| 00:00:03 |
|  22 |    UNION-ALL                    |                 |       |       |            |          |
|* 23 |     FILTER                      |                 |       |       |            |          |
|* 24 |      HASH JOIN                  |                 | 71138 |  8475K|   178   (5)| 00:00:03 |
|  25 |       TABLE ACCESS FULL         | USER$           |    87 |  1479 |     3   (0)| 00:00:01 |
|* 26 |       HASH JOIN                 |                 | 71138 |  7294K|   174   (5)| 00:00:03 |
|  27 |        INDEX FULL SCAN          | I_USER2         |    87 |  2001 |     1   (0)| 00:00:01 |
|* 28 |        TABLE ACCESS FULL        | OBJ$            | 71138 |  5696K|   172   (4)| 00:00:03 |
|* 29 |      TABLE ACCESS BY INDEX ROWID| IND$            |     1 |     8 |     2   (0)| 00:00:01 |
|* 30 |       INDEX UNIQUE SCAN         | I_IND1          |     1 |       |     1   (0)| 00:00:01 |
|  31 |      NESTED LOOPS               |                 |     1 |    28 |     2   (0)| 00:00:01 |
|* 32 |       INDEX FULL SCAN           | I_USER2         |     1 |    20 |     1   (0)| 00:00:01 |
|* 33 |       INDEX RANGE SCAN          | I_OBJ4          |     1 |     8 |     1   (0)| 00:00:01 |
|  34 |     NESTED LOOPS                |                 |     2 |    86 |     3   (0)| 00:00:01 |
|  35 |      INDEX FULL SCAN            | I_LINK1         |     2 |    52 |     1   (0)| 00:00:01 |
|  36 |      TABLE ACCESS CLUSTER       | USER$           |     1 |    17 |     1   (0)| 00:00:01 |
|* 37 |       INDEX UNIQUE SCAN         | I_USER#         |     1 |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("OBJECT_ID"="R"."KSQRSID1")
   3 - access("SADDR"="S"."ADDR")
.
.
.
  37 - access("L"."OWNER#"="U"."USER#")


統計資訊
----------------------------------------------------------
         15  recursive calls
          3  db block gets
        977  consistent gets
          0  physical reads
          0  redo size
        667  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

這個執行時間和統計資訊是正常的,透過對比也可以發現,第一個查詢的執行計劃確實存在問題。

SQL> SELECT /*+ RULE */ OWNER, OBJECT_NAME FROM DBA_OBJECTS
  2  WHERE OBJECT_ID IN (SELECT ID1 FROM V$LOCK WHERE SID = 305);

OWNER                          OBJECT_NAME
------------------------------ ------------------------------
SYS                            ORA$BASE
TEST                           T_PARALLEL

已用時間:  00: 00: 01.31

執行計劃
----------------------------------------------------------
Plan hash value: 2735497195

----------------------------------------------------------
| Id  | Operation                          | Name        |
----------------------------------------------------------
|   0 | SELECT STATEMENT                   |             |
|   1 |  MERGE JOIN                        |             |
|   2 |   SORT JOIN                        |             |
|   3 |    VIEW                            | DBA_OBJECTS |
|   4 |     UNION-ALL                      |             |
|*  5 |      FILTER                        |             |
|   6 |       NESTED LOOPS                 |             |
|   7 |        NESTED LOOPS                |             |
|   8 |         TABLE ACCESS FULL          | USER$       |
|*  9 |         TABLE ACCESS BY INDEX ROWID| OBJ$        |
|* 10 |          INDEX RANGE SCAN          | I_OBJ5      |
|  11 |        TABLE ACCESS CLUSTER        | USER$       |
|* 12 |         INDEX UNIQUE SCAN          | I_USER#     |
|* 13 |       TABLE ACCESS BY INDEX ROWID  | IND$        |
|* 14 |        INDEX UNIQUE SCAN           | I_IND1      |
|  15 |       NESTED LOOPS                 |             |
|* 16 |        INDEX RANGE SCAN            | I_OBJ4      |
|* 17 |        TABLE ACCESS CLUSTER        | USER$       |
|* 18 |         INDEX UNIQUE SCAN          | I_USER#     |
|  19 |      NESTED LOOPS                  |             |
|  20 |       TABLE ACCESS FULL            | USER$       |
|* 21 |       INDEX RANGE SCAN             | I_LINK1     |
|* 22 |   SORT JOIN                        |             |
|  23 |    VIEW                            | VW_NSO_1    |
|  24 |     SORT UNIQUE                    |             |
|  25 |      MERGE JOIN                    |             |
|  26 |       SORT JOIN                    |             |
|  27 |        MERGE JOIN                  |             |
|  28 |         SORT JOIN                  |             |
|  29 |          FIXED TABLE FULL          | X$KSQRS     |
|* 30 |         SORT JOIN                  |             |
|  31 |          VIEW                      | GV$_LOCK    |
|  32 |           UNION-ALL                |             |
|* 33 |            FILTER                  |             |
|  34 |             VIEW                   | GV$_LOCK1   |
|  35 |              UNION-ALL             |             |
|* 36 |               FIXED TABLE FULL     | X$KDNSSF    |
|* 37 |               FIXED TABLE FULL     | X$KSQEQ     |
|* 38 |            FIXED TABLE FULL        | X$KTADM     |
|* 39 |            FIXED TABLE FULL        | X$KTATRFIL  |
|* 40 |            FIXED TABLE FULL        | X$KTATRFSL  |
|* 41 |            FIXED TABLE FULL        | X$KTATL     |
|* 42 |            FIXED TABLE FULL        | X$KTSTUSC   |
|* 43 |            FIXED TABLE FULL        | X$KTSTUSS   |
|* 44 |            FIXED TABLE FULL        | X$KTSTUSG   |
|* 45 |            FIXED TABLE FULL        | X$KTCXB     |
|* 46 |       SORT JOIN                    |             |
|* 47 |        FIXED TABLE FULL            | X$KSUSE     |
----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - filter(("O"."TYPE#"<>1 AND "O"."TYPE#"<>10 OR "O"."TYPE#"=1 AND
              (SELECT 1 FROM "SYS"."IND$" "I" WHERE "I"."OBJ#"=:B1 AND ("I"."TYPE#"=1
              OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR
.
.
.
  47 - filter("S"."INST_ID"=USERENV('INSTANCE') AND "S"."KSUSENUM"=305)

Note
-----
   - rule based optimizer used (consider using cbo)


統計資訊
----------------------------------------------------------
         15  recursive calls
          3  db block gets
     124231  consistent gets
          0  physical reads
          0  redo size
        667  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          7  sorts (memory)
          0  sorts (disk)
          2  rows processed

和其他類似情況一樣,新增RULEhint能避免問題的產生。

問題在10.2.0.3上也可以再現,但是在9204上則不會出現。

 

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/4227/viewspace-438592/,如需轉載,請註明出處,否則將追究法律責任。

相關文章