Unshared cursors (WAS: Stored outline not being used...)

Hey all,
 
I opened an SR on this.  While I don't have a repeatable process yet
(and therefore no BUG apparently as per a previous thread here), this
problem seems to be pointing to flushing the shared pool.  The kicker is
that I don't think I'll be able to look at this problem any longer other
than to put in our SOPs not to flush the shared pool.
 
Has anyone else seen this in 10gR2, specifically with flushing the
shared pool?  From past experience and advice, I flush our hybrid
production 9.2.0.5.0 nightly with no known side effects.
 
Rich

________________________________

From: Jesse, Rich 
Sent: Thursday, December 07, 2006 7:40 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Stored outline not being used all the times...


Hi Fairle,
 
The key word here is "should".  I agree, but if you look at my query,
you'll see that it's returning multiple rows with all "N"s for the same
address.  At least it is for me on 10.2.0.2.0.
 
My 1 year old kept me up until 2:00 AM last night, so I could be wrong.
I'll try and hammer away at it some more tomorrow.
 
Thanks!
Rich

________________________________

From: fairlie rego [mailto:fairlie_r@xxxxxxxxx] 
Sent: Thursday, December 07, 2006 5:48 PM
To: binhpham15@xxxxxxxxxxx; Jesse, Rich
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: Stored outline not being used all the times...


The row with all 'N' should be the first child as mentioned here
<http://el-caro.blogspot.com/2006/11/diagnosing-unshared-sql-in-10g.html
> 
All subsequent children should have some mismatch.
 
-Fairlie

Binh Pham <binhpham15@xxxxxxxxxxx> wrote:

        I've seen those before, tried to understand why but no clue. Any
clue?
        
        

                
________________________________

                From: "Jesse, Rich" <Rich.Jesse@xxxxxx>
                Reply-To: Rich.Jesse@xxxxxx
                To: <oracle-l@xxxxxxxxxxxxx>
                Subject: RE: Stored outline not being used all the
times...
                Date: Thu, 7 Dec 2006 16:22:25 -0600
                
                
                The real fun begins when rows in there have multiple
entries for the same address, but all the columns are "N".
                 
                Oh, wait, that's the fun I'm having right now...
                 
                Good timing on the thread!
                 
                Rich
                 
                p.s.  I used this quick'n'dirty query in 10.2.0.2.0,
which was easy to whip out via Toad:
                 
                select *
                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||
mv_rewrite_mismatch|| 
                    roll_invalid_mismatch|| optimizer_mode_mismatch||
px_mismatch|| 
                    mv_staleobj_mismatch|| flashback_table_mismatch||
litrep_comp_mismatch "FLAGS",
                   count(*)
                 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||
mv_rewrite_mismatch|| 
                    roll_invalid_mismatch|| optimizer_mode_mismatch||
px_mismatch|| 
                    mv_staleobj_mismatch|| flashback_table_mismatch||
litrep_comp_mismatch
                 having count(*) > 1
                 order by count(*) desc
                )
                where flags =
'NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN';

Other related posts: