Re: Multiple SQL version count with cusror_sharing=similar
- From: "Rich Jesse" <rjoralist@xxxxxxxxxxxxxxxxxxxxx>
- To: "Oracle L" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 1 Jul 2009 08:39:30 -0500 (CDT)
> BTW, while search in metalink, there are numbers of bug hits related to the
> issue.
>
> Regards,
> Neeraj Bhatia
FWIW, I've run into a situation without using CS=S on 10.1 and 10.2. The
interesting part is that v$sql_shared_cursor showed "N" for ALL reasons for
a few cursors. I had opened an SR with Oracle Support, but didn't get any
resolution. Here's a query for 10gR1 that can show SQLs with children with
no reason:
select sc.address, sc.total, vsa.sql_text
from
(
SELECT address,
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|| litrep_comp_mismatch "FLAGS",
count(*) "TOTAL"
FROM v$sql_shared_cursor
group by
address,
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|| litrep_comp_mismatch
having count(*) > 1
) "SC", v$sqlarea vsa
where flags = 'NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN'
and sc.address = vsa.address
order by total desc;
This may work in 10gR2 or 11, but I'm almost positive there are more columns
for v$sql_shared_cursor in the newer versions of Oracle.
GL!
Rich
--
//www.freelists.org/webpage/oracle-l
Other related posts:
