2010年1月6日

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

mac

Author

11 min

Read Time

Views

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

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

maclean 2013-01-18

提供另一种方案就是修改 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

评论 (1)