I was contacted for advice after a DBA had received alerts from database monitoring software about the Library Cache Hit Ratio being 93%.
According to a Metalink note:
Library Cache Hit Ratio
The hit ratio helps to measure the usage of the shared pool based on how many times a SQL/PLSQL statement needed to be parsed instead of being reused. The following SQL statement help you to calculate the library cache hit ratio: SELECT SUM(PINS) “EXECUTIONS”,SUM(RELOADS) “CACHE MISSES WHILE EXECUTING”
FROM V$LIBRARYCACHE; If the ratio of misses to executions is more than 1%, then try to reduce the library cache misses by increasing the shared pool size.
So, the question was, should the shared pool be increased?
Below is my reply:
I don’t think increasing the shared pool necessary in this case.
The usual suspect (application not using bind variables) is not to blame in this case.
If you look at the number of cursor versions, then you will see what I believe to be the problem:
col sql format a40
SELECT SQL_ID,
version_count ,
users_opening ,
users_executing,
substr(sql_text,1,40) "SQL"
FROM v$sqlarea
WHERE version_count > 10
order by version_count
;
..snip...
4cfmfucf91ctq 425 8 0 INSERT INTO zz_address_info(address_info
gt3t2nz6bg3vt 494 8 0 INSERT INTO zzzzz_ship_addr(shipping_gro
93zhuybvwamvv 585 5 0 INSERT INTO zz_address_info(address_info
2hmfmuvfn4x40 632 9 0 INSERT INTO zzzzz_ship_addr(shipping_gro
205xff0tkub6z 942 1 0 INSERT INTO zzzzz_ship_addr(shipping_gro
Many cursors have multiple versions. (Child cursors that can’t be shared).
Increasing the shared pool size might cause long hash chains because of all the child cursors and may even make performance worse for a database like this.
Let’s look at one example cursor in v$SQL that has only three versions:
sql_id: 0bs8t16vfdjf7
PARSING PARSING
CHLD SHARE PERST RUN LOADED KEPT OPEN PARSE USER SCHEMA
NUM MEM MEM MEM VERS VERS VERS LOADS INVL CALLS FIRST_LOAD_TIME LAST_LOAD_TIME ID ID
---- ---------- ---------- ---------- ------ ---- ---- ----- ---- ----- ------------------- ------------------- ------- -------
1 26998 10976 9016 1 0 0 2 1 1 2010-01-01/09:46:54 2010-01-11/11:36:28 35 35
2 26998 10976 9016 1 0 0 2 1 2 2010-01-01/09:46:54 2010-01-11/11:40:59 35 35
3 26998 10976 9016 1 0 1 1 0 10 2010-01-01/09:46:54 2010-01-11/11:18:53 35 35
CHLD FULL DISK BUFFER DIRECT ROWS
NUM EXECS EXECS READS GETS SORTS WRITES FETCHES PROCESSED COST
---- --------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------
1 1 1 0 18 0 0 1 6 5
2 4 4 0 72 0 0 4 24 5
3 9 9 0 162 0 0 9 54 5
SQL_FULLTEXT
------------------------------------------------------------------------------------------------------------------------------------
SELECT t1.product_id,t1.version,t1.creation_date,t1.product_type,t1.admin_display,t1.display_name,t1.version,t1.description,t1.end_d
ate,t1.start_date,t1.long_description
FROM zzz_product t1
WHERE t1.product_id IN (:1,:2,:3,:4,:5,:6)
We can see that the same user account executed the same SQL statement fourteen times, yet three child cursors were needed.
Why wasn’t the first one shared?
SQL> ( SYS @ somedb ) select * from V$SQL_SHARED_CURSOR where SQL_ID='0bs8t16vfdjf7';
CHLD
SQL_ID ADDRESS CHILD_ADDRESS NUM U S O O S L S E B P I S T A B D L T R I I R L I O S M U T N F A I T D L D B P C
------------- ---------------- ---------------- ---- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
S R P T M B M R O P M F L
- - - - - - - - - - - - -
0bs8t16vfdjf7 00000003A0BD18B0 0000000398D8E090 1 N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N
N N N N N N N N N N N N N
0bs8t16vfdjf7 00000003A0BD18B0 00000003A0501C10 2 N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N
N N N N N N N N N N N N N
0bs8t16vfdjf7 00000003A0BD18B0 000000039CE992A0 3 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
N N N N N N N N N N N N N
3 rows selected.
So BIND_MISMATCH is the reason for not sharing cursors.
SQL> ( SYS @ somedb) select * from v$sql_bind_metadata where ADDRESS='000000039CE992A0';
ADDRESS POSITION DATATYPE MAX_LENGTH ARRAY_LEN BIND_NAME
---------------- ---------- ---------- ---------- ---------- ------------------------------
000000039CE992A0 6 1 32 0 6
000000039CE992A0 5 1 128 0 5
000000039CE992A0 4 1 32 0 4
000000039CE992A0 3 1 128 0 3
000000039CE992A0 2 1 32 0 2
000000039CE992A0 1 1 32 0 1
6 rows selected.
SQL> ( SYS @ somedb) select * from v$sql_bind_metadata where ADDRESS='00000003A0501C10';
ADDRESS POSITION DATATYPE MAX_LENGTH ARRAY_LEN BIND_NAME
---------------- ---------- ---------- ---------- ---------- ------------------------------
00000003A0501C10 6 1 32 0 6
00000003A0501C10 5 1 128 0 5
00000003A0501C10 4 1 128 0 4
00000003A0501C10 3 1 128 0 3
00000003A0501C10 2 1 128 0 2
00000003A0501C10 1 1 128 0 1
6 rows selected.
SQL> ( SYS @ somedb ) select * from v$sql_bind_metadata where ADDRESS='0000000398D8E090';
ADDRESS POSITION DATATYPE MAX_LENGTH ARRAY_LEN BIND_NAME
---------------- ---------- ---------- ---------- ---------- ------------------------------
0000000398D8E090 6 1 32 0 6
0000000398D8E090 5 1 128 0 5
0000000398D8E090 4 1 32 0 4
0000000398D8E090 3 1 128 0 3
0000000398D8E090 2 1 32 0 2
0000000398D8E090 1 1 128 0 1
It looks like a fault with the application design.
The developers have set different sizes for the bind variables being used in the same SQL!
By the way, Oracle rounds the maximum length up to 32, 128, 2000 or ‘higher’. (Search for “bind variable graduation” on this page).
A bind variable size of 33 will show as 128 in this view.
If the performance is a problem, then I suggest contacting the application developers to see if they can fix their code or check with Oracle Support to see if there is a workaround.
If the performance is not a problem, then calibrating the monitoring to the nature of the application might be the best option.
hpdba
James Kim
