Re: performance issue after upgrade to oracle 11.2.0.1 linux 32 bit.
Date: Thu, 5 Nov 2009 08:37:32 -0800 (PST)
Message-ID: <7ffda914-30ca-4971-83b2-55cbb14a6cf5_at_q40g2000prh.googlegroups.com>
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
Received on Thu Nov 05 2009 - 10:37:32 CST