Re: performance issue after upgrade to oracle 11.2.0.1 linux 32 bit.

From: Mladen Gogala <no_at_email.here.invalid>
Date: Tue, 3 Nov 2009 23:19:47 +0000 (UTC)
Message-ID: <pan.2009.11.03.23.19.45_at_email.here.invalid>

On Tue, 03 Nov 2009 07:29:46 -0800, lsllcm wrote:

> Hi All,
>
> I have one performance issue after upgrade to oracle 11.2.0.1 linux 32
> bit.
>
> The optimizer always choose index of "order by column", not use index of
> "where clause columns". Any comments are appreciated.
>
> Below are three test cases:
>
> Case 1: TEST on 11.2.0.1 DBs with ORDER BY ORDER BY gf_fee_apply_date
> Result: Choose incorrect execution plan.
>
> 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> 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.01
> SQL> exec :efd:='2008-12-04 23:59:59';
>
> PL/SQL procedure successfully completed.
>
> Elapsed: 00:00:00.00
> SQL> exec :p1:='SET07%'
>
> PL/SQL procedure successfully completed.
>
> Elapsed: 00:00:00.00
> SQL>
> SQL> SELECT 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:02:15.28
>
> Execution Plan
> ---------------------------------------------------------- Plan hash
> value: 3748720781
>
>

> ----------------------------
>
> | 0 | SELECT STATEMENT | | 103
> | 242
> 05 | 9 (0)| 00:00:01 |
>
> | 1 | NESTED LOOPS | | |
> | | |
>
> | 2 | NESTED LOOPS | | 103
> | 242
> 05 | 9 (0)| 00:00:01 |
>
> | 3 | NESTED LOOPS | | 3 | 5
> 79 | 8 (0)| 00:00:01 |
>
> | 4 | NESTED LOOPS | | 39 | 60
> 45 | 4 (0)| 00:00:01 |
>
> | 5 | TABLE ACCESS BY INDEX ROWID| F4INVOICE |
> 60771 | 14
> 24K| 1 (0)| 00:00:01 |
>
> |* 6 | INDEX RANGE SCAN | F4INVOICE_DATE_IX | 27 |
> | 1 (0)| 00:00:01 |
>
> |* 7 | TABLE ACCESS BY INDEX ROWID| X4FEEITEM_INVOICE | 1 |
> 1
> 31 | 1 (0)| 00:00:01 |
>
> |* 8 | INDEX RANGE SCAN | X4FEEITEM_INVOICE_NBR_IX |
> 1 |
> | 1 (0)| 00:00:01 |
>
> |* 9 | TABLE ACCESS BY INDEX ROWID | SETDETAILS | 1 |
> 38 | 1 (0)| 00:00:01 |
>
> |* 10 | INDEX RANGE SCAN | SETDETAILS_PID_IX | 1 |
> | 1 (0)| 00:00:01 |
>
> |* 11 | INDEX UNIQUE SCAN | F4FEEITEM_PK | 1 |
> | 1 (0)| 00:00:01 |
>
> | 12 | TABLE ACCESS BY INDEX ROWID | F4FEEITEM | 34 | 14
> 28 | 1 (0)| 00:00:01 |
>
>

> ----------------------------
>
>
> 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')
>
> 8 - access("XFI"."SERV_PROV_CODE"=:SPC AND
> "FINV"."INVOICE_NBR"="XFI"."INVOIC
> E_NBR")
>
> 9 - filter(UPPER("SET_ID")=:P1)
> 10 - access("SD"."SERV_PROV_CODE"=:SPC AND
> "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")
>
> 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")
>
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 0 db block gets
> 2891740 consistent gets
> 25708 physical reads
> 0 redo size
> 1763 bytes sent via SQL*Net to client
> 380 bytes received via SQL*Net from client
> 1 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 0 rows processed
> 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
>
> 2. Case 2: test on 11.2.0.1 without ORDER BY ORDER BY
gf_fee_apply_date
> Result: Choose correct execution plan.
>
> C:\>sqlplus accela/dbs_at_dbs26 @e:\tools\sql\test.sql
>
> SQL*Plus: Release 10.2.0.4.0 - Production on Sat Oct 31 13:40:08 2009
>
> Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
>
>
> 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> 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:='SET07%'
>
> PL/SQL procedure successfully completed.
>
> Elapsed: 00:00:00.00
> SQL>
> SQL> SELECT 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.06
>
> Execution Plan
> ---------------------------------------------------------- Plan hash
> value: 1421766072
>
>

> ----------------------------
>
> | 0 | SELECT STATEMENT | | 101
> | 369
> 66 | 5 (20)| 00:00:01 |
>
> | 1 | NESTED LOOPS | | |
> | | |
>
> | 2 | NESTED LOOPS | | 101
> | 369
> 66 | 5 (20)| 00:00:01 |
>
> | 3 | NESTED LOOPS | | 3 | 9
> 72 | 4 (25)| 00:00:01 |
>
> |* 4 | HASH JOIN | | 3 | 9
> 00 | 3 (34)| 00:00:01 |
>
> | 5 | TABLE ACCESS BY INDEX ROWID| SETDETAILS | 107
> | 40
> 66 | 1 (0)| 00:00:01 |
>
> |* 6 | INDEX RANGE SCAN | SETDETAILS_SETID_IX | 107
> |
> | 1 (0)| 00:00:01 |
>
> |* 7 | TABLE ACCESS BY INDEX ROWID| X4FEEITEM_INVOICE |
> 2680 | 3
> 42K| 1 (0)| 00:00:01 |
>
> |* 8 | INDEX RANGE SCAN | X4FEEITEM_INVOICE_NBR_IX |
> 255K|
> | 1 (0)| 00:00:01 |
>
> | 9 | TABLE ACCESS BY INDEX ROWID | F4INVOICE | 1 |
> 24 | 1 (0)| 00:00:01 |
>
> |* 10 | INDEX UNIQUE SCAN | F4INVOICE_PK | 1 |
> | 1 (0)| 00:00:01 |
>
> |* 11 | INDEX UNIQUE SCAN | F4FEEITEM_PK | 1 |
> | 1 (0)| 00:00:01 |
>
> | 12 | TABLE ACCESS BY INDEX ROWID | F4FEEITEM | 34 | 14
> 28 | 1 (0)| 00:00:01 |
>
>

> ----------------------------
>
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 4 - access("SD"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE" AND
> "SD"."B1_PER_ID1"
> ="XFI"."B1_PER_ID1"
>
> AND "SD"."B1_PER_ID2"="XFI"."B1_PER_ID2" AND
> "SD"."B1_PER_ID3"="XF
> I"."B1_PER_ID3")
>
> 6 - access("SD"."SERV_PROV_CODE"=:SPC AND UPPER("SET_ID")=:P1) 7 -
> filter("XFI"."FEEITEM_INVOICE_STATUS"='INVOICED' AND
> "XFI"."REC_STATUS"='
> A')
>
> 8 - access("XFI"."SERV_PROV_CODE"=:SPC)
> 10 - access("FINV"."SERV_PROV_CODE"=:SPC AND
> "FINV"."INVOICE_NBR"="XFI"."INVOI
> CE_NBR")
>
> 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")
>
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 0 db block gets
> 3 consistent gets
> 3 physical reads
> 0 redo size
> 1763 bytes sent via SQL*Net to client
> 380 bytes received via SQL*Net from client
> 1 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 0 rows processed
>
> 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
>
> C:\>
>
>
> 3. Case 3: TEST on 11.1.0.6/10.2.0.4 DBs Result: Choose correct
> execution plan.
>
> Connected to:
> Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
> With the Partitioning, OLAP, Data Mining and Real Application Testing
> options
>
> SQL> set autotrace trace
> SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is
> enabled
> SP2-0611: Error enabling STATISTICS report 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> exec :spc:='SACRAMENTO';
>
> PL/SQL procedure successfully completed.
>
> Elapsed: 00:00:00.01
> 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.01
> SQL> exec :p1:='SET07%'
>
> PL/SQL procedure successfully completed.
>
> Elapsed: 00:00:00.00
> SQL>
> SQL> SELECT 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 /
> Elapsed: 00:00:00.51
>
> Execution Plan
> ---------------------------------------------------------- Plan hash
> value: 2996231674
>
>

> -------------------------
>
> | 0 | SELECT STATEMENT | | 417 |
> 90072
> | 16 (13)| 00:00:01 |
>
> | 1 | SORT ORDER BY | | 417 |
> 90072
> | 16 (13)| 00:00:01 |
>
> |* 2 | HASH JOIN | | 417 |
> 90072
> | 15 (7)| 00:00:01 |
>
> | 3 | NESTED LOOPS | | |
> | | |
>
> | 4 | NESTED LOOPS | | 29 |
> 5626
> | 11 (0)| 00:00:01 |
>
> | 5 | NESTED LOOPS | | 3 |
> 441
> | 8 (0)| 00:00:01 |
>
> | 6 | TABLE ACCESS BY INDEX ROWID| SETDETAILS | 3 |
> 105
> | 2 (0)| 00:00:01 |
>
> |* 7 | INDEX RANGE SCAN | SETDETAILS_SETID_IX | 3 |
> | 1 (0)| 00:00:01 |
>
> |* 8 | TABLE ACCESS BY INDEX ROWID| X4FEEITEM_INVOICE | 1 |
> 112
> | 2 (0)| 00:00:01 |
>
> |* 9 | INDEX RANGE SCAN | X4FEEITEM_INVOICE_PK | 1 |
> | 1 (0)| 00:00:01 |
>
> |* 10 | INDEX UNIQUE SCAN | F4FEEITEM_PK | 1 |
> | 0 (0)| 00:00:01 |
>
> | 11 | TABLE ACCESS BY INDEX ROWID | F4FEEITEM | 10 |
> 470
> | 1 (0)| 00:00:01 |
>
> | 12 | TABLE ACCESS BY INDEX ROWID | F4INVOICE | 9453 |
> 203K
> | 3 (0)| 00:00:01 |
>
> |* 13 | INDEX RANGE SCAN | F4INVOICE_DATE_IX | 9453 |
> | 2 (0)| 00:00:01 |
>
>

> -------------------------
>
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 2 - access("FINV"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE" AND
> "FINV"."INVOICE_NBR"="XFI"."INVOICE_NBR")
> 7 - access("SD"."SERV_PROV_CODE"=:SPC AND UPPER("SET_ID")=:P1) 8 -
> filter("XFI"."FEEITEM_INVOICE_STATUS"='INVOICED' AND
> "XFI"."REC_STATUS"='
> A')
>
> 9 - 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")
>
> 10 - 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")
> 13 - access("FINV"."SERV_PROV_CODE"=:SPC)
>
> 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

Do you happen to have a SQL trace? I don't see whether the CPU costing is on and whether the tables are analyzed. If the analysis job is running nightly, tables are probably analyzed with the system default options.

-- 
http://mgogala.freehostia.com

Received on Tue Nov 03 2009 - 17:19:47 CST

This message: [ Message body ]
Next message: joel garry: "Re: performance issue after upgrade to oracle 11.2.0.1 linux 32 bit."
Previous message: Gerard H. Pille: "Re: oracle-l"
In reply to: lsllcm: "performance issue after upgrade to oracle 11.2.0.1 linux 32 bit."
Next in thread: joel garry: "Re: performance issue after upgrade to oracle 11.2.0.1 linux 32 bit."

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message