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! :)
Heavy reads on the library cache related v$ views.
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.
