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

Did you check swap/paging activity when sort_area_size was at 5m?

Gaining performance on sorts by reducing the amount of memory used sounds like your box is low on RAM.

Jared

On Friday 14 February 2003 08:44, Glenn Travis wrote:
> 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: Jared Still
  INET: jkstill_at_cybcon.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).