Unshared cursors redux

From: Rich Jesse <rjoralist_at_society.servebeer.com>
Date: Wed, 21 Feb 2007 10:21:50 -0600 (CST)
Message-ID: <53892.12.17.117.251.1172074910.squirrel@12.17.117.251>

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

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

Received on Wed Feb 21 2007 - 10:21:50 CST