Why isn’t my cursor being shared?

This is a decent way to determine why a cursor isn’t being shared, resulting in cache locking during parsing (134 minutes total yesterday, which is very high historically)…

SQL> select count(*),sql_id from gv$active_session_history where event = 'library cache lock' group by sql_id;

  COUNT(*) SQL_ID
---------- -------------
         1 acw00kt1g6n72
         3
        32 8szmwam7fysa3
       152 b6usrg82hwsa3
         1 gckv8vvtcy5uw
      8013 9n7wrxfxa60kw
         1 6khs807zv1qgg
        10 am6hf3up3tgch
         1 8ur95t8x1br3t
         1 76krfytdy0tn6
        41 6t6v2p2trcmb3

11 rows selected.

SQL> select distinct sql_text from gv$sql where sql_id = '9n7wrxfxa60kw';

SQL_TEXT
--------------------------------------------------------------------------------
INSERT INTO dcspp_order(order_id,type,version,order_class_type,profile_id,description,state,creation_date,
                        origin_of_order,explicitly_saved,last_modified_date,sales_channel,creation_site_id,site_id,gwp)  
  VALUES(:1 ,:2 ,:3 ,:4 ,:5 ,:6 ,:7 ,:8 ,:9 ,:10 ,:11 ,:12 ,:13 ,:14 ,:15 )

SQL> select count(*) from gv$sql where sql_id = '9n7wrxfxa60kw';

  COUNT(*)
----------
       134

SQL>

134 versions sounds really high, so we find out why they aren’t being shared using a quick python script and cx_Oracle (*really* good library for ad hoc queries like this)…

#!/usr/local/bin/python

import cx_Oracle, sys

connection = cx_Oracle.connect("*****/******@db_server:1521/db_service.db_domain")
cursor = connection.cursor()
cursor.execute("select * from gv$sql_shared_cursor where sql_id = '9n7wrxfxa60kw'")
colcount = len(cursor.description)
d = dict()
for row in cursor:
  for col in range(colcount):
    if col == 1:
      sql_id = row[col]
    elif row[col] == "Y":
      if cursor.description[col][0] in d:
        d[cursor.description[col][0]] = d[cursor.description[col][0]] + 1
      else:
        d[cursor.description[col][0]] = 1
for j in d:
  print j.ljust(40),d[j]

…and when we run it, we find the following…

/home/showard>./shared.py
BIND_MISMATCH                            79
BIND_LENGTH_UPGRADEABLE                  7
TRANSLATION_MISMATCH                     118
AUTH_CHECK_MISMATCH                      118
ROLL_INVALID_MISMATCH                    123
/home/showard>