Dion Cho – Oracle Performance Storyteller

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