Dion Cho – Oracle Performance Storyteller

We are natural born scientists

Heavy reads on the library cache related v$ views.

with 6 comments

Riyaji posted a very interesting case here.

I have similar experience in my customer sites that a specific query which was designed to monitor the library cache suddenly got extremely slow and made the whole system perform poor.

When implementing the library cache/shared pool monitoring query, 2 things borne in mind.

  • When poorly designed, the query would require CPU and latch acquisition very aggressively, which would result in the heavy latch contention.
  • Because this query reads the memory structure(library cache/shared pool), no logical reads would be reported. It would be very confusing at first time – the query with high CPU usage is not reading anything at all?

Here is a simple demonstration.

@mon_on userenv('sid')

-- Poorly designed query on the library cache
select /*+ ordered use_nl(s1, s2) */ 
  count(*)
from v$sql s1, v$sql s2
;

@mon_off

@mon_show

-- Notice that only 40 logical reads reported
01. statistics
NAME                                             DIFF       VALUE1       VALUE2
---------------------------------------- ------------ ------------ ------------
...
DB time                                         1,496        5,360        6,856
CPU used by this session                        1,477        5,295        6,772
CPU used when call started                      1,477        5,295        6,772
...
session logical reads                              40        4,347        4,387
db block changes                                   34        6,349        6,383
consistent changes                                 22        3,211        3,233
db block gets                                      22        3,340        3,362
db block gets from cache                           22        3,340        3,362
consistent gets                                    18        1,007        1,025
consistent gets from cache                         18        1,007        1,025

-- But consumed 15sec!
02. time model                                                                 
                                                                               
STAT_NAME                                        DIFF       VALUE1       VALUE2
---------------------------------------- ------------ ------------ ------------
DB time                                    14,956,306   53,699,686   68,655,992
sql execute elapsed time                   14,949,563   53,616,498   68,566,061
DB CPU                                     14,732,737   52,980,340   67,713,077
parse time elapsed                              6,489      150,264      156,753
...

-- See the aggressive library cache latch acquistion?
LATCH_NAME                         D_GETS   D_MISSES   D_SLEEPS  D_IM_GETS
------------------------------ ---------- ---------- ---------- ----------
library cache                     2639468          5          0          0
SQL memory manager workarea li        339          0          0          0
st latch                                                                  
...


Well, the problem is absolutely poor execution plan. Nested full scan on the X$KGLCURSOR_CHILD view!

explain plan for
select /*+ ordered use_nl(s1, s2) */ 
  count(*)
from v$sql s1, v$sql s2
;

----------------------------------------------------------------------------------------
| Id  | Operation          | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |     1 |    26 |     0   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |                   |     1 |    26 |            |          |
|   2 |   NESTED LOOPS     |                   |     1 |    26 |     0   (0)| 00:00:01 |
|*  3 |    FIXED TABLE FULL| X$KGLCURSOR_CHILD |     1 |    13 |     0   (0)| 00:00:01 |
|*  4 |    FIXED TABLE FULL| X$KGLCURSOR_CHILD |     1 |    13 |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------



V$views and X$views are not some magical things that are automatically highly efficient. They are just memory structures that sometimes need the protection of latches. Keep this in mind when implementing monitoring queries.

PS) @mon_on, @mon_off and @mon_show are my own scripts for session diff.

About these ads

Written by Dion Cho

March 16, 2009 at 9:07 am

Posted in Wait Event

Tagged with , ,

6 Responses

Subscribe to comments with RSS.

  1. Very Nice, Will give it a try. I do use stored outlines.

    Please keep up the good work

    Regards & Thanks
    BN

    BN

    March 17, 2009 at 12:38 am

  2. Great Page for performance. My friend informed me about your site. Really good site, indeed. I have also linked your page in my web site at

    http://www.jiltin.com/index.php/scripts-oracle/oracle-and-oracle-applications-blogs-websites-for-reference/

    Jiltin

    March 18, 2009 at 6:59 am

  3. [...] Riyaj Shamsudeen also was sleuthing—into dynamic_plan_table, x$kqlfxpl and extreme library cache latch contention. Dion Cho, the Oracle Performance Storyteller, responded with an item about a similar experience of his: heavy reads on the library cache related v$ views. [...]

    Pythian Group - Blog

    March 20, 2009 at 4:40 pm

  4. [...] I mentioned here, a general query tuning technique should always be applied to queries on v$ or x$ [...]

  5. Wonderful blog, exactly where did you obtain the template?

    MicivOus2

    May 11, 2010 at 8:49 pm


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

Follow

Get every new post delivered to your Inbox.

Join 61 other followers

%d bloggers like this: