Re: query slow in 9i, but not slow in 8i

From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Date: Mon, 1 Mar 2004 19:11:53 -0000
As has already been pointed out, the estimated
volume of data to sort is different.

> >    1    0   SORT (UNIQUE) (Cost=118228 Card=8046044 Bytes=225289232)
> >    2    1     MERGE JOIN (Cost=1102 Card=8046044 Bytes=225289232)

> >    1    0   SORT (UNIQUE) (Cost=77324 Card=6490972 Bytes=188238188)
> >    2    1     HASH JOIN (Cost=41347 Card=6490972 Bytes=188238188)

9.2 has estimated 225MB as its input and
output, 8.1 has estimated 188MB as its
input and output.  Given the change in volume
you should at least expect the incremental
cost to go up by about 50%.

In fact it has gone up by 117,000 in 9.2
and 33,000 in 8.1 a factor of 4: which
may be explained by the 9.2 parameter:

        _NEW_SORT_COST_ESTIMATE = TRUE

Are you also running cpu_costing ? This could
add a significant amount to the estimated cost
of a large sort.

You could run a 10053 trace on the two statements, and
look for the bit that is the sort (unique).  It will look something
like:

SORT resource Sort statistics

Sort width: 5 Area size: 131072 Max Area size: 1257472 Degree: 1

Blocks to Sort: 30 Row size: 49 Rows: 4882

Initial runs: 2 Merge passes: 1 IO Cost / pass: 16

Total IO sort cost: 46

Total CPU sort cost: 6933962

Total Temp space used: 484000




Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

March 2004 Hotsos Symposium - The Burden of Proof
  Dynamic Sampling - an investigation
March 2004 Charlotte OUG (www.cltoug.org) CBO Tutorial
April 2004 Iceland
June  2004      UK - Optimising Oracle Seminar


----- Original Message ----- 
From: "Guang Mei" <gmei@xxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Monday, March 01, 2004 5:22 PM
Subject: RE: query slow in 9i, but not slow in 8i


> Yes, The statistics is update to date on all the tables (in both 8i and
9i).
> I ran
>
> execute DBMS_STATS.GATHER_TABLE_STATS(<owenr>, <table_name> ,cascade =>
> TRUE);
>
> on all the tables in the schema.
>
>
> Guang
>
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Juan Cachito Reyes
> Pacheco
> Sent: Monday, March 01, 2004 12:11 PM
> To: oracle-l@xxxxxxxxxxxxx
> Subject: Re: query slow in 9i, but not slow in 8i
>
>
> If you have statictis up to date with dbms package.
>
> We had a problem with the new views cbo feature
> try to use the /*+ NO_MERGE */ hint,
> if this is the same problem we had , this will fix that.
> Otherwise no idea.
>
> ----- Original Message -----
> From: "Guang Mei" <gmei@xxxxxxxxxx>
> To: "Oracle-L-freelists" <oracle-l@xxxxxxxxxxxxx>
> Sent: Monday, March 01, 2004 1:09 PM
> Subject: query slow in 9i, but not slow in 8i
>
>
> > Hi:
> >
> > I have a query which gave two very different explain plan on 8173 and
> 9204.
> > The two instances (on two separate Sun Solaris boxes) both have the same
> > db_file_multiblock_read_count (8), block_size (8k), sort_area_size
> > (90000000) and sort_area_retained_size (9000000). I narrowed down the
part
> > which causeed this:
> >


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
References:
- RE: query slow in 9i, but not slow in 8i
  - From: Guang Mei
Re: query slow in 9i, but not slow in 8i

Other related posts:
