Just learn more from V$LOCK

I posted Excessive waited 'SQL*Net message from client' - when query V$LOCK. Thank You for first comment (Marcus). He figured me out about STATE and reviewed EXPLAIN PLAN!!! I want to test it more...

CASE 1: SELECT * FROM V$LOCK

SQL> EXPLAIN PLAN FOR SELECT * FROM V$LOCK;

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 554400005
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 156 | 1 (100)| 00:00:04 |
|* 1 | HASH JOIN | | 1 | 156 | 1 (100)| 00:00:04 |
| 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 |
-------------------------------------------------------------------------------------

CASE 2: SELECT /*+ PARALLEL */ * FROM V$LOCK

SQL> EXPLAIN PLAN FOR SELECT /*+ PARALLEL */ * FROM V$LOCK;

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1453144240
--------------------------------------------------------------------------------------
| 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 | FIXED TABLE FULL | X$KSUSE | 1 | 32 | 0 (0)| 00:00:01 |
| 4 | VIEW | GV$_LOCK | 10 | 760 | 0 (0)| 00:00:01 |
| 5 | UNION-ALL | | | | | |
|* 6 | FILTER | | | | | |
| 7 | VIEW | GV$_LOCK1 | 2 | 152 | 0 (0)| 00:00:01 |
| 8 | UNION-ALL | | | | | |
|* 9 | FIXED TABLE FULL| X$KDNSSF | 1 | 102 | 0 (0)| 00:00:01 |
|* 10 | FIXED TABLE FULL| X$KSQEQ | 1 | 102 | 0 (0)| 00:00:01 |
|* 11 | FIXED TABLE FULL | X$KTADM | 1 | 102 | 0 (0)| 00:00:01 |
|* 12 | FIXED TABLE FULL | X$KTATRFIL | 1 | 102 | 0 (0)| 00:00:01 |
|* 13 | FIXED TABLE FULL | X$KTATRFSL | 1 | 102 | 0 (0)| 00:00:01 |
|* 14 | FIXED TABLE FULL | X$KTATL | 1 | 102 | 0 (0)| 00:00:01 |
|* 15 | FIXED TABLE FULL | X$KTSTUSC | 1 | 102 | 0 (0)| 00:00:01 |
|* 16 | FIXED TABLE FULL | X$KTSTUSS | 1 | 102 | 0 (0)| 00:00:01 |
|* 17 | FIXED TABLE FULL | X$KTSTUSG | 1 | 102 | 0 (0)| 00:00:01 |
|* 18 | FIXED TABLE FULL | X$KTCXB | 1 | 102 | 0 (0)| 00:00:01 |
| 19 | FIXED TABLE FULL | X$KSQRS | 100 | 4800 | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

He told me review "MERGE JOIN CARTESIAN", it can make Excessive fetch data. OK test test test it. then I found out V$LOCK!!!

SQL> SELECT VIEW_DEFINITION FROM V$FIXED_VIEW_DEFINITION WHERE VIEW_NAME='V$LOCK';

VIEW_DEFINITION
-----------------------------------------------------------------------------------------------------------------------------------------------
select ADDR , KADDR , SID , TYPE , ID1 , ID2 , LMODE , REQUEST , CTIME , BLOCK from GV$LOCK where inst_id = USERENV('Instance')

SQL> SELECT VIEW_DEFINITION FROM V$FIXED_VIEW_DEFINITION WHERE VIEW_NAME='GV$LOCK';

VIEW_DEFINITION
-----------------------------------------------------------------------------------------------------------------------------------------------
select s.inst_id,l.laddr,l.kaddr,s.ksusenum,r.ksqrsidt,r.ksqrsid1,r.ksqrsid2,l.lmode,l.request,l.ctime,decode(l.lmode,0,0,l.block) from v$_lock l, x$ksuse s, x$ksqrs r where l.saddr=s.addr and concat(USERENV('Instance'),l.raddr)=concat(r.inst_id,r.addr)

So, SQL statement for V$LOCK!!!
select s.inst_id,l.laddr,l.kaddr,s.ksusenum,r.ksqrsidt,r.ksqrsid1, r.ksqrsid2,l.lmode,l.request,l.ctime,decode(l.lmode,0,0,l.block) from v$_lock l, x$ksuse s, x$ksqrs r where l.saddr=s.addr and concat(USERENV('Instance'),l.raddr)=concat(r.inst_id,r.addr) and s.inst_id = USERENV('Instance');

and tested more ... with this sql statement.

SQL> explain plan for select s.inst_id,l.laddr,l.kaddr,s.ksusenum,r.ksqrsidt,r.ksqrsid1, r.ksqrsid2,l.lmode,l.request,l.ctime,decode(l.lmode,0,0,l.block) from v$_lock l, x$ksuse s, x$ksqrs r where l.saddr=s.addr and concat(USERENV('Instance'),l.raddr)=concat(r.inst_id,r.addr) and s.inst_id = USERENV('Instance');

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 554400005
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 156 | 1 (100)| 00:00:04 |
|* 1 | HASH JOIN | | 1 | 156 | 1 (100)| 00:00:04 |
| 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 |
-------------------------------------------------------------------------------------

How to avoid "MERGE JOIN CARTESIAN", tried to use ORDERED hint - The ORDERED hint instructs Oracle to join tables in the order in which they appear in the FROM clause.
When you omit the ORDERED hint from a SQL statement requiring a join, the optimizer chooses the order in which to join the tables. You might want to use the ORDERED hint to specify a join order if you know something that the optimizer does not know about the number of rows selected from each table. Such information lets you choose an inner and outer table better than the optimizer could.

SQL> explain plan for select /*+ ordered */ s.inst_id,l.laddr,l.kaddr,s.ksusenum,r.ksqrsidt,r.ksqrsid1, r.ksqrsid2,l.lmode,l.request,l.ctime,decode(l.lmode,0,0,l.block) from v$_lock l, x$ksuse s, x$ksqrs r where l.saddr=s.addr and concat(USERENV('Instance'),l.raddr)=concat(r.inst_id,r.addr) and s.inst_id = USERENV('Instance');

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3524752130
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 156 | 1 (100)| 00:00:07 |
|* 1 | HASH JOIN | | 1 | 156 | 1 (100)| 00:00:07 |
|* 2 | HASH JOIN | | 1 | 108 | 1 (100)| 00:00:04 |
| 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 |
--------------------------------------------------------------------------------------

Good!!! joining changed!!! Test more...

SQL> select /*+ ordered */ s.inst_id,l.laddr,l.kaddr,s.ksusenum,r.ksqrsidt,r.ksqrsid1, r.ksqrsid2,l.lmode,l.request,l.ctime,decode(l.lmode,0,0,l.block) from v$_lock l, x$ksuse s, x$ksqrs r where l.saddr=s.addr and concat(USERENV('Instance'),l.raddr)=concat(r.inst_id,r.addr) and s.inst_id = USERENV('Instance');
.
.
.
807 rows selected.

Elapsed: 00:00:00.09

It's faster than....
I posted about 'SQL*Net message from client' wrong.... because "MERGE JOIN CARTESIAN" made excessive response time. So, I use : select /*+ ordered */ * from v$lock !!!

select /*+ ordered */ INST_ID, SID, TYPE, ID1, ID2, LMODE, REQUEST, CTIME, BLOCK from gv$lock where (ID1,ID2,TYPE) in (select ID1,ID2,TYPE from gv$lock where request>0) ORDER BY id1, request;

This may work for me... but not someone. But I learned "Check More EXPLAIN PLAN!!!, don't just say it's slowly".

Related Post:
Excessive waited 'SQL*Net message from client' - when query V$LOCK