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