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.

Advertisements

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