Unshared cursors redux
- From: "Rich Jesse" <rjoralist@xxxxxxxxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 21 Feb 2007 10:21:50 -0600 (CST)
Hi all,
While investigating a hot table in 10.1.0.5, I see that one of the SELECTs
hitting it has multiple children. No big deal, I'll just use my buddy
V$SQL_SHARED_CURSOR to see the issue, right? Wrong. Once again, all
explanation columns for every occurance of the cursor is "N". Here's the
SQL I used to check for more of them (note: this is specific to 10gR1!):
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;
<groan> Here we go again! The last time I saw this problem was on 10gR2,
but after extensive work on an SR, that issue could possibly be blamed on
nightly shared pool flushing (which I still think is a BUG, but I'm no
longer able to access that particular SR). According to the alert log for
this database, there has been no SP flushing.
Could it be that the 10gR1 fixed view is just not "mature" enough and that
the reason columns explaining the multiple cursors was added in 10gR2?
I *really* don't have the time to deal with an SR on this... Thoughts anyone
???
TIA!
Rich
--
//www.freelists.org/webpage/oracle-l
Other related posts:
