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: [EMAIL PROTECTED] > > > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
