Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Unshared cursors (WAS: Stored outline not being used...)

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

From: <Rajesh.Rao_at_jpmchase.com>
Date: Tue, 2 Jan 2007 12:02:59 -0500
Message-ID: <OFB57250D7.5792D558-ON85257257.005D1A0B-85257257.005DA8C2@jpmchase.com> 





Do you have cursor_sharing set? and do you collect histograms on columns? 



I have encountered similar bugs on 9.2.0.7 versions, and Oracle support 
wanted us to generate a reproducible test case, which we could not. We 
stopped collecting histograms on all columns and have not seen the 
behaviour since then. 



HTH - Raj







"Jesse, Rich" <Rich.Jesse_at_qg.com>



Sent by: oracle-l-bounce_at_freelists.org


01/02/2007 10:48 AM


Please respond to Rich.Jesse

 

        To:     <oracle-l_at_freelists.org>
        cc: 
        Subject:        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_at_freelists.org


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_at_yahoo.com] 
Sent: Thursday, December 07, 2006 5:48 PM
To: binhpham15_at_hotmail.com; Jesse, Rich


Cc: oracle-l_at_freelists.org


Subject: RE: Stored outline not being used all the times...



The row with all 'N' should be the first child as mentioned here
All subsequent children should have some mismatch.
 


-Fairlie



Binh Pham <binhpham15_at_hotmail.com> wrote:
I've seen those before, tried to understand why but no clue. Any clue?



From: "Jesse, Rich" <Rich.Jesse_at_qg.com>


Reply-To: Rich.Jesse_at_qg.com


To: <oracle-l_at_freelists.org>


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';




-----------------------------------------


This transmission may contain information that is privileged,
confidential, legally privileged, and/or exempt from disclosure
under applicable law.  If you are not the intended recipient, you
are hereby notified that any disclosure, copying, distribution, or
use of the information contained herein (including any reliance
thereon) is STRICTLY PROHIBITED.  Although this transmission and
any attachments are believed to be free of any virus or other
defect that might affect any computer system into which it is
received and opened, it is the responsibility of the recipient to
ensure that it is virus free and no responsibility is accepted by
JPMorgan Chase & Co., its subsidiaries and affiliates, as
applicable, for any loss or damage arising in any way from its use.
If you received this transmission in error, please immediately
contact the sender and destroy the material in its entirety,
whether in electronic or hard copy format. Thank you.



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


Received on Tue Jan 02 2007 - 11:02:59 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US