performance issue after upgrade to oracle 11.2.0.1 linux 32 bit.
in [Oracle]
|
From: Charles Hooper on 5 Nov 2009 07:27 On Nov 4, 7:56 pm, joel garry <joel-ga...(a)home.com> wrote: > Yes, Jonathan blogged about an example of this in "FBI > problem" (http://jonathanlewis.wordpress.com/2007/11/18/fbi-problem/ > ) But it's still usually worth a try, if just to narrow > possibilities. > > Also, just to clearly emphasize to the OP, it is recommended in a lot > of places not to set OPTIMIZER_INDEX_COST_ADJ and > OPTIMIZER_INDEX_CACHING, at least until testing shows they help - it's > likely a mistake to keep the settings from earlier versions. Seehttp://richardfoote.wordpress.com/2009/09/01/optimizer_index_caching-... > for some idea of what to look for. > > jg > -- Joel, Thanks for providing the links. It looks like Jonathan's blog post describes what I saw while trying to un-fix 11.2.0.1. Richard Foote also has at least a 3 part series on the effects of OPTIMIZER_INDEX_COST_ADJ. This example shows a query requiring 00:01:49.75 (just under 2 minutes) with the default value of OPTIMIZER_INDEX_COST_ADJ, and 02:16:44.42 (just over 2.25 hours) with a very small value for the parameter: http://richardfoote.wordpress.com/2009/07/22/the-cbo-and-indexes-optimizer_index_cost_adj-part-ii/ Of course there is also one of my test cases here: http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/6c436cee329326ec/65f7487bd653d0df "What about the performance results? * Oracle 11.1.0.7 on 64 bit Windows finished the full tablescan access path for the query in 35.38 seconds and the index access path in 1 hour, 38 minutes and 7 seconds. * Oracle 11.1.0.6 on 64 bit Oracle Enterprise Linux 5 required 41.98 seconds for the full tablescan access path and one hour, 14 minutes and 40 seconds for the index access path. * Oracle 11.2.0.1 required 13.13 seconds (a significant drop from the 41.98 seconds received on the same server with the same operating system and disk subsystem) and 22.13 seconds for the index access path (an unbelievable drop from the one hour, 14 minutes and 40 seconds on the same server, operating system, and I/O subsystem)." 35.38 seconds with the default value compared to 1:38:07 (just over an hour and a half) with a value of 5 (10 might have also worked, but not tested). 41.98 seconds with the default value compared to 1:14:40 (just under 1.25 hours) with a value of 5. 13.13 seconds with the default value compared to 22.13 seconds with a value of 5. I guess the message is to test to see why the performance is different. Charles Hooper IT Manager/Oracle DBA K&M Machine-Fabricating, Inc.
From: lsllcm on 5 Nov 2009 11:37 Thank for your good suggestions and comments: I have done some test based on your good suggestions: below are some test cases --------------------case 1-------------------------------------------- 1. What cost / plan do you get if you request to use the index SETDETAILS_SETID_IX? Result: as same as no index hint --------------------case 2-------------------------------------------- 2. What plan do you get in 11.2 if you're setting OPTIMIZER_INDEX_COST_ADJ and OPTIMIZER_INDEX_CACHING to default values: Result: get below execution plan. The consistent read is 3656. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> set autotrace trace SQL> set timing on SQL> SQL> variable spc varchar2(15); SQL> variable bfd varchar2(40); SQL> variable efd varchar2(40); SQL> variable p1 varchar2(60); SQL> variable p2 varchar2(60); SQL> exec :spc:='SACRAMENTO'; PL/SQL procedure successfully completed. Elapsed: 00:00:00.00 SQL> exec :bfd:='2005-12-05'; PL/SQL procedure successfully completed. Elapsed: 00:00:00.00 SQL> exec :efd:='2008-12-04 23:59:59'; PL/SQL procedure successfully completed. Elapsed: 00:00:00.00 SQL> exec :p1:='254413' PL/SQL procedure successfully completed. Elapsed: 00:00:00.00 SQL> exec :p2:='Contractor' PL/SQL procedure successfully completed. Elapsed: 00:00:00.01 SQL> SQL> --alter session set optimizer_features_enable = "11.1.0.6"; SQL> alter session set optimizer_index_caching = 0; Session altered. Elapsed: 00:00:00.00 SQL> alter session set optimizer_index_cost_adj = 100; Session altered. Elapsed: 00:00:00.01 SQL> alter session set optimizer_mode = ALL_ROWS; Session altered. Elapsed: 00:00:00.00 SQL> --alter session set optimizer_mode = FIRST_ROWS_100; SQL> SQL> SQL> --SELECT SQL> SELECT /*+ index(SD SETDETAILS_SETID_IX) */ 2 xfi.serv_prov_code, 3 xfi.b1_per_id1, 4 xfi.b1_per_id2, 5 xfi.b1_per_id3, 6 xfi.feeitem_seq_nbr, 7 xfi.invoice_nbr, 8 xfi.gf_fee_period, 9 xfi.gf_fee, 10 xfi.gf_des, 11 xfi.gf_unit, 12 xfi.gf_udes, 13 finv.invoice_date AS gf_fee_apply_date, 14 xfi.feeitem_invoice_status, 15 xfi.gf_l1, 16 xfi.gf_l2, 17 xfi.gf_l3, 18 xfi.x4feeitem_invoice_udf1, 19 xfi.x4feeitem_invoice_udf2, 20 xfi.x4feeitem_invoice_udf3, 21 xfi.x4feeitem_invoice_udf4, 22 xfi.gf_fee_schedule, 23 xfi.fee_schedule_version, 24 xfi.rec_date, 25 xfi.rec_ful_nam, 26 xfi.rec_status, 27 f4.GF_COD, 28 f4.GF_PRIORITY 29 FROM x4feeitem_invoice xfi, setdetails sd, f4invoice finv, F4FEEITEM f4 30 WHERE sd.serv_prov_code = xfi.serv_prov_code 31 AND sd.b1_per_id1 = xfi.b1_per_id1 32 AND sd.b1_per_id2 = xfi.b1_per_id2 33 AND sd.b1_per_id3 = xfi.b1_per_id3 34 AND xfi.serv_prov_code = f4.serv_prov_code 35 AND xfi.b1_per_id1 = f4.b1_per_id1 36 AND xfi.b1_per_id2 = f4.b1_per_id2 37 AND xfi.b1_per_id3 = f4.b1_per_id3 38 And xfi.feeitem_seq_nbr = f4.feeitem_seq_nbr 39 AND finv.serv_prov_code = xfi.serv_prov_code 40 AND finv.invoice_nbr = xfi.invoice_nbr 41 AND sd.serv_prov_code = :spc 42 AND upper(sd.set_id) = :p1 43 AND xfi.rec_status = 'A' 44 AND xfi.feeitem_invoice_status = 'INVOICED' 45 ORDER BY gf_fee_apply_date 46 / no rows selected Elapsed: 00:00:00.53 Execution Plan ---------------------------------------------------------- Plan hash value: 3678138156 -------------------------------------------------------------------------------- ----------------------------------- | Id | Operation | Name | Rows | Byte s |TempSpc| Cost (%CPU)| Time | -------------------------------------------------------------------------------- ----------------------------------- | 0 | SELECT STATEMENT | | 20239 | 464 4K| | 6832 (1)| 00:01:22 | | 1 | SORT ORDER BY | | 20239 | 464 4K| 5072K| 6832 (1)| 00:01:22 | |* 2 | HASH JOIN | | 20239 | 464 4K| 2152K| 5801 (1)| 00:01:10 | | 3 | TABLE ACCESS BY INDEX ROWID | F4INVOICE | 61172 | 143 3K| | 924 (1)| 00:00:12 | |* 4 | INDEX RANGE SCAN | F4INVOICE_BATCH_NBR_IX | 61172 | | | 193 (0)| 00:00:03 | |* 5 | HASH JOIN | | 19441 | 400 5K| 3440K| 4566 (1)| 00:00:55 | | 6 | NESTED LOOPS | | | | | | | | 7 | NESTED LOOPS | | 19441 | 320 8K| | 327 (0)| 00:00:04 | | 8 | TABLE ACCESS BY INDEX ROWID| SETDETAILS | 107 | 406 6 | | 6 (0)| 00:00:01 | |* 9 | INDEX RANGE SCAN | SETDETAILS_SETID_IX | 107 | | | 3 (0)| 00:00:01 | |* 10 | INDEX RANGE SCAN | X4FEEITEM_INVOICE_PK | 1 | | | 2 (0)| 00:00:01 | |* 11 | TABLE ACCESS BY INDEX ROWID | X4FEEITEM_INVOICE | 182 | 2384 2 | | 3 (0)| 00:00:01 | | 12 | TABLE ACCESS BY INDEX ROWID | F4FEEITEM | 117K| 481 4K| | 3770 (1)| 00:00:46 | |* 13 | INDEX RANGE SCAN | F4FEEITEM_POS_IX | 117K| | | 577 (1)| 00:00:07 | -------------------------------------------------------------------------------- ----------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("FINV"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE" AND "FINV"."INVOICE _NBR"="XFI"."INVOICE_NBR") 4 - access("FINV"."SERV_PROV_CODE"=:SPC) 5 - access("XFI"."SERV_PROV_CODE"="F4"."SERV_PROV_CODE" AND "XFI"."B1_PER_ID1 "="F4"."B1_PER_ID1" AND "XFI"."B1_PER_ID2"="F4"."B1_PER_ID2" AND "XFI"."B1_PER_ID3"="F4"." B1_PER_ID3" AND "XFI"."FEEITEM_SEQ_NBR"="F4"."FEEITEM_SEQ_NBR") 9 - access("SD"."SERV_PROV_CODE"=:SPC AND UPPER("SET_ID")=:P1) 10 - access("XFI"."SERV_PROV_CODE"=:SPC AND "SD"."B1_PER_ID1"="XFI"."B1_PER_ID 1" AND "SD"."B1_PER_ID2"="XFI"."B1_PER_ID2" AND "SD"."B1_PER_ID3"="XFI"." B1_PER_ID3") 11 - filter("XFI"."FEEITEM_INVOICE_STATUS"='INVOICED' AND "XFI"."REC_STATUS"=' A') 13 - access("F4"."SERV_PROV_CODE"=:SPC) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 3656 consistent gets 0 physical reads 0 redo size 1763 bytes sent via SQL*Net to client 381 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 0 rows processed SQL> rem SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR (NULL,NULL,'ALLSTATS LAST')) ; 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 E:\tools\syncdb> --------------------case 3-------------------------------------------- 3. import statistics: not ready, will do test When I try to import sql test case dmp from 11.2.0.1 to 11.1.0.6. It prompts message as below: ORA-20006: ORA-39001: invalid argument value ORA-39000: bad dump file specification ORA-39142: incompatible version number 3.1 in dump file "oratcb1_00C201740001dpexp.dmp" I will try import sql test case dmp from 11.1.0.6 to 11.2.0.1 tomorrow. --------------------item 4-------------------------------------------- 4. A general question: Is your application actually using the FIRST_ROWS_100 optimizer mode properly? Yes, most of applications are oltp type and return first of rows. So we choose aggressive OPTIMIZER_INDEX_COST_ADJ and OPTIMIZER_INDEX_CACHING --------------------item 5-------------------------------------------- 5. question Why is "_optimizer_cost_based_transformation = off" specified? Why is "optimizer_mode = first_rows_100" specified? Why is "optimizer_index_cost_adj = 10" specified? Why is "optimizer_index_caching = 90" specified? [lsllcm] The _optimizer_cost_based_transformation parameter is set useless, I remove the setting About other three parameters, please see item 4 --------------------item 6-------------------------------------------- 6. system statistics: There are about 10 scheduled jobs. I stop them at first. Thank you again lsllcm
From: Charles Hooper on 5 Nov 2009 15:14 On Nov 5, 11:37 am, lsllcm <lsl...(a)gmail.com> wrote: > Thank for your good suggestions and comments: > > I have done some test based on your good suggestions: below are some > test cases > > --------------------case 2-------------------------------------------- > 2. What plan do you get in 11.2 if you're setting > OPTIMIZER_INDEX_COST_ADJ and OPTIMIZER_INDEX_CACHING to default > values: > > Result: get below execution plan. The consistent read is 3656. > > Connected to: > Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production > With the Partitioning, OLAP, Data Mining and Real Application Testing > options > > SQL> set autotrace trace > SQL> set timing on > SQL> > SQL> variable spc varchar2(15); > SQL> variable bfd varchar2(40); > SQL> variable efd varchar2(40); > SQL> variable p1 varchar2(60); > SQL> variable p2 varchar2(60); > SQL> exec :spc:='SACRAMENTO'; > > PL/SQL procedure successfully completed. > > Elapsed: 00:00:00.00 > SQL> exec :bfd:='2005-12-05'; > > PL/SQL procedure successfully completed. > > Elapsed: 00:00:00.00 > SQL> exec :efd:='2008-12-04 23:59:59'; > > PL/SQL procedure successfully completed. > > Elapsed: 00:00:00.00 > SQL> exec :p1:='254413' > > PL/SQL procedure successfully completed. > > Elapsed: 00:00:00.00 > SQL> exec :p2:='Contractor' > > PL/SQL procedure successfully completed. > > Elapsed: 00:00:00.01 > SQL> > SQL> --alter session set optimizer_features_enable = "11.1.0.6"; > SQL> alter session set optimizer_index_caching = 0; > > Session altered. > > Elapsed: 00:00:00.00 > SQL> alter session set optimizer_index_cost_adj = 100; > > Session altered. > > Elapsed: 00:00:00.01 > SQL> alter session set optimizer_mode = ALL_ROWS; > > Session altered. > > Elapsed: 00:00:00.00 > SQL> --alter session set optimizer_mode = FIRST_ROWS_100; > SQL> > SQL> > SQL> --SELECT > SQL> SELECT /*+ index(SD SETDETAILS_SETID_IX) */ > 2 xfi.serv_prov_code, > 3 xfi.b1_per_id1, (snip) > > no rows selected > > Elapsed: 00:00:00.53 > > Statistics > ---------------------------------------------------------- > 0 recursive calls > 0 db block gets > 3656 consistent gets > 0 physical reads > 0 redo size > 1763 bytes sent via SQL*Net to client > 381 bytes received via SQL*Net from client > 1 SQL*Net roundtrips to/from client > 1 sorts (memory) > 0 sorts (disk) > 0 rows processed > The performance of the above is about 0.02 seconds slower than on the older release of Oracle. Please try the test again without the index hint. Also, you might need to force the join order with SETDETAILS as the leading table (I am not sure why this is not happening automatically based on the old 10053 trace): /*+ LEADING(SD) */ > --------------------item 4-------------------------------------------- > 4. A general question: Is your application actually using the > FIRST_ROWS_100 optimizer mode properly? > Yes, most of applications are oltp type and return first of rows. So > we choose aggressive OPTIMIZER_INDEX_COST_ADJ and > OPTIMIZER_INDEX_CACHING Even in OLTP type applications an OPTIMIZER_MODE of ALL_ROWS may be appropriate. I believe that Randolf's comment was along the lines of: your 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. That is probably not happening in your environment, and I believe that is the intention of the FIRST_ROWS_100 optimizer mode. A case in point where FIRST_ROWS_n may lead to problems (at least with Oracle 10.2.0.x) is poor performance resulting when querying certain data dictionary views when the OPTIMIZER_MODE is not set to ALL_ROWS - I believe that this situation is most severe when fixed object statistics have not been collected. Take a look at the following links: http://jonathanlewis.wordpress.com/2008/11/11/first_rows_n/ Likewise, it is generally not advised to modify OPTIMIZER_INDEX_COST_ADJ due to the index costing problem mentioned by Randolf (also covered in detail in the book "Troubleshooting Oracle Performance"), but to instead verify that suitable system statistics are present - see below. > --------------------item 5-------------------------------------------- > 5. question > Why is "_optimizer_cost_based_transformation = off" specified? > Why is "optimizer_mode = first_rows_100" specified? > Why is "optimizer_index_cost_adj = 10" specified? > Why is "optimizer_index_caching = 90" specified? > [lsllcm] The _optimizer_cost_based_transformation parameter is set > useless, I remove the setting > About other three parameters, please see item 4 Does the above comment mean that you removed the _optimizer_cost_based_transformation parameter? > --------------------item 6-------------------------------------------- > 6. system statistics: There are about 10 scheduled jobs. I stop them > at first. > > Thank you again > lsllcm I think that there is a misunderstanding about the system statistics comment that I made. That comment was related to this showing up in the 10053 trace file: SYSTEM STATISTICS INFORMATION ----------------------------- Using NOWORKLOAD Stats CPUSPEEDNW: 2696 millions instructions/sec (default is 100) IOTFRSPEED: 4096 bytes per millisecond (default is 4096) IOSEEKTIM: 10 milliseconds (default is 10) MBRC: -1 blocks (default is 8) That information is also viewed by executing this query: SELECT * FROM SYS.AUX_STATS$; Charles Hooper IT Manager/Oracle DBA K&M Machine-Fabricating, Inc.
From: Jonathan Lewis on 7 Nov 2009 04:31 "lsllcm" <lsllcm(a)gmail.com> wrote in message news:871566ee-22ef-4895-bcc6-753269a7bc3a(a)f18g2000prf.googlegroups.com... > The trace file is too long, I use three parts. > > ************************************* > PARAMETERS WITH ALTERED VALUES > ****************************** > Compilation Environment Dump > optimizer_features_enable = 11.1.0.6 > optimizer_mode = first_rows_100 > optimizer_index_cost_adj = 10 > optimizer_index_caching = 90 > _optimizer_cost_based_transformation = off Sorry about picking this one up so late. I've had a quick read through the posts I can see at the moment. The critical issue is (as Charles and Randolf have pointed out) the use of first_rows_100, combined with Randolf's observation that the expected result set is 40 million rows. >> First K Rows: K = 100.00, N = 40640900.00 >> First K Rows: Setup end When you don't have the order by clause, Oracle is working on a plan that will pick up any 100 rows as quickly as possible. When you add the order by the optimizer "knows" it has to acquire 40M rows and sort them before returning the first 100. Consequently any path that avoids doing the sorting is likely to be a good path - hence the choice of index to drive the query. -- Regards Jonathan Lewis http://jonathanlewis.wordpress.com Author: Cost Based Oracle: Fundamentals http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html
From: lsllcm on 7 Nov 2009 09:10
------------------------case 1------------------------------------------------ 1. Use hint /*+ index(SD SETDETAILS_SETID_IX) */ Result: the result is same as no index hint Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> rem set autotrace trace SQL> set timing on SQL> SQL> variable spc varchar2(15); SQL> variable bfd varchar2(40); SQL> variable efd varchar2(40); SQL> variable p1 varchar2(60); SQL> variable p2 varchar2(60); SQL> exec :spc:='SACRAMENTO'; PL/SQL procedure successfully completed. Elapsed: 00:00:00.39 SQL> exec :bfd:='2005-12-05'; PL/SQL procedure successfully completed. Elapsed: 00:00:00.07 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 SQL> SQL> rem alter session set optimizer_index_caching = 0; SQL> rem alter session set optimizer_index_cost_adj = 100; SQL> rem alter session set optimizer_mode = ALL_ROWS; SQL> alter session set optimizer_index_caching = 90; Session altered. Elapsed: 00:00:00.03 SQL> alter session set optimizer_index_cost_adj = 10; Session altered. Elapsed: 00:00:00.03 SQL> alter session set optimizer_mode = FIRST_ROWS_100; Session altered. Elapsed: 00:00:00.00 SQL> SQL> SQL> SELECT /*+ index(SD SETDETAILS_SETID_IX) */ xfi.serv_prov_code, 2 xfi.b1_per_id1, 3 xfi.b1_per_id2, 4 xfi.b1_per_id3, 5 xfi.feeitem_seq_nbr, 6 xfi.invoice_nbr, 7 xfi.gf_fee_period, 8 xfi.gf_fee, 9 xfi.gf_des, 10 xfi.gf_unit, 11 xfi.gf_udes, 12 finv.invoice_date AS gf_fee_apply_date, 13 xfi.feeitem_invoice_status, 14 xfi.gf_l1, 15 xfi.gf_l2, 16 xfi.gf_l3, 17 xfi.x4feeitem_invoice_udf1, 18 xfi.x4feeitem_invoice_udf2, 19 xfi.x4feeitem_invoice_udf3, 20 xfi.x4feeitem_invoice_udf4, 21 xfi.gf_fee_schedule, 22 xfi.fee_schedule_version, 23 xfi.rec_date, 24 xfi.rec_ful_nam, 25 xfi.rec_status, 26 f4.GF_COD, 27 f4.GF_PRIORITY 28 FROM x4feeitem_invoice xfi, setdetails sd, f4invoice finv, F4FEEITEM f4 29 WHERE sd.serv_prov_code = xfi.serv_prov_code 30 AND sd.b1_per_id1 = xfi.b1_per_id1 31 AND sd.b1_per_id2 = xfi.b1_per_id2 32 AND sd.b1_per_id3 = xfi.b1_per_id3 33 AND xfi.serv_prov_code = f4.serv_prov_code 34 AND xfi.b1_per_id1 = f4.b1_per_id1 35 AND xfi.b1_per_id2 = f4.b1_per_id2 36 AND xfi.b1_per_id3 = f4.b1_per_id3 37 And xfi.feeitem_seq_nbr = f4.feeitem_seq_nbr 38 AND finv.serv_prov_code = xfi.serv_prov_code 39 AND finv.invoice_nbr = xfi.invoice_nbr 40 AND sd.serv_prov_code = :spc 41 AND upper(sd.set_id) = :p1 42 AND xfi.rec_status = 'A' 43 AND xfi.feeitem_invoice_status = 'INVOICED' 44 ORDER BY gf_fee_apply_date 45 / no rows selected Elapsed: 00:06:07.08 SQL> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR (NULL,NULL,'ALLSTATS LAST' Plan hash value: 1519767420 ----------------------------------------------------------------------------- | Id | Operation | Name | E- Rows | PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 | NESTED LOOPS | | | | 2 | NESTED LOOPS | | 117 | | 3 | NESTED LOOPS | | 1 | | 4 | NESTED LOOPS | | 15 | | 5 | TABLE ACCESS BY INDEX ROWID| F4INVOICE | 248K| |* 6 | INDEX RANGE SCAN | F4INVOICE_DATE_IX | 3 | |* 7 | TABLE ACCESS BY INDEX ROWID| X4FEEITEM_INVOICE | 5 | |* 8 | INDEX RANGE SCAN | X4FEEITEM_INVOICE_NBR_IX | 5 | |* 9 | TABLE ACCESS BY INDEX ROWID | SETDETAILS | 1 | PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- |* 10 | INDEX RANGE SCAN | SETDETAILS_SETID_IX | 24 | |* 11 | INDEX UNIQUE SCAN | F4FEEITEM_PK | 1 | | 12 | TABLE ACCESS BY INDEX ROWID | F4FEEITEM | 101 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 6 - access("FINV"."SERV_PROV_CODE"=:SPC) 7 - filter(("XFI"."FEEITEM_INVOICE_STATUS"='INVOICED' AND "XFI"."REC_STATUS"='A')) PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- 8 - access("XFI"."SERV_PROV_CODE"=:SPC AND "FINV"."INVOICE_NBR"="XFI"."INVOICE_NBR") 9 - filter(("SD"."B1_PER_ID1"="XFI"."B1_PER_ID1" AND "SD"."B1_PER_ID2"="XFI"."B1_PER_ID2" AND "SD"."B1_PER_ID3"="XFI"."B1_PER_ID3")) 10 - access("SD"."SERV_PROV_CODE"=:SPC AND "SD"."SYS_NC00017$"=:P1) 11 - access("F4"."SERV_PROV_CODE"=:SPC AND "XFI"."B1_PER_ID1"="F4"."B1_PER_ID1" AND "XFI"."B1_PER_ID2"="F4"."B1_PER_ID2" AND "XFI"."B1_PER_ID3"="F4"."B1_PER_ID3" AND "XFI"."FEEITEM_SEQ_NBR"="F4"."FEEITEM_SEQ_NBR") PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- 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 leve l 62 rows selected. Elapsed: 00:00:11.59 SQL> exit ------------------------case 2------------------------------------------------ 2. Use hint /*+ LEADING(SD) index(SD SETDETAILS_SETID_IX) */ Result: the result is use SD at first in nested loop. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> rem set autotrace trace SQL> set timing on SQL> SQL> variable spc varchar2(15); SQL> variable bfd varchar2(40); SQL> variable efd varchar2(40); SQL> variable p1 varchar2(60); SQL> variable p2 varchar2(60); SQL> exec :spc:='SACRAMENTO'; PL/SQL procedure successfully completed. Elapsed: 00:00:00.00 SQL> exec :bfd:='2005-12-05'; PL/SQL procedure successfully completed. Elapsed: 00:00:00.00 SQL> exec :efd:='2008-12-04 23:59:59'; PL/SQL procedure successfully completed. Elapsed: 00:00:00.00 SQL> rem exec :p1:='254413' SQL> exec :p1:='SET07' PL/SQL procedure successfully completed. Elapsed: 00:00:00.01 SQL> exec :p2:='Contractor' PL/SQL procedure successfully completed. Elapsed: 00:00:00.00 SQL> SQL> rem alter session set optimizer_index_caching = 0; SQL> rem alter session set optimizer_index_cost_adj = 100; SQL> rem alter session set optimizer_mode = ALL_ROWS; SQL> alter session set optimizer_index_caching = 90; Session altered. Elapsed: 00:00:00.00 SQL> alter session set optimizer_index_cost_adj = 10; Session altered. Elapsed: 00:00:00.01 SQL> alter session set optimizer_mode = FIRST_ROWS_100; Session altered. Elapsed: 00:00:00.00 SQL> SQL> SQL> SELECT /*+ LEADING(SD) index(SD SETDETAILS_SETID_IX) */ xfi.serv_prov_code, 2 xfi.b1_per_id1, 3 xfi.b1_per_id2, 4 xfi.b1_per_id3, 5 xfi.feeitem_seq_nbr, 6 xfi.invoice_nbr, 7 xfi.gf_fee_period, 8 xfi.gf_fee, 9 xfi.gf_des, 10 xfi.gf_unit, 11 xfi.gf_udes, 12 finv.invoice_date AS gf_fee_apply_date, 13 xfi.feeitem_invoice_status, 14 xfi.gf_l1, 15 xfi.gf_l2, 16 xfi.gf_l3, 17 xfi.x4feeitem_invoice_udf1, 18 xfi.x4feeitem_invoice_udf2, 19 xfi.x4feeitem_invoice_udf3, 20 xfi.x4feeitem_invoice_udf4, 21 xfi.gf_fee_schedule, 22 xfi.fee_schedule_version, 23 xfi.rec_date, 24 xfi.rec_ful_nam, 25 xfi.rec_status, 26 f4.GF_COD, 27 f4.GF_PRIORITY 28 FROM x4feeitem_invoice xfi, setdetails sd, f4invoice finv, F4FEEITEM f4 29 WHERE sd.serv_prov_code = xfi.serv_prov_code 30 AND sd.b1_per_id1 = xfi.b1_per_id1 31 AND sd.b1_per_id2 = xfi.b1_per_id2 32 AND sd.b1_per_id3 = xfi.b1_per_id3 33 AND xfi.serv_prov_code = f4.serv_prov_code 34 AND xfi.b1_per_id1 = f4.b1_per_id1 35 AND xfi.b1_per_id2 = f4.b1_per_id2 36 AND xfi.b1_per_id3 = f4.b1_per_id3 37 And xfi.feeitem_seq_nbr = f4.feeitem_seq_nbr 38 AND finv.serv_prov_code = xfi.serv_prov_code 39 AND finv.invoice_nbr = xfi.invoice_nbr 40 AND sd.serv_prov_code = :spc 41 AND upper(sd.set_id) = :p1 42 AND xfi.rec_status = 'A' 43 AND xfi.feeitem_invoice_status = 'INVOICED' 44 ORDER BY gf_fee_apply_date 45 / no rows selected Elapsed: 00:00:00.65 SQL> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR (NULL,NULL,'ALLSTATS LAST')) ; Plan hash value: 3678138156 -------------------------------------------------------------------------------- ----------------------- PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- | Id | Operation | Name | E- Rows | OM em | 1Mem | Used-Mem | -------------------------------------------------------------------------------- ----------------------- | 0 | SELECT STATEMENT | | | | | | | 1 | SORT ORDER BY | | 112K| 10 PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- 24 | 1024 | | |* 2 | HASH JOIN | | 112K| 11M| 2154K| 13M (0)| | 3 | TABLE ACCESS BY INDEX ROWID | F4INVOICE | 248K| | | | |* 4 | INDEX RANGE SCAN | F4INVOICE_BATCH_NBR_IX | 248K| | | | PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- |* 5 | HASH JOIN | | 90650 | 6 94K| 694K| 214K (0)| | 6 | NESTED LOOPS | | | | | | | 7 | NESTED LOOPS | | 90650 | | | | | 8 | TABLE ACCESS BY INDEX ROWID| SETDETAILS | 25 | | | | PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- |* 9 | INDEX RANGE SCAN | SETDETAILS_SETID_IX | 25 | | | | |* 10 | INDEX RANGE SCAN | X4FEEITEM_INVOICE_PK | 1 | | | | |* 11 | TABLE ACCESS BY INDEX ROWID | X4FEEITEM_INVOICE | 3578 | | | | | 12 | TABLE ACCESS BY INDEX ROWID | F4FEEITEM | 1231K| PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- | | | |* 13 | INDEX RANGE SCAN | F4FEEITEM_POS_IX | 1231K| | | | -------------------------------------------------------------------------------- ----------------------- Predicate Information (identified by operation id): --------------------------------------------------- PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- 2 - access("FINV"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE" AND "FINV"."INVOICE_NBR"="XFI"."INVOICE_NBR") 4 - access("FINV"."SERV_PROV_CODE"=:SPC) 5 - access("XFI"."SERV_PROV_CODE"="F4"."SERV_PROV_CODE" AND "XFI"."B1_PER_ID1"="F4"."B1_PER_ID1" AND "XFI"."B1_PER_ID2"="F4"." B1_PER_ID2" AND "XFI"."B1_PER_ID3"="F4"."B1_PER_ID3" AND "XFI"."FEEITEM_SEQ_NBR"=" F4"."FEEITEM_SEQ_NBR") PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- 9 - access("SD"."SERV_PROV_CODE"=:SPC AND "SD"."SYS_NC00017$"=:P1) 10 - access("XFI"."SERV_PROV_CODE"=:SPC AND "SD"."B1_PER_ID1"="XFI"."B1_PER_ID 1" AND "SD"."B1_PER_ID2"="XFI"."B1_PER_ID2" AND "SD"."B1_PER_ID3"="XFI"." B1_PER_ID3") 11 - filter(("XFI"."FEEITEM_INVOICE_STATUS"='INVOICED' AND "XFI"."REC_STATUS"= 'A')) 13 - access("F4"."SERV_PROV_CODE"=:SPC) PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- 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 leve l 60 rows selected. Elapsed: 00:00:00.32 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 E:\tools\syncdata> ------------------------case 3------------------------------------------------ 3. combination ALL_ROWS + OICA (10) + OIC (90) + NO HINTS Result: as below | Id | Operation | Name | E- Rows | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | | | 1 | SORT ORDER BY | | 112K| 1024 | 1024 | | |* 2 | HASH JOIN | | 112K| 11M| 2154K| 13M (0)| | 3 | TABLE ACCESS BY INDEX ROWID | F4INVOICE | 248K| | | | |* 4 | INDEX RANGE SCAN | F4INVOICE_BATCH_NBR_IX | 248K| | | | |* 5 | HASH JOIN | | 90650 | 694K| 694K| 214K (0)| | 6 | NESTED LOOPS | | | | | | | 7 | NESTED LOOPS | | 90650 | | | | | 8 | TABLE ACCESS BY INDEX ROWID| SETDETAILS | 25 | | | | |* 9 | INDEX RANGE SCAN | SETDETAILS_SETID_IX | 25 | | | | |* 10 | INDEX RANGE SCAN | X4FEEITEM_INVOICE_PK | 1 | | | | |* 11 | TABLE ACCESS BY INDEX ROWID | X4FEEITEM_INVOICE | 3578 | | | | | 12 | TABLE ACCESS BY INDEX ROWID | F4FEEITEM | 1231K| | | | |* 13 | INDEX RANGE SCAN | F4FEEITEM_POS_IX | 1231K| | | | ------------------------case 4------------------------------------------------ 4. combination ALL_ROWS + OICA (10) + OIC (0) + NO HINTS Result: as below | Id | Operation | Name | E- Rows | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | | | 1 | SORT ORDER BY | | 112K| 1024 | 1024 | | |* 2 | HASH JOIN | | 112K| 11M| 2154K| 13M (0)| | 3 | TABLE ACCESS BY INDEX ROWID | F4INVOICE | 248K| | | | |* 4 | INDEX RANGE SCAN | F4INVOICE_BATCH_NBR_IX | 248K| | | | |* 5 | HASH JOIN | | 90650 | 694K| 694K| 1047K (0)| | 6 | NESTED LOOPS | | | | | | | 7 | NESTED LOOPS | | 90650 | | | | | 8 | TABLE ACCESS BY INDEX ROWID| SETDETAILS | 25 | | | | |* 9 | INDEX RANGE SCAN | SETDETAILS_SETID_IX | 25 | | | | |* 10 | INDEX RANGE SCAN | X4FEEITEM_INVOICE_PK | 1 | | | | |* 11 | TABLE ACCESS BY INDEX ROWID | X4FEEITEM_INVOICE | 3578 | | | | | 12 | TABLE ACCESS BY INDEX ROWID | F4FEEITEM | 1231K| | | | |* 13 | INDEX RANGE SCAN | F4FEEITEM_POS_IX | 1231K| | | | ------------------------------------------------------------------------------------------------------- ------------------------case 5------------------------------------------------ 5. combination ALL_ROWS + OICA (100) + OIC (0) + NO HINTS Result: as below | Id | Operation | Name | E- Rows | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | | | 1 | SORT ORDER BY | | 112K| 1024 | 1024 | | |* 2 | HASH JOIN | | 112K| 11M| 2154K| 13M (0)| | 3 | TABLE ACCESS BY INDEX ROWID | F4INVOICE | 248K| | | | |* 4 | INDEX RANGE SCAN | F4INVOICE_BATCH_NBR_IX | 248K| | | | |* 5 | HASH JOIN | | 90650 | 694K| 694K| 1047K (0)| | 6 | NESTED LOOPS | | | | | | | 7 | NESTED LOOPS | | 90650 | | | | | 8 | TABLE ACCESS BY INDEX ROWID| SETDETAILS | 25 | | | | |* 9 | INDEX RANGE SCAN | SETDETAILS_SETID_IX | 25 | | | | |* 10 | INDEX RANGE SCAN | X4FEEITEM_INVOICE_PK | 1 | | | | |* 11 | TABLE ACCESS BY INDEX ROWID | X4FEEITEM_INVOICE | 3578 | | | | | 12 | TABLE ACCESS BY INDEX ROWID | F4FEEITEM | 1231K| | | | |* 13 | INDEX RANGE SCAN | F4FEEITEM_POS_IX | 1231K| | | | ------------------------------------------------------------------------------------------------------- ------------------------item 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. I use below command to gather statistics. exec dbms_stats.gather_schema_stats ('TEST',estimate_percent=>100,cascade=> TRUE); Below are histogram of SYS_NC00017$ COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ---------------------------------- --------------- -------------- SYS_NC00017$ 0 2.502076227359 SYS_NC00017$ 1 3.388853334909 SYS_NC00017$ 2 3.391679403148 SYS_NC00017$ 3 3.391679403478 SYS_NC00017$ 4 3.391679403525 SYS_NC00017$ 5 3.442991320807 SYS_NC00017$ 6 3.492087683254 SYS_NC00017$ 7 3.495116616164 SYS_NC00017$ 8 3.547450636185 SYS_NC00017$ 9 3.649479141472 SYS_NC00017$ 10 3.699779507944 SYS_NC00017$ 11 3.703217969722 SYS_NC00017$ 12 3.703622906366 SYS_NC00017$ 13 3.857771503121 SYS_NC00017$ 14 3.907648005162 SYS_NC00017$ 15 3.956124399555 SYS_NC00017$ 16 3.964259789323 SYS_NC00017$ 17 4.013711600766 SYS_NC00017$ 38 4.013711600766 SYS_NC00017$ 40 4.013711600766 COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ---------------------------------- --------------- -------------- SYS_NC00017$ 41 4.013737736751 SYS_NC00017$ 42 4.013737736908 SYS_NC00017$ 43 4.013739364774 SYS_NC00017$ 44 4.013741723170 SYS_NC00017$ 45 4.013741723170 SYS_NC00017$ 56 4.013748076424 SYS_NC00017$ 63 4.013748076644 SYS_NC00017$ 67 4.013748076645 SYS_NC00017$ 68 4.013748076656 SYS_NC00017$ 69 4.013748076657 SYS_NC00017$ 70 4.013748076669 SYS_NC00017$ 72 4.013748076681 SYS_NC00017$ 73 4.013748076718 SYS_NC00017$ 99 4.013748076765 SYS_NC00017$ 122 4.013748076766 SYS_NC00017$ 123 4.013748076798 SYS_NC00017$ 124 4.013748076801 SYS_NC00017$ 125 4.013748076802 SYS_NC00017$ 140 4.013748076826 SYS_NC00017$ 147 4.013748076826 SYS_NC00017$ 148 4.013748076838 COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ---------------------------------- --------------- -------------- SYS_NC00017$ 151 4.013748076838 SYS_NC00017$ 152 4.013748076919 SYS_NC00017$ 161 4.013748881378 SYS_NC00017$ 188 4.013748881378 SYS_NC00017$ 192 4.013748881378 SYS_NC00017$ 211 4.013748881378 SYS_NC00017$ 212 4.013749655139 SYS_NC00017$ 213 4.013751258162 SYS_NC00017$ 214 4.013752022627 SYS_NC00017$ 215 4.013761551658 SYS_NC00017$ 216 4.063638056887 SYS_NC00017$ 222 4.117383319540 SYS_NC00017$ 223 4.167086228207 SYS_NC00017$ 224 4.271108002299 SYS_NC00017$ 225 4.271744272749 SYS_NC00017$ 226 4.273769335395 SYS_NC00017$ 227 4.319380127847 SYS_NC00017$ 228 4.319380127847 SYS_NC00017$ 229 4.319380127847 SYS_NC00017$ 230 4.319380127847 SYS_NC00017$ 231 4.319380127847 COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ---------------------------------- --------------- -------------- SYS_NC00017$ 232 4.319380127847 SYS_NC00017$ 233 4.319380127847 SYS_NC00017$ 234 4.319380127847 SYS_NC00017$ 238 4.319380127847 SYS_NC00017$ 240 4.319380127847 SYS_NC00017$ 241 4.319380127847 SYS_NC00017$ 242 4.319380127847 SYS_NC00017$ 243 4.319380127847 SYS_NC00017$ 244 4.319380127847 SYS_NC00017$ 245 4.319380127847 SYS_NC00017$ 247 4.319380127847 SYS_NC00017$ 248 4.319380127847 SYS_NC00017$ 249 4.324469045995 SYS_NC00017$ 250 4.376187633557 SYS_NC00017$ 251 4.377607232242 SYS_NC00017$ 252 4.430327275963 SYS_NC00017$ 253 4.530723636900 SYS_NC00017$ 254 4.690372424101 ------------------------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. ------------------------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. At first I use below command to gather statistics. exec dbms_stats.gather_schema_stats ('TEST',estimate_percent=>100,cascade=> TRUE); 11g has automatic statistics gathering job too. BEGIN DBMS_AUTO_TASK_ADMIN.disable( client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL); END; / ------------------------item 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). |