Re: Unshared cursors redux

On 2/21/07, Rich Jesse <rjoralist_at_society.servebeer.com> 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

--
http://www.freelists.org/webpage/oracle-l