ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 11G访问DBA_OBJECTS和V$LOCK视图时HANG住

11G访问DBA_OBJECTS和V$LOCK视图时HANG住

原创 Linux操作系统 作者:yangtingkun 时间:2008-08-27 23:56:26 0 删除 编辑

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

感觉似乎并不是常见的Oracle将VIEW进行MERGE导致的执行计划效率低的问题,从现有的执行计划上看,两个视图并没有被MERGE。

不过从统计信息上看,就存在很大问题了,这么简单的一个查询怎么会导致了7万多的db block gets呢。

而且在这个会话的执行过程中,检查了会话的等待时间,发现也比较有意思,前后出现了gc cr request、latch free、db 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

和其他类似情况一样,添加RULE的hint能避免问题的产生。

问题在10.2.0.3上也可以再现,但是在9204上则不会出现。

 

来自 “ ITPUB博客 ” ,链接:https://blog.itpub.net/4227/viewspace-438592/ ,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1964
  • 访问量
    11085795