| 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: query slow in 9i, but not slow in 8i
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
> 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_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Juan Cachito Reyes
> Pacheco
> Sent: Monday, March 01, 2004 12:11 PM
> To: oracle-l_at_freelists.org
> 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_at_incyte.com>
> To: "Oracle-L-freelists" <oracle-l_at_freelists.org>
> 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:
> >
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Mon Mar 01 2004 - 13:08:51 CST
 |
 |