Sunday, August 21, 2011

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

4 comments:

Flávio Soares said...

Hi

This is the very interesting study on v$lock. How you said, "don´t just say it´s slowly" is need prove.

For me is work very good, using the ORDERED hint.

Thanks

Flávio Soares
From Brazil

Surachart Opun said...

thank you for your comment.

don't just say it's slowly -

In the real world, when someone saw the issue... example: sql got slow or ...
they have never tested why it slow... or what is happened?

So, I mean we should test it or find out more ...

Surachart Opun said...

I found out on Oracle support.

Query Against v$lock Run from OEM Performs Slowly [ID 1328789.1]

They talk about solution by using "dbms_stats.GATHER_FIXED_OBJECTS_STATS"

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

COUNT(*)
----------
45

1 row selected.

Elapsed: 00:00:00.30
SQL> exec dbms_stats.GATHER_FIXED_OBJECTS_STATS


PL/SQL procedure successfully completed.

Elapsed: 00:02:08.21
SQL> select count(*) from v$lock;

COUNT(*)
----------
45

1 row selected.

Elapsed: 00:00:00.06

Surachart Opun said...

Check execution plan :)
It's changed after executed dbms_stats.GATHER_FIXED_OBJECTS_STATS

SQL> exec dbms_stats.GATHER_FIXED_OBJECTS_STATS

PL/SQL procedure successfully completed.

Elapsed: 00:01:35.72

SQL> explain plan for select * from v$lock;

Explained.

Elapsed: 00:00:00.01
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 | | 976 | 95648 | 3 (100)| 00:00:01 |
| 1 | HASH JOIN | | 976 | 95648 | 3 (100)| 00:00:01 |
| 2 | HASH JOIN | | 27 | 2160 | 2 (100)| 00:00:01 |
| 3 | VIEW | GV$_LOCK | 27 | 1836 | 2 (100)| 00:00:01 |
| 4 | UNION-ALL | | | | | |
| 5 | FILTER | | | | | |
| 6 | VIEW | GV$_LOCK1 | 19 | 1292 | 1 (100)| 00:00:01 |
| 7 | UNION-ALL | | | | | |
| 8 | FIXED TABLE FULL| X$KDNSSF | 1 | 32 | 0 (0)| 00:00:01 |
| 9 | FIXED TABLE FULL| X$KSQEQ | 18 | 594 | 1 (100)| 00:00:01 |
| 10 | FIXED TABLE FULL | X$KTADM | 1 | 31 | 0 (0)| 00:00:01 |
| 11 | FIXED TABLE FULL | X$KTATRFIL | 1 | 28 | 0 (0)| 00:00:01 |
| 12 | FIXED TABLE FULL | X$KTATRFSL | 1 | 28 | 0 (0)| 00:00:01 |
| 13 | FIXED TABLE FULL | X$KTATL | 1 | 32 | 0 (0)| 00:00:01 |
| 14 | FIXED TABLE FULL | X$KTSTUSC | 1 | 28 | 0 (0)| 00:00:01 |
| 15 | FIXED TABLE FULL | X$KTSTUSS | 1 | 31 | 0 (0)| 00:00:01 |
| 16 | FIXED TABLE FULL | X$KTSTUSG | 1 | 28 | 0 (0)| 00:00:01 |
| 17 | FIXED TABLE FULL | X$KTCXB | 1 | 30 | 0 (0)| 00:00:01 |
| 18 | FIXED TABLE FULL | X$KSUSE | 772 | 9264 | 0 (0)| 00:00:01 |
| 19 | FIXED TABLE FULL | X$KSQRS | 3616 | 65088 | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

26 rows selected.

Elapsed: 00:00:00.09

NO MERGE JOIN CARTESIAN