{"id":1233,"date":"2015-10-29T15:32:31","date_gmt":"2015-10-29T13:32:31","guid":{"rendered":"http:\/\/www.ludovicocaldara.net\/dba\/?p=1233"},"modified":"2020-08-18T16:29:37","modified_gmt":"2020-08-18T14:29:37","slug":"cursor-sharing-sql-id","status":"publish","type":"post","link":"https:\/\/www.ludovicocaldara.net\/dba\/cursor-sharing-sql-id\/","title":{"rendered":"Get information about Cursor Sharing for a SQL_ID"},"content":{"rendered":"

Yesterday I’ve got a weird problem with Adaptive Cursor Sharing. I’m not sure yet about the issue, but it seems to be related to cursor sharing histograms. Hopefully one day I will blog about what I’ve learnt from this experience.<\/p>\n

To better monitor the problem on that specific query, I’ve prepared this script (tested on 12.1.0.2):<\/p>\n

COLUMN Shareable HEADING 'S|H|A|R|E|A|B|L|E'\r\nCOLUMN \"Bind-Aware\" HEADING 'B|I|N|D| |A|W|A|R|E'\r\nCOLUMN Sensitive HEADING 'S|E|N|S|I|T|I|V|E'\r\nCOLUMN Reoptimizable HEADING 'R|E|O|P|T|I|M|I|Z|A|B|L|E'\r\nBREAK on child_number on Execs on \"Gets\/Exec\" on \"Ela\/Exec\" on \"Sensitive\" on \"Shareable\" on \"Bind-Aware\" on bucket0 on bucket1 on bucket2 on cnt on \"Reoptimizable\" on is_resolved_adaptive_plan\r\n\r\nselect * from (select *\r\n  from (\r\nselect \r\ns.child_number,\r\n  s.plan_hash_value,\r\n  executions as Execs, \r\n  round(buffer_gets\/executions) as \"Gets\/Exec\",\r\n  round(elapsed_time\/executions) as \"Ela\/Exec\",\r\n  is_bind_sensitive as \"Sensitive\",\r\n  is_shareable as \"Shareable\",\r\n  is_bind_aware as \"Bind-Aware\",\r\n  to_char(h.bucket_id) as bucket, h.count as cnt,\r\n  is_reoptimizable as \"Reoptimizable\",\r\n  is_resolved_adaptive_plan,\r\n  \"UNBOUND_CURSOR\",  \"SQL_TYPE_MISMATCH\",  \"OPTIMIZER_MISMATCH\",\r\n  \"OUTLINE_MISMATCH\", \"STATS_ROW_MISMATCH\", \"LITERAL_MISMATCH\",\r\n  \"FORCE_HARD_PARSE\", \"EXPLAIN_PLAN_CURSOR\", \"BUFFERED_DML_MISMATCH\",\r\n  \"PDML_ENV_MISMATCH\", \"INST_DRTLD_MISMATCH\", \"SLAVE_QC_MISMATCH\",\r\n  \"TYPECHECK_MISMATCH\", \"AUTH_CHECK_MISMATCH\", \"BIND_MISMATCH\",\r\n  \"DESCRIBE_MISMATCH\", \"LANGUAGE_MISMATCH\", \"TRANSLATION_MISMATCH\",\r\n  \"BIND_EQUIV_FAILURE\", \"INSUFF_PRIVS\", \"INSUFF_PRIVS_REM\",\r\n  \"REMOTE_TRANS_MISMATCH\", \"LOGMINER_SESSION_MISMATCH\", \"INCOMP_LTRL_MISMATCH\",\r\n  \"OVERLAP_TIME_MISMATCH\", \"EDITION_MISMATCH\", \"MV_QUERY_GEN_MISMATCH\",\r\n  \"USER_BIND_PEEK_MISMATCH\", \"TYPCHK_DEP_MISMATCH\", \"NO_TRIGGER_MISMATCH\",\r\n  \"FLASHBACK_CURSOR\", \"ANYDATA_TRANSFORMATION\", \"PDDL_ENV_MISMATCH\",\r\n  \"TOP_LEVEL_RPI_CURSOR\", \"DIFFERENT_LONG_LENGTH\", \"LOGICAL_STANDBY_APPLY\",\r\n  \"DIFF_CALL_DURN\", \"BIND_UACS_DIFF\", \"PLSQL_CMP_SWITCHS_DIFF\",\r\n  \"CURSOR_PARTS_MISMATCH\", \"STB_OBJECT_MISMATCH\", \"CROSSEDITION_TRIGGER_MISMATCH\",\r\n  \"PQ_SLAVE_MISMATCH\", \"TOP_LEVEL_DDL_MISMATCH\", \"MULTI_PX_MISMATCH\",\r\n  \"BIND_PEEKED_PQ_MISMATCH\", \"MV_REWRITE_MISMATCH\", \"ROLL_INVALID_MISMATCH\",\r\n  \"OPTIMIZER_MODE_MISMATCH\", \"PX_MISMATCH\", \"MV_STALEOBJ_MISMATCH\",\r\n  \"FLASHBACK_TABLE_MISMATCH\", \"LITREP_COMP_MISMATCH\", \"PLSQL_DEBUG\",\r\n  \"LOAD_OPTIMIZER_STATS\", \"ACL_MISMATCH\", \"FLASHBACK_ARCHIVE_MISMATCH\",\r\n  \"LOCK_USER_SCHEMA_FAILED\", \"REMOTE_MAPPING_MISMATCH\", \"LOAD_RUNTIME_HEAP_FAILED\",\r\n  \"HASH_MATCH_FAILED\", \"PURGED_CURSOR\", \"BIND_LENGTH_UPGRADEABLE\",\r\n  \"USE_FEEDBACK_STATS\"\r\nfrom v$sql s\r\n  join v$sql_cs_histogram h\r\n    on (s.sql_id=h.sql_id and\r\n\ts.child_number=h.child_number and\r\n\ts.con_id=h.con_id\r\n\t)\r\n  join v$sql_shared_cursor shc\r\n    on (shc.sql_id=h.sql_id and \r\n\tshc.child_number=h.child_number and\r\n\ts.con_id=shc.con_id\r\n\t)\r\n\twhere s.sql_id='&sql_id'\r\n)\r\npivot (sum(cnt) for (bucket) IN ('0' AS Bucket0,'1' AS Bucket1,'2' AS Bucket2))\r\n)\r\nunpivot (result FOR reason_type IN (\"UNBOUND_CURSOR\",\r\n  \"SQL_TYPE_MISMATCH\", \"OPTIMIZER_MISMATCH\",\r\n  \"OUTLINE_MISMATCH\", \"STATS_ROW_MISMATCH\", \"LITERAL_MISMATCH\",\r\n  \"FORCE_HARD_PARSE\", \"EXPLAIN_PLAN_CURSOR\", \"BUFFERED_DML_MISMATCH\",\r\n  \"PDML_ENV_MISMATCH\", \"INST_DRTLD_MISMATCH\", \"SLAVE_QC_MISMATCH\",\r\n  \"TYPECHECK_MISMATCH\", \"AUTH_CHECK_MISMATCH\", \"BIND_MISMATCH\",\r\n  \"DESCRIBE_MISMATCH\", \"LANGUAGE_MISMATCH\", \"TRANSLATION_MISMATCH\",\r\n  \"BIND_EQUIV_FAILURE\", \"INSUFF_PRIVS\", \"INSUFF_PRIVS_REM\",\r\n  \"REMOTE_TRANS_MISMATCH\", \"LOGMINER_SESSION_MISMATCH\", \"INCOMP_LTRL_MISMATCH\",\r\n  \"OVERLAP_TIME_MISMATCH\", \"EDITION_MISMATCH\", \"MV_QUERY_GEN_MISMATCH\",\r\n  \"USER_BIND_PEEK_MISMATCH\", \"TYPCHK_DEP_MISMATCH\", \"NO_TRIGGER_MISMATCH\",\r\n  \"FLASHBACK_CURSOR\", \"ANYDATA_TRANSFORMATION\", \"PDDL_ENV_MISMATCH\",\r\n  \"TOP_LEVEL_RPI_CURSOR\", \"DIFFERENT_LONG_LENGTH\", \"LOGICAL_STANDBY_APPLY\",\r\n  \"DIFF_CALL_DURN\", \"BIND_UACS_DIFF\", \"PLSQL_CMP_SWITCHS_DIFF\",\r\n  \"CURSOR_PARTS_MISMATCH\", \"STB_OBJECT_MISMATCH\", \"CROSSEDITION_TRIGGER_MISMATCH\",\r\n  \"PQ_SLAVE_MISMATCH\", \"TOP_LEVEL_DDL_MISMATCH\", \"MULTI_PX_MISMATCH\",\r\n  \"BIND_PEEKED_PQ_MISMATCH\", \"MV_REWRITE_MISMATCH\", \"ROLL_INVALID_MISMATCH\",\r\n  \"OPTIMIZER_MODE_MISMATCH\", \"PX_MISMATCH\", \"MV_STALEOBJ_MISMATCH\",\r\n  \"FLASHBACK_TABLE_MISMATCH\", \"LITREP_COMP_MISMATCH\", \"PLSQL_DEBUG\",\r\n  \"LOAD_OPTIMIZER_STATS\", \"ACL_MISMATCH\", \"FLASHBACK_ARCHIVE_MISMATCH\",\r\n  \"LOCK_USER_SCHEMA_FAILED\", \"REMOTE_MAPPING_MISMATCH\", \"LOAD_RUNTIME_HEAP_FAILED\",\r\n  \"HASH_MATCH_FAILED\", \"PURGED_CURSOR\", \"BIND_LENGTH_UPGRADEABLE\",\r\n  \"USE_FEEDBACK_STATS\"))\r\nwhere result='Y'\r\norder by child_number;<\/pre>\n
The result is something similar (in my case it has 26 child cursors):<\/p>\n
                                                                    R\r\n                                                                    E\r\n                                                                    O\r\n                                                                  B P\r\n                                                              S S I T\r\n                                                              E H N I\r\n                                                              N A D M\r\n                                                              S R   I\r\n                                                              I E A Z\r\n                                                              T A W A\r\n                                                              I B A B\r\n                                                              V L R L\r\nCHILD_NUMBER PLAN_HASH_VALUE      EXECS  Gets\/Exec   Ela\/Exec E E E E I    BUCKET0    BUCKET1    BUCKET2 REASON_TYPE                   R\r\n------------ --------------- ---------- ---------- ---------- - - - - - ---------- ---------- ---------- ----------------------------- -\r\n           0      2293695281        455       2466      14464 Y Y Y N            0        455          0 ROLL_INVALID_MISMATCH         Y\r\n                  2293695281                                                                             BIND_EQUIV_FAILURE            Y\r\n           1      1690560038         99      13943     103012 Y Y Y N            0         99          0 ROLL_INVALID_MISMATCH         Y\r\n                  1690560038                                                                             BIND_EQUIV_FAILURE            Y\r\n           2      3815006743        541      43090     230245 Y Y Y N            0        541          0 BIND_EQUIV_FAILURE            Y\r\n                  3815006743                                                                             ROLL_INVALID_MISMATCH         Y\r\n           3      1483632464        251       4111      18940 Y Y Y N           49        202          0 ROLL_INVALID_MISMATCH         Y\r\n                  1483632464                                                                             BIND_EQUIV_FAILURE            Y\r\n           4      3815006743       1152      42632     220730 Y Y Y N            0       1000          0 BIND_EQUIV_FAILURE            Y\r\n                  3815006743                                                                             ROLL_INVALID_MISMATCH         Y\r\n           5      3922835573        150      39252     184176 Y Y Y N            0        150          0 ROLL_INVALID_MISMATCH         Y\r\n                  3922835573                                                                             BIND_EQUIV_FAILURE            Y\r\n           6       767857637          3       4731     124707 Y Y Y N            0          3          0 ROLL_INVALID_MISMATCH         Y\r\n                   767857637                                                                             BIND_EQUIV_FAILURE            Y\r\n           7       767857637         11       4739      71119 Y Y Y N            0         11          0 BIND_EQUIV_FAILURE            Y\r\n           8      2800467281          1        307     249727 Y Y Y N            0          1          0 BIND_EQUIV_FAILURE            Y\r\n           9      3123241890        536       2982      14428 Y Y Y N            6        530          0 ROLL_INVALID_MISMATCH         Y\r\n                  3123241890                                                                             BIND_EQUIV_FAILURE            Y\r\n          10      3125518635         17        315      16492 Y Y Y N           16          1          0 ROLL_INVALID_MISMATCH         Y\r\n                  3125518635                                                                             BIND_EQUIV_FAILURE            Y\r\n          11      2184442252        130       4686      40188 Y Y Y N            0        130          0 ROLL_INVALID_MISMATCH         Y\r\n                  2184442252                                                                             BIND_EQUIV_FAILURE            Y\r\n          12      3815006743        553      42765     231391 Y Y Y N            0        553          0 ROLL_INVALID_MISMATCH         Y\r\n                  3815006743                                                                             BIND_EQUIV_FAILURE            Y\r\n          13      1166983254         47      14193     111256 Y Y Y N            0         47          0 BIND_EQUIV_FAILURE            Y\r\n                  1166983254                                                                             ROLL_INVALID_MISMATCH         Y\r\n          14      2307602173          2         38      45922 Y Y Y N            2          0          0 BIND_EQUIV_FAILURE            Y\r\n                  2307602173                                                                             ROLL_INVALID_MISMATCH         Y\r\n          15       767857637         11       4304      59617 Y Y Y N            0         11          0 BIND_EQUIV_FAILURE            Y\r\n                   767857637                                                                             ROLL_INVALID_MISMATCH         Y\r\n          16      3108045525          2      34591     176749 Y N N N            1          1          0 ROLL_INVALID_MISMATCH         Y\r\n                  3108045525                                                                             LOAD_OPTIMIZER_STATS          Y\r\n                  3108045525                                                                             BIND_EQUIV_FAILURE            Y\r\n          17      3108045525          6       1794      33335 Y Y Y N            4          2          0 BIND_EQUIV_FAILURE            Y\r\n                  3108045525                                                                             ROLL_INVALID_MISMATCH         Y\r\n          18      2440443365        470       2009      13361 Y Y Y N            0        470          0 ROLL_INVALID_MISMATCH         Y\r\n                  2440443365                                                                             BIND_EQUIV_FAILURE            Y\r\n          19      4079924956         15       2032      19773 Y Y Y N            8          7          0 ROLL_INVALID_MISMATCH         Y\r\n                  4079924956                                                                             BIND_EQUIV_FAILURE            Y\r\n          20       777919270         32       2675      18260 Y Y Y N           11         21          0 BIND_EQUIV_FAILURE            Y\r\n                   777919270                                                                             ROLL_INVALID_MISMATCH         Y\r\n          21      1428146033         63      13929     111116 Y Y Y N            0         63          0 ROLL_INVALID_MISMATCH         Y\r\n                  1428146033                                                                             BIND_EQUIV_FAILURE            Y\r\n          22      3815006743        218      43673     234642 Y Y Y N            0        218          0 BIND_EQUIV_FAILURE            Y\r\n                  3815006743                                                                             ROLL_INVALID_MISMATCH         Y\r\n          23       277802667          1         62      99268 Y Y Y N            1          0          0 BIND_EQUIV_FAILURE            Y\r\n                   277802667                                                                             ROLL_INVALID_MISMATCH         Y\r\n          24      3898025231          3       2364     111231 Y Y Y N            0          3          0 BIND_EQUIV_FAILURE            Y\r\n                  3898025231                                                                             ROLL_INVALID_MISMATCH         Y\r\n          25       767857637          2       6495     169363 Y Y Y N            0          2          0 ROLL_INVALID_MISMATCH         Y\r\n                   767857637                                                                             BIND_EQUIV_FAILURE            Y\r\n          26      3690167092        100       2998      20138 Y Y Y N            0        100          0 BIND_EQUIV_FAILURE            Y\r\n                  3690167092                                                                             ROLL_INVALID_MISMATCH         Y\r\n<\/pre>\n
It’s a quick way to get the relevant information in a single result.<\/p>\n
Off course, if you need deeper details, you should consider something more powerful like SQLd360 <\/a>from Mauro Pagano.<\/p>\n
Credits: I’ve got the unpivot idea (and copied that part of the code) from this post<\/a> by Timur Akhmadeev<\/a>.<\/p>\n
—<\/p>\n
Ludo<\/p>\n","protected":false},"excerpt":{"rendered":"
Yesterday I’ve got a weird problem with Adaptive Cursor Sharing. I’m not sure yet about the issue, but it seems to be related to cursor sharing histograms. Hopefully one day I will blog about what I’ve learnt from this experience. … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[326,3,52,6,132],"tags":[243,244,22],"class_list":["post-1233","post","type-post","status-publish","format-standard","hentry","category-oracle","category-oracledb","category-12c","category-perf","category-triblog","tag-adaptive-cursor-sharing","tag-cursor-sharing","tag-oracle-database"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/1233","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/comments?post=1233"}],"version-history":[{"count":3,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/1233\/revisions"}],"predecessor-version":[{"id":1236,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/1233\/revisions\/1236"}],"wp:attachment":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/media?parent=1233"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/categories?post=1233"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/tags?post=1233"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}