| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Unshared cursors (WAS: Stored outline not being used...)
Well, almost. That article refers to CURSOR_SHARING set to SIMILAR or FORCE, and is a very good pointer to one of the considerations when using those. But, as I mentioned, our 10gR2 DB has the default of EXACT.
Thanks,
Rich
-----Original Message-----
From: Adrian [mailto:ade.turner_at_gmail.com]
Sent: Tuesday, January 02, 2007 2:58 PM
To: Jesse, Rich; oracle-l_at_freelists.org
Subject: RE: Unshared cursors (WAS: Stored outline not being used...)
Rich,
Not been following all of this, but note 261020.1 seems relevant.
Cheers
Adrian
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
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
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
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?
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]
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Jan 02 2007 - 15:14:38 CST
 |
 |