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

Hi Raj,
 
In our 10.2.0.2.0 DB, CS is the default of EXACT -- I refuse to use it
anymore!  However, in 9.2.0.5.0, our DB does have CS=S and there are
indeed histograms, but without any problems with cursors being unshared
having multiple rows in V$SQL_SHARED_CURSOR with no reasons given (read
that carefully).
 
And since bouncing our 10.2 DB after stopping the nightly flush, I see
no problems.  Wierd.  May be a BUG yet...
 
Thanks!
 
Rich

________________________________

From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of
Rajesh.Rao@xxxxxxxxxxxx
Sent: Tuesday, January 02, 2007 11:03 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Unshared cursors (WAS: Stored outline not being used...)



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@xxxxxx> 
Sent by: oracle-l-bounce@xxxxxxxxxxxxx 

01/02/2007 10:48 AM 
Please respond to Rich.Jesse 


        
        To:        <oracle-l@xxxxxxxxxxxxx> 
        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@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|| 


[snip]

Other related posts: