In addition to bind size change, data type change also causes Bind Mismatch, not Type Check Mismatch, at least according to v$sql_shared_cursor. (for more info about this view, see Note:296377.1) SQL> create table t (s varchar2(10)); Table created. SQL> declare 2 s_var varchar2(10) := 'a string'; 3 n_var number := 123; 4 begin 5 insert into t (s) values (s_var); 6 insert into t (s) values (n_var); 7 end; 8 / PL/SQL procedure successfully completed. SQL> col sql_text for a50 SQL> select address, child_address, sql_text from v$sql where lower(sql_text) like 'insert into t (s) %'; ADDRESS CHILD_AD SQL_TEXT -------- -------- -------------------------------------------------- 7A3B1920 7A38F464 INSERT into t (s) values (:b1) 7A3B1920 7A38ECB8 INSERT into t (s) values (:b1) -- The above two SQLs have the same sql_text but are not shared. Hence two -- entries in v$sql with the same address and hash_value, but different -- child_address, due to bind value data type difference (note the column -- Bind_Mismatch). Replace kglhdpar with address in 10g. SQL> select * from v$sql_shared_cursor where kglhdpar = '7A3B1920'; ADDRESS KGLHDPAR 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 -------- -------- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 7A38F464 7A3B1920 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 7A38ECB8 7A3B1920 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 ^ That B is Bind_Mismatch. -------------------------------------------------------------------------------- > Questions about translating v$sql_shared_cursor. > > First, for my particular SQL, I found there are 1026 child cursors. > But when I plug in the address to v$sql_shared_cursor, it only > returned 5. So my question is why not 1026? > > Here is SQL's with excessive child cursors: > > HASH_VALUE ADDRESS COUNT(*) > ---------- ---------------- ---------- > 10714010 0000000906F1FA70 1026 > 10714010 000000092AEF20E0 438 > 2786767810 00000008D7FEC5C8 1026 <-- > 2786767810 000000091FBECF20 791 > 3584469599 0000000906F25A48 1026 > 3584469599 000000092AC78870 796 > > I am interested in the one pointed to above. > > SQL> select * from v$sql_shared_cursor where KGLHDPAR = hextoraw('00000008D7FEC5C8'); > > ADDRESS KGLHDPAR 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 > ---------------- ---------------- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - > 0000000911FE1910 00000008D7FEC5C8 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 > 000000090B258D10 00000008D7FEC5C8 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 > 0000000903A45028 00000008D7FEC5C8 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 > 00000009193B26B0 00000008D7FEC5C8 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 > 0000000919AA9558 00000008D7FEC5C8 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 > > So why does it only return 5? What about the rest 1021 cursors? I don't know the exact conditions for SQL statements to appear in v$sql but not in v$sql_shared_cursor. Possible conditions are * They're DDLs. * Part of the cursor optimization info of the SQLs is flushed from library cache, causing v$sql.optimizer_mode='NONE' (Ref: http://groups.google.com/group/comp.databases.oracle.server/msg/03a3b1fed9a16999) * They're recursive SQLs. In your case, that specific SQL may have optmizer_mode='NONE' in v$sql for most of the 1021 children. If not, see if other columns of v$sql can give a clue (i.e. compare other columns between the 1021 rows and the 5 rows) Yong > You are right. All the remaining optimizer_mode is "NONE". > > So does that mean all these cursors' "heap 6" (no clue what that means) > are flushed? > > The question is why so many of them and why isn't the entire child > cursor aged out? If optimizer_mode='NONE', then yes their heap 6, which stores execution plans, is flushed. The reason why not the entire child cursor is flushed may be related to cursor invalidations; e.g. DDL was run on base objects. (If somebody flushed shared pool, the entire cursor would be out.) Can you check those NONE optimizer_mode cursors to see if their last_load_time differs from first_load_time? It should. What time is it? Does it correspond to the time of some DDL or ANALYZE job? BTW, heap 6 is also called cursor body (J. Morle, "Scaling Oracle8i") or sql area (download-west.oracle.com/oowsf2005/003wp.pdf). You can view cursor head and body as two entries separately in v$db_object_cache where type = 'CURSOR' and name = ''. The one with non-zero executions and larger sharable_mem is heap 6. Yong > All the last_load_time are blank and we have no analysis of any kind. > We still run rule mode. The first load time is when the box was started > midnight 2 weeks ago. -------------------------------------------------------------------------------- Commonly used queries against v$sql_shared_cursor when you don't know the address of a specific cursor /***** 9i version *****/ select a.* from v$sql_shared_cursor a, v$sql b where a.address = b.child_address and b.child_number >= 100; select * from v$sql_shared_cursor where unbound_cursor || sql_type_mismatch || optimizer_mismatch || outline_mismatch || stats_row_mismatch || literal_mismatch || sec_depth_mismatch || explain_plan_cursor || buffered_dml_mismatch || pdml_env_mismatch || inst_drtld_mismatch || slave_qc_mismatch || typecheck_mismatch || auth_check_mismatch || bind_mismatch || describe_mismatch || language_mismatch || translation_mismatch || row_level_sec_mismatch || insuff_privs || insuff_privs_rem || remote_trans_mismatch || logminer_session_mismatch || incomp_ltrl_mismatch || overlap_time_mismatch || sql_redirect_mismatch || mv_query_gen_mismatch || user_bind_peek_mismatch || typchk_dep_mismatch || no_trigger_mismatch || flashback_cursor like '%Y%'; --Matching on parent address select * from v$sql where address in (select kglhdpar from v$sql_shared_cursor where unbound_cursor || sql_type_mismatch || optimizer_mismatch || outline_mismatch || stats_row_mismatch || literal_mismatch || sec_depth_mismatch || explain_plan_cursor || buffered_dml_mismatch || pdml_env_mismatch || inst_drtld_mismatch || slave_qc_mismatch || typecheck_mismatch || auth_check_mismatch || bind_mismatch || describe_mismatch || language_mismatch || translation_mismatch || row_level_sec_mismatch || insuff_privs || insuff_privs_rem || remote_trans_mismatch || logminer_session_mismatch || incomp_ltrl_mismatch || overlap_time_mismatch || sql_redirect_mismatch || mv_query_gen_mismatch || user_bind_peek_mismatch || typchk_dep_mismatch || no_trigger_mismatch || flashback_cursor like '%Y%' ); --Matching on child address select * from v$sql where child_address in (select address from v$sql_shared_cursor where unbound_cursor || sql_type_mismatch || optimizer_mismatch || outline_mismatch || stats_row_mismatch || literal_mismatch || sec_depth_mismatch || explain_plan_cursor || buffered_dml_mismatch || pdml_env_mismatch || inst_drtld_mismatch || slave_qc_mismatch || typecheck_mismatch || auth_check_mismatch || bind_mismatch || describe_mismatch || language_mismatch || translation_mismatch || row_level_sec_mismatch || insuff_privs || insuff_privs_rem || remote_trans_mismatch || logminer_session_mismatch || incomp_ltrl_mismatch || overlap_time_mismatch || sql_redirect_mismatch || mv_query_gen_mismatch || user_bind_peek_mismatch || typchk_dep_mismatch || no_trigger_mismatch || flashback_cursor like '%Y%' ); /***** end for 9i version *****/ /***** 10gR2 version *****/ Differences from 9i: kglhdpar in 9i changed to address in 10g address in 9i changed to child_address in 10g plus much more columns in v$sql_shared_cursor Note: 10gR1 has more columns than 9i but less than 10gR2 select a.* from v$sql_shared_cursor a, v$sql b where a.child_address = b.child_address and b.child_number >= 100; select * from v$sql_shared_cursor where unbound_cursor || sql_type_mismatch || optimizer_mismatch || outline_mismatch || stats_row_mismatch || literal_mismatch || sec_depth_mismatch || explain_plan_cursor || buffered_dml_mismatch || pdml_env_mismatch || inst_drtld_mismatch || slave_qc_mismatch || typecheck_mismatch || auth_check_mismatch || bind_mismatch || describe_mismatch || language_mismatch || translation_mismatch || row_level_sec_mismatch || insuff_privs || insuff_privs_rem || remote_trans_mismatch || logminer_session_mismatch || incomp_ltrl_mismatch || overlap_time_mismatch || sql_redirect_mismatch || mv_query_gen_mismatch || user_bind_peek_mismatch || typchk_dep_mismatch || no_trigger_mismatch || flashback_cursor || anydata_transformation || incomplete_cursor || top_level_rpi_cursor || different_long_length || logical_standby_apply || diff_call_durn || bind_uacs_diff || plsql_cmp_switchs_diff || cursor_parts_mismatch || stb_object_mismatch || row_ship_mismatch || pq_slave_mismatch || top_level_ddl_mismatch || multi_px_mismatch || bind_peeked_pq_mismatch || mv_rewrite_mismatch || roll_invalid_mismatch || optimizer_mode_mismatch || px_mismatch || mv_staleobj_mismatch || flashback_table_mismatch || litrep_comp_mismatch like '%Y%'; --Matching on parent address select * from v$sql where address in (select address from v$sql_shared_cursor where unbound_cursor || sql_type_mismatch || optimizer_mismatch || outline_mismatch || stats_row_mismatch || literal_mismatch || sec_depth_mismatch || explain_plan_cursor || buffered_dml_mismatch || pdml_env_mismatch || inst_drtld_mismatch || slave_qc_mismatch || typecheck_mismatch || auth_check_mismatch || bind_mismatch || describe_mismatch || language_mismatch || translation_mismatch || row_level_sec_mismatch || insuff_privs || insuff_privs_rem || remote_trans_mismatch || logminer_session_mismatch || incomp_ltrl_mismatch || overlap_time_mismatch || sql_redirect_mismatch || mv_query_gen_mismatch || user_bind_peek_mismatch || typchk_dep_mismatch || no_trigger_mismatch || flashback_cursor || anydata_transformation || incomplete_cursor || top_level_rpi_cursor || different_long_length || logical_standby_apply || diff_call_durn || bind_uacs_diff || plsql_cmp_switchs_diff || cursor_parts_mismatch || stb_object_mismatch || row_ship_mismatch || pq_slave_mismatch || top_level_ddl_mismatch || multi_px_mismatch || bind_peeked_pq_mismatch || mv_rewrite_mismatch || roll_invalid_mismatch || optimizer_mode_mismatch || px_mismatch || mv_staleobj_mismatch || flashback_table_mismatch || litrep_comp_mismatch like '%Y%' ); --Matching on child address select * from v$sql where child_address in (select child_address from v$sql_shared_cursor where unbound_cursor || sql_type_mismatch || optimizer_mismatch || outline_mismatch || stats_row_mismatch || literal_mismatch || sec_depth_mismatch || explain_plan_cursor || buffered_dml_mismatch || pdml_env_mismatch || inst_drtld_mismatch || slave_qc_mismatch || typecheck_mismatch || auth_check_mismatch || bind_mismatch || describe_mismatch || language_mismatch || translation_mismatch || row_level_sec_mismatch || insuff_privs || insuff_privs_rem || remote_trans_mismatch || logminer_session_mismatch || incomp_ltrl_mismatch || overlap_time_mismatch || sql_redirect_mismatch || mv_query_gen_mismatch || user_bind_peek_mismatch || typchk_dep_mismatch || no_trigger_mismatch || flashback_cursor || anydata_transformation || incomplete_cursor || top_level_rpi_cursor || different_long_length || logical_standby_apply || diff_call_durn || bind_uacs_diff || plsql_cmp_switchs_diff || cursor_parts_mismatch || stb_object_mismatch || row_ship_mismatch || pq_slave_mismatch || top_level_ddl_mismatch || multi_px_mismatch || bind_peeked_pq_mismatch || mv_rewrite_mismatch || roll_invalid_mismatch || optimizer_mode_mismatch || px_mismatch || mv_staleobj_mismatch || flashback_table_mismatch || litrep_comp_mismatch like '%Y%' ); /***** end for 10gR2 version *****/ /***** 11.1.0.7 version (11.1.0.6 doesn't have the last column hash_match_failed) *****/ select * from v$sql_shared_cursor where unbound_cursor || sql_type_mismatch || optimizer_mismatch || outline_mismatch || stats_row_mismatch || literal_mismatch || force_hard_parse || explain_plan_cursor || buffered_dml_mismatch || pdml_env_mismatch || inst_drtld_mismatch || slave_qc_mismatch || typecheck_mismatch || auth_check_mismatch || bind_mismatch || describe_mismatch || language_mismatch || translation_mismatch || row_level_sec_mismatch || insuff_privs || insuff_privs_rem || remote_trans_mismatch || logminer_session_mismatch || incomp_ltrl_mismatch || overlap_time_mismatch || edition_mismatch || mv_query_gen_mismatch || user_bind_peek_mismatch || typchk_dep_mismatch || no_trigger_mismatch || flashback_cursor || anydata_transformation || incomplete_cursor || top_level_rpi_cursor || different_long_length || logical_standby_apply || diff_call_durn || bind_uacs_diff || plsql_cmp_switchs_diff || cursor_parts_mismatch || stb_object_mismatch || crossedition_trigger_mismatch || pq_slave_mismatch || top_level_ddl_mismatch || multi_px_mismatch || bind_peeked_pq_mismatch || mv_rewrite_mismatch || roll_invalid_mismatch || optimizer_mode_mismatch || px_mismatch || mv_staleobj_mismatch || flashback_table_mismatch || litrep_comp_mismatch || plsql_debug || load_optimizer_stats || acl_mismatch || flashback_archive_mismatch || lock_user_schema_failed || remote_mapping_mismatch || load_runtime_heap_failed || hash_match_failed like '%Y%'; /***** end for 11.1.0.7 version *****/ -------------------------------------------------------------------------------- Sometimes v$sql_shared_cursor fails to reveal why cursors are not shared. It's known that when cursor_sharing=similar, a histogram on a column causes hard parses (even when the data is not skewed). Test code is from www.oracle.com/technology/oramag/oracle/06-jan/o16asktom.html except for the first SQL (create table). Test is done on 9.2.0.1.0 and reproduced in 10gR1, 10gR2, 11.1.0.6. create table t as select rownum id from all_objects; create index t_idx on t (id); begin dbms_stats.gather_table_stats ( ownname => USER, tabname => 'T', method_opt => 'for all indexed columns size 254', cascade => TRUE ); end; / alter session set cursor_sharing=similar; select * from t CS_SIMILAR where id = 1; select * from t CS_SIMILAR where id = 50; select * from t CS_SIMILAR where id = 99; select * from t CS_SIMILAR where id = 1; select * from t CS_SIMILAR where id = 50; select * from t CS_SIMILAR where id = 99; select sql_text from v$sql where sql_text like 'select * from t CS% where id = %' order by sql_text; The last query does show 3 rows: SQL_TEXT ------------------------------------------------ select * from t CS_SIMILAR where id = :"SYS_B_0" select * from t CS_SIMILAR where id = :"SYS_B_0" select * from t CS_SIMILAR where id = :"SYS_B_0" As usual, I want to see why the same SQL is not shared (change kglhdpar to address in 10g; my address '7911C6A0' is from v$sql.address): SQL> select * from v$sql_shared_cursor where kglhdpar = '7911C6A0'; ADDRESS KGLHDPAR 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 -------- -------- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 7911C184 7911C6A0 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 7910BF7C 7911C6A0 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 7910BA00 7911C6A0 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 You see, none of the "reason for not sharing" columns is 'Y'. If I look at the plan (select * from v$sql_plan where address = '7911C6A0'), I see 3 rows with no difference except, of course, under child_number. This means it doesn't matter whether the data is skewed or not. If you have a histogram, a different SQL child cursor is created (i.e. a hard parse happens). Unfortunately, v$sql_shared_cursor fails to tell us why the sibling cursors are not one single cursor. I realized that I actually read the same conclusion in Jonathan Lewis's "Cost-Based Oracle" earlier. On p.159 of his book, he says "two things will trigger this reoptimization [when cursor_sharing=similar]: first, if any of the predicates involves a range scan, and second, even on a simple equality, if there is histogram on a column that appears in a predicate, the query will be reoptimized." In my test case, both conditions are met, although just the second condition is enough (as in biti's test at blog.csdn.net/biti_rainy/archive/2004/07/12/39466.aspx).