Dion Cho – Oracle Performance Storyteller

We are natural born scientists

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! :)

About these ads

Written by Dion Cho

March 26, 2009 at 4:21 am

Posted in Cursor, Wait Event

Tagged with ,

9 Responses

Subscribe to comments with RSS.

  1. Don’t worry, there are more than enough mutex issues to keep you in a job, particularly waits on mutex: pin s wait for x.

    dombrooks

    March 26, 2009 at 9:02 am

  2. Yes. Mutex!
    Our next revenue source. :)

    Dion Cho

    March 26, 2009 at 9:09 am

  3. Btw *almost* all library cache related latches are gone, the library cache load lock latch is still there ;)

    and yep mutex contention is fun as well and i’ve seen a *lot* of mutex issues recently more than library cache latch stuff.. this is partially because of the bugs involved.

    Tanel Poder

    March 26, 2009 at 5:06 pm

  4. Interesting case this shared pool latch usage btw.
    You can use latchprofx to check from which function the shared pool latches were taken – it may be kghupr (kgh “unpin recreatable” – when unpinned chunks are put into shared pool LRU list)

    Tanel Poder

    March 26, 2009 at 5:23 pm

    • Tanel.

      You’re always diving into the core of the problem. I love it! :)
      I’ve been using a simplified version of your magnificent latch profiler(sorry for that) and here is the result.

      SID LADDR CALLED NAME HITS
      ---------- -------- ---------------------------------------- -------------------- ----------
      148 4E117AA8 kglsim_unpin_simhp shared pool simulator 44
      148 4E117AA8 kglsim_unpin_simhp: fast path shared pool simulator 674
      148 092E9104 kghupr1 shared pool 5952

      kghupr1! This result is always reproducible.

      My local Oracle 11g has small but sufficent shared pool(__shared_pool_size 155189248). Not sure why Oracle 11g needs more shared pool acquisition especially compared with 10g.

      Frankly speaking, help me! :)

      PS) The latch profiling in 10g is as following.

      SID LADDR CALLED NAME HITS
      ---------- -------- ---------------------------------------- -------------------- ----------
      1311 2B7E922C kglpnal: child: alloc space library cache pin 369
      1311 2B7E922C kglpndl library cache pin 146
      1311 2B7E9364 kgllkal: child: multiinstance library cache lock 614
      1311 2B7E9364 kgllkdl: child: cleanup library cache lock 621
      1311 2B7E90F4 kglhdgc: child: library cache 1350
      1311 2B7E90F4 kglhdgn: child: library cache 344
      1311 2B7E90F4 kgllkdl: child: no lock handle library cache 2396
      1311 2B7E90F4 kglpin: child: heap processing library cache 2562
      1311 2B7E90F4 kglpndl: child: before processing library cache 672

      Dion Cho

      March 27, 2009 at 3:05 am

  5. [...] Cho, the Oracle Performance Storyteller muses on the fact of library cache latch being gone: “Good news to DBAs and developers, but bad news to people like me who make moneny from [...]

    Pythian Group - Blog

    March 27, 2009 at 5:17 pm

  6. When a chunk is in transient part of LRU list, it has to be moved to the MRU end of the LRU list every time its unpinned. Shared pool heap LRU list modifications must be done under protection of shared pool latch, that’s why you see latch contention even though you’re not hard parsing.

    Once the chunk makes its way to recurrent part of the LRU list, it will be considered hot enough so it doesn’t need to be moved around in LRU list at every unpin, then you’ll see almost no shared pool latch gets.

    I’m gonna write a blog entry with more details next week…

    Tanel Poder

    April 5, 2009 at 6:30 pm

    • Tanel. Thank you for your comment.
      Yes, it must be related to LRU list management as latch profiling clearly shows.

      What makes me really curios is why Oracle 11g has far more active shared pool latch activity than Oracle 10g(under almost same configuration). Maybe related with the change of mechanism of memory management or it would be just my failure to build isolated test case. I would try to build more concrete test case.

      Dion Cho

      April 5, 2009 at 11:04 pm

  7. Hi Dion,
    A great blog indeed. I marked it for regular reading.

    sandro

    April 6, 2009 at 8:21 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: