Dion Cho – Oracle Performance Storyteller

We are natural born scientists

Posts Tagged ‘mutex

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 ,

Follow

Get every new post delivered to your Inbox.

Join 58 other followers