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

I've gathered statistics many different ways to get queries against the trading partner tables (i.e., HZ) to do something better to or equal a rule hint, and in the end a sort area of 1M or less and leaving hash_area_size unset (defaults to 2mb or less) works best. With hash_area_size < 2Mb, hash joins on every table in the query fall out of favor w/ CBO, and in the case of the HZ tables on 11.5.x, NL joins seem to outperform Hash joins.

Incidentally, in 11.5.7 and higher, many of the trading partner views seem better optimized. With 11.5.5 and lower, we've had to customize to get things working well.

-----Original Message-----
Still
Sent: Saturday, February 15, 2003 1:34 PM To: Multiple recipients of list ORACLE-L

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
> >

-- 
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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: John Clarke
  INET: jclarke_at_centroidsys.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).