This is a decent way to determine why a cursor isn\u2019t being shared, resulting in cache locking during parsing (134 minutes total yesterday, which is very high historically)\u2026<\/p>\n
\r\nSQL> select count(*),sql_id from gv$active_session_history where event = 'library cache lock' group by sql_id;\r\n\r\n COUNT(*) SQL_ID\r\n---------- -------------\r\n 1 acw00kt1g6n72\r\n 3\r\n 32 8szmwam7fysa3\r\n 152 b6usrg82hwsa3\r\n 1 gckv8vvtcy5uw\r\n 8013 9n7wrxfxa60kw\r\n 1 6khs807zv1qgg\r\n 10 am6hf3up3tgch\r\n 1 8ur95t8x1br3t\r\n 1 76krfytdy0tn6\r\n 41 6t6v2p2trcmb3\r\n\r\n11 rows selected.\r\n\r\nSQL> select distinct sql_text from gv$sql where sql_id = '9n7wrxfxa60kw';\r\n\r\nSQL_TEXT\r\n--------------------------------------------------------------------------------\r\nINSERT INTO dcspp_order(order_id,type,version,order_class_type,profile_id,description,state,creation_date,\r\n origin_of_order,explicitly_saved,last_modified_date,sales_channel,creation_site_id,site_id,gwp) \r\n VALUES(:1 ,:2 ,:3 ,:4 ,:5 ,:6 ,:7 ,:8 ,:9 ,:10 ,:11 ,:12 ,:13 ,:14 ,:15 )\r\n\r\nSQL> select count(*) from gv$sql where sql_id = '9n7wrxfxa60kw';\r\n\r\n COUNT(*)\r\n----------\r\n 134\r\n\r\nSQL>\r\n<\/pre>\n134 versions sounds really high, so we find out why they aren\u2019t being shared using a quick python script and cx_Oracle (*really* good library for ad hoc queries like this)\u2026<\/p>\n
\r\n#!\/usr\/local\/bin\/python\r\n\r\nimport cx_Oracle, sys\r\n\r\nconnection = cx_Oracle.connect(\"*****\/******@db_server:1521\/db_service.db_domain\")\r\ncursor = connection.cursor()\r\ncursor.execute(\"select * from gv$sql_shared_cursor where sql_id = '9n7wrxfxa60kw'\")\r\ncolcount = len(cursor.description)\r\nd = dict()\r\nfor row in cursor:\r\n for col in range(colcount):\r\n if col == 1:\r\n sql_id = row[col]\r\n elif row[col] == \"Y\":\r\n if cursor.description[col][0] in d:\r\n d[cursor.description[col][0]] = d[cursor.description[col][0]] + 1\r\n else:\r\n d[cursor.description[col][0]] = 1\r\nfor j in d:\r\n print j.ljust(40),d[j]\r\n<\/pre>\n\u2026and when we run it, we find the following\u2026<\/p>\n
\r\n\/home\/showard>.\/shared.py\r\nBIND_MISMATCH 79\r\nBIND_LENGTH_UPGRADEABLE 7\r\nTRANSLATION_MISMATCH 118\r\nAUTH_CHECK_MISMATCH 118\r\nROLL_INVALID_MISMATCH 123\r\n\/home\/showard>\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"This is a decent way to determine why a cursor isn\u2019t being shared, resulting in cache locking during parsing (134 minutes total yesterday, which is very high historically)\u2026 SQL> select count(*),sql_id from gv$active_session_history where event = ‘library cache lock’ group…<\/p>\n