performance issue after upgrade to oracle 11.2.0.1 linux 32 bit.
in [Oracle]
|
From: lsllcm on 8 Nov 2009 11:20 On Nov 8, 9:36 pm, Charles Hooper <hooperc2...(a)yahoo.com> wrote: > On Nov 7, 9:13 am, lsllcm <lsl...(a)gmail.com> wrote: > > > Thanks Charles and Randolf for your comments: > > Thanks for running all of the test cases. Many of the plans produced > are identical, but none show the same plan as you posted for 11.1.0.6 > and 11.2.0.1. > > > Below are test cases and results. > > > ------------------------case > > 1------------------------------------------------ > > 1. Use hint /*+ index(SD SETDETAILS_SETID_IX) */ > > > Result: the result is same as no index hint > > It appears that the optimizer was originally using the > SETDETAILS_PID_IX index based on your first post in this thread, so it > does appear that the index hint did actually change the plan when > using your original OPTIMIZER_MODE, OPTIMIZER_INDEX_COST_ADJ and > OPTIMIZER_INDEX_CACHING parameter settings. > > (snip) > > > SQL> exec :efd:='2008-12-04 23:59:59'; > > PL/SQL procedure successfully completed. > > Elapsed: 00:00:00.37 > > > SQL> rem exec :p1:='254413' > > SQL> exec :p1:='SET07' > > PL/SQL procedure successfully completed. > > Elapsed: 00:00:00.03 > > > SQL> exec :p2:='Contractor' > > PL/SQL procedure successfully completed. > > Elapsed: 00:00:00.18 > > It seems a little odd that some of these commands to set bind variable > values requires 370ms - about 1/2 as long as it takes to actually > execute the SQL statement with OPTIMIZER_MODE set to ALL_ROWS. I am > not sure if there is significance to this observation, or not. > > (snip) > > > > > > > 6------------------------------------------------ > > 6. If query is expected to retrieve 100,000 rows, but the application > > front-end will ONLY read just the first 100 rows and then throw away > > the rest of the query results without bother fetching the remaining > > rows. > > [lsllcm] There are many queries like the case in our environment. So > > we use FIRST_ROWS_100. I will test different queries in both > > FIRST_ROWS_100 and ALL_ROWS in 10g db and see difference. > > > ------------------------item > > 7------------------------------------------------ > > Is it a special case that no rows are returned due to some of the > > bind > > values used? How many rows does this kind of statement return > > typically? > > > [lsllcm] The data in table SETDETAILS is skew, most of them have less > > than 100 rows returned > > based on SETDETAILS_SETID_IX. 10 of them have from 10,000 - 40,000 > > rows returned. > > Skewed data and bind variables may lead to problems with the re-use of > plans, especially if histograms are present. Oracle 11.2.0.1 (with > OPTIMIZER_MODE = ALL_ROWS) might be trying to build a "safer" plan > which will work reasonably well for 10 or 40,000 rows, while 11.1.0.6 > and 10.2.0.4 appear to be relying on a short-circuit in the plan which > required only 3 logical IOs. Any idea how the plan produced on the > older releases of Oracle might have handled the situations where the > plan returned 40,000 rows? In other words, while the one case you > have identified with returning 0 rows executes quickly, the case where > 40,000 rows are returned might execute much slower than the 11.2.0.1 > execution. > > > I use below command to gather statistics. > > > exec dbms_stats.gather_schema_stats > > ('TEST',estimate_percent=>100,cascade=> TRUE); > > OK, the above collects table and index statistics, but I am not sure > if that will collect the statistics on the hidden columns (Randolf or > Jonathan should be able to answer this question). > > > Below are histogram of SYS_NC00017$ > > What is the purpose of this index? This is the same index that I > mentioned previously that showed a strange IO cost in the 10053 trace > file: > cost_io > 179769313486231570814527423731704356798070567525844996598917476803157260780 > 028538760589558632766878171540458953514382464234321326889464182768467546703 > 537516986049910576551282076245490090389328944075868508455133942304583236903 > 222948165808559332123348274797826204144723168738177180919299881250404026184 > 124858368.00 > > > > > ------------------------item > > 8------------------------------------------------ > > [lsllcm] The reason to set "_optimizer_cost_based_transformation is to > > check if > > the issue is related to the setting. Now I have set it back to > > default. > > OK, so you temporarily set _optimizer_cost_based_transformation to > false to check for changes in performance, and have returned the > parameter to its original value. I might be remembering incorrectly, > but I believe that there was a bug in 10.2.0.4, and the work-around > for the bug required this hidden parameter to be set to false. That > might explain why you saw that parameter set to false in 10.2.0.4. > > > ------------------------item > > 9------------------------------------------------ > > 9. You might try collecting system statistics during a busy time > > period. > > To collect the system statistics with a 60 minute monitoring period, > > enter the following in SQL*Plus (the SQL*Plus command prompt will > > return immediately): > > EXEC DBMS_STATS.GATHER_SYSTEM_STATS('interval',interval=>60) > > > [lsllcm] I did not gather system statistics, and I check again > > scheduled job, no job > > gather system staitstics. > > There is no automated job for *system* (CPU) statistics gathering - > the DBA must tell Oracle when to collect the statistics using > DBMS_STATS.GATHER_SYSTEM_STATS. > From:http://download.oracle.com/docs/cd/E11882_01/server.112/e10821/stats..... > "System statistics describe the system's hardware characteristics, > such as I/O and CPU performance and utilization, to the query > optimizer. When choosing an execution plan, the optimizer estimates > the I/O and CPU resources required for each query. System statistics > enable the query optimizer to more accurately estimate I/O and CPU > costs, enabling the query optimizer to choose a better execution > plan." > > Jonathan has also written several articles on the topic, here are a > couple of those articles:http://jonathanlewis.wordpress.com/2007/04/30/system-statistics/http://jonathanlewis.wordpress.com/2007/05/20/system-stats-strategy/http://jonathanlewis.wordpress.com/2007/10/17/system-statistics-3/ > > > 10------------------------------------------------ > > 10. As you suggested, I change the optimizer setting into default as > > combination ALL_ROWS + OICA (100) + OIC (0) in test environment. The > > result is > > system looks like to become stable. Even if the query does not choose > > best > > execution plan, it chooses not worst execution plan. Like the test > > case above, > > it has 3 consistent reads in 10g db (it should be best execution > > plan). In 11g > > (ALL_ROWS), it has 3656 consistent reads (it is not best, but it is > > not worst > > too). In 11g (FIRST_ROWS_100), it has 2891740 consistent gets(like > > worst one). > > It is good that you found a suitable plan, which might be suitable if > 0 rows, 10 rows, or 40,000 rows are returned. > > Charles Hooper > IT Manager/Oracle DBA > K&M Machine-Fabricating, Inc.- Hide quoted text - > > - Show quoted text - Thanks Charles and Randolf, I learned a lot about optimizer from this thread. ------------------------case 1------------------------------------------------ 1. Use hint /*+ index(SD SETDETAILS_SETID_IX) */ Result: yes, the execution plan is different and use different index. I did not check it carefully. ------------------------item 2------------------------------------------------ Below are histogram of SYS_NC00017$ Yes, SYS_NC00017$ is function index column UPPER("SD"."SET_ID") in index SETDETAILS_SET_IX It showed a strange IO cost in the 10053 trace file: cost_io 179769313486231570814527423731704356798070567525844996598917476803157260780-- 028538760589558632766878171540458953514382464234321326889464182768467546703-- 537516986049910576551282076245490090389328944075868508455133942304583236903-- 222948165808559332123348274797826204144723168738177180919299881250404026184-- 124858368.00 ------------------------item 3------------------------------------------------ Below sql still runs slow regardless optimzer mode is FIRST_ROWS_100 and ALL_ROWS SELECT * FROM (SELECT A.SERV_PROV_CODE AS SERV_PROV_CODE, A.B1_PER_ID1 AS B1_PER_ID1, A.B1_PER_ID2 AS B1_PER_ID2, A.B1_PER_ID3 AS B1_PER_ID3, A.B1_PER_GROUP, A.B1_PER_TYPE, A.B1_PER_SUB_TYPE, A.B1_PER_CATEGORY, A.B1_SPECIAL_TEXT, A.B1_CREATED_BY_ACA, A.R3_STD_TIME_CLASS_CODE, A.B1_STANDARD_TIME, A.B1_EVENT_CODE, A.B1_REF_ID, A.B1_APPL_STATUS, A.REC_FUL_NAM, A.REC_STATUS, A.PROJECT_NBR, A.B1_ALT_ID, A.B1_TRACKING_NBR, A.B1_APPL_STATUS_DATE, A.B1_REF_ID1, A.B1_REF_ID2, A.B1_REF_ID3, A.B1_Q_UD1, A.B1_Q_UD2, A.B1_Q_UD3, A.B1_Q_UD4, A.APP_STATUS_GROUP_CODE, A.B1_MODULE_NAME, A.B1_FILE_DD, A.REC_DATE, A.B1_REPORTED_DATE, A.B1_APPL_CLASS, A.B1_APP_TYPE_ALIAS, A.B1_CREATED_BY, A.B1_INITIATED_BY_PRODUCT, B.APP_STATUS, B.APP_STATUS_DATE, B.DISPOSITION, B.DISPOSITION_DATE, B.TOTAL_FEE, B.TOTAL_PAY, B.PERCENT_COMPLETE, B.BALANCE, B.BALANCE_DATE, B.HOUSE_COUNT, B.BUILDING_COUNT, B.PUBLIC_OWNED, B.CONST_TYPE_CODE, B.ACTION, B.GA_AGENCY_CODE, B.GA_BUREAU_CODE, B.GA_DIVISION_CODE, B.GA_OFFICE_CODE, B.GA_SECTION_CODE, B.GA_GROUP_CODE, B.GA_FNAME, B.GA_MNAME, B.GA_LNAME, B.B1_APPL_SUB_STATUS, B.B1_SHORT_NOTES, B.B1_CLOSED_DATE, B.B1_CLOSEDBY, B.B1_REPORTED_CHANNEL, B.B1_ASGN_DEPT, B.B1_ASGN_STAFF, B.B1_PRIORITY, B.B1_SEVERITY, B.B1_ASGN_DATE, B.TOTAL_JOB_COST, B.B1_CLOSED_DEPT, B.B1_COMPLETE_BY, B.B1_COMPLETE_DEPT, B.B1_COMPLETE_DATE, B.B1_SCHEDULED_DATE, B.B1_CREATOR_DEPT, B.PM_SCHEDULE_SEQ, B.B1_EST_PROD_UNITS, B.B1_ACTUAL_PROD_UNITS, B.B1_EST_COST_PER_UNIT, B.B1_COST_PER_UNIT, B.B1_EST_JOB_COST, B.B1_PROD_UNIT_TYPE, B.B1_CREATED_BY AS B1CREATEDBY, B.B1_TRACK_START_DATE, B.B1_ESTIMATED_DUE_DATE, B.B1_IN_POSSESSION_TIME, B.C6_ANONYMOUS_FLAG, B.C6_REFERENCE_TYPE, B.C6_APPEARANCE_DAYOFWEEK, B.C6_APPEARANCE_DD, B.C6_BOOKING_FLAG, B.C6_DFNDT_SIGNATURE_FLAG, B.C6_ENFORCE_OFFICER_ID, B.C6_ENFORCE_OFFICER_NAME, B.C6_INFRACTION_FLAG, B.C6_INSPECTOR_ID, B.C6_MISDEMEANOR_FLAG, B.C6_OFFN_WITNESSED_FLAG, B.C6_INSPECTOR_NAME, B.C6_ENFORCE_DEPT, B.C6_INSPECTOR_DEPT, B.APP_STATUS_REASON, B.FIRST_ISSUED_DATE, B.UNDISTRIBUTED_JOB_COST, B.B1_VAL_MULTIPLIER, B.B1_VAL_EXTRA_AMT, G.EXPIRATION_CODE, G.EXPIRATION_STATUS, G.EXPIRATION_DATE, G.EXPIRATION_INTERVAL, G.EXPIRATION_INTERVAL_UNITS, G.GRACE_PERIOD_INTERVAL, G.GRACE_PERIOD_INTERVAL_UNITS, G.PENALTY_INTERVAL, G.PENALTY_INTERVAL_UNITS, G.NUM_PENALTY_PERIODS, G.PENALTY_PERIOD, G.RENEWAL_FEE_FUNCTION, G.RENEWAL_FEE_CODE, G.PENALTY_FEE_FUNCTION, G.PENALTY_FEE_CODE, G.PAY_PERIOD_GROUP, G.UDF1, G.UDF2, G.UDF3, G.UDF4, C.B1_WORK_DESC FROM B1PERMIT A LEFT OUTER JOIN BPERMIT_DETAIL B ON A.SERV_PROV_CODE = B.SERV_PROV_CODE AND A.B1_PER_ID1 = B.B1_PER_ID1 AND A.B1_PER_ID2 = B.B1_PER_ID2 AND A.B1_PER_ID3 = B.B1_PER_ID3 LEFT OUTER JOIN BWORKDES C ON A.SERV_PROV_CODE = C.SERV_PROV_CODE AND A.B1_PER_ID1 = C.B1_PER_ID1 AND A.B1_PER_ID2 = C.B1_PER_ID2 AND A.B1_PER_ID3 = C.B1_PER_ID3 LEFT OUTER JOIN B1_EXPIRATION G ON A.SERV_PROV_CODE = G.SERV_PROV_CODE AND A.B1_PER_ID1 = G.B1_PER_ID1 AND A.B1_PER_ID2 = G.B1_PER_ID2 AND A.B1_PER_ID3 = G.B1_PER_ID3, B3CONTRA L WHERE A.REC_STATUS = 'A' AND A.B1_PER_ID3 NOT LIKE '#%' AND (A.B1_APPL_CLASS = 'COMPLETE' OR A.B1_APPL_CLASS IS NULL) AND A.SERV_PROV_CODE = L.SERV_PROV_CODE AND A.B1_PER_ID1 = L.B1_PER_ID1 AND A.B1_PER_ID2 = L.B1_PER_ID2 AND A.B1_PER_ID3 = L.B1_PER_ID3 AND A.SERV_PROV_CODE = :spc AND L.B1_LICENSE_NBR = :p1 AND L.B1_LICENSE_TYPE = :p2 ORDER BY A.B1_FILE_DD DESC) WHERE ROWNUM < 101 -- in 10g and 11.1.0.6 db The plan as below: ----------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 4157 | 6 (17)| 00:00:01 | |* 1 | COUNT STOPKEY | | | | | | | 2 | VIEW | | 1 | 4157 | 6 (17)| 00:00:01 | |* 3 | SORT ORDER BY STOPKEY | | 1 | 616 | 6 (17)| 00:00:01 | | 4 | NESTED LOOPS OUTER | | 1 | 616 | 5 (0)| 00:00:01 | | 5 | NESTED LOOPS OUTER | | 1 | 447 | 3 (0)| 00:00:01 | | 6 | NESTED LOOPS OUTER | | 1 | 319 | 2 (0)| 00:00:01 | | 7 | NESTED LOOPS | | 1 | 235 | 1 (0)| 00:00:01 | |* 8 | TABLE ACCESS BY INDEX ROWID| B3CONTRA | 1 | 48 | 0 (0)| 00:00:01 | |* 9 | INDEX RANGE SCAN | B3CONTRA_BUS_NAM2_IX | 1 | | 0 (0)| 00:00:01 | |* 10 | TABLE ACCESS BY INDEX ROWID| B1PERMIT | 1 | 187 | 1 (0)| 00:00:01 | |* 11 | INDEX UNIQUE SCAN | B1PERMIT_PK | 1 | | 0 (0)| 00:00:01 | | 12 | TABLE ACCESS BY INDEX ROWID | BWORKDES | 4 | 336 | 1 (0)| 00:00:01 | |* 13 | INDEX UNIQUE SCAN | BWORKDES_PK | 1 | | 0 (0)| 00:00:01 | | 14 | TABLE ACCESS BY INDEX ROWID | BPERMIT_DETAIL | 1 | 128 | 1 (0)| 00:00:01 | |* 15 | INDEX UNIQUE SCAN | BPERMIT_DETAIL_PK | 1 | | 0 (0)| 00:00:01 | | 16 | TABLE ACCESS BY INDEX ROWID | B1_EXPIRATION | 2 | 338 | 2 (0)| 00:00:01 | |* 17 | INDEX RANGE SCAN | B1_EXPIRATION_PK | 1 | | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------------- -- in 11.2.0.1 db The plan as below: --------------------------------------------------------------- +-----------------------------------+ | Id | Operation | Name | Rows | Bytes | Cost | Time | --------------------------------------------------------------- +-----------------------------------+ | 0 | SELECT STATEMENT | | | | 10K | | | 1 | COUNT STOPKEY | | | | | | | 2 | VIEW | | 123 | 499K | 10K | 00:02:01 | | 3 | NESTED LOOPS OUTER | | 123 | 71K | 10K | 00:02:01 | | 4 | NESTED LOOPS OUTER | | 123 | 61K | 9903 | 00:02:59 | | 5 | NESTED LOOPS OUTER | | 123 | 43K | 9779 | 00:02:58 | | 6 | NESTED LOOPS | | 123 | 25K | 9533 | 00:02:55 | | 7 | TABLE ACCESS BY INDEX ROWID | B1PERMIT | 501K | 76M | 1359 | 00:00:17 | | 8 | INDEX FULL SCAN DESCENDING | B1PERMIT_FILEDD_IX| 4114 | | 37 | 00:00:01 | | 9 | INDEX RANGE SCAN | B3CONTRA_PK | 1 | 48 | 2 | 00:00:01 | | 10 | TABLE ACCESS BY INDEX ROWID | BPERMIT_DETAIL | 1 | 158 | 2 | 00:00:01 | | 11 | INDEX UNIQUE SCAN | BPERMIT_DETAIL_PK | 1 | | 1 | 00:00:01 | | 12 | TABLE ACCESS BY INDEX ROWID | B1_EXPIRATION | 1 | 146 | 2 | 00:00:01 | | 13 | INDEX RANGE SCAN | B1_EXPIRATION_PK | 1 | | 1 | 00:00:01 | | 14 | TABLE ACCESS BY INDEX ROWID | BWORKDES | 1 | 79 | 2 | 00:00:01 | | 15 | INDEX UNIQUE SCAN | BWORKDES_PK | 1 | | 1 | 00:00:01 | --------------------------------------------------------------- +-----------------------------------+
From: Charles Hooper on 9 Nov 2009 09:38 On Nov 8, 11:20 am, lsllcm <lsl...(a)gmail.com> wrote: > Below sql still runs slow regardless optimzer mode is FIRST_ROWS_100 > and ALL_ROWS > > SELECT * > FROM (SELECT A.SERV_PROV_CODE AS SERV_PROV_CODE, > A.B1_PER_ID1 AS B1_PER_ID1, > A.B1_PER_ID2 AS B1_PER_ID2, > A.B1_PER_ID3 AS B1_PER_ID3, > A.B1_PER_GROUP, > A.B1_PER_TYPE, > A.B1_PER_SUB_TYPE, > A.B1_PER_CATEGORY, > A.B1_SPECIAL_TEXT, > A.B1_CREATED_BY_ACA, > A.R3_STD_TIME_CLASS_CODE, > A.B1_STANDARD_TIME, > A.B1_EVENT_CODE, > A.B1_REF_ID, > A.B1_APPL_STATUS, > A.REC_FUL_NAM, > A.REC_STATUS, > A.PROJECT_NBR, > A.B1_ALT_ID, > A.B1_TRACKING_NBR, > A.B1_APPL_STATUS_DATE, > A.B1_REF_ID1, > A.B1_REF_ID2, > A.B1_REF_ID3, > A.B1_Q_UD1, > A.B1_Q_UD2, > A.B1_Q_UD3, > A.B1_Q_UD4, > A.APP_STATUS_GROUP_CODE, > A.B1_MODULE_NAME, > A.B1_FILE_DD, > A.REC_DATE, > A.B1_REPORTED_DATE, > A.B1_APPL_CLASS, > A.B1_APP_TYPE_ALIAS, > A.B1_CREATED_BY, > A.B1_INITIATED_BY_PRODUCT, > B.APP_STATUS, > B.APP_STATUS_DATE, > B.DISPOSITION, > B.DISPOSITION_DATE, > B.TOTAL_FEE, > B.TOTAL_PAY, > B.PERCENT_COMPLETE, > B.BALANCE, > B.BALANCE_DATE, > B.HOUSE_COUNT, > B.BUILDING_COUNT, > B.PUBLIC_OWNED, > B.CONST_TYPE_CODE, > B.ACTION, > B.GA_AGENCY_CODE, > B.GA_BUREAU_CODE, > B.GA_DIVISION_CODE, > B.GA_OFFICE_CODE, > B.GA_SECTION_CODE, > B.GA_GROUP_CODE, > B.GA_FNAME, > B.GA_MNAME, > B.GA_LNAME, > B.B1_APPL_SUB_STATUS, > B.B1_SHORT_NOTES, > B.B1_CLOSED_DATE, > B.B1_CLOSEDBY, > B.B1_REPORTED_CHANNEL, > B.B1_ASGN_DEPT, > B.B1_ASGN_STAFF, > B.B1_PRIORITY, > B.B1_SEVERITY, > B.B1_ASGN_DATE, > B.TOTAL_JOB_COST, > B.B1_CLOSED_DEPT, > B.B1_COMPLETE_BY, > B.B1_COMPLETE_DEPT, > B.B1_COMPLETE_DATE, > B.B1_SCHEDULED_DATE, > B.B1_CREATOR_DEPT, > B.PM_SCHEDULE_SEQ, > B.B1_EST_PROD_UNITS, > B.B1_ACTUAL_PROD_UNITS, > B.B1_EST_COST_PER_UNIT, > B.B1_COST_PER_UNIT, > B.B1_EST_JOB_COST, > B.B1_PROD_UNIT_TYPE, > B.B1_CREATED_BY AS B1CREATEDBY, > B.B1_TRACK_START_DATE, > B.B1_ESTIMATED_DUE_DATE, > B.B1_IN_POSSESSION_TIME, > B.C6_ANONYMOUS_FLAG, > B.C6_REFERENCE_TYPE, > B.C6_APPEARANCE_DAYOFWEEK, > B.C6_APPEARANCE_DD, > B.C6_BOOKING_FLAG, > B.C6_DFNDT_SIGNATURE_FLAG, > B.C6_ENFORCE_OFFICER_ID, > B.C6_ENFORCE_OFFICER_NAME, > B.C6_INFRACTION_FLAG, > B.C6_INSPECTOR_ID, > B.C6_MISDEMEANOR_FLAG, > B.C6_OFFN_WITNESSED_FLAG, > B.C6_INSPECTOR_NAME, > B.C6_ENFORCE_DEPT, > B.C6_INSPECTOR_DEPT, > B.APP_STATUS_REASON, > B.FIRST_ISSUED_DATE, > B.UNDISTRIBUTED_JOB_COST, > B.B1_VAL_MULTIPLIER, > B.B1_VAL_EXTRA_AMT, > G.EXPIRATION_CODE, > G.EXPIRATION_STATUS, > G.EXPIRATION_DATE, > G.EXPIRATION_INTERVAL, > G.EXPIRATION_INTERVAL_UNITS, > G.GRACE_PERIOD_INTERVAL, > G.GRACE_PERIOD_INTERVAL_UNITS, > G.PENALTY_INTERVAL, > G.PENALTY_INTERVAL_UNITS, > G.NUM_PENALTY_PERIODS, > G.PENALTY_PERIOD, > G.RENEWAL_FEE_FUNCTION, > G.RENEWAL_FEE_CODE, > G.PENALTY_FEE_FUNCTION, > G.PENALTY_FEE_CODE, > G.PAY_PERIOD_GROUP, > G.UDF1, > G.UDF2, > G.UDF3, > G.UDF4, > C.B1_WORK_DESC > FROM B1PERMIT A > LEFT OUTER JOIN BPERMIT_DETAIL B ON A.SERV_PROV_CODE = > B.SERV_PROV_CODE > AND A.B1_PER_ID1 = > B.B1_PER_ID1 > AND A.B1_PER_ID2 = > B.B1_PER_ID2 > AND A.B1_PER_ID3 = > B.B1_PER_ID3 > LEFT OUTER JOIN BWORKDES C ON A.SERV_PROV_CODE = > C.SERV_PROV_CODE > AND A.B1_PER_ID1 = C.B1_PER_ID1 > AND A.B1_PER_ID2 = C.B1_PER_ID2 > AND A.B1_PER_ID3 = C.B1_PER_ID3 > LEFT OUTER JOIN B1_EXPIRATION G ON A.SERV_PROV_CODE = > G.SERV_PROV_CODE > AND A.B1_PER_ID1 = > G.B1_PER_ID1 > AND A.B1_PER_ID2 = > G.B1_PER_ID2 > AND A.B1_PER_ID3 = > G.B1_PER_ID3, > B3CONTRA L > WHERE A.REC_STATUS = 'A' > AND A.B1_PER_ID3 NOT LIKE '#%' > AND (A.B1_APPL_CLASS = 'COMPLETE' OR A.B1_APPL_CLASS IS > NULL) > AND A.SERV_PROV_CODE = L.SERV_PROV_CODE > AND A.B1_PER_ID1 = L.B1_PER_ID1 > AND A.B1_PER_ID2 = L.B1_PER_ID2 > AND A.B1_PER_ID3 = L.B1_PER_ID3 > AND A.SERV_PROV_CODE = :spc > AND L.B1_LICENSE_NBR = :p1 > AND L.B1_LICENSE_TYPE = :p2 > ORDER BY A.B1_FILE_DD DESC) > WHERE ROWNUM < 101 Randolf is of course correct that the Access and Filter predicates for the plans would be very helpful. It appears that this may be a case were Bug 6845871 - Suboptimal plan from ROWNUM predicate is actually helping 10.2.0.4 and 11.1.0.6 derive a better plan than 11.2.0.1, but it seems that the 10.2.0.4 and 11.1.0.6 plan is behaving like "WHERE ROWNUM < 1" is specified rather than "WHERE ROWNUM < 101". Hopefully, the following re-print of your posted plans will not have word-wrapping problems: 10g and 11.1.0.6 db --------------------------------------------------------------------------- | Id | Operation | Name | Rows | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | |* 1 | COUNT STOPKEY | | | | 2 | VIEW | | 1 | |* 3 | SORT ORDER BY STOPKEY | | 1 | | 4 | NESTED LOOPS OUTER | | 1 | | 5 | NESTED LOOPS OUTER | | 1 | | 6 | NESTED LOOPS OUTER | | 1 | | 7 | NESTED LOOPS | | 1 | |* 8 | TABLE ACCESS BY INDEX ROWID| B3CONTRA | 1 | |* 9 | INDEX RANGE SCAN | B3CONTRA_BUS_NAM2_IX | 1 | |* 10 | TABLE ACCESS BY INDEX ROWID| B1PERMIT | 1 | |* 11 | INDEX UNIQUE SCAN | B1PERMIT_PK | 1 | | 12 | TABLE ACCESS BY INDEX ROWID | BWORKDES | 4 | |* 13 | INDEX UNIQUE SCAN | BWORKDES_PK | 1 | | 14 | TABLE ACCESS BY INDEX ROWID | BPERMIT_DETAIL | 1 | |* 15 | INDEX UNIQUE SCAN | BPERMIT_DETAIL_PK | 1 | | 16 | TABLE ACCESS BY INDEX ROWID | B1_EXPIRATION | 2 | |* 17 | INDEX RANGE SCAN | B1_EXPIRATION_PK | 1 | --------------------------------------------------------------------------- 10g and 11.1.0.6 db(cont) ---------------------------------------- | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------- | 1 | 4157 | 6 (17)| 00:00:01 | | | | | | | 1 | 4157 | 6 (17)| 00:00:01 | | 1 | 616 | 6 (17)| 00:00:01 | | 1 | 616 | 5 (0)| 00:00:01 | | 1 | 447 | 3 (0)| 00:00:01 | | 1 | 319 | 2 (0)| 00:00:01 | | 1 | 235 | 1 (0)| 00:00:01 | | 1 | 48 | 0 (0)| 00:00:01 | | 1 | | 0 (0)| 00:00:01 | | 1 | 187 | 1 (0)| 00:00:01 | | 1 | | 0 (0)| 00:00:01 | | 4 | 336 | 1 (0)| 00:00:01 | | 1 | | 0 (0)| 00:00:01 | | 1 | 128 | 1 (0)| 00:00:01 | | 1 | | 0 (0)| 00:00:01 | | 2 | 338 | 2 (0)| 00:00:01 | | 1 | | 1 (0)| 00:00:01 | ---------------------------------------- Notice how the ROWNUM predicate was apparently pushed far into the view. Now compare with 11.2.0.1: 11.2.0.1 --------------------------------------------------------------- +-------- | Id | Operation | Name | Rows | --------------------------------------------------------------- +-------- | 0 | SELECT STATEMENT | | | | 1 | COUNT STOPKEY | | | | 2 | VIEW | | 123 | | 3 | NESTED LOOPS OUTER | | 123 | | 4 | NESTED LOOPS OUTER | | 123 | | 5 | NESTED LOOPS OUTER | | 123 | | 6 | NESTED LOOPS | | 123 | | 7 | TABLE ACCESS BY INDEX ROWID | B1PERMIT | 501K | | 8 | INDEX FULL SCAN DESCENDING | B1PERMIT_FILEDD_IX| 4114 | | 9 | INDEX RANGE SCAN | B3CONTRA_PK | 1 | | 10 | TABLE ACCESS BY INDEX ROWID | BPERMIT_DETAIL | 1 | | 11 | INDEX UNIQUE SCAN | BPERMIT_DETAIL_PK | 1 | | 12 | TABLE ACCESS BY INDEX ROWID | B1_EXPIRATION | 1 | | 13 | INDEX RANGE SCAN | B1_EXPIRATION_PK | 1 | | 14 | TABLE ACCESS BY INDEX ROWID | BWORKDES | 1 | | 15 | INDEX UNIQUE SCAN | BWORKDES_PK | 1 | --------------------------------------------------------------- +-------- 11.2.0.1 (Cont) +-----------------------------------+ | Rows | Bytes | Cost | Time | +-----------------------------------+ | | | 10K | | | | | | | | 123 | 499K | 10K | 00:02:01 | | 123 | 71K | 10K | 00:02:01 | | 123 | 61K | 9903 | 00:02:59 | | 123 | 43K | 9779 | 00:02:58 | | 123 | 25K | 9533 | 00:02:55 | | 501K | 76M | 1359 | 00:00:17 | | 4114 | | 37 | 00:00:01 | | 1 | 48 | 2 | 00:00:01 | | 1 | 158 | 2 | 00:00:01 | | 1 | | 1 | 00:00:01 | | 1 | 146 | 2 | 00:00:01 | | 1 | | 1 | 00:00:01 | | 1 | 79 | 2 | 00:00:01 | | 1 | | 1 | 00:00:01 | +-----------------------------------+ This OTN thread might provide a little more information about why it appears in the 10.2.0.4 and 11.1.0.6 plans that "WHERE ROWNUM < 1" was specified: http://forums.oracle.com/forums/thread.jspa?messageID=3774308 Of course, it could also have to do with shared cursors and bind variables from a previous execution. Suggestions: Use a /*+ gather_plan_statistics */ hint in the SQL statement to allow DBMS_XPLAN to retrieve the predicted number of rows and the actual number of rows. The SQL statement with the hint in place would start like this: SELECT /*+ gather_plan_statistics */ * FROM (SELECT A.SERV_PROV_CODE AS SERV_PROV_CODE, You might also want to gather a 10053 trace for the SQL statement executed on 11.1.0.6 and 11.2.0.1 and then compare the two trace files. Charles Hooper IT Manager/Oracle DBA K&M Machine-Fabricating, Inc.
From: lsllcm on 10 Nov 2009 10:22 Thanks Charles and Randolf -----------------------item 1 --------------------------------- 1. I include the Predicate Information here, from gather plan statistics, the estimated rows in 11.1.0.6 is less than 11.2.0.1. -------------------------11.1.0.6 execution plan------------------------------------------------ Elapsed: 00:00:03.55 SQL> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR (NULL,NULL,'ALLSTATS LAST')) ; PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 4h3u67mxcfa6m, child number 0 ------------------------------------- SELECT /*+ gather_plan_statistics */ * FROM (SELECT A.SERV_PROV_CODE AS SERV_PROV_CODE, A.B1_PER_ID1 AS B1_PER_ID1, A.B1_PER_ID2 AS B1_PER_ID2, A.B1_PER_ID3 AS B1_PER_ID3, A.B1_PER_GROUP, A.B1_PER_TYPE, A.B1_PER_SUB_TYPE, A.B1_PER_CATEGORY, A.B1_SPECIAL_TEXT, A.B1_CREATED_BY_ACA, A.R3_STD_TIME_CLASS_CODE, A.B1_STANDARD_TIME, A.B1_EVENT_CODE, A.B1_REF_ID, A.B1_APPL_STATUS, PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------- A.REC_FUL_NAM, A.REC_STATUS, A.PROJECT_NBR, A.B1_ALT_ID, A.B1_TRACKING_NBR, A.B1_APPL_STATUS_DATE, A.B1_REF_ID1, A.B1_REF_ID2, A.B1_REF_ID3, A.B1_Q_UD1, A.B1_Q_UD2, A.B1_Q_UD3, A.B1_Q_UD4, A.APP_STATUS_ Plan hash value: 1735401230 ------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used- Mem PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------- |* 1 | COUNT STOPKEY | | 1 | | 2 |00:00:00.02 | 1971 | | | | 2 | VIEW | | 1 | 9 | 2 |00:00:00.02 | 1971 | | | |* 3 | SORT ORDER BY STOPKEY | | 1 | 9 | 2 |00:00:00.02 | 1971 | 2048 | 2048 | 2048 (0 | 4 | NESTED LOOPS OUTER | | 1 | 9 | 2 |00:00:00.02 | 1971 | | | | 5 | NESTED LOOPS OUTER | | 1 | 9 | 2 |00:00:00.02 | 1963 | | | | 6 | NESTED LOOPS OUTER | | 1 | 9 | 2 |00:00:00.02 | 1955 | | | | 7 | NESTED LOOPS | | 1 | 9 | 2 |00:00:00.02 | 1954 | | | |* 8 | INDEX FAST FULL SCAN | B3CONTRA_PK | 1 | 9 | 2 |00:00:00.02 | 1946 | | | |* 9 | TABLE ACCESS BY INDEX ROWID| B1PERMIT | 2 | 1 | 2 |00:00:00.01 | 8 | | | |* 10 | INDEX UNIQUE SCAN | B1PERMIT_PK | 2 | 1 | 2 |00:00:00.01 | 6 | | | PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------- | 11 | TABLE ACCESS BY INDEX ROWID | B1_EXPIRATION | 2 | 1 | 0 |00:00:00.01 | 1 | | | |* 12 | INDEX RANGE SCAN | B1_EXPIRATION_PK | 2 | 1 | 0 |00:00:00.01 | 1 | | | | 13 | TABLE ACCESS BY INDEX ROWID | BWORKDES | 2 | 1 | 2 |00:00:00.01 | 8 | | | |* 14 | INDEX UNIQUE SCAN | BWORKDES_PK | 2 | 1 | 2 |00:00:00.01 | 6 | | | | 15 | TABLE ACCESS BY INDEX ROWID | BPERMIT_DETAIL | 2 | 1 | 2 |00:00:00.01 | 8 | | | |* 16 | INDEX UNIQUE SCAN | BPERMIT_DETAIL_PK | 2 | 1 | 2 |00:00:00.01 | 6 | | | ------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------- 1 - filter(ROWNUM<101) 3 - filter(ROWNUM<101) 8 - filter(("L"."B1_LICENSE_NBR"=:P1 AND "L"."B1_LICENSE_TYPE"=:P2 AND "L"."SERV_PROV_CODE"=:SPC)) 9 - filter((("A"."B1_APPL_CLASS" IS NULL OR "A"."B1_APPL_CLASS"='COMPLETE') AND "A"."REC_STATUS"='A')) 10 - access("A"."SERV_PROV_CODE"=:SPC AND "A"."B1_PER_ID1"="L"."B1_PER_ID1" AND "A"."B1_PER_ID2"="L"."B1_PER_ID2" AND "A"."B1_PER_ID3"="L"."B1_PER_ID3") filter("A"."B1_PER_ID3" NOT LIKE '#%') 12 - access("G"."SERV_PROV_CODE"=:SPC AND "A"."B1_PER_ID1"="G"."B1_PER_ID1" AND "A"."B1_PER_ID2"="G"."B1_PER_ID2" AND "A"."B1_PER_ID3"="G"."B1_PER_ID3") 14 - access("C"."SERV_PROV_CODE"=:SPC AND "A"."B1_PER_ID1"="C"."B1_PER_ID1" AND "A"."B1_PER_ID2"="C"."B1_PER_ID2" AND "A"."B1_PER_ID3"="C"."B1_PER_ID3") PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------- 16 - access("B"."SERV_PROV_CODE"=:SPC AND "A"."B1_PER_ID1"="B"."B1_PER_ID1" AND "A"."B1_PER_ID2"="B"."B1_PER_ID2" AND "A"."B1_PER_ID3"="B"."B1_PER_ID3") 58 rows selected. Elapsed: 00:00:00.17 SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options D:\> -------------------------11.2.0.1 execution plan------------------------------------------------ Elapsed: 00:00:53.55 SQL> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR (NULL,NULL,'ALLSTATS LAST')) ; PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------- SQL_ID 4h3u67mxcfa6m, child number 0 ------------------------------------- SELECT /*+ gather_plan_statistics */ * FROM (SELECT A.SERV_PROV_CODE AS SERV_PROV_CODE, A.B1_PER_ID1 AS B1_PER_ID1, A.B1_PER_ID2 AS B1_PER_ID2, A.B1_PER_ID3 AS B1_PER_ID3, A.B1_PER_GROUP, A.B1_PER_TYPE, A.B1_PER_SUB_TYPE, A.B1_PER_CATEGORY, A.B1_SPECIAL_TEXT, A.B1_CREATED_BY_ACA, A.R3_STD_TIME_CLASS_CODE, A.B1_STANDARD_TIME, A.B1_EVENT_CODE, A.B1_REF_ID, A.B1_APPL_STATUS, PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------- A.REC_FUL_NAM, A.REC_STATUS, A.PROJECT_NBR, A.B1_ALT_ID, A.B1_TRACKING_NBR, A.B1_APPL_STATUS_DATE, A.B1_REF_ID1, A.B1_REF_ID2, A.B1_REF_ID3, A.B1_Q_UD1, A.B1_Q_UD2, A.B1_Q_UD3, A.B1_Q_UD4, A.APP_STATUS_ Plan hash value: 3893163705 --------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 3 |00:00:19.43 | 1067K| 1506 | |* 1 | COUNT STOPKEY | | 1 | | 3 |00:00:19.43 | 1067K| 1506 | | 2 | VIEW | | 1 | 123 | 3 |00:00:19.43 | 1067K| 1506 | | 3 | NESTED LOOPS OUTER | | 1 | 123 | 3 |00:00:19.43 | 1067K| 1506 | | 4 | NESTED LOOPS OUTER | | 1 | 123 | 3 |00:00:20.24 | 1067K| 1498 | | 5 | NESTED LOOPS OUTER | | 1 | 123 | 3 |00:00:20.24 | 1067K| 1498 | | 6 | NESTED LOOPS | | 1 | 123 | 3 |00:00:20.05 | 1067K| 1488 | |* 7 | TABLE ACCESS BY INDEX ROWID| B1PERMIT | 1 | 513K| 515K|00:00:22.49 | 153K| 1256 | |* 8 | INDEX FULL SCAN DESCENDING| B1PERMIT_FILEDD_IX | 1 | 4114 | 517K|00:00:05.99 | 4331 | 334 | |* 9 | INDEX RANGE SCAN | B3CONTRA_PK | 515K| 1 | 3 |00:00:10.67 | 913K| 232 | PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------- | 10 | TABLE ACCESS BY INDEX ROWID | BPERMIT_DETAIL | 3 | 1 | 3 |00:00:00.22 | 13 | 10 | |* 11 | INDEX UNIQUE SCAN | BPERMIT_DETAIL_PK | 3 | 1 | 3 |00:00:00.10 | 8 | 5 | | 12 | TABLE ACCESS BY INDEX ROWID | B1_EXPIRATION | 3 | 1 | 0 |00:00:00.01 | 2 | 0 | |* 13 | INDEX RANGE SCAN | B1_EXPIRATION_PK | 3 | 1 | 0 |00:00:00.01 | 2 | 0 | | 14 | TABLE ACCESS BY INDEX ROWID | BWORKDES | 3 | 1 | 3 |00:00:00.20 | 11 | 8 | |* 15 | INDEX UNIQUE SCAN | BWORKDES_PK | 3 | 1 | 3 |00:00:00.12 | 8 | 5 | --------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------- 1 - filter(ROWNUM<101) 7 - filter((("A"."B1_APPL_CLASS" IS NULL OR "A"."B1_APPL_CLASS"='COMPLETE') AND "A"."REC_STATUS"='A' AND "A"."B1_PER_ID3" NOT LIKE '#%')) 8 - access("A"."SERV_PROV_CODE"=:SPC) filter("A"."SERV_PROV_CODE"=:SPC) 9 - access("L"."SERV_PROV_CODE"=:SPC AND "A"."B1_PER_ID1"="L"."B1_PER_ID1" AND "A"."B1_PER_ID2"="L"."B1_PER_ID2" AND "A"."B1_PER_ID3"="L"."B1_PER_ID3" AND "L"."B1_LICENSE_NBR"=:P1 AND "L"."B1_LICENSE_TYPE"=:P2) filter("L"."B1_LICENSE_TYPE"=:P2) 11 - access("B"."SERV_PROV_CODE"=:SPC AND "A"."B1_PER_ID1"="B"."B1_PER_ID1" AND "A"."B1_PER_ID2"="B"."B1_PER_ID2" AND "A"."B1_PER_ID3"="B"."B1_PER_ID3") PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------- 13 - access("G"."SERV_PROV_CODE"=:SPC AND "A"."B1_PER_ID1"="G"."B1_PER_ID1" AND "A"."B1_PER_ID2"="G"."B1_PER_ID2" AND "A"."B1_PER_ID3"="G"."B1_PER_ID3") 15 - access("C"."SERV_PROV_CODE"=:SPC AND "A"."B1_PER_ID1"="C"."B1_PER_ID1" AND "A"."B1_PER_ID2"="C"."B1_PER_ID2" AND "A"."B1_PER_ID3"="C"."B1_PER_ID3") 60 rows selected. Elapsed: 00:00:02.12 SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ------------------------------item 2----------------------------------- 2. below are first K rows in 10053 trace file in 11.2.0.1 version, I am not familiar with 10053 trace file, I will spend some time to research it. The trace file is about 2m, it is too long to paste here. The forum has no upload file feature. First K Rows: K = 100.00, N = 40640900.00 First K Rows: K = 100.00, N = 40640900.00 First K Rows: K = 100.00, N = 26416585.00 First K Rows: K = 100.00, N = 26416585.00 First K Rows: K = 100.00, N = 73255.00 First K Rows: K = 100.00, N = 26416585.00 First K Rows: K = 100.00, N = 26416585.00 First K Rows: K = 100.00, N = 26416585.00 First K Rows: K = 100.00, N = 40640900.00 First K Rows: K = 100.00, N = 32739848.00 First K Rows: K = 100.00, N = 90790.00 First K Rows: K = 100.00, N = 112700.00 First K Rows: K = 100.00, N = 40640900.00 First K Rows: K = 100.00, N = 112700.00 First K Rows: K = 100.00, N = 32739848.00 First K Rows: K = 100.00, N = 90790.00 First K Rows: K = 100.00, N = 112700.00 First K Rows: K = 100.00, N = 112700.00 First K Rows: K = 100.00, N = 112700.00 First K Rows: K = 100.00, N = 112700.00 ------------------------------item 3--------------------------------------- Thanks for the link, I am reading on it. This OTN thread might provide a little more information about why it appears in the 10.2.0.4 and 11.1.0.6 plans that "WHERE ROWNUM < 1" was specified: http://forums.oracle.com/forums/thread.jspa?messageID=3774308 Thanks lsllcm
From: lsllcm on 18 Nov 2009 05:26 Hi Charles and Randolf, There is one more interesting issue. When I use A JOIN B and A JOIN C, the optimizer choose index scan on table C. When I use A JOIN B and B JOIN C, the optimizer choose full table scan on table C. Below is test case: Case 1: When I use A JOIN B and A JOIN C, the optimizer choose index scan on table C. SELECT X.SERV_PROV_CODE, X.B1_PER_ID1, X.B1_PER_ID2, X.B1_PER_ID3, SUM(NVL(X.Fee_Allocation, 0)) GF_FEE FROM SETDETAILS S, X4PAYMENT_FEEITEM X, F4FEEITEM F WHERE S.SERV_PROV_CODE = 'SACRAMENTO' AND S.SET_ID = 'CONNIE' AND S.REC_STATUS = 'A' AND S.SERV_PROV_CODE = X.SERV_PROV_CODE AND F.SERV_PROV_CODE = S.SERV_PROV_CODE AND S.B1_PER_ID1 = X.B1_PER_ID1 AND S.B1_PER_ID2 = X.B1_PER_ID2 AND S.B1_PER_ID3 = X.B1_PER_ID3 AND F.B1_PER_ID1 = S.B1_PER_ID1 AND F.B1_PER_ID2 = S.B1_PER_ID2 AND F.B1_PER_ID3 = S.B1_PER_ID3 AND F.FEEITEM_SEQ_NBR = X.FEEITEM_SEQ_NBR AND S.REC_STATUS = X.REC_STATUS AND F.REC_STATUS = X.REC_STATUS AND (X.PAYMENT_FEEITEM_STATUS != 'VOIDED' OR X.PAYMENT_FEEITEM_STATUS IS NULL) GROUP BY X.SERV_PROV_CODE, X.B1_PER_ID1, X.B1_PER_ID2, X.B1_PER_ID3 Elapsed: 00:00:00.06 Plan hash value: 305769021 --------------------------------------------------------------------------------------------------- | Id | Operation | Name | E- Rows | OMem | 1Mem | Used-Mem --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------- | 1 | HASH GROUP BY | | 40516 | 745K| 745K| 668K (0) | 2 | NESTED LOOPS | | | | | | 3 | NESTED LOOPS | | 40516 | | | | 4 | NESTED LOOPS | | 85 | | | |* 5 | TABLE ACCESS BY INDEX ROWID| SETDETAILS | 24 | | | |* 6 | INDEX RANGE SCAN | SETDETAILS_SETID1_IX | 24 | | | |* 7 | TABLE ACCESS BY INDEX ROWID| F4FEEITEM | 3 | | | |* 8 | INDEX RANGE SCAN | F4FEEITEM_PK | 1 | | | |* 9 | INDEX RANGE SCAN | X4PAYMENT_FEEITEM_PK | 1 | | | |* 10 | TABLE ACCESS BY INDEX ROWID | X4PAYMENT_FEEITEM | 475 | | | --------------------------------------------------------------------------------------------------- PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - filter("S"."REC_STATUS"='A') 6 - access("S"."SERV_PROV_CODE"='SACRAMENTO' AND "S"."SET_ID"='CONNIE') 7 - filter("F"."REC_STATUS"='A') 8 - access("F"."SERV_PROV_CODE"='SACRAMENTO' AND "F"."B1_PER_ID1"="S"."B1_PER_ID1" AND "F"."B1_PER_ID2"="S"."B1_PER_ID2" AND "F"."B1_PER_ID3"="S"."B1_PER_ID3") 9 - access("X"."SERV_PROV_CODE"='SACRAMENTO' AND "S"."B1_PER_ID1"="X"."B1_PER_ID1" AND "S"."B1_PER_ID2"="X"."B1_PER_ID2" AND "S"."B1_PER_ID3"="X"."B1_PER_ID3" AND PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------- "F"."FEEITEM_SEQ_NBR"="X"."FEEITEM_SEQ_NBR") filter("F"."FEEITEM_SEQ_NBR"="X"."FEEITEM_SEQ_NBR") 10 - filter((("X"."PAYMENT_FEEITEM_STATUS" IS NULL OR "X"."PAYMENT_FEEITEM_STATUS"<>'VOIDED') AND "X"."REC_STATUS"='A')) Note ----- - Warning: basic plan statistics not available. These are only collected when: * hint 'gather_plan_statistics' is used for the statement or * parameter 'statistics_level' is set to 'ALL', at session or system level 55 rows selected. Elapsed: 00:00:04.05 SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Case 2: When I use A JOIN B and B JOIN C, the optimizer choose full table scan on table C. Plan hash value: 2372462434 ---------------------------------------------------------------------------------------------------- | Id | Operation | Name | E- Rows | OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | | PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------- | 1 | HASH GROUP BY | | 1 | 745K| 745K| 660K (0)| |* 2 | HASH JOIN | | 95859 | 744K| 744K| 1185K (0)| | 3 | NESTED LOOPS | | | | | | | 4 | NESTED LOOPS | | 95859 | | | | |* 5 | TABLE ACCESS BY INDEX ROWID| SETDETAILS | 24 | | | | |* 6 | INDEX RANGE SCAN | SETDETAILS_SETID1_IX | 24 | | | | |* 7 | INDEX RANGE SCAN | X4PAYMENT_FEEITEM_PK | 1 | | | | |* 8 | TABLE ACCESS BY INDEX ROWID | X4PAYMENT_FEEITEM | 3921 | | | | |* 9 | TABLE ACCESS FULL | F4FEEITEM | 1173K| | | | ---------------------------------------------------------------------------------------------------- PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("F"."SERV_PROV_CODE"="S"."SERV_PROV_CODE" AND "F"."B1_PER_ID1"="X"."B1_PER_ID1" AND "F"."B1_PER_ID2"="X"."B1_PER_ID2" AND "F"."B1_PER_ID3"="X"."B1_PER_ID3" AND "F"."FEEITEM_SEQ_NBR"="X"."FEEITEM_SEQ_NBR" AND "F"."REC_STATUS"="X"."REC_STATUS") 5 - filter("S"."REC_STATUS"='A') 6 - access("S"."SERV_PROV_CODE"='SACRAMENTO' AND "S"."SET_ID"='CONNIE') 7 - access("X"."SERV_PROV_CODE"='SACRAMENTO' AND "S"."B1_PER_ID1"="X"."B1_PER_ID1" AND "S"."B1_PER_ID2"="X"."B1_PER_ID2" AND "S"."B1_PER_ID3"="X"."B1_PER_ID3") PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------- 8 - filter((("X"."PAYMENT_FEEITEM_STATUS" IS NULL OR "X"."PAYMENT_FEEITEM_STATUS"<>'VOIDED') AND "X"."REC_STATUS"='A')) 9 - filter(("F"."SERV_PROV_CODE"='SACRAMENTO' AND "F"."REC_STATUS"='A')) Note ----- - Warning: basic plan statistics not available. These are only collected when: * hint 'gather_plan_statistics' is used for the statement or * parameter 'statistics_level' is set to 'ALL', at session or system level 54 rows selected. Elapsed: 00:00:02.80 SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Thanks lsllcm
From: Charles Hooper on 18 Nov 2009 07:53
On Nov 18, 5:26 am, lsllcm <lsl...(a)gmail.com> wrote: > Hi Charles and Randolf, > > There is one more interesting issue. > > When I use A JOIN B and A JOIN C, the optimizer choose index scan on > table C. > When I use A JOIN B and B JOIN C, the optimizer choose full table scan > on table C. > > Below is test case: > > Case 1: When I use A JOIN B and A JOIN C, the optimizer choose index > scan on table C. > > SELECT X.SERV_PROV_CODE, > X.B1_PER_ID1, > X.B1_PER_ID2, > X.B1_PER_ID3, > SUM(NVL(X.Fee_Allocation, 0)) GF_FEE > FROM SETDETAILS S, X4PAYMENT_FEEITEM X, F4FEEITEM F > WHERE S.SERV_PROV_CODE = 'SACRAMENTO' > AND S.SET_ID = 'CONNIE' > AND S.REC_STATUS = 'A' > AND S.SERV_PROV_CODE = X.SERV_PROV_CODE > AND F.SERV_PROV_CODE = S.SERV_PROV_CODE > AND S.B1_PER_ID1 = X.B1_PER_ID1 > AND S.B1_PER_ID2 = X.B1_PER_ID2 > AND S.B1_PER_ID3 = X.B1_PER_ID3 > AND F.B1_PER_ID1 = S.B1_PER_ID1 > AND F.B1_PER_ID2 = S.B1_PER_ID2 > AND F.B1_PER_ID3 = S.B1_PER_ID3 > AND F.FEEITEM_SEQ_NBR = X.FEEITEM_SEQ_NBR > AND S.REC_STATUS = X.REC_STATUS > AND F.REC_STATUS = X.REC_STATUS > AND (X.PAYMENT_FEEITEM_STATUS != 'VOIDED' OR > X.PAYMENT_FEEITEM_STATUS IS NULL) > GROUP BY X.SERV_PROV_CODE, X.B1_PER_ID1, X.B1_PER_ID2, X.B1_PER_ID3 Please post the other query also. Transitive closure should apply to the query, and Oracle should be able to derive the additional join conditions: If A=B and B=C, then A=C If A=B and A=C, then B=C Thus, after transformation the two queries should appear nearly identical. Please generate a 10053 trace at level 1 for each of the two queries. Search the 10053 trace for the following line: Final query after transformations:******* UNPARSED QUERY IS ******* Below that line in a 11.2.0.1 10053 trace file you should see the final version of the query after transformation (although it appears that Oracle 11.1.0.6 and above may not show derived "IS NOT NULL" predicates in the final query output.) Please post the transformed version of each query which appears below the line "Final query after transformations:******* UNPARSED QUERY IS *******" in the trace file. There must be a significant difference in the two transformed versions of the queries as one plan predicts that 40,516 rows will be returned while the other predicts that 1 row will be returned. If you are now experiencing performance problems with a different query, you might consider creating a new message thread in this group - there is a chance that more people will see the new message thread and offer assistance. Charles Hooper IT Manager/Oracle DBA K&M Machine-Fabricating, Inc. |