Re: Sorry about picking this one up so late.

From: Moorthy Rekapalli <mrekapalli_at_gmail.com>
Date: Tue, 23 Nov 2010 21:00:30 GMT
Message-ID: <2010112316029usenet_at_eggheadcafe.com>



Hi,

How about setting the _sort_elimination_cost_ratio to 4? With the default value 0, if any "order by" column is indexed, it may pick up that index and create performance problem. We are experiencing the same issue and we set this value to 4 in test instances. Once we go through regression, planning to promote this to production. Sharing my 2 cents...

Thanks,
Moorthy.

> On Tuesday, November 03, 2009 10:29 AM 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

>> On Tuesday, November 03, 2009 10:58 AM ddf wrote:

>>

>>> On Tuesday, November 03, 2009 11:07 AM Robert Klemme wrote:

>>> On 11/03/2009 04:58 PM, ddf wrote:
>>> 
>>> 
>>> And: are statistics (table, index and system) current?
>>> 
>>> Kind regards
>>> 
>>> robert
>>> 
>>> --
>>> remember.guy do |as, often| as.you_can - without end
>>> http://blog.rubybestpractices.com/


>>>> On Tuesday, November 03, 2009 11:22 AM Charles Hooper wrote:

>>>>

>>>>> On Tuesday, November 03, 2009 12:44 PM joel garry wrote:

>>>>> A bit of a stretch, but consider this:  rownum is assigned after the
>>>>> predicate is processed, but before sorting is done.  "Bug 6438892 :
>>>>> Suboptimal plan with ROWNUM predicate and NESTED LOOPS." is in the
>>>>> 11.1.0.6 patch set, bug not published.  So maybe there is some
>>>>> mysterious optimizer bug here, set off by the order by even though
>>>>> rownum is not originally a predicate.  Try patching beyond the base
>>>>> release and see if the problem is still there.
>>>>> 
>>>>> jg
>>>>> --
>>>>> _at_home.com is bogus.
>>>>> Do not fart next to a cellphone.  http://www.signonsandiego.com/news/2009/n=
>>>>> ov/02/wwwxconomycom48394/


>>>>>> On Tuesday, November 03, 2009 1:11 PM Charles Hooper wrote:

>>>>>> The OP is experiencing problems in 11.2.0.1, which does not exhibit
>>>>>> the ROWNUM bug that you mentioned, based on my testing.  You might be
>>>>>> suggesting - what if the fix of that bug caused another bug?
>>>>>> Interesting, quite possible.
>>>>>> 
>>>>>> I noticed the large number of NESTED LOOPS in his posted plan also,
>>>>>> which is one of the reasons why I mentioned FIRST_ROWS_n
>>>>>> (OPTIMIZER_MODE).  It might even be the case that the OPTIMIZER_MODE
>>>>>> is set to FIRST_ROWS (or maybe even CHOOSE), rather than FIRST_ROWS_1,
>>>>>> FIRST_ROWS_10, FIRST_ROWS_100, ALL_ROWS, etc.
>>>>>> 
>>>>>> lsllcm, please try setting OPTIMIZER_FEATURES_ENABLE to 11.1.0.6 in
>>>>>> Oracle 11.2.0.1 and re-execute the SQL statement - note that this will
>>>>>> force a hard parse.  How does the performance compare?  If the
>>>>>> performance is better, set OPTIMIZER_FEATURES_ENABLE to 11.2.0.1, add
>>>>>> a couple extra spaces in the SQL statement, and try your test again.
>>>>>> Is the performance the same as it was on 11.1.0.6 and 10.2.0.4?
>>>>>> 
>>>>>> Charles Hooper
>>>>>> IT Manager/Oracle DBA
>>>>>> K&M Machine-Fabricating, Inc.


>>>>>>> On Tuesday, November 03, 2009 6:19 PM Mladen Gogala wrote:

>>>>>>> --------------------------------------------------------------------------------


>>>>>>>> On Tuesday, November 03, 2009 7:38 PM joel garry wrote:

>>>>>>>> r
>>>>>>>> 
>>>>>>>> Uh yeah, that is the ticket.  (OK, I admit, somewhere along the line I
>>>>>>>> bugeyed the 11.2.0.1 to 11.1...)
>>>>>>>> 
>>>>>>>> 
>>>>>>>> That may be informative.
>>>>>>>> 
>>>>>>>> jg
>>>>>>>> --
>>>>>>>> _at_home.com is bogus.
>>>>>>>> http://users.rcn.com/eslowry/inexcus.htm


>>>>>>>>> On Tuesday, November 03, 2009 8:46 PM lsllcm wrote:

>>>>>>>>> r
>>>>>>>>> 
>>>>>>>>> Thank you All at first.
>>>>>>>>> 
>>>>>>>>> I have done one quick test after set OPTIMIZER_FEATURES_ENABLE to
>>>>>>>>> 11.1.0.6 again, the performance is same as the value to 11.2.0.1.
>>>>>>>>> 
>>>>>>>>> I will double test it again and get 10053 trace file.
>>>>>>>>> 
>>>>>>>>> Thanks


>>>>>>>>>> On Wednesday, November 04, 2009 7:01 AM lsllcm wrote:

>>>>>>>>>> The trace file is too long, I use three parts.
>>>>>>>>>> 
>>>>>>>>>> Trace file /u01/app/oracle/diag/rdbms/dbs26/dbs26/trace/
>>>>>>>>>> dbs26_ora_29590.trc
>>>>>>>>>> Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
>>>>>>>>>> With the Partitioning, OLAP, Data Mining and Real Application Testing
>>>>>>>>>> options
>>>>>>>>>> ORACLE_HOME =3D /u01/app/oracle/product/1101/db
>>>>>>>>>> System name:	Linux
>>>>>>>>>> Node name:	localhost.localdomain
>>>>>>>>>> Release:	2.6.18-164.el5xen
>>>>>>>>>> Version:	#1 SMP Thu Sep 3 02:41:56 EDT 2009
>>>>>>>>>> Machine:	i686
>>>>>>>>>> Instance name: dbs26
>>>>>>>>>> Redo thread mounted by this instance: 1
>>>>>>>>>> Oracle process number: 21
>>>>>>>>>> Unix process pid: 29590, image: oracle_at_localhost.localdomain
>>>>>>>>>> 
>>>>>>>>>> 
>>>>>>>>>> *** 2009-11-04 12:27:11.627
>>>>>>>>>> *** SESSION ID:(91.2909) 2009-11-04 12:27:11.627
>>>>>>>>>> *** CLIENT ID:() 2009-11-04 12:27:11.627
>>>>>>>>>> *** SERVICE NAME:(dbs26) 2009-11-04 12:27:11.627
>>>>>>>>>> *** MODULE NAME:(SQL*Plus) 2009-11-04 12:27:11.627
>>>>>>>>>> *** ACTION NAME:() 2009-11-04 12:27:11.627
>>>>>>>>>> 
>>>>>>>>>> Registered qb: SEL$1 0x6d3604 (PARSER)
>>>>>>>>>> 
>>>>>>>>>> *** 2009-11-04 12:27:12.103
>>>>>>>>>> ---------------------
>>>>>>>>>> QUERY BLOCK SIGNATURE
>>>>>>>>>> ---------------------
>>>>>>>>>> signature (): qb_name=3DSEL$1 nbfros=3D4 flg=3D0
>>>>>>>>>> fro(0): flg=3D4 objn=3D73740 hint_alias=3D"F4"_at_"SEL$1"
>>>>>>>>>> fro(1): flg=3D4 objn=3D73746 hint_alias=3D"FINV"_at_"SEL$1"
>>>>>>>>>> fro(2): flg=3D4 objn=3D74784 hint_alias=3D"SD"_at_"SEL$1"
>>>>>>>>>> fro(3): flg=3D4 objn=3D74848 hint_alias=3D"XFI"_at_"SEL$1"
>>>>>>>>>> 
>>>>>>>>>> SPM: statement not found in SMB
>>>>>>>>>> 
>>>>>>>>>> **************************
>>>>>>>>>> Automatic degree of parallelism (ADOP)
>>>>>>>>>> **************************
>>>>>>>>>> Automatic degree of parallelism is disabled: Parameter.
>>>>>>>>>> 
>>>>>>>>>> PM: Considering predicate move-around in query block SEL$1 (#0)
>>>>>>>>>> **************************
>>>>>>>>>> Predicate Move-Around (PM)
>>>>>>>>>> **************************
>>>>>>>>>> OPTIMIZER INFORMATION
>>>>>>>>>> 
>>>>>>>>>> ******************************************
>>>>>>>>>> ----- Current SQL Statement for this session (sql_id=3D5n7ufx7tz1uks)
>>>>>>>>>> -----
>>>>>>>>>> SELECT xfi.serv_prov_code,
>>>>>>>>>> xfi.b1_per_id1,
>>>>>>>>>> xfi.b1_per_id2,
>>>>>>>>>> xfi.b1_per_id3,
>>>>>>>>>> xfi.feeitem_seq_nbr,
>>>>>>>>>> xfi.invoice_nbr,
>>>>>>>>>> xfi.gf_fee_period,
>>>>>>>>>> xfi.gf_fee,
>>>>>>>>>> xfi.gf_des,
>>>>>>>>>> xfi.gf_unit,
>>>>>>>>>> xfi.gf_udes,
>>>>>>>>>> finv.invoice_date AS gf_fee_apply_date,
>>>>>>>>>> xfi.feeitem_invoice_status,
>>>>>>>>>> xfi.gf_l1,
>>>>>>>>>> xfi.gf_l2,
>>>>>>>>>> xfi.gf_l3,
>>>>>>>>>> xfi.x4feeitem_invoice_udf1,
>>>>>>>>>> xfi.x4feeitem_invoice_udf2,
>>>>>>>>>> xfi.x4feeitem_invoice_udf3,
>>>>>>>>>> xfi.x4feeitem_invoice_udf4,
>>>>>>>>>> xfi.gf_fee_schedule,
>>>>>>>>>> xfi.fee_schedule_version,
>>>>>>>>>> xfi.rec_date,
>>>>>>>>>> xfi.rec_ful_nam,
>>>>>>>>>> xfi.rec_status,
>>>>>>>>>> f4.GF_COD,
>>>>>>>>>> f4.GF_PRIORITY
>>>>>>>>>> FROM x4feeitem_invoice xfi, setdetails sd, f4invoice finv, F4FEEITEM
>>>>>>>>>> f4
>>>>>>>>>> WHERE sd.serv_prov_code =3D xfi.serv_prov_code
>>>>>>>>>> AND sd.b1_per_id1 =3D xfi.b1_per_id1
>>>>>>>>>> AND sd.b1_per_id2 =3D xfi.b1_per_id2
>>>>>>>>>> AND sd.b1_per_id3 =3D xfi.b1_per_id3
>>>>>>>>>> AND xfi.serv_prov_code =3D f4.serv_prov_code
>>>>>>>>>> AND xfi.b1_per_id1 =3D f4.b1_per_id1
>>>>>>>>>> AND xfi.b1_per_id2 =3D f4.b1_per_id2
>>>>>>>>>> AND xfi.b1_per_id3 =3D f4.b1_per_id3
>>>>>>>>>> And xfi.feeitem_seq_nbr =3D f4.feeitem_seq_nbr
>>>>>>>>>> AND finv.serv_prov_code =3D xfi.serv_prov_code
>>>>>>>>>> AND finv.invoice_nbr =3D xfi.invoice_nbr
>>>>>>>>>> AND sd.serv_prov_code =3D 'SACRAMENTO'
>>>>>>>>>> AND upper(sd.set_id) =3D 'SET07'
>>>>>>>>>> AND xfi.rec_status =3D 'A'
>>>>>>>>>> AND xfi.feeitem_invoice_status =3D 'INVOICED'
>>>>>>>>>> ORDER BY gf_fee_apply_date


>>>>>>>>>>> On Wednesday, November 04, 2009 7:02 AM lsllcm wrote:

>>>>>>>>>>> part-2
>>>>>>>>>>> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
>>>>>>>>>>> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
>>>>>>>>>>> =3D=3D=3D
>>>>>>>>>>> 
>>>>>>>>>>> ***************
>>>>>>>>>>> Now joining: F4INVOICE[FINV]#1
>>>>>>>>>>> ***************
>>>>>>>>>>> NL Join
>>>>>>>>>>> Outer table: Card: 6.26  Cost: 2.00  Resp: 2.00  Degree: 1  Bytes:
>>>>>>>>>>> 169
>>>>>>>>>>> Access path analysis for F4INVOICE
>>>>>>>>>>> Inner table: F4INVOICE  Alias: FINV
>>>>>>>>>>> Access Path: TableScan
>>>>>>>>>>> NL Join:  Cost: 4788.36  Resp: 4788.36  Degree: 1
>>>>>>>>>>> Cost_io: 4770.00  Cost_cpu: 594128854
>>>>>>>>>>> Resp_io: 4770.00  Resp_cpu: 594128854
>>>>>>>>>>> kkofmx: index filter:"FINV"."SERV_PROV_CODE"=3D"XFI"."SERV_PROV_CODE"
>>>>>>>>>>> 
>>>>>>>>>>> OPTIMIZER PERCENT INDEX CACHING =3D 90
>>>>>>>>>>> Access Path: index (UniqueScan)
>>>>>>>>>>> Index: F4INVOICE_PK
>>>>>>>>>>> resc_io: 1.00  resc_cpu: 8461
>>>>>>>>>>> ix_sel: 0.000004  ix_sel_with_filters: 0.000004
>>>>>>>>>>> ***** Logdef predicate Adjustment ******
>>>>>>>>>>> Final IO cst 0.00 , CPU cst 0.00
>>>>>>>>>>> ***** End Logdef Adjustment ******
>>>>>>>>>>> NL Join : Cost: 3.00  Resp: 3.00  Degree: 1
>>>>>>>>>>> Cost_io: 3.00  Cost_cpu: 11030
>>>>>>>>>>> Resp_io: 3.00  Resp_cpu: 11030
>>>>>>>>>>> kkofmx: index filter:"FINV"."SERV_PROV_CODE"=3D"XFI"."SERV_PROV_CODE"
>>>>>>>>>>> 
>>>>>>>>>>> kkofmx: index filter:"FINV"."SERV_PROV_CODE"=3D"XFI"."SERV_PROV_CODE"
>>>>>>>>>>> 
>>>>>>>>>>> kkofmx: index filter:"FINV"."SERV_PROV_CODE"=3D"XFI"."SERV_PROV_CODE"
>>>>>>>>>>> 
>>>>>>>>>>> kkofmx: index filter:"FINV"."SERV_PROV_CODE"=3D"XFI"."SERV_PROV_CODE"
>>>>>>>>>>> 
>>>>>>>>>>> OPTIMIZER PERCENT INDEX CACHING =3D 90
>>>>>>>>>>> 
>>>>>>>>>>> 
>>>>>>>>>>> Access Path: index (RangeScan)
>>>>>>>>>>> Index: F4INVOICE_BATCH_DATE_IX
>>>>>>>>>>> resc_io: 3052.00  resc_cpu: 120046275
>>>>>>>>>>> ix_sel: 0.813255  ix_sel_with_filters: 0.813255
>>>>>>>>>>> ***** Logdef predicate Adjustment ******
>>>>>>>>>>> Final IO cst 0.00 , CPU cst 0.00
>>>>>>>>>>> ***** End Logdef Adjustment ******
>>>>>>>>>>> NL Join : Cost: 1835.43  Resp: 1835.43  Degree: 1
>>>>>>>>>>> Cost_io: 1833.20  Cost_cpu: 72033718
>>>>>>>>>>> Resp_io: 1833.20  Resp_cpu: 72033718
>>>>>>>>>>> OPTIMIZER PERCENT INDEX CACHING =3D 90
>>>>>>>>>>> 
>>>>>>>>>>> 
>>>>>>>>>>> Access Path: index (RangeScan)
>>>>>>>>>>> Index: F4INVOICE_BATCH_NBR_IX
>>>>>>>>>>> resc_io: 2454.00  resc_cpu: 114871354
>>>>>>>>>>> ix_sel: 0.813255  ix_sel_with_filters: 0.813255
>>>>>>>>>>> ***** Logdef predicate Adjustment ******
>>>>>>>>>>> Final IO cst 0.00 , CPU cst 0.00
>>>>>>>>>>> ***** End Logdef Adjustment ******
>>>>>>>>>>> NL Join : Cost: 1476.53  Resp: 1476.53  Degree: 1
>>>>>>>>>>> Cost_io: 1474.40  Cost_cpu: 68928765
>>>>>>>>>>> Resp_io: 1474.40  Resp_cpu: 68928765
>>>>>>>>>>> OPTIMIZER PERCENT INDEX CACHING =3D 90
>>>>>>>>>>> 
>>>>>>>>>>> 
>>>>>>>>>>> Access Path: index (RangeScan)
>>>>>>>>>>> Index: F4INVOICE_DATE_IX
>>>>>>>>>>> resc_io: 2999.00  resc_cpu: 119206769
>>>>>>>>>>> ix_sel: 0.813255  ix_sel_with_filters: 0.813255
>>>>>>>>>>> ***** Logdef predicate Adjustment ******
>>>>>>>>>>> Final IO cst 0.00 , CPU cst 0.00
>>>>>>>>>>> ***** End Logdef Adjustment ******
>>>>>>>>>>> NL Join : Cost: 1803.61  Resp: 1803.61  Degree: 1
>>>>>>>>>>> Cost_io: 1801.40  Cost_cpu: 71530014
>>>>>>>>>>> Resp_io: 1801.40  Resp_cpu: 71530014
>>>>>>>>>>> OPTIMIZER PERCENT INDEX CACHING =3D 90
>>>>>>>>>>> 
>>>>>>>>>>> 
>>>>>>>>>>> Access Path: index (AllEqUnique)
>>>>>>>>>>> Index: F4INVOICE_PK
>>>>>>>>>>> resc_io: 1.00  resc_cpu: 8461
>>>>>>>>>>> ix_sel: 0.000004  ix_sel_with_filters: 0.000004
>>>>>>>>>>> ***** Logdef predicate Adjustment ******
>>>>>>>>>>> Final IO cst 0.00 , CPU cst 0.00
>>>>>>>>>>> ***** End Logdef Adjustment ******
>>>>>>>>>>> NL Join : Cost: 3.00  Resp: 3.00  Degree: 1
>>>>>>>>>>> Cost_io: 3.00  Cost_cpu: 11030
>>>>>>>>>>> Resp_io: 3.00  Resp_cpu: 11030
>>>>>>>>>>> ****** trying bitmap/domain indexes ******
>>>>>>>>>>> ****** finished trying bitmap/domain indexes ******
>>>>>>>>>>> 
>>>>>>>>>>> Best NL cost: 3.00
>>>>>>>>>>> resc: 3.00  resc_io: 3.00  resc_cpu: 11030
>>>>>>>>>>> resp: 3.00  resp_io: 3.00  resc_cpu: 11030
>>>>>>>>>>> Join Card:  6.259212 =3D =3D outer (6.259204) * inner (200076.276865) *
>>>>>>>>>>> sel (0.000005)
>>>>>>>>>>> Join Card - Rounded: 6 Computed: 6.26
>>>>>>>>>>> Outer table:  X4FEEITEM_INVOICE  Alias: XFI


>>>>>>>>>>>> On Wednesday, November 04, 2009 7:04 AM lsllcm wrote:

>>>>>>>>>>>> part-5
>>>>>>>>>>>> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
>>>>>>>>>>>> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
>>>>>>>>>>>> 
>>>>>>>>>>>> ***************
>>>>>>>>>>>> Now joining: F4INVOICE[FINV]#1
>>>>>>>>>>>> ***************
>>>>>>>>>>>> NL Join
>>>>>>>>>>>> Outer table: Card: 1.06  Cost: 2.50  Resp: 2.50  Degree: 1  Bytes:
>>>>>>>>>>>> 300
>>>>>>>>>>>> Access path analysis for F4INVOICE
>>>>>>>>>>>> Inner table: F4INVOICE  Alias: FINV
>>>>>>>>>>>> Access Path: TableScan
>>>>>>>>>>>> NL Join:  Cost: 801.56  Resp: 801.56  Degree: 1
>>>>>>>>>>>> Cost_io: 798.00  Cost_cpu: 115210413
>>>>>>>>>>>> Resp_io: 798.00  Resp_cpu: 115210413
>>>>>>>>>>>> kkofmx: index filter:"FINV"."SERV_PROV_CODE"=3D"XFI"."SERV_PROV_CODE"
>>>>>>>>>>>> 
>>>>>>>>>>>> OPTIMIZER PERCENT INDEX CACHING =3D 90
>>>>>>>>>>>> Access Path: index (UniqueScan)
>>>>>>>>>>>> Index: F4INVOICE_PK
>>>>>>>>>>>> resc_io: 1.00  resc_cpu: 8461
>>>>>>>>>>>> ix_sel: 0.000004  ix_sel_with_filters: 0.000004
>>>>>>>>>>>> ***** Logdef predicate Adjustment ******
>>>>>>>>>>>> Final IO cst 0.00 , CPU cst 0.00
>>>>>>>>>>>> ***** End Logdef Adjustment ******
>>>>>>>>>>>> NL Join : Cost: 3.50  Resp: 3.50  Degree: 1
>>>>>>>>>>>> Cost_io: 3.00  Cost_cpu: 16190776
>>>>>>>>>>>> Resp_io: 3.00  Resp_cpu: 16190776
>>>>>>>>>>>> kkofmx: index filter:"FINV"."SERV_PROV_CODE"=3D"XFI"."SERV_PROV_CODE"
>>>>>>>>>>>> 
>>>>>>>>>>>> kkofmx: index filter:"FINV"."SERV_PROV_CODE"=3D"XFI"."SERV_PROV_CODE"
>>>>>>>>>>>> 
>>>>>>>>>>>> kkofmx: index filter:"FINV"."SERV_PROV_CODE"=3D"XFI"."SERV_PROV_CODE"
>>>>>>>>>>>> 
>>>>>>>>>>>> kkofmx: index filter:"FINV"."SERV_PROV_CODE"=3D"XFI"."SERV_PROV_CODE"
>>>>>>>>>>>> 
>>>>>>>>>>>> OPTIMIZER PERCENT INDEX CACHING =3D 90
>>>>>>>>>>>> 
>>>>>>>>>>>> 
>>>>>>>>>>>> Access Path: index (RangeScan)
>>>>>>>>>>>> Index: F4INVOICE_BATCH_DATE_IX
>>>>>>>>>>>> resc_io: 3052.00  resc_cpu: 120046275
>>>>>>>>>>>> ix_sel: 0.813255  ix_sel_with_filters: 0.813255
>>>>>>>>>>>> ***** Logdef predicate Adjustment ******
>>>>>>>>>>>> Final IO cst 0.00 , CPU cst 0.00
>>>>>>>>>>>> ***** End Logdef Adjustment ******
>>>>>>>>>>>> NL Join : Cost: 308.07  Resp: 308.07  Degree: 1
>>>>>>>>>>>> Cost_io: 307.20  Cost_cpu: 28194557
>>>>>>>>>>>> Resp_io: 307.20  Resp_cpu: 28194557
>>>>>>>>>>>> OPTIMIZER PERCENT INDEX CACHING =3D 90
>>>>>>>>>>>> 
>>>>>>>>>>>> 
>>>>>>>>>>>> Access Path: index (RangeScan)
>>>>>>>>>>>> Index: F4INVOICE_BATCH_NBR_IX
>>>>>>>>>>>> resc_io: 2454.00  resc_cpu: 114871354
>>>>>>>>>>>> ix_sel: 0.813255  ix_sel_with_filters: 0.813255
>>>>>>>>>>>> ***** Logdef predicate Adjustment ******
>>>>>>>>>>>> Final IO cst 0.00 , CPU cst 0.00
>>>>>>>>>>>> ***** End Logdef Adjustment ******
>>>>>>>>>>>> NL Join : Cost: 248.26  Resp: 248.26  Degree: 1
>>>>>>>>>>>> Cost_io: 247.40  Cost_cpu: 27677065
>>>>>>>>>>>> Resp_io: 247.40  Resp_cpu: 27677065
>>>>>>>>>>>> OPTIMIZER PERCENT INDEX CACHING =3D 90
>>>>>>>>>>>> 
>>>>>>>>>>>> 
>>>>>>>>>>>> Access Path: index (RangeScan)
>>>>>>>>>>>> Index: F4INVOICE_DATE_IX
>>>>>>>>>>>> resc_io: 2999.00  resc_cpu: 119206769
>>>>>>>>>>>> ix_sel: 0.813255  ix_sel_with_filters: 0.813255
>>>>>>>>>>>> ***** Logdef predicate Adjustment ******
>>>>>>>>>>>> Final IO cst 0.00 , CPU cst 0.00
>>>>>>>>>>>> ***** End Logdef Adjustment ******
>>>>>>>>>>>> NL Join : Cost: 302.77  Resp: 302.77  Degree: 1
>>>>>>>>>>>> Cost_io: 301.90  Cost_cpu: 28110607
>>>>>>>>>>>> Resp_io: 301.90  Resp_cpu: 28110607
>>>>>>>>>>>> OPTIMIZER PERCENT INDEX CACHING =3D 90
>>>>>>>>>>>> 
>>>>>>>>>>>> 
>>>>>>>>>>>> Access Path: index (AllEqUnique)
>>>>>>>>>>>> Index: F4INVOICE_PK
>>>>>>>>>>>> resc_io: 1.00  resc_cpu: 8461
>>>>>>>>>>>> ix_sel: 0.000004  ix_sel_with_filters: 0.000004
>>>>>>>>>>>> ***** Logdef predicate Adjustment ******
>>>>>>>>>>>> Final IO cst 0.00 , CPU cst 0.00
>>>>>>>>>>>> ***** End Logdef Adjustment ******
>>>>>>>>>>>> NL Join : Cost: 3.50  Resp: 3.50  Degree: 1
>>>>>>>>>>>> Cost_io: 3.00  Cost_cpu: 16190776
>>>>>>>>>>>> Resp_io: 3.00  Resp_cpu: 16190776
>>>>>>>>>>>> ****** trying bitmap/domain indexes ******
>>>>>>>>>>>> ****** finished trying bitmap/domain indexes ******
>>>>>>>>>>>> 
>>>>>>>>>>>> Best NL cost: 3.50
>>>>>>>>>>>> resc: 3.50  resc_io: 3.00  resc_cpu: 16190776
>>>>>>>>>>>> resp: 3.50  resp_io: 3.00  resc_cpu: 16190776
>>>>>>>>>>>> Join Card:  1.061187 =3D =3D outer (1.061185) * inner (200076.276865) *
>>>>>>>>>>>> sel (0.000005)
>>>>>>>>>>>> Join Card - Rounded: 1 Computed: 1.06
>>>>>>>>>>>> Outer table:  SETDETAILS  Alias: SD
>>>>>>>>>>>> resc: 3044.54  card 90650.35  bytes: 169  deg: 1  resp: 3044.54


>>>>>>>>>>>>> On Wednesday, November 04, 2009 7:04 AM lsllcm wrote:

>>>>>>>>>>>>> part6-
>>>>>>>>>>>>> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
>>>>>>>>>>>>> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
>>>>>>>>>>>>> =3D=3D=3D=3D=3D
>>>>>>>>>>>>> ***************
>>>>>>>>>>>>> Now joining: SETDETAILS[SD]#0
>>>>>>>>>>>>> ***************
>>>>>>>>>>>>> NL Join
>>>>>>>>>>>>> Outer table: Card: 1187146.28  Cost: 18174.63  Resp: 18174.63
>>>>>>>>>>>>> Degree: 1  Bytes: 173
>>>>>>>>>>>>> Access path analysis for SETDETAILS
>>>>>>>>>>>>> Inner table: SETDETAILS  Alias: SD
>>>>>>>>>>>>> Access Path: TableScan
>>>>>>>>>>>>> NL Join:  Cost: 1179682709.66  Resp: 1179682709.66  Degree: 1
>>>>>>>>>>>>> Cost_io: 1172275359.70  Cost_cpu: 239647535518960
>>>>>>>>>>>>> Resp_io: 1172275359.70  Resp_cpu: 239647535518960
>>>>>>>>>>>>> kkofmx: index filter:"SD"."SERV_PROV_CODE"=3D"XFI"."SERV_PROV_CODE"
>>>>>>>>>>>>> 
>>>>>>>>>>>>> kkofmx: index filter:"SD"."SERV_PROV_CODE"=3D"XFI"."SERV_PROV_CODE"
>>>>>>>>>>>>> 
>>>>>>>>>>>>> kkofmx: index filter:"SD"."B1_PER_ID1"=3D"XFI"."B1_PER_ID1"
>>>>>>>>>>>>> 
>>>>>>>>>>>>> kkofmx: index filter:"SD"."B1_PER_ID2"=3D"XFI"."B1_PER_ID2"
>>>>>>>>>>>>> 
>>>>>>>>>>>>> kkofmx: index filter:"SD"."B1_PER_ID3"=3D"XFI"."B1_PER_ID3"
>>>>>>>>>>>>> 
>>>>>>>>>>>>> kkofmx: index filter:"SD"."SERV_PROV_CODE"=3D"XFI"."SERV_PROV_CODE"
>>>>>>>>>>>>> 
>>>>>>>>>>>>> kkofmx: index filter:"SD"."SERV_PROV_CODE"=3D"XFI"."SERV_PROV_CODE"
>>>>>>>>>>>>> 
>>>>>>>>>>>>> kkofmx: index filter:"SD"."SERV_PROV_CODE"=3D"XFI"."SERV_PROV_CODE"
>>>>>>>>>>>>> 
>>>>>>>>>>>>> kkofmx: index filter:"SD"."SERV_PROV_CODE"=3D"XFI"."SERV_PROV_CODE"
>>>>>>>>>>>>> 
>>>>>>>>>>>>> kkofmx: index filter:"SD"."SERV_PROV_CODE"=3D"XFI"."SERV_PROV_CODE"
>>>>>>>>>>>>> 
>>>>>>>>>>>>> OPTIMIZER PERCENT INDEX CACHING =3D 90
>>>>>>>>>>>>> 
>>>>>>>>>>>>> 
>>>>>>>>>>>>> Access Path: index (RangeScan)
>>>>>>>>>>>>> Index: SETDETAILS_ADDR_IX
>>>>>>>>>>>>> resc_io: 3823.00  resc_cpu: 261762790
>>>>>>>>>>>>> ix_sel: 0.999999  ix_sel_with_filters: 0.999999
>>>>>>>>>>>>> ***** Logdef predicate Adjustment ******
>>>>>>>>>>>>> Final IO cst 0.00 , CPU cst 0.00
>>>>>>>>>>>>> ***** End Logdef Adjustment ******
>>>>>>>>>>>>> NL Join : Cost: 454824600.54  Resp: 454824600.54  Degree: 1
>>>>>>>>>>>>> Cost_io: 453864063.50  Cost_cpu: 31075936080355
>>>>>>>>>>>>> Resp_io: 453864063.50  Resp_cpu: 31075936080355
>>>>>>>>>>>>> OPTIMIZER PERCENT INDEX CACHING =3D 90
>>>>>>>>>>>>> 
>>>>>>>>>>>>> 
>>>>>>>>>>>>> Access Path: index (RangeScan)
>>>>>>>>>>>>> Index: SETDETAILS_IX
>>>>>>>>>>>>> resc_io: 433.00  resc_cpu: 81263079
>>>>>>>>>>>>> ix_sel: 0.999999  ix_sel_with_filters: 0.000001
>>>>>>>>>>>>> ***** Logdef predicate Adjustment ******
>>>>>>>>>>>>> Final IO cst 0.00 , CPU cst 0.00
>>>>>>>>>>>>> ***** End Logdef Adjustment ******
>>>>>>>>>>>>> ***** Logdef predicate Adjustment ******
>>>>>>>>>>>>> Final IO cst 0.00 , CPU cst 0.00
>>>>>>>>>>>>> ***** End Logdef Adjustment ******
>>>>>>>>>>>>> ***** Logdef predicate Adjustment ******
>>>>>>>>>>>>> Final IO cst 0.00 , CPU cst 0.00
>>>>>>>>>>>>> ***** End Logdef Adjustment ******
>>>>>>>>>>>>> ***** Logdef predicate Adjustment ******
>>>>>>>>>>>>> Final IO cst 0.00 , CPU cst 0.00
>>>>>>>>>>>>> ***** End Logdef Adjustment ******
>>>>>>>>>>>>> NL Join : Cost: 51719782.47  Resp: 51719782.47  Degree: 1
>>>>>>>>>>>>> Cost_io: 51421569.50  Cost_cpu: 9647985131049
>>>>>>>>>>>>> Resp_io: 51421569.50  Resp_cpu: 9647985131049
>>>>>>>>>>>>> OPTIMIZER PERCENT INDEX CACHING =3D 90
>>>>>>>>>>>>> 
>>>>>>>>>>>>> 
>>>>>>>>>>>>> Access Path: index (AllEqJoinGuess)
>>>>>>>>>>>>> Index: SETDETAILS_PID_IX
>>>>>>>>>>>>> resc_io: 1.00  resc_cpu: 8467
>>>>>>>>>>>>> ix_sel: 0.000001  ix_sel_with_filters: 0.000001
>>>>>>>>>>>>> ***** Logdef predicate Adjustment ******
>>>>>>>>>>>>> Final IO cst 0.00 , CPU cst 0.00
>>>>>>>>>>>>> ***** End Logdef Adjustment ******
>>>>>>>>>>>>> NL Join : Cost: 136920.30  Resp: 136920.30  Degree: 1
>>>>>>>>>>>>> Cost_io: 136862.30  Cost_cpu: 1876359817
>>>>>>>>>>>>> Resp_io: 136862.30  Resp_cpu: 1876359817
>>>>>>>>>>>>> OPTIMIZER PERCENT INDEX CACHING =3D 90
>>>>>>>>>>>>> 
>>>>>>>>>>>>> 
>>>>>>>>>>>>> Access Path: index (RangeScan)
>>>>>>>>>>>>> Index: SETDETAILS_PK
>>>>>>>>>>>>> resc_io: 354384.00  resc_cpu: 2758261918
>>>>>>>>>>>>> ix_sel: 0.999999  ix_sel_with_filters: 0.999999
>>>>>>>>>>>>> ***** Logdef predicate Adjustment ******
>>>>>>>>>>>>> Final IO cst 0.00 , CPU cst 0.00
>>>>>>>>>>>>> ***** End Logdef Adjustment ******
>>>>>>>>>>>>> NL Join : Cost: 42080694123.37  Resp: 42080694123.37  Degree: 1
>>>>>>>>>>>>> Cost_io: 42070572954.10  Cost_cpu: 327446831442229
>>>>>>>>>>>>> Resp_io: 42070572954.10  Resp_cpu: 327446831442229
>>>>>>>>>>>>> OPTIMIZER PERCENT INDEX CACHING =3D 90


>>>>>>>>>>>>>> On Wednesday, November 04, 2009 11:11 AM Randolf Geist wrote:

>>>>>>>>>>>>>> This might be caused by some code change / bug introduced in 11.2.0.1,
>>>>>>>>>>>>>> but since Charles already asked you to run the same with the optimizer
>>>>>>>>>>>>>> features reverted to 11.1.0.6 and you confirmed that the problem still
>>>>>>>>>>>>>> persists with this setting, there are some other possibilities:
>>>>>>>>>>>>>> 
>>>>>>>>>>>>>> 1. Looking at the 10053 optimizer trace file, we can see that the
>>>>>>>>>>>>>> optimizer actually estimates that this statement is going to generate
>>>>>>>>>>>>>> millions of rows, so this is already way off from the 0 rows that it
>>>>>>>>>>>>>> actually produces. So the starting point of the whole calculation is
>>>>>>>>>>>>>> already totally wrong, since otherwise the optimizer would recognize
>>>>>>>>>>>>>> that using the FIRST_ROWS_n mode will not change the outcome of the
>>>>>>>>>>>>>> query. Looking at the 3 consistent gets with the good plan, it looks
>>>>>>>>>>>>>> like the whole execution already stops after the first index / table
>>>>>>>>>>>>>> access to SD, otherwise we probably would see more consistent gets. So
>>>>>>>>>>>>>> the runtime engine can take a shortcut here with the NESTED LOOP joins
>>>>>>>>>>>>>> if one the driving row sources generates 0 rows.
>>>>>>>>>>>>>> 
>>>>>>>>>>>>>> 2. You're using the FIRST_ROWS_100 optimizer mode together with very
>>>>>>>>>>>>>> aggressive OPTIMIZER_INDEX_COST_ADJ and OPTIMIZER_INDEX_CACHING
>>>>>>>>>>>>>> settings which will lower cost of index access paths dramatically, in
>>>>>>>>>>>>>> particular when performing NESTED LOOP joins. This might lead to
>>>>>>>>>>>>>> multiple optimizer choices with similar costs, due to the lowering of
>>>>>>>>>>>>>> the cost. Using the FIRST_ROWS_n modes will introduce additional re-
>>>>>>>>>>>>>> costing steps by the optimizer for ORDER BY access. This is very
>>>>>>>>>>>>>> likely the reason why you do not see the effect when omitting the ORDER
>>>>>>>>>>>>>> BY.
>>>>>>>>>>>>>> 
>>>>>>>>>>>>>> 3. You say that you have migrated to 11.2.0.1, but we do not know if the
>>>>>>>>>>>>>> remaining environment in terms of optimizer settings and object
>>>>>>>>>>>>>> statistics is the same as in the 10.2 and 11.1 databases you have used
>>>>>>>>>>>>>> to show the "correct" plan chosen. May be the 11.2 database has re-
>>>>>>>>>>>>>> gathered table/index statistics and with those modified statistics the
>>>>>>>>>>>>>> 11.1 and 10.2 database might come to the same "bad" execution plan
>>>>>>>>>>>>>> 
>>>>>>>>>>>>>> 4. As pointed out by Charles already, you need to be very careful with
>>>>>>>>>>>>>> testing when bind variables are involved, in order to make sure that
>>>>>>>>>>>>>> you do not share an execution plan from previous executions available
>>>>>>>>>>>>>> in the Shared Pool. This is obviously a general issue, and only
>>>>>>>>>>>>>> partially addressed by the new Adaptive Cursor Sharing introduced with
>>>>>>>>>>>>>> 11.1
>>>>>>>>>>>>>> 
>>>>>>>>>>>>>> What you could try:
>>>>>>>>>>>>>> 
>>>>>>>>>>>>>> 1. What cost / plan do you get if you request to use the index
>>>>>>>>>>>>>> SETDETAILS_SETID_IX?
>>>>>>>>>>>>>> 
>>>>>>>>>>>>>> SELECT /*+ INDEX(SD, SETDETAILS_SETID_IX) */ ...
>>>>>>>>>>>>>> 
>>>>>>>>>>>>>> 2. What plan do you get in 11.2 if you are setting
>>>>>>>>>>>>>> OPTIMIZER_INDEX_COST_ADJ and OPTIMIZER_INDEX_CACHING to default
>>>>>>>>>>>>>> values:
>>>>>>>>>>>>>> 
>>>>>>>>>>>>>> ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ =3D 100;
>>>>>>>>>>>>>> ALTER SESSION SET OPTIMIZER_INDEX_CACHING =3D 0;
>>>>>>>>>>>>>> 
>>>>>>>>>>>>>> 3. What plan do you get in your 10.2 and 11.1 databases if you use the
>>>>>>>>>>>>>> same environment as in 11.2? In particular the optimizer settings and
>>>>>>>>>>>>>> object statistics should be the same. You can either try to export /
>>>>>>>>>>>>>> import the object statistics manually or (I am not sure if it requires
>>>>>>>>>>>>>> an additional license cost, but if your license allows for it) you can
>>>>>>>>>>>>>> very simply create a test case using the SQL Test Case Builder in 11.2
>>>>>>>>>>>>>> and try to import this into the 11.1 and 10.2 databases to create a
>>>>>>>>>>>>>> similar environment, however I am not sure if this will work due to
>>>>>>>>>>>>>> potential compatibility issues in the lower version environments.
>>>>>>>>>>>>>> 
>>>>>>>>>>>>>> Use the DBMS_SQLDIAG.EXPORT_SQL_TESTCASE / IMPORT_SQL_TESTCASE to do
>>>>>>>>>>>>>> so.
>>>>>>>>>>>>>> 
>>>>>>>>>>>>>> See the manuals or e.g. http://optimizermagic.blogspot.com/2008/03/oracle-s=
>>>>>>>>>>>>>> upport-keeps-closing-my-tar.html
>>>>>>>>>>>>>> for more information.
>>>>>>>>>>>>>> 
>>>>>>>>>>>>>> 4. A general question: Is your application actually using the
>>>>>>>>>>>>>> FIRST_ROWS_100 optimizer mode properly? Which means, do you always
>>>>>>>>>>>>>> fetch only a few rows from a larger result set? Does your application
>>>>>>>>>>>>>> show only the top N ordered results of a search result in "pagination"
>>>>>>>>>>>>>> style like Google search? Only then the FIRST_ROWS_100 mode is
>>>>>>>>>>>>>> reasonable, otherwise you should actually use the ALL_ROWS mode. Given
>>>>>>>>>>>>>> your aggressive OPTIMIZER_INDEX_COST_ADJ and OPTIMIZER_INDEX_CACHING
>>>>>>>>>>>>>> the optimizer would still favor index access / nested loop operations
>>>>>>>>>>>>>> quite a lot even in ALL_ROWS mode.
>>>>>>>>>>>>>> 
>>>>>>>>>>>>>> Regards,
>>>>>>>>>>>>>> Randolf
>>>>>>>>>>>>>> 
>>>>>>>>>>>>>> Oracle related stuff blog:
>>>>>>>>>>>>>> http://oracle-randolf.blogspot.com/
>>>>>>>>>>>>>> 
>>>>>>>>>>>>>> SQLTools++ for Oracle (Open source Oracle GUI for Windows):
>>>>>>>>>>>>>> http://www.sqltools-plusplus.org:7676/
>>>>>>>>>>>>>> http://sourceforge.net/projects/sqlt-pp/


>>>>>>>>>>>>>>> On Saturday, November 07, 2009 4:31 AM Jonathan Lewis wrote:

>>>>>>>>>>>>>>> Sorry about picking this one up so late.
>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>> I have 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.
>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>> When you do not 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


>>>>>>>>>>>>>>>> On Saturday, November 07, 2009 9:13 AM lsllcm wrote:

>>>>>>>>>>>>>>>> Thanks Charles and Randolf for your comments:
>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>> 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
>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>> 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


>>>>>>>>>>>>>>>>> On Saturday, November 07, 2009 9:53 AM lsllcm wrote:

>>>>>>>>>>>>>>>>> wrote:
>>>>>>>>>>>>>>>>> s_100
>>>>>>>>>>>>>>>>> book/ind_book.html
>>>>>>>>>>>>>>>>> aq.html
>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>> Thanks Jonathan,


>>>>>>>>>>>>>>>>>> On Saturday, November 07, 2009 2:19 PM Randolf Geist wrote:

>>>>>>>>>>>>>>>>>> wrote:
>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>> Jonathan,
>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>> that was one of my first thoughts as well but I was a bit reluctant to
>>>>>>>>>>>>>>>>>> draw this conclusion because - probably due to the "dynamic proration"
>>>>>>>>>>>>>>>>>> feature - there are more ALL_ROWS plans further down the trace file,
>>>>>>>>>>>>>>>>>> leading e.g. to this one:
>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>> First K Rows: K =3D 100.00, N =3D 73255.00
>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>> which makes the sort operation very likely look much cheaper, so I
>>>>>>>>>>>>>>>>>> was not entirely sure that this is the root cause of the problem, given
>>>>>>>>>>>>>>>>>> that we obviously do not see the complete trace file.
>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>> Regards,
>>>>>>>>>>>>>>>>>> Randolf
>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>> Oracle related stuff blog:
>>>>>>>>>>>>>>>>>> http://oracle-randolf.blogspot.com/
>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>> SQLTools++ for Oracle (Open source Oracle GUI for Windows):
>>>>>>>>>>>>>>>>>> http://www.sqltools-plusplus.org:7676/
>>>>>>>>>>>>>>>>>> http://sourceforge.net/projects/sqlt-pp/


>>>>>>>>>>>>>>>>>>> On Saturday, November 07, 2009 8:52 PM lsllcm wrote:

>>>>>>>>>>>>>>>>>>> tools-plusplus.org:7676/http://sourceforge.net/projects/sqlt-pp/
>>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>>> Hi Randolf,
>>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>>> The trace file is too long, I have saperated into 6 parts, to see
>>>>>>>>>>>>>>>>>>> whole information of each part, please click read more link in the
>>>>>>>>>>>>>>>>>>> left bottom.


>>>>>>>>>>>>>>>>>>>> On Saturday, November 07, 2009 8:54 PM lsllcm wrote:

>>>>>>>>>>>>>>>>>>>> tools-plusplus.org:7676/http://sourceforge.net/projects/sqlt-pp/
>>>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>>>> Hi Randolf,
>>>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>>>> The trace file is too long, I have saperated into 6 parts, to see
>>>>>>>>>>>>>>>>>>>> whole information of each part, please click read more link in the
>>>>>>>>>>>>>>>>>>>> left bottom.
>>>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>>>> Thanks
>>>>>>>>>>>>>>>>>>>> lsllcm


>>>>>>>>>>>>>>>>>>>>> On Sunday, November 08, 2009 4:43 AM Randolf Geist wrote:

>>>>>>>>>>>>>>>>>>>>> Well, if you check your posts you might realize that "part 4" is
>>>>>>>>>>>>>>>>>>>>> missing. So either you managed to post everything in 5 parts, or one
>>>>>>>>>>>>>>>>>>>>> part is missing. The numbering suggests the latter, that is why I and
>>>>>>>>>>>>>>>>>>>>> Charles mentioned that probably a part of the trace file is not
>>>>>>>>>>>>>>>>>>>>> available. I am aware of the "read more->download" procedure, but it
>>>>>>>>>>>>>>>>>>>>> does not help if part 4 has not been posted.
>>>>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>>>>> Regards,
>>>>>>>>>>>>>>>>>>>>> Randolf
>>>>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>>>>> Oracle related stuff blog:
>>>>>>>>>>>>>>>>>>>>> http://oracle-randolf.blogspot.com/
>>>>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>>>>> SQLTools++ for Oracle (Open source Oracle GUI for Windows):
>>>>>>>>>>>>>>>>>>>>> http://www.sqltools-plusplus.org:7676/
>>>>>>>>>>>>>>>>>>>>> http://sourceforge.net/projects/sqlt-pp/


>>>>>>>>>>>>>>>>>>>>>> On Sunday, November 08, 2009 8:36 AM Charles Hooper wrote:

>>>>>>>>>>>>>>>>>>>>>> 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.
>>>>>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>>>>>> 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)
>>>>>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>>>>>> 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)
>>>>>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>>>>>> 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 =3D 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.
>>>>>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>>>>>> 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).
>>>>>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>>>>>> 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=
>>>>>>>>>>>>>>>>>>>>>> =AD
>>>>>>>>>>>>>>>>>>>>>> 028538760589558632766878171540458953514382464234321326889464182768467546703=
>>>>>>>>>>>>>>>>>>>>>> =AD
>>>>>>>>>>>>>>>>>>>>>> 537516986049910576551282076245490090389328944075868508455133942304583236903=
>>>>>>>>>>>>>>>>>>>>>> =AD
>>>>>>>>>>>>>>>>>>>>>> 222948165808559332123348274797826204144723168738177180919299881250404026184=
>>>>>>>>>>>>>>>>>>>>>> =AD
>>>>>>>>>>>>>>>>>>>>>> 124858368.00


>>>>>>>>>>>>>>>>>>>>>>> On Sunday, November 08, 2009 10:06 AM lsllcm wrote:

>>>>>>>>>>>>>>>>>>>>>>> tools-plusplus.org:7676/http://sourceforge.net/projects/sqlt-pp/
>>>>>>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>>>>>>> Hi Randolf,
>>>>>>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>>>>>>> Sorry for the confusion, the number is not correct. There are total 5
>>>>>>>>>>>>>>>>>>>>>>> parts. The parts are 1,2,3,5,6. The part 5 should be part 4. The part
>>>>>>>>>>>>>>>>>>>>>>> 6 should be part 5.
>>>>>>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>>>>>>> Thanks
>>>>>>>>>>>>>>>>>>>>>>> lsllcm


>>>>>>>>>>>>>>>>>>>>>>>> On Sunday, November 08, 2009 10:24 AM lsllcm wrote:

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


>>>>>>>>>>>>>>>>>>>>>>>>> On Sunday, November 08, 2009 11:15 AM lsllcm wrote:

>>>>>>>>>>>>>>>>>>>>>>>>> 80=AD=AD


>>>>>>>>>>>>>>>>>>>>>>>>>> On Sunday, November 08, 2009 11:20 AM lsllcm wrote:

>>>>>>>>>>>>>>>>>>>>>>>>>> 80=AD=AD


>>>>>>>>>>>>>>>>>>>>>>>>>>> On Sunday, November 08, 2009 11:22 AM Randolf Geist wrote:

>>>>>>>>>>>>>>>>>>>>>>>>>>> Charles, it depends on the configuration of DBMS_STATS via SET_PARAM
>>>>>>>>>>>>>>>>>>>>>>>>>>> resp. SET_*_PREFS in 11g, but the unmodified default settings will
>>>>>>>>>>>>>>>>>>>>>>>>>>> gather statistics on hidden columns.
>>>>>>>>>>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>>>>>>>>>>> Obviously this is a virtual column belonging to the function-based
>>>>>>>>>>>>>>>>>>>>>>>>>>> index SETDETAILS_SETID_IX on SETDETAILS  which covers at least
>>>>>>>>>>>>>>>>>>>>>>>>>>> serv_prov_code and upper(sd.set_id).
>>>>>>>>>>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>>>>>>>>>>> I think the strange output in the 10053 trace is a known oddity
>>>>>>>>>>>>>>>>>>>>>>>>>>> introduced with 11.1 for virtual columns. It does not seem to harm the
>>>>>>>>>>>>>>>>>>>>>>>>>>> actual calculation however.
>>>>>>>>>>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>>>>>>>>>>> To the OP: How many distinct values of "upper(sd.set_id)" are there?
>>>>>>>>>>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>>>>>>>>>>> Regards,
>>>>>>>>>>>>>>>>>>>>>>>>>>> Randolf
>>>>>>>>>>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>>>>>>>>>>> Oracle related stuff blog:
>>>>>>>>>>>>>>>>>>>>>>>>>>> http://oracle-randolf.blogspot.com/
>>>>>>>>>>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>>>>>>>>>>> SQLTools++ for Oracle (Open source Oracle GUI for Windows):
>>>>>>>>>>>>>>>>>>>>>>>>>>> http://www.sqltools-plusplus.org:7676/
>>>>>>>>>>>>>>>>>>>>>>>>>>> http://sourceforge.net/projects/sqlt-pp/


>>>>>>>>>>>>>>>>>>>>>>>>>>>> On Sunday, November 08, 2009 3:29 PM Randolf Geist wrote:

>>>>>>>>>>>>>>>>>>>>>>>>>>>> First, could you please always include the "Predicate Information"
>>>>>>>>>>>>>>>>>>>>>>>>>>>> section below the plan, it often contains very important information,
>>>>>>>>>>>>>>>>>>>>>>>>>>>> and without it it is sometimes really hard to understand what is going.
>>>>>>>>>>>>>>>>>>>>>>>>>>>> The 11.2 plan seems to come from the end of the 10053 trace file and
>>>>>>>>>>>>>>>>>>>>>>>>>>>> not from the DBMS_XPLAN output, by the way.
>>>>>>>>>>>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>>>>>>>>>>>> Second, since 11.2 again attempts to avoid the SORT operation, and
>>>>>>>>>>>>>>>>>>>>>>>>>>>> comes up with a plan having a cost of 10K, you might again have the
>>>>>>>>>>>>>>>>>>>>>>>>>>>> issue that 11.2 overestimates the cardinality and therefore thinks the
>>>>>>>>>>>>>>>>>>>>>>>>>>>> sort is much more costly than it effectively is.
>>>>>>>>>>>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>>>>>>>>>>>> You can again search for that "First K Rows: K =3D 100.00" in the 10053
>>>>>>>>>>>>>>>>>>>>>>>>>>>> trace file to get an impression of the estimated ALL_ROWS cardinality,
>>>>>>>>>>>>>>>>>>>>>>>>>>>> or run the statement with ALL_ROWS mode and without the outer "ROWNUM
>>>>>>>>>>>>>>>>>>>>>>>>>>>> < 101" query to see the estimated ALL_ROWS cardinality.
>>>>>>>>>>>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>>>>>>>>>>>> The mode ALL_ROWS or FIRST_ROWS_100 OPTIMIZER_MODE does not matter here
>>>>>>>>>>>>>>>>>>>>>>>>>>>> because the "ROWNUM < 101" attribute will enable the FIRST_ROWS_n mode
>>>>>>>>>>>>>>>>>>>>>>>>>>>> anyway regardless of the OPTIMIZER_MODE used in the session
>>>>>>>>>>>>>>>>>>>>>>>>>>>> (controlled by the underscore parameter
>>>>>>>>>>>>>>>>>>>>>>>>>>>> "_optimizer_rownum_pred_based_fkr" which defaults to true).
>>>>>>>>>>>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>>>>>>>>>>>> I think the bottom line is you need to understand why 11.2
>>>>>>>>>>>>>>>>>>>>>>>>>>>> overestimates the cardinality - and you should get try to confirm that
>>>>>>>>>>>>>>>>>>>>>>>>>>>> this is different from your 10.2 and 11.1 systems, which means that I
>>>>>>>>>>>>>>>>>>>>>>>>>>>> expect those to come up with a cardinality estimate closer to reality.
>>>>>>>>>>>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>>>>>>>>>>>> This may not be an issue about the general optimizer settings, but
>>>>>>>>>>>>>>>>>>>>>>>>>>>> more about the differing object statistics or different calculations
>>>>>>>>>>>>>>>>>>>>>>>>>>>> performed by 11.2 using these object statistics.
>>>>>>>>>>>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>>>>>>>>>>>> Once the cardinality estimates are closer to the actual ones, 11.2
>>>>>>>>>>>>>>>>>>>>>>>>>>>> very likely will come up with better performing plans.
>>>>>>>>>>>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>>>>>>>>>>>> Regards,
>>>>>>>>>>>>>>>>>>>>>>>>>>>> Randolf
>>>>>>>>>>>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>>>>>>>>>>>> Oracle related stuff blog:
>>>>>>>>>>>>>>>>>>>>>>>>>>>> http://oracle-randolf.blogspot.com/
>>>>>>>>>>>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>>>>>>>>>>>> SQLTools++ for Oracle (Open source Oracle GUI for Windows):
>>>>>>>>>>>>>>>>>>>>>>>>>>>> http://www.sqltools-plusplus.org:7676/
>>>>>>>>>>>>>>>>>>>>>>>>>>>> http://sourceforge.net/projects/sqlt-pp/


>>>>>>>>>>>>>>>>>>>>>>>>>>>>> On Monday, November 09, 2009 9:38 AM Charles Hooper wrote:

>>>>>>>>>>>>>>>>>>>>>>>>>>>>> 


>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> On Tuesday, November 10, 2009 10:22 AM lsllcm wrote:

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


>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> On Wednesday, November 18, 2009 5:26 AM lsllcm 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
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> 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


>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> On Wednesday, November 18, 2009 7:53 AM Charles Hooper wrote:

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> 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=3DB and B=3DC, then A=3DC
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> If A=3DB and A=3DC, then B=3DC
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> 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.


>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> On Friday, November 20, 2009 9:24 AM lsllcm wrote:

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> On 11=E6=9C=8818=E6=97=A5, =E4=B8=8B=E5=8D=888=E6=97=B653=E5=88=86, Charles=
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> D3
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> o
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> =A8=E6=96=87=E5=AD=97 -
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> Thanks Charles,
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> I have created one new thread
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> http://groups.google.com.tw/group/comp.databases.oracle.server/browse_threa=
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> d/thread/6aac0570974c9d62#
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> I will put related information at that thread.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> 
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> Thanks
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> lsllcm


>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> Submitted via EggHeadCafe 
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> Excel Tips: Net Present Value (NPV) and Internal Rate of Return (IRR)
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> http://www.eggheadcafe.com/tutorials/aspnet/e426181a-90f1-4bdb-a58a-ef76c7e7cc85/excel-tips-net-present-value-npv-and-internal-rate-of-return-irr.aspx
Received on Tue Nov 23 2010 - 15:00:30 CST

Original text of this message