RE: Optimizer help, get query to run as good as with RULE hint

I changed my sort_area_size to 1M (down from 5M) and the query completed in 18 seconds.

We had set sort_area_size to 5M at the suggestion of Oracle or other reasons. Looks like it's time to set it back.

I ran the disk_sorts query and it returned this:

DISK_SORTS AVERAGE_SIZE PEAK_CONCURRENT
---------- ------------ ---------------

     47073 23815K 826

Doesn't this suggest setting sort_area_size larger?

> -----Original Message-----
> From: Glenn Travis
> Sent: Friday, February 14, 2003 11:04 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Optimizer help, get query to run as good as with RULE hint
>
>
> I have a problem query which will not complete. This query
> is part of a report run within Oracle Applications.
>
> Our versions are: Oracle Server 8.1.7.4, Apps 11.5.3, HP-UX 11.11
>
> Listed below is the query and the explain plan. I ran full
> statistics on all the tables immediately before executing the
> query. Using the CBO, it never returns. I cancel the query,
> but it won't die until I kill the unix process.
>
> If I use the /*+ RULE */ hint, the query plan (also listed
> below) changes dramatically and the query executes in 30 seconds.
>
> What could cause the optimizer to behave so differently? We
> cannot change our instance to RBO, as it would adversely
> affect everything else and Apps requires CBO anyway. Any
> suggestions on what else I could do to improve the explain
> plan withou having to use the hint? (I've tried setting
> optimizer_index_cost_adj=10, and it changes the plan a little
> but still does not complete).
>
> --------------------------------
>
> 1 SELECT COUNT(*)
> 2 FROM HZ_CUST_ACCT_SITES ACCT_SITE,HZ_PARTY_SITES PARTY_SITE,
> 3 HZ_LOCATIONS LOC,HZ_LOC_ASSIGNMENTS LOC_ASSIGN
> 4 WHERE ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
> 5 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
> 6 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
> 7 AND NVL(ACCT_SITE.ORG_ID,-99) = NVL(LOC_ASSIGN.ORG_ID,-99)
> 8* ;
>
> Id Par Pos Ins Plan
> ---- ---- ----- ----
> --------------------------------------------------------------
> ------------------------------------------------
> 0 3218 SELECT STATEMENT (choose)
> Cost,rows,bytes (3218,1,31)
> 1 0 1 SORT (aggregate)
> 2 1 1 NESTED LOOPS Cost,rows,bytes
> (3218,1466,45446)
> 3 2 1 HASH JOIN Cost,rows,bytes
> (3218,617422807,16052992982)
> 4 3 1 HASH JOIN Cost,rows,bytes
> (2681,4307,77526)
> 5 4 1 5 TABLE ACCESS (analyzed) AR
> HZ_CUST_ACCT_SITES_ALL (full) Cost,rows,bytes (1263,4307,34456)
> 6 4 2 2 TABLE ACCESS (analyzed) AR
> HZ_PARTY_SITES (full) Cost,rows,bytes (1414,493760,4937600)
> 7 3 2 4 TABLE ACCESS (analyzed) AR
> HZ_LOC_ASSIGNMENTS (full) Cost,rows,bytes (533,430060,3440480)
> 8 2 2 INDEX (analyzed) UNIQUE AR
> HZ_LOCATIONS_U1 (unique scan)
>
>
>
> Using the RULE hint (completes in 30 seconds):
>
> 1 SELECT /*+ RULE */ COUNT(*)
> 2 FROM HZ_CUST_ACCT_SITES ACCT_SITE,HZ_PARTY_SITES PARTY_SITE,
> 3 HZ_LOCATIONS LOC,HZ_LOC_ASSIGNMENTS LOC_ASSIGN
> 4 WHERE ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
> 5 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
> 6 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
> 7 AND NVL(ACCT_SITE.ORG_ID,-99) = NVL(LOC_ASSIGN.ORG_ID,-99)
> 8* ;
>
> Id Par Pos Ins Plan
> ---- ---- ----- ----
> --------------------------------------------------------------
> ------------------------------------------------
> 0 SELECT STATEMENT (hint: rule)
> 1 0 1 SORT (aggregate)
> 2 1 1 NESTED LOOPS
> 3 2 1 NESTED LOOPS
> 4 3 1 NESTED LOOPS
> 5 4 1 4 TABLE ACCESS (analyzed) AR
> HZ_LOC_ASSIGNMENTS (full)
> 6 4 2 INDEX (analyzed) UNIQUE AR
> HZ_LOCATIONS_U1 (unique scan)
> 7 3 2 2 TABLE ACCESS (analyzed) AR
> HZ_PARTY_SITES (by index rowid)
> 8 7 1 INDEX (analyzed) NON-UNIQUE
> AR HZ_PARTY_SITES_N2 (range scan)
> 9 2 2 5 TABLE ACCESS (analyzed) AR
> HZ_CUST_ACCT_SITES_ALL (by index rowid)
> 10 9 1 INDEX (analyzed) NON-UNIQUE AR
> HZ_CUST_ACCT_SITES_N1 (range scan)
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Glenn Travis
> INET: Glenn.Travis_at_sas.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: Glenn Travis
  INET: Glenn.Travis_at_sas.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).