Oracle Scratchpad

May 2, 2013

v$lock

Filed under: Locks,Oracle,Statistics,Troubleshooting — Jonathan Lewis @ 7:03 pm BST May 2,2013

The problem of slow queries on v$lock just came up again on the OTN database forum, so I thought I’d better push out a post that’s been hanging around on my blog for the last few months. This is actually mentioned in MOS in note 1328789.1: “Query Against v$lock Run from OEM Performs Slowly” which points out that it is basically a problem of bad statistics and all you have to do is collect the stats.

However, because the view is a messy union and join of several dynamic performance views sitting on top of a load of x$ structures, the advisory from MOS is to call the procedure  dbms_stats.gather_fixed_objects_stats. This is not a nice thing to do on a busy production system, especially if it has a large number of users and a large shared pool – and doing it at a representative run-time is important if you’re going to do it at all.

There is an alternative. Here’s the basic execution plan (on my 11.2.0.2) for “select * from v$lock”:

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

Note, particularly, the Cartesian merge join at line 17, which assumes there will be one row from v$session (x$ksuse) joined to 100 rows from v$resource (x$ksqrs – the structure used to represent any resources that you want to lock). The big UNION ALL is then all the different types of locks (enqueues) that you might attach to a resource. The estimates relating to these two structures are the most significant problem – v$session always has FAR more than one row in it, and v$resource isn’t small: in my little system the Cartesian join produced about 325,000 rows, and that was just after starting up the database.

Having identified a couple of critical tables, I decided to see what would happen if I just collected stats on these two objects rather than doing the whole system, and the following little piece of pl/sql did what I wanted:

begin
        dbms_stats.gather_table_stats('SYS','x$ksuse',method_opt=>'for all columns size 1');
        dbms_stats.gather_table_stats('SYS','x$ksqrs',method_opt=>'for all columns size 1');
end;
/

The effect of the stats was to change the plan to the following which, while it might not be the absolute best in all cases, is certainly better than the previous one (it’s possible that you may also find that it helps to collect stats on x$ksqeq (the “generic enqueues” structure) which is likely to be the next most significant in terms of number of rows):

------------------------------------------------------
| Id  | Operation               | Name       | Rows  |
------------------------------------------------------
|   0 | SELECT STATEMENT        |            |   133 |
|   1 |  HASH JOIN              |            |   133 |
|   2 |   HASH JOIN             |            |    10 |
|   3 |    VIEW                 | GV$_LOCK   |    10 |
|   4 |     UNION-ALL           |            |       |
|   5 |      FILTER             |            |       |
|   6 |       VIEW              | GV$_LOCK1  |     2 |
|   7 |        UNION-ALL        |            |       |
|   8 |         FIXED TABLE FULL| X$KDNSSF   |     1 |
|   9 |         FIXED TABLE FULL| X$KSQEQ    |     1 |
|  10 |      FIXED TABLE FULL   | X$KTADM    |     1 |
|  11 |      FIXED TABLE FULL   | X$KTATRFIL |     1 |
|  12 |      FIXED TABLE FULL   | X$KTATRFSL |     1 |
|  13 |      FIXED TABLE FULL   | X$KTATL    |     1 |
|  14 |      FIXED TABLE FULL   | X$KTSTUSC  |     1 |
|  15 |      FIXED TABLE FULL   | X$KTSTUSS  |     1 |
|  16 |      FIXED TABLE FULL   | X$KTSTUSG  |     1 |
|  17 |      FIXED TABLE FULL   | X$KTCXB    |     1 |
|  18 |    FIXED TABLE FULL     | X$KSUSE    |   252 |
|  19 |   FIXED TABLE FULL      | X$KSQRS    |  1328 |
------------------------------------------------------

Thanks to Timur Akhmadeev who recently published a note pointing out that you could collect stats on individual X$ tables. Do make sure you test this on your specific version of Oracle, though, and don’t use the production system as your first test case.

2 Comments »

  1. Hi Jonathan, in this situation I usually use following hints to avoid MJC

    select /*+ cardinality(l.s 3000) cardinality(l.r 13000) */ * from v$lock l

    Comment by Valentin Nikotin — May 2, 2013 @ 7:58 pm BST May 2,2013 | Reply

    • Valentin,

      I think that would be a valid strategy in controlled circumstances. When you KNOW the optimizer is going to get it wrong, and it’s unsafe, or too expensive, to get the stats that would fix the problem, and when (perhaps) dynamic sampling isn’t going to work or is going to be too expensive, then putting in some cardinality hints to give the optimizer a better idea about the statistics is a sound move. I don’t really like doing it on code that’s going into the production application though – DBA code for monitoring and trouble-shooting is a different matter.

      It’s unfortunate that Oracle Corp. won’t allow developers or DBAs who know the data to use hints like this legally to supply the intelligence that the optimizer sometimes really needs

      Comment by Jonathan Lewis — June 9, 2013 @ 12:22 pm BST Jun 9,2013 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,113 other followers