Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: 9i query runs slower than 8i

Re: 9i query runs slower than 8i

From: James Williams <willjamu_at_mindspring.com>
Date: Sun, 26 Sep 2004 16:05:02 GMT
Message-ID: <4156e8bc.932400@news.east.earthlink.net>


On 22 Sep 2004 09:16:08 -0700, datavector_at_hotmail.com (Ken Chesak) wrote:

Check your PGA-AGGREGATE-TARGET value in 9i. It might not be large enough. Check v$sysstat and see if you are doing disk sorts.

In 8i sort-area-size in influenced the hash memory as does PGA_AGGREGATE_TARGET in 9i.

>The following query runs slower in 9i about 30 seconds than 8i about 15 seconds.
>Seems 8i is choosing a hash join and 9i is not.
>
>Any ideas?
>
>SELECT (beg_bal - closed) beg_bal
> FROM
> (select count(*) beg_bal
> FROM rg101 c
> WHERE dt_run < '01-jan-04'
> ) beg_bal_tbl,
> (select /* NO_UNNEST */ count(*) closed
> FROM rg101 c, s_code_disposition s_code_disposition
> WHERE c.cd_disposition = s_code_disposition.cd_disposition
> and action_status = 'Cleared'
> and dt_disposition < '01-jan-04'
> AND dt_run < '01-jan-04')
>
>
>Oracle9i Enterprise Edition Release 9.2.0.4.0
>----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3401 Card=1 Bytes=26)
> 1 0 MERGE JOIN (CARTESIAN) (Cost=3401 Card=1 Bytes=26)
> 2 1 VIEW (Cost=270 Card=1 Bytes=13)
> 3 2 SORT (AGGREGATE)
> 4 3 INDEX (FAST FULL SCAN) OF 'IX_RG101_09' (NON-UNIQUE) (Cost=270
> Card=686814 Bytes=5
> 494512)
>
> 5 1 FIRST ROW
> 6 5 VIEW (Cost=3131 Card=1 Bytes=13)
> 7 6 SORT (AGGREGATE)
> 8 7 NESTED LOOPS (Cost=3131 Card=225703 Bytes=8125308)
> 9 8 TABLE ACCESS (FULL) OF 'RG101' (Cost=3131 Card=580380 Byte
>s=12768360)
> 10 8 TABLE ACCESS (BY INDEX ROWID) OF 'S_CODE_DISPOSITION'
> 11 10 INDEX (UNIQUE SCAN) OF 'PK_S_CODE_DISPOSITION' (UNIQUE)
>
>
>Oracle8i Enterprise Edition Release 8.1.7.4.0
>----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5249 Card=1 Bytes=26)
> 1 0 MERGE JOIN (CARTESIAN) (Cost=5249 Card=1 Bytes=26)
> 2 1 VIEW (Cost=352 Card=1 Bytes=13)
> 3 2 SORT (AGGREGATE)
> 4 3 INDEX (FAST FULL SCAN) OF 'IX_RG101_09' (NON-UNIQUE) (Cost=352
> Card=535257 Bytes=3
> 746799)
>
> 5 1 FIRST ROW
> 6 5 VIEW
> 7 6 SORT (AGGREGATE)
> 8 7 HASH JOIN (Cost=4897 Card=294477 Bytes=9128787)
> 9 8 TABLE ACCESS (FULL) OF 'S_CODE_DISPOSITION' (Cost=1 Card=1
>0 Bytes=120)
> 10 8 TABLE ACCESS (FULL) OF 'RG101' (Cost=4893 Card=353372 Byte
>s=6714068)
Received on Sun Sep 26 2004 - 11:05:02 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US