Heavy reads on the library cache related v$ views.

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