| 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: cursor_sharing in 8.1.7.4 - good or bad
Anjo,
My tests in 9.2.0.1 on W2k showed, that each session can get it's own
execution plan if it's first execution's bind and histogram values require
to. If a different execution plan is computed (only soft parse needed - hard
parse count stayed zero for given session), then just a different version of
exec plan is stored with original SQL - you can verify it from v$sql_plan
for example. I'm sure that different execution paths were actually used as
well - I checed the waits from 10046 trace (scattered vs. sequential).
But it seems that autotrace & explain plan only take the first version of execution plan (child_number = 0) when explaining, maybe the confusion comes from there..
Btw, I just found an unexpected issue with analyze, that if you issue analyze table x compute statistics for all columns, only histograms are collected, not the basic table stats themselves. I checked, that's also true on 8.1.7.1.. I kind of expected that "for all columns" analyzes everything, the docs also say so... It took a while to figure out why CBO wasn't used although statistics were "collected"...
SQL> analyze table t delete statistics;
Table analyzed.
SQL> analyze table t compute statistics for all columns;
Table analyzed.
SQL> select last_analyzed from user_tables where table_name = 'T';
LAST_ANALY
SQL> analyze table t compute statistics;
Table analyzed.
SQL> select last_analyzed from user_tables where table_name = 'T';
LAST_ANALY
Tanel.
May be it should say that the peek is only done at the hard parse stage
(seems
to be implied by example of Jonathan Lewis)
On Friday 22 August 2003 00:09, Tanel Poder wrote:
> Hi!
>
> How did you check which execution plan was chosen? Explain plan and
> autotrace lie, use 10046 or v$sql_plan instead.
> For num_buckets, dba_tab_cols view substracts one from real bucket count
> for some reason. To get real value go to base tables and check row_cnt
from
> sys.hist_head$ or just count(*) appropriate rows from sys.histgrm$.
>
> But yes, the execution plan is chosen once per session, if first one took
> FTS based on one value, then if second one picks index access, another
> version of query execution plan is generated for the same SQL statement -
> only soft parse is required.
>
> Tanel.
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Thursday, August 21, 2003 7:54 PM
>
> > I was curious about your statement "the kernel only peeks once per
> > session". I wondered if another session that executed the same statement
> > would be impacted by the peeking or would the optimizer reparse the
> > statement.
> >
> > So I set up a test. Million row table with 2 columns. c1 - number, c2 -
> > date. C1 contains 2 distinct values 0 (999 rows) and 1 (999001 rows). I
> > analyzed the table and 'tried' to generate a 2 bucket histogram.
However,
> > the num_buckets is still 1 on the column. When I run the query with a
> > bind variable, it always selects a FTS, regardless of the first value I
> > place
>
> in
>
> > the variable.
> >
> > I am obviously missing something, but I've run out of ideas. I 'think'
> > the problem is the number of buckets, but the analyze table column
> > command seems to think 1 bucket is enough.
> >
> > Daniel
> >
> > Cary Millsap wrote:
> > > And don't count too heavily upon 9i's "bind value peek" capability.
> > > Here's a simple test that Jonathan Lewis conjured up last year:
> > >
> > > PREPARATION. Create data for "select c1, c2 from t1 where c1 =
:bind1",
> > > where different bind1 values could have different paths if we used
> > > literals.
> > >
> > > 1. flush shared pool
> > > set bind1 = 5
> > > execute query, notice an indexed access (optimal plan for bind1=5)
> > > set bind1 = 70
> > > execute query, notice that kernel still uses the index
> > >
> > > 2. flush shared pool
> > > set bind1 = 70
> > > execute query, notice a table scan (optimal plan for bind1=70)
> > > set bind1 = 5
> > > execute query, notice that kernel still uses the table scan
> > >
> > > The moral: the kernel only peeks once per session.
> > >
> > > Cary Millsap
> > > Hotsos Enterprises, Ltd.
> > > http://www.hotsos.com
> > >
> > > Upcoming events:
> > > - Hotsos Clinic 101 in Sydney
> > > - Hotsos Symposium 2004, March 7-10 Dallas
> > > - Visit www.hotsos.com for schedule details...
> > >
> > > -----Original Message-----
> > > Jesse, Rich
> > > Sent: Tuesday, August 19, 2003 3:09 PM
> > > To: Multiple recipients of list ORACLE-L
> > >
> > > True, but using binds isn't always an option, like when running a 3rd
> > > party
> > > product. I also could not justify weeks of recoding homegrown
software
> > > for
> > > the relatively minor gains we would have using binds. And scalability
> > > (or
> > > lack thereof) didn't seem to be argument enough.
> > >
> > > We're using CS=F on 8.1.7.4 on HP/UX 11.0. We haven't yet had a
single
> > > issue attributable to it in the 7 months we've been at the .4 release.
> > > There were some nasty issues with 8.1.7.2 (ORA-600 and silently
> > > returning
> > > WRONG data), but like I said, .4 seems to have cleaned that up. YMMV.
> > >
> > > Remember that under 8i, you may see some performance DROP by using
> > > binds.
> > > The case that comes to mind is if you're using histograms with ANALYZE
> > > or
> > > DBMS_STATS. Under 8i, the optimizer can no longer "see" the values
and
> > > therefore can't use the histograms. Under 9i (R2 only?), you can tell
> > > the
> > > optimizer to peek at the bind variables' values in order to make use
of
> > > histograms. We plan on doing this when we get to 9i in order to use a
> > > very
> > > low cardinality index, where less than 0.0001% of the rows is what we
> > > need
> > > to fetch, but 8i won't use the index because of the cardinality.
> > >
> > > Your best bet, of course, is to test! HTH! GL! :)
> > >
> > > Rich
> > >
> > > Rich Jesse System/Database Administrator
> > > rjesse_at_qtiworld.com Quad/Tech Inc, Sussex, WI USA
> > >
> > > -----Original Message-----
> > > Sent: Tuesday, August 19, 2003 2:49 PM
> > > To: Multiple recipients of list ORACLE-L
> > >
> > > Hi,
> > > No. I recommend that you do not change cursor_sharing if you are
> > > doing it
> > > just to get rid of 4031. There could be serious problems. ora-600 etc.
> > > Maybe
> > > Oracle has fixed everything in latest 817 but I suggest not to take
> > > that chance.
> > > For 4031 you can do the following : pin pl/sql sql preferably at
> > > startup,
> > > use bind variables and periodically check for sql not using bind
> > > variables.
> > > Goodluck.
> > >
> > > "Fedock, John (KAM.RHQ)" <John.Fedock_at_us.kline.com> wrote:
> > >
> > > Does anyone have any experience using cursor_sharing in 8.1.7.4? I
> > > feel I
> > > need to use it due to literal SQL statements being used. We have had
> > > misc
> > > 4031 errors off and on and I feel this is my last hope. I have a very
> > > busy
> > > OPFS server (HP-UX).
> > >
> > > I did try using cursor_sharing in 8.1.7.2, and I remember incorrect
> > > sorting
> > > and other unexpected results happening.
> > >
> > > Thanks for any input.
> > >
> > >
> > > John
> > > --
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > > --
> > > Author: Jesse, Rich
> > > INET: Rich.Jesse_at_qtiworld.com
> > >
> > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > > San Diego, California -- Mailing list and web hosting services
> > > ---------------------------------------------------------------------
> > > To REMOVE yourself from this mailing list, send an E-Mail message
> > > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > > the message BODY, include a line containing: UNSUB ORACLE-L
> > > (or the name of mailing list you want to be removed from). You may
> > > also send the HELP command for other information (like subscribing).
> > >
> > > --
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > > --
> > > Author: Cary Millsap
> > > INET: cary.millsap_at_hotsos.com
> > >
> > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > > San Diego, California -- Mailing list and web hosting services
> > > ---------------------------------------------------------------------
> > > To REMOVE yourself from this mailing list, send an E-Mail message
> > > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > > the message BODY, include a line containing: UNSUB ORACLE-L
> > > (or the name of mailing list you want to be removed from). You may
> > > also send the HELP command for other information (like subscribing).
-- ---------------------------------------------------------------- Anjo Kolk http://www.oraperf.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: tanel.poder.003_at_mail.ee Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Thu Aug 21 2003 - 20:39:26 CDT
 |
 |