Dion Cho – Oracle Performance Storyteller

We are natural born scientists

Posts Tagged ‘library cache latch

Library cache latch has gone?

with 9 comments

As Tanel Poder declared with big proud, all the library cache related latches have gone in Oracle 11g!

Good news to DBAs and developers, but bad news to people like me who make moneny from troubleshooting library cache latch contention. :)

So, I decided to prove that I’m still capable of something meaningful.

-- This query has only one child
@shared_cursor 'select /* cursor_share_1 */ * from t1 where c1 = :b1%'

SQL_TEXT                       = select /* cursor_share_1 */ * from t1 where c1 
= :b1                                                                           
SQL_ID                         = 6jf82h3bq1tzr                                  
ADDRESS                        = 2B3F7078                                       
CHILD_ADDRESS                  = 2B3F6F34                                       
CHILD_NUMBER                   = 0                                              
--------------------------------------------------                              

PL/SQL procedure successfully completed.

-- This query has 5 children!
@shared_cursor 'select /* cursor_share */ * from t1 where c1 = :b1%'

SQL_TEXT                       = select /* cursor_share */ * from t1 where c1 =
:b1
SQL_ID                         = 2zu6xb9130t89
ADDRESS                        = 2B38369C
CHILD_ADDRESS                  = 2AC50F60
CHILD_NUMBER                   = 0
--------------------------------------------------
SQL_TEXT                       = select /* cursor_share */ * from t1 where c1 =
:b1
SQL_ID                         = 2zu6xb9130t89
ADDRESS                        = 2B38369C
CHILD_ADDRESS                  = 2A652B48
CHILD_NUMBER                   = 1
OPTIMIZER_MODE_MISMATCH        = Y
--------------------------------------------------
SQL_TEXT                       = select /* cursor_share */ * from t1 where c1 =
:b1
SQL_ID                         = 2zu6xb9130t89
ADDRESS                        = 2B38369C
CHILD_ADDRESS                  = 2A3C7968
CHILD_NUMBER                   = 2
BIND_MISMATCH                  = Y
--------------------------------------------------
SQL_TEXT                       = select /* cursor_share */ * from t1 where c1 =
:b1
SQL_ID                         = 2zu6xb9130t89
ADDRESS                        = 2B38369C
CHILD_ADDRESS                  = 2A1EFD0C
CHILD_NUMBER                   = 3
BIND_MISMATCH                  = Y
--------------------------------------------------
SQL_TEXT                       = select /* cursor_share */ * from t1 where c1 =
:b1
SQL_ID                         = 2zu6xb9130t89
ADDRESS                        = 2B38369C
CHILD_ADDRESS                  = 2A1DA0F0
CHILD_NUMBER                   = 4
BIND_MISMATCH                  = Y
OPTIMIZER_MODE_MISMATCH        = Y
--------------------------------------------------

PL/SQL procedure successfully completed.



Now I make a heavy soft parse to make massive lacth and/or mutex acquistion, and make a simple comparsion for one child vs. filve children.

@mon_on userenv('sid')

declare
  v_cursor      number;
begin
  for idx in 1 .. 200000 loop
    v_cursor := dbms_sql.open_cursor;
    dbms_sql.parse(v_cursor, 'select /* cursor_share_1 */ * from t1 where c1 = :b1', dbms_sql.native);
    dbms_sql.close_cursor(v_cursor);
  end loop;
end;
/

@mon_off

declare
  v_cursor      number;
begin
  for idx in 1 .. 200000 loop
    v_cursor := dbms_sql.open_cursor;
    dbms_sql.parse(v_cursor, 'select /* cursor_share */ * from t1 where c1 = :b1', dbms_sql.native);
    dbms_sql.close_cursor(v_cursor);
  end loop;
end;
/

@mon_off2
@mon_show2


The result of 10g. The long child chain caused 1.5M + 0.8M + 0.8M = 3.1M of more latch acquisition.

LATCH_NAME                         D_GETS   D_MISSES   D_SLEEPS  D_IM_GETS      
------------------------------ ---------- ---------- ---------- ----------      
library cache                     1599584         -9         -1          0      
library cache lock                 799878          0          0          0      
library cache pin                  799743          1          0          0      
session allocation                  -1497          0          0          0      
shared pool                          -517          0          0          0      
...


The result of 11g. The long child chain caused 0.4M of more shared pool latch acquisition.

LATCH_NAME                         D_GETS   D_MISSES   D_SLEEPS  D_IM_GETS      
------------------------------ ---------- ---------- ---------- ----------      
shared pool                        399866          3          1          0      
cache buffers chains                 -175          0          0         -7      
enqueue hash chains                    58          0          0          0      
enqueues                               57          0          0          0      
row cache objects                     -46          0          0          0      
shared pool simulator                 -40          0          0          0      
...


Great improvement, isn’t it? We really don’t need any latch for searching through library cache chain and looking into child cursors. Instead, mutexes are used for these operations. Mutex for library cache hash bucket, library cache lock and library cache pin.

But wait… Why I’m getting shared pool activity in 11g? I’m just soft parsing. Well, at this point of time, I have no explanation. More research in need.

The most important thing is that from now on, I’m going to make my living troubleshooting shared pool latch contention. More more annoying library cache latch contention! :)

Written by Dion Cho

March 26, 2009 at 4:21 am

Posted in Cursor, Wait Event

Tagged with ,

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.

Written by Dion Cho

March 16, 2009 at 9:07 am

Posted in Wait Event

Tagged with , ,

Follow

Get every new post delivered to your Inbox.

Join 60 other followers