Posts Tagged ‘library cache latch’
Library cache latch has gone?
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! :)
