Re: Unshared cursors redux
- From: "Paul Drake" <bdbafh@xxxxxxxxx>
- To: rjoralist@xxxxxxxxxxxxxxxxxxxxx
- Date: Wed, 21 Feb 2007 20:42:24 -0500
On 2/21/07, Rich Jesse <rjoralist@xxxxxxxxxxxxxxxxxxxxx> wrote:
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
Rich,
The 10.2.0.3 patch 1 for MS Win32 got a shared_pool miss rate down from 98%
to 97% in one database. I don't think that you're going to see relief in
anything out there currently available.
SR it is, unless it can wait until after the NCAA Men's Basketball
Championship Bracket is announced - I mean the DST change occurs.
Paul
Other related posts:
