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

2010/01/06 by admin 1 Comment

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

Filed Under: Oracle Tagged With: direct path write temp, slow, v$lock

Comments

maclean says

2013/01/18 at 20:42

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

回复

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

Comments

Comment 取消回复