Re: Multiple SQL version count with cusror_sharing=similar
From: Rich Jesse <rjoralist_at_society.servebeer.com>
Date: Wed, 1 Jul 2009 08:39:30 -0500 (CDT)
Message-ID: <e882d0aa87e69b0eedb1d5f89f8536a5.squirrel_at_society.servebeer.com>
> BTW, while search in metalink, there are numbers of bug hits related to the
> issue.
>
> Regards,
> Neeraj Bhatia
FROM v$sql_shared_cursor
group by
address,
unbound_cursor|| sql_type_mismatch||
) "SC", v$sqlarea vsa
order by total desc;
Date: Wed, 1 Jul 2009 08:39:30 -0500 (CDT)
Message-ID: <e882d0aa87e69b0eedb1d5f89f8536a5.squirrel_at_society.servebeer.com>
> 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
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Jul 01 2009 - 08:39:30 CDT