Oracle Database 10g -- Are Cursors Being Shared?

Problem

You want to identify if cursors are being shared for the statements

Solution

A good starting point is to check the Library Cache statistics. The SQL AREA namespace shows all the allocations for SQL and PL/SQL code.

SQL> select gets, gethits, gethitratio
  2  from V$LIBRARYCACHE
  3  where namespace='SQL AREA';

      GETS    GETHITS GETHITRATIO
---------- ---------- -----------
   8369732       7176  .000857375

The GETHITRATIO value shows the ratio of how many reusable cursors were found in the library cache. In the example above the value is very low.

The Pct Miss for SQL AREA in Library Cache Activity section of the AWR or Statspack reports shows the percentage of parse calls that do not find a cursor to share: 

                         Get    Pct            Pin    Pct
Namespace           Requests   Miss       Requests   Miss
--------------- ------------ ------ -------------- ------
BODY                   1,651   75.7          5,659   29.9
CLUSTER               14,077    2.2         83,769    2.6
INDEX                  8,063   68.3         25,652   28.3
SQL AREA           1,735,832   99.9     52,983,422   12.5
TABLE/PROCEDURE    1,908,933    8.0     25,184,164    6.6
TRIGGER                3,905   43.1          5,384   38.6

Identify statements with high number of versions. Use either AWR or Statspack reports for particular period of time or for the current situation issue the below query:

SQL> select plan_hash_value, count(*) from v$sql
  2  group by plan_hash_value
  3  order by 2 desc;

PLAN_HASH_VALUE   COUNT(*)
--------------- ----------
     2750403111         20
      808800347         14
     3862606077         13
      523145261         12
     1998571716         11

Note that all cursors using the same plan could be shared.

Using plan hash value 2750403111 find the statements that could be shared. Notice in the example that there are 20 child cursors

SQL> select  sql_id, address from v$sql
  2  where plan_hash_value=2750403111;

SQL_ID        ADDRESS
------------- --------
gm6myh185tp2u 478FC3B0
gm6myh185tp2u 478FC3B0

Note that full result output is omitted. 

Now having the SQL_ID and parent address of the cursors find the reasons for the cursors not being shared:

select  * from v$sql_shared_cursor
where (sql_id,address) in (
 select  sql_id, address from v$sql
 where plan_hash_value=2750403111
);