查询v$lock缓慢和direct path write temp等待

v$lock是常用的enqueue lock队列锁动态性能视图,不管是用户自己部署的监控脚本也好、还是enterprise manager都多少会使用到该V$LOCK视图, 但是在10g中遇到了v$lock查询缓慢的问题, 例如下面的查询会等待较多direct path write temp等待事件:

 

 

select count(*) from v$lock; 

  COUNT(*) 
---------- 
       163 

Elapsed: 00:00:60.90 

Execution Plan 
---------------------------------------------------------- 
Plan hash value: 2384831130 

-------------------------------------------------------------------------------------- 
| Id  | Operation               | Name       | Rows  | Bytes | Cost (%CPU)| Time     | 
-------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT        |            |     1 |    50 |     1 (100)| 00:00:01 | 
|   1 |  SORT AGGREGATE         |            |     1 |    50 |            |          | 
|*  2 |   HASH JOIN             |            |     1 |    50 |     1 (100)| 00:00:01 | 
|   3 |    MERGE JOIN CARTESIAN |            |   100 |  3800 |     0   (0)| 00:00:01 | 
|*  4 |     FIXED TABLE FULL    | X$KSUSE    |     1 |    19 |     0   (0)| 00:00:01 | 
|   5 |     BUFFER SORT         |            |   100 |  1900 |     0   (0)| 00:00:01 | 
|   6 |      FIXED TABLE FULL   | X$KSQRS    |   100 |  1900 |     0   (0)| 00:00:01 | 
|   7 |    VIEW                 | GV$_LOCK   |    10 |   120 |     0   (0)| 00:00:01 | 
|   8 |     UNION-ALL           |            |       |       |            |          | 
|*  9 |      FILTER             |            |       |       |            |          | 
|  10 |       VIEW              | GV$_LOCK1  |     2 |    24 |     0   (0)| 00:00:01 | 
|  11 |        UNION-ALL        |            |       |       |            |          | 
|* 12 |         FIXED TABLE FULL| X$KDNSSF   |     1 |    64 |     0   (0)| 00:00:01 | 
|* 13 |         FIXED TABLE FULL| X$KSQEQ    |     1 |    64 |     0   (0)| 00:00:01 | 
|* 14 |      FIXED TABLE FULL   | X$KTADM    |     1 |    64 |     0   (0)| 00:00:01 | 
|* 15 |      FIXED TABLE FULL   | X$KTATRFIL |     1 |    64 |     0   (0)| 00:00:01 | 
|* 16 |      FIXED TABLE FULL   | X$KTATRFSL |     1 |    64 |     0   (0)| 00:00:01 | 
|* 17 |      FIXED TABLE FULL   | X$KTATL    |     1 |    64 |     0   (0)| 00:00:01 | 
|* 18 |      FIXED TABLE FULL   | X$KTSTUSC  |     1 |    64 |     0   (0)| 00:00:01 | 
|* 19 |      FIXED TABLE FULL   | X$KTSTUSS  |     1 |    64 |     0   (0)| 00:00:01 | 
|* 20 |      FIXED TABLE FULL   | X$KTSTUSG  |     1 |    64 |     0   (0)| 00:00:01 | 
|* 21 |      FIXED TABLE FULL   | X$KTCXB    |     1 |    64 |     0   (0)| 00:00:01 | 
--------------------------------------------------------------------------------------

direct path write temp
direct path write temp
direct path write temp
................

 

 

显然仅返回100多条记录的v$LOCK视图的查询不该这么慢,也不该由SORT或HASH造成大量的临时空间使用, 究其根本还是FIXED TABLE即X$的内部表上的统计信息不准确导致的执行计划使用,通过使用RULE HINT可以马上获得较好的性能:

 

 

select /*+ RULE */ count(*) from v$LOCK;

  COUNT(*) 
---------- 
       190 

Elapsed: 00:00:00.18

Execution Plan 
---------------------------------------------------------- 
Plan hash value: 2026431807 

------------------------------------------------- 
| Id  | Operation                  | Name       | 
------------------------------------------------- 
|   0 | SELECT STATEMENT           |            | 
|   1 |  SORT AGGREGATE            |            | 
|   2 |   MERGE JOIN               |            | 
|   3 |    SORT JOIN               |            | 
|   4 |     MERGE JOIN             |            | 
|   5 |      SORT JOIN             |            | 
|   6 |       FIXED TABLE FULL     | X$KSQRS    | 
|*  7 |      SORT JOIN             |            | 
|   8 |       VIEW                 | GV$_LOCK   | 
|   9 |        UNION-ALL           |            | 
|* 10 |         FILTER             |            | 
|  11 |          VIEW              | GV$_LOCK1  | 
|  12 |           UNION-ALL        |            | 
|* 13 |            FIXED TABLE FULL| X$KDNSSF   | 
|* 14 |            FIXED TABLE FULL| X$KSQEQ    | 
|* 15 |         FIXED TABLE FULL   | X$KTADM    | 
|* 16 |         FIXED TABLE FULL   | X$KTATRFIL | 
|* 17 |         FIXED TABLE FULL   | X$KTATRFSL | 
|* 18 |         FIXED TABLE FULL   | X$KTATL    | 
|* 19 |         FIXED TABLE FULL   | X$KTSTUSC  | 
|* 20 |         FIXED TABLE FULL   | X$KTSTUSS  | 
|* 21 |         FIXED TABLE FULL   | X$KTSTUSG  | 
|* 22 |         FIXED TABLE FULL   | X$KTCXB    | 
|* 23 |    SORT JOIN               |            | 
|* 24 |     FIXED TABLE FULL       | X$KSUSE    | 
-------------------------------------------------

 

 

针对上述问题考虑为FIXED TABLE收集统计信息,可以使用DBMS_STATS.GATHER_FIXED_OBJECTS_STATS标准存储过程,特别是对于版本升级上来的数据库,特别需要考虑执行该存储过程更新FIXED TABLE STATISTICS:

 

 

SQL> set timing on;
SQL> exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

PL/SQL procedure successfully completed.

Elapsed: 00:01:24.87

 

 

GATHER_FIXED_OBJECTS_STATS

 

Create fixed table statistics
Directly after catupgrd.sql has been completed
This will speed up processing for recompilation with utlrp.sql
Create fixed table statistics again after a week with regular production workload
This task should be done only a few times per year

Comments

  1. maclean says

    提供另一种方案就是修改 V$LOCK的公共同义词,创建一个V$LOCK1的加入HINT的视图,之后普通非SYS用户会使用该HINT视图,SYSDBA仍使用原视图

    SQL> select * from v$fixed_view_definition where view_name=’V$LOCK’;

    VIEW_NAME
    ——————————
    VIEW_DEFINITION
    ——————————————————————————–
    V$LOCK
    select ADDR , KADDR , SID , TYPE , ID1 , ID2 , LMODE , REQUEST , CTIME , BLOCK
    from GV$LOCK where inst_id = USERENV(‘Instance’)

    create or replace view V$LOCK1 as select /*+ USE_MERGE_CARTESIAN */ ADDR , KADDR , SID , TYPE , ID1 , ID2 , LMODE , REQUEST , CTIME , BLOCK
    from GV$LOCK where inst_id = USERENV(‘Instance’);

    SQL> create view v$lock1 as select /*+ ordered */ * from v$lock;

    View created.

    SQL> create public synonym v$lock for v$lock1;

    Synonym created.

    conn maclean/oracle

    SQL> set autotrace traceonly;
    SQL> set timing on;
    SQL>
    SQL>
    SQL> select * from v$lock;

    39 rows selected.

    Elapsed: 00:00:00.03

    Elapsed: 00:00:00.01

    Execution Plan
    ———————————————————-
    Plan hash value: 3524752130

    ————————————————————————————–
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ————————————————————————————–
    | 0 | SELECT STATEMENT | | 1 | 156 | 1 (100)| 00:00:01 |
    |* 1 | HASH JOIN | | 1 | 156 | 1 (100)| 00:00:01 |
    |* 2 | HASH JOIN | | 1 | 108 | 1 (100)| 00:00:01 |
    | 3 | VIEW | GV$_LOCK | 10 | 760 | 0 (0)| 00:00:01 |
    | 4 | UNION-ALL | | | | | |
    |* 5 | FILTER | | | | | |
    | 6 | VIEW | GV$_LOCK1 | 2 | 152 | 0 (0)| 00:00:01 |
    | 7 | UNION-ALL | | | | | |
    |* 8 | FIXED TABLE FULL| X$KDNSSF | 1 | 102 | 0 (0)| 00:00:01 |
    |* 9 | FIXED TABLE FULL| X$KSQEQ | 1 | 102 | 0 (0)| 00:00:01 |
    |* 10 | FIXED TABLE FULL | X$KTADM | 1 | 102 | 0 (0)| 00:00:01 |
    |* 11 | FIXED TABLE FULL | X$KTATRFIL | 1 | 102 | 0 (0)| 00:00:01 |
    |* 12 | FIXED TABLE FULL | X$KTATRFSL | 1 | 102 | 0 (0)| 00:00:01 |
    |* 13 | FIXED TABLE FULL | X$KTATL | 1 | 102 | 0 (0)| 00:00:01 |
    |* 14 | FIXED TABLE FULL | X$KTSTUSC | 1 | 102 | 0 (0)| 00:00:01 |
    |* 15 | FIXED TABLE FULL | X$KTSTUSS | 1 | 102 | 0 (0)| 00:00:01 |
    |* 16 | FIXED TABLE FULL | X$KTSTUSG | 1 | 102 | 0 (0)| 00:00:01 |
    |* 17 | FIXED TABLE FULL | X$KTCXB | 1 | 102 | 0 (0)| 00:00:01 |
    |* 18 | FIXED TABLE FULL | X$KSUSE | 1 | 32 | 0 (0)| 00:00:01 |
    | 19 | FIXED TABLE FULL | X$KSQRS | 100 | 4800 | 0 (0)| 00:00:01 |
    ————————————————————————————–

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

    1 – access(TO_CHAR(USERENV(‘INSTANCE’))||RAWTOHEX(“RADDR”)=TO_CHAR(“R”.”INS
    T_ID”)||RAWTOHEX(“R”.”ADDR”))
    2 – access(“SADDR”=”S”.”ADDR”)
    5 – filter(USERENV(‘INSTANCE’) IS NOT NULL)
    8 – filter((“KSQLKMOD”0 OR “KSQLKREQ”0) AND
    “INST_ID”=USERENV(‘INSTANCE’) AND BITAND(“KSSOBFLG”,1)0)
    9 – filter((“KSQLKMOD”0 OR “KSQLKREQ”0) AND
    “INST_ID”=USERENV(‘INSTANCE’) AND BITAND(“KSSOBFLG”,1)0)
    10 – filter((“KSQLKMOD”0 OR “KSQLKREQ”0) AND
    “INST_ID”=USERENV(‘INSTANCE’) AND BITAND(“KSSOBFLG”,1)0)
    11 – filter((“KSQLKMOD”0 OR “KSQLKREQ”0) AND
    “INST_ID”=USERENV(‘INSTANCE’) AND BITAND(“KSSOBFLG”,1)0)
    12 – filter((“KSQLKMOD”0 OR “KSQLKREQ”0) AND
    “INST_ID”=USERENV(‘INSTANCE’) AND BITAND(“KSSOBFLG”,1)0)
    13 – filter((“KSQLKMOD”0 OR “KSQLKREQ”0) AND
    “INST_ID”=USERENV(‘INSTANCE’) AND BITAND(“KSSOBFLG”,1)0)
    14 – filter((“KSQLKMOD”0 OR “KSQLKREQ”0) AND
    “INST_ID”=USERENV(‘INSTANCE’) AND BITAND(“KSSOBFLG”,1)0)
    15 – filter((“KSQLKMOD”0 OR “KSQLKREQ”0) AND
    “INST_ID”=USERENV(‘INSTANCE’) AND BITAND(“KSSOBFLG”,1)0)
    16 – filter((“KSQLKMOD”0 OR “KSQLKREQ”0) AND
    “INST_ID”=USERENV(‘INSTANCE’) AND BITAND(“KSSOBFLG”,1)0)
    17 – filter((“KSQLKMOD”0 OR “KSQLKREQ”0) AND
    “INST_ID”=USERENV(‘INSTANCE’) AND BITAND(“KSSPAFLG”,1)0)
    18 – filter(“S”.”INST_ID”=USERENV(‘INSTANCE’))

    Statistics
    ———————————————————-
    0 recursive calls
    1 db block gets
    0 consistent gets
    0 physical reads
    0 redo size
    3026 bytes sent via SQL*Net to client
    545 bytes received via SQL*Net from client
    4 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    43 rows processed

    SQL> select count(*) from v$lock;

    Elapsed: 00:00:00.02

    Execution Plan
    ———————————————————-
    Plan hash value: 2965011029

    —————————————————————————————
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    —————————————————————————————
    | 0 | SELECT STATEMENT | | 1 | 50 | 1 (100)| 00:00:01 |
    | 1 | SORT AGGREGATE | | 1 | 50 | | |
    |* 2 | HASH JOIN | | 1 | 50 | 1 (100)| 00:00:01 |
    |* 3 | HASH JOIN | | 1 | 31 | 1 (100)| 00:00:01 |
    | 4 | VIEW | GV$_LOCK | 10 | 120 | 0 (0)| 00:00:01 |
    | 5 | UNION-ALL | | | | | |
    |* 6 | FILTER | | | | | |
    | 7 | VIEW | GV$_LOCK1 | 2 | 24 | 0 (0)| 00:00:01 |
    | 8 | UNION-ALL | | | | | |
    |* 9 | FIXED TABLE FULL| X$KDNSSF | 1 | 64 | 0 (0)| 00:00:01 |
    |* 10 | FIXED TABLE FULL| X$KSQEQ | 1 | 64 | 0 (0)| 00:00:01 |
    |* 11 | FIXED TABLE FULL | X$KTADM | 1 | 64 | 0 (0)| 00:00:01 |
    |* 12 | FIXED TABLE FULL | X$KTATRFIL | 1 | 64 | 0 (0)| 00:00:01 |
    |* 13 | FIXED TABLE FULL | X$KTATRFSL | 1 | 64 | 0 (0)| 00:00:01 |
    |* 14 | FIXED TABLE FULL | X$KTATL | 1 | 64 | 0 (0)| 00:00:01 |
    |* 15 | FIXED TABLE FULL | X$KTSTUSC | 1 | 64 | 0 (0)| 00:00:01 |
    |* 16 | FIXED TABLE FULL | X$KTSTUSS | 1 | 64 | 0 (0)| 00:00:01 |
    |* 17 | FIXED TABLE FULL | X$KTSTUSG | 1 | 64 | 0 (0)| 00:00:01 |
    |* 18 | FIXED TABLE FULL | X$KTCXB | 1 | 64 | 0 (0)| 00:00:01 |
    |* 19 | FIXED TABLE FULL | X$KSUSE | 1 | 19 | 0 (0)| 00:00:01 |
    | 20 | FIXED TABLE FULL | X$KSQRS | 100 | 1900 | 0 (0)| 00:00:01 |
    —————————————————————————————

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

    2 – access(TO_CHAR(USERENV(‘INSTANCE’))||RAWTOHEX(“RADDR”)=TO_CHAR(“R”.”INST
    _ID”)||RAWTOHEX(“R”.”ADDR”))
    3 – access(“SADDR”=”S”.”ADDR”)
    6 – filter(USERENV(‘INSTANCE’) IS NOT NULL)
    9 – filter((“KSQLKMOD”0 OR “KSQLKREQ”0) AND
    “INST_ID”=USERENV(‘INSTANCE’) AND BITAND(“KSSOBFLG”,1)0)
    10 – filter((“KSQLKMOD”0 OR “KSQLKREQ”0) AND
    “INST_ID”=USERENV(‘INSTANCE’) AND BITAND(“KSSOBFLG”,1)0)
    11 – filter((“KSQLKMOD”0 OR “KSQLKREQ”0) AND
    “INST_ID”=USERENV(‘INSTANCE’) AND BITAND(“KSSOBFLG”,1)0)
    12 – filter((“KSQLKMOD”0 OR “KSQLKREQ”0) AND
    “INST_ID”=USERENV(‘INSTANCE’) AND BITAND(“KSSOBFLG”,1)0)
    13 – filter((“KSQLKMOD”0 OR “KSQLKREQ”0) AND
    “INST_ID”=USERENV(‘INSTANCE’) AND BITAND(“KSSOBFLG”,1)0)
    14 – filter((“KSQLKMOD”0 OR “KSQLKREQ”0) AND
    “INST_ID”=USERENV(‘INSTANCE’) AND BITAND(“KSSOBFLG”,1)0)
    15 – filter((“KSQLKMOD”0 OR “KSQLKREQ”0) AND
    “INST_ID”=USERENV(‘INSTANCE’) AND BITAND(“KSSOBFLG”,1)0)
    16 – filter((“KSQLKMOD”0 OR “KSQLKREQ”0) AND
    “INST_ID”=USERENV(‘INSTANCE’) AND BITAND(“KSSOBFLG”,1)0)
    17 – filter((“KSQLKMOD”0 OR “KSQLKREQ”0) AND
    “INST_ID”=USERENV(‘INSTANCE’) AND BITAND(“KSSOBFLG”,1)0)
    18 – filter((“KSQLKMOD”0 OR “KSQLKREQ”0) AND
    “INST_ID”=USERENV(‘INSTANCE’) AND BITAND(“KSSPAFLG”,1)0)
    19 – filter(“S”.”INST_ID”=USERENV(‘INSTANCE’))

    Statistics
    ———————————————————-
    8 recursive calls
    0 db block gets
    2 consistent gets
    0 physical reads
    0 redo size
    526 bytes sent via SQL*Net to client
    523 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    1 rows processed

    conn / as sysdba

    SQL> set autotrace traceonly;
    SQL> set timing on;
    SQL> set linesize 200 pagesize 2000
    SQL> select * from v$lock;

    39 rows selected.

    Elapsed: 00:00:00.18

    Execution Plan
    ———————————————————-
    Plan hash value: 554400005

    ————————————————————————————-
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ————————————————————————————-
    | 0 | SELECT STATEMENT | | 1 | 156 | 1 (100)| 00:00:01 |
    |* 1 | HASH JOIN | | 1 | 156 | 1 (100)| 00:00:01 |
    | 2 | VIEW | GV$_LOCK | 10 | 760 | 0 (0)| 00:00:01 |
    | 3 | UNION-ALL | | | | | |
    |* 4 | FILTER | | | | | |
    | 5 | VIEW | GV$_LOCK1 | 2 | 152 | 0 (0)| 00:00:01 |
    | 6 | UNION-ALL | | | | | |
    |* 7 | FIXED TABLE FULL| X$KDNSSF | 1 | 102 | 0 (0)| 00:00:01 |
    |* 8 | FIXED TABLE FULL| X$KSQEQ | 1 | 102 | 0 (0)| 00:00:01 |
    |* 9 | FIXED TABLE FULL | X$KTADM | 1 | 102 | 0 (0)| 00:00:01 |
    |* 10 | FIXED TABLE FULL | X$KTATRFIL | 1 | 102 | 0 (0)| 00:00:01 |
    |* 11 | FIXED TABLE FULL | X$KTATRFSL | 1 | 102 | 0 (0)| 00:00:01 |
    |* 12 | FIXED TABLE FULL | X$KTATL | 1 | 102 | 0 (0)| 00:00:01 |
    |* 13 | FIXED TABLE FULL | X$KTSTUSC | 1 | 102 | 0 (0)| 00:00:01 |
    |* 14 | FIXED TABLE FULL | X$KTSTUSS | 1 | 102 | 0 (0)| 00:00:01 |
    |* 15 | FIXED TABLE FULL | X$KTSTUSG | 1 | 102 | 0 (0)| 00:00:01 |
    |* 16 | FIXED TABLE FULL | X$KTCXB | 1 | 102 | 0 (0)| 00:00:01 |
    | 17 | MERGE JOIN CARTESIAN | | 100 | 8000 | 0 (0)| 00:00:01 |
    |* 18 | FIXED TABLE FULL | X$KSUSE | 1 | 32 | 0 (0)| 00:00:01 |
    | 19 | BUFFER SORT | | 100 | 4800 | 0 (0)| 00:00:01 |
    | 20 | FIXED TABLE FULL | X$KSQRS | 100 | 4800 | 0 (0)| 00:00:01 |
    ————————————————————————————-

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

    1 – access(“SADDR”=”S”.”ADDR” AND TO_CHAR(USERENV(‘INSTANCE’))||RAWTOHEX(”
    RADDR”)=TO_CHAR(“R”.”INST_ID”)||RAWTOHEX(“R”.”ADDR”))
    4 – filter(USERENV(‘INSTANCE’) IS NOT NULL)
    7 – filter((“KSQLKMOD”0 OR “KSQLKREQ”0) AND
    “INST_ID”=USERENV(‘INSTANCE’) AND BITAND(“KSSOBFLG”,1)0)
    8 – filter((“KSQLKMOD”0 OR “KSQLKREQ”0) AND
    “INST_ID”=USERENV(‘INSTANCE’) AND BITAND(“KSSOBFLG”,1)0)
    9 – filter((“KSQLKMOD”0 OR “KSQLKREQ”0) AND
    “INST_ID”=USERENV(‘INSTANCE’) AND BITAND(“KSSOBFLG”,1)0)
    10 – filter((“KSQLKMOD”0 OR “KSQLKREQ”0) AND
    “INST_ID”=USERENV(‘INSTANCE’) AND BITAND(“KSSOBFLG”,1)0)
    11 – filter((“KSQLKMOD”0 OR “KSQLKREQ”0) AND
    “INST_ID”=USERENV(‘INSTANCE’) AND BITAND(“KSSOBFLG”,1)0)
    12 – filter((“KSQLKMOD”0 OR “KSQLKREQ”0) AND
    “INST_ID”=USERENV(‘INSTANCE’) AND BITAND(“KSSOBFLG”,1)0)
    13 – filter((“KSQLKMOD”0 OR “KSQLKREQ”0) AND
    “INST_ID”=USERENV(‘INSTANCE’) AND BITAND(“KSSOBFLG”,1)0)
    14 – filter((“KSQLKMOD”0 OR “KSQLKREQ”0) AND
    “INST_ID”=USERENV(‘INSTANCE’) AND BITAND(“KSSOBFLG”,1)0)
    15 – filter((“KSQLKMOD”0 OR “KSQLKREQ”0) AND
    “INST_ID”=USERENV(‘INSTANCE’) AND BITAND(“KSSOBFLG”,1)0)
    16 – filter((“KSQLKMOD”0 OR “KSQLKREQ”0) AND
    “INST_ID”=USERENV(‘INSTANCE’) AND BITAND(“KSSPAFLG”,1)0)
    18 – filter(“S”.”INST_ID”=USERENV(‘INSTANCE’))

    Statistics
    ———————————————————-
    1 recursive calls
    0 db block gets
    0 consistent gets
    0 physical reads
    0 redo size
    2875 bytes sent via SQL*Net to client
    545 bytes received via SQL*Net from client
    4 SQL*Net roundtrips to/from client
    1 sorts (memory)
    0 sorts (disk)
    39 rows processed

    SQL> select count(*) from v$lock;

    Elapsed: 00:00:00.21

    Execution Plan
    ———————————————————-
    Plan hash value: 2399206389

    ————————————————————————————–
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ————————————————————————————–
    | 0 | SELECT STATEMENT | | 1 | 50 | 1 (100)| 00:00:01 |
    | 1 | SORT AGGREGATE | | 1 | 50 | | |
    |* 2 | HASH JOIN | | 1 | 50 | 1 (100)| 00:00:01 |
    | 3 | MERGE JOIN CARTESIAN | | 100 | 3800 | 0 (0)| 00:00:01 |
    |* 4 | FIXED TABLE FULL | X$KSUSE | 1 | 19 | 0 (0)| 00:00:01 |
    | 5 | BUFFER SORT | | 100 | 1900 | 0 (0)| 00:00:01 |
    | 6 | FIXED TABLE FULL | X$KSQRS | 100 | 1900 | 0 (0)| 00:00:01 |
    | 7 | VIEW | GV$_LOCK | 10 | 120 | 0 (0)| 00:00:01 |
    | 8 | UNION-ALL | | | | | |
    |* 9 | FILTER | | | | | |
    | 10 | VIEW | GV$_LOCK1 | 2 | 24 | 0 (0)| 00:00:01 |
    | 11 | UNION-ALL | | | | | |
    |* 12 | FIXED TABLE FULL| X$KDNSSF | 1 | 64 | 0 (0)| 00:00:01 |
    |* 13 | FIXED TABLE FULL| X$KSQEQ | 1 | 64 | 0 (0)| 00:00:01 |
    |* 14 | FIXED TABLE FULL | X$KTADM | 1 | 64 | 0 (0)| 00:00:01 |
    |* 15 | FIXED TABLE FULL | X$KTATRFIL | 1 | 64 | 0 (0)| 00:00:01 |
    |* 16 | FIXED TABLE FULL | X$KTATRFSL | 1 | 64 | 0 (0)| 00:00:01 |
    |* 17 | FIXED TABLE FULL | X$KTATL | 1 | 64 | 0 (0)| 00:00:01 |
    |* 18 | FIXED TABLE FULL | X$KTSTUSC | 1 | 64 | 0 (0)| 00:00:01 |
    |* 19 | FIXED TABLE FULL | X$KTSTUSS | 1 | 64 | 0 (0)| 00:00:01 |
    |* 20 | FIXED TABLE FULL | X$KTSTUSG | 1 | 64 | 0 (0)| 00:00:01 |
    |* 21 | FIXED TABLE FULL | X$KTCXB | 1 | 64 | 0 (0)| 00:00:01 |
    ————————————————————————————–

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

    2 – access(“SADDR”=”S”.”ADDR” AND TO_CHAR(USERENV(‘INSTANCE’))||RAWTOHEX(“R
    ADDR”)=TO_CHAR(“R”.”INST_ID”)||RAWTOHEX(“R”.”ADDR”))
    4 – filter(“S”.”INST_ID”=USERENV(‘INSTANCE’))
    9 – filter(USERENV(‘INSTANCE’) IS NOT NULL)
    12 – filter((“KSQLKMOD”0 OR “KSQLKREQ”0) AND
    “INST_ID”=USERENV(‘INSTANCE’) AND BITAND(“KSSOBFLG”,1)0)
    13 – filter((“KSQLKMOD”0 OR “KSQLKREQ”0) AND
    “INST_ID”=USERENV(‘INSTANCE’) AND BITAND(“KSSOBFLG”,1)0)
    14 – filter((“KSQLKMOD”0 OR “KSQLKREQ”0) AND
    “INST_ID”=USERENV(‘INSTANCE’) AND BITAND(“KSSOBFLG”,1)0)
    15 – filter((“KSQLKMOD”0 OR “KSQLKREQ”0) AND
    “INST_ID”=USERENV(‘INSTANCE’) AND BITAND(“KSSOBFLG”,1)0)
    16 – filter((“KSQLKMOD”0 OR “KSQLKREQ”0) AND
    “INST_ID”=USERENV(‘INSTANCE’) AND BITAND(“KSSOBFLG”,1)0)
    17 – filter((“KSQLKMOD”0 OR “KSQLKREQ”0) AND
    “INST_ID”=USERENV(‘INSTANCE’) AND BITAND(“KSSOBFLG”,1)0)
    18 – filter((“KSQLKMOD”0 OR “KSQLKREQ”0) AND
    “INST_ID”=USERENV(‘INSTANCE’) AND BITAND(“KSSOBFLG”,1)0)
    19 – filter((“KSQLKMOD”0 OR “KSQLKREQ”0) AND
    “INST_ID”=USERENV(‘INSTANCE’) AND BITAND(“KSSOBFLG”,1)0)
    20 – filter((“KSQLKMOD”0 OR “KSQLKREQ”0) AND
    “INST_ID”=USERENV(‘INSTANCE’) AND BITAND(“KSSOBFLG”,1)0)
    21 – filter((“KSQLKMOD”0 OR “KSQLKREQ”0) AND
    “INST_ID”=USERENV(‘INSTANCE’) AND BITAND(“KSSPAFLG”,1)0)

    Statistics
    ———————————————————-
    0 recursive calls
    1 db block gets
    0 consistent gets
    0 physical reads
    0 redo size
    526 bytes sent via SQL*Net to client
    523 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    1 sorts (memory)
    0 sorts (disk)
    1 rows processed

Comment

*

沪ICP备14014813号-2

沪公网安备 31010802001379号