From: lsllcm on
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
31 AND sd.b1_per_id2 = xfi.b1_per_id2
32 AND sd.b1_per_id3 = xfi.b1_per_id3
33 AND xfi.serv_prov_code = f4.serv_prov_code
34 AND xfi.b1_per_id1 = f4.b1_per_id1
35 AND xfi.b1_per_id2 = f4.b1_per_id2
36 AND xfi.b1_per_id3 = f4.b1_per_id3
37 And xfi.feeitem_seq_nbr = f4.feeitem_seq_nbr
38 AND finv.serv_prov_code = xfi.serv_prov_code
39 AND finv.invoice_nbr = xfi.invoice_nbr
40 AND sd.serv_prov_code = :spc
41 AND upper(sd.set_id) = :p1
42 AND xfi.rec_status = 'A'
43 AND xfi.feeitem_invoice_status = 'INVOICED'
44 ORDER BY gf_fee_apply_date
45 /

no rows selected

Elapsed: 00:06:07.08
SQL> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR
(NULL,NULL,'ALLSTATS LAST'

Plan hash value: 1519767420

-----------------------------------------------------------------------------
| Id | Operation | Name | E-
Rows |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| |
| 1 | NESTED LOOPS |
| |
| 2 | NESTED LOOPS |
| 117 |
| 3 | NESTED LOOPS |
| 1 |
| 4 | NESTED LOOPS |
| 15 |
| 5 | TABLE ACCESS BY INDEX ROWID| F4INVOICE
| 248K|
|* 6 | INDEX RANGE SCAN | F4INVOICE_DATE_IX
| 3 |
|* 7 | TABLE ACCESS BY INDEX ROWID| X4FEEITEM_INVOICE
| 5 |
|* 8 | INDEX RANGE SCAN | X4FEEITEM_INVOICE_NBR_IX
| 5 |
|* 9 | TABLE ACCESS BY INDEX ROWID | SETDETAILS
| 1 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 10 | INDEX RANGE SCAN | SETDETAILS_SETID_IX
| 24 |
|* 11 | INDEX UNIQUE SCAN | F4FEEITEM_PK
| 1 |
| 12 | TABLE ACCESS BY INDEX ROWID | F4FEEITEM
| 101 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

6 - access("FINV"."SERV_PROV_CODE"=:SPC)
7 - filter(("XFI"."FEEITEM_INVOICE_STATUS"='INVOICED' AND
"XFI"."REC_STATUS"='A'))

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
8 - access("XFI"."SERV_PROV_CODE"=:SPC AND
"FINV"."INVOICE_NBR"="XFI"."INVOICE_NBR")
9 - filter(("SD"."B1_PER_ID1"="XFI"."B1_PER_ID1" AND
"SD"."B1_PER_ID2"="XFI"."B1_PER_ID2" AND
"SD"."B1_PER_ID3"="XFI"."B1_PER_ID3"))
10 - access("SD"."SERV_PROV_CODE"=:SPC AND "SD"."SYS_NC00017$"=:P1)
11 - access("F4"."SERV_PROV_CODE"=:SPC AND
"XFI"."B1_PER_ID1"="F4"."B1_PER_ID1" AND
"XFI"."B1_PER_ID2"="F4"."B1_PER_ID2" AND
"XFI"."B1_PER_ID3"="F4"."B1_PER_ID3" AND
"XFI"."FEEITEM_SEQ_NBR"="F4"."FEEITEM_SEQ_NBR")

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Note
-----
- Warning: basic plan statistics not available. These are only
collected when
:

* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or
system leve
l



62 rows selected.

Elapsed: 00:00:11.59
SQL> exit

------------------------case
2------------------------------------------------
2. Use hint /*+ LEADING(SD) index(SD SETDETAILS_SETID_IX) */

Result: the result is use SD at first in nested loop.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing
options

SQL> rem set autotrace trace
SQL> set timing on
SQL>
SQL> variable spc varchar2(15);
SQL> variable bfd varchar2(40);
SQL> variable efd varchar2(40);
SQL> variable p1 varchar2(60);
SQL> variable p2 varchar2(60);
SQL> exec :spc:='SACRAMENTO';

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL> exec :bfd:='2005-12-05';

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL> exec :efd:='2008-12-04 23:59:59';

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL> rem exec :p1:='254413'
SQL> exec :p1:='SET07'

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL> exec :p2:='Contractor'

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL>
SQL> rem alter session set optimizer_index_caching = 0;
SQL> rem alter session set optimizer_index_cost_adj = 100;
SQL> rem alter session set optimizer_mode = ALL_ROWS;
SQL> alter session set optimizer_index_caching = 90;

Session altered.

Elapsed: 00:00:00.00
SQL> alter session set optimizer_index_cost_adj = 10;

Session altered.

Elapsed: 00:00:00.01
SQL> alter session set optimizer_mode = FIRST_ROWS_100;

Session altered.

Elapsed: 00:00:00.00
SQL>
SQL>
SQL> SELECT /*+ LEADING(SD) index(SD SETDETAILS_SETID_IX) */
xfi.serv_prov_code,
2 xfi.b1_per_id1,
3 xfi.b1_per_id2,
4 xfi.b1_per_id3,
5 xfi.feeitem_seq_nbr,
6 xfi.invoice_nbr,
7 xfi.gf_fee_period,
8 xfi.gf_fee,
9 xfi.gf_des,
10 xfi.gf_unit,
11 xfi.gf_udes,
12 finv.invoice_date AS gf_fee_apply_date,
13 xfi.feeitem_invoice_status,
14 xfi.gf_l1,
15 xfi.gf_l2,
16 xfi.gf_l3,
17 xfi.x4feeitem_invoice_udf1,
18 xfi.x4feeitem_invoice_udf2,
19 xfi.x4feeitem_invoice_udf3,
20 xfi.x4feeitem_invoice_udf4,
21 xfi.gf_fee_schedule,
22 xfi.fee_schedule_version,
23 xfi.rec_date,
24 xfi.rec_ful_nam,
25 xfi.rec_status,
26 f4.GF_COD,
27 f4.GF_PRIORITY
28 FROM x4feeitem_invoice xfi, setdetails sd, f4invoice finv,
F4FEEITEM f4
29 WHERE sd.serv_prov_code = xfi.serv_prov_code
30 AND sd.b1_per_id1 = xfi.b1_per_id1
31 AND sd.b1_per_id2 = xfi.b1_per_id2
32 AND sd.b1_per_id3 = xfi.b1_per_id3
33 AND xfi.serv_prov_code = f4.serv_prov_code
34 AND xfi.b1_per_id1 = f4.b1_per_id1
35 AND xfi.b1_per_id2 = f4.b1_per_id2
36 AND xfi.b1_per_id3 = f4.b1_per_id3
37 And xfi.feeitem_seq_nbr = f4.feeitem_seq_nbr
38 AND finv.serv_prov_code = xfi.serv_prov_code
39 AND finv.invoice_nbr = xfi.invoice_nbr
40 AND sd.serv_prov_code = :spc
41 AND upper(sd.set_id) = :p1
42 AND xfi.rec_status = 'A'
43 AND xfi.feeitem_invoice_status = 'INVOICED'
44 ORDER BY gf_fee_apply_date
45 /

no rows selected

Elapsed: 00:00:00.65
SQL> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR
(NULL,NULL,'ALLSTATS LAST')) ;

Plan hash value: 3678138156

--------------------------------------------------------------------------------
-----------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

| Id | Operation | Name | E-
Rows | OM
em | 1Mem | Used-Mem |

--------------------------------------------------------------------------------
-----------------------

| 0 | SELECT STATEMENT |
| |
| | |

| 1 | SORT ORDER BY | |
112K| 10

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
24 | 1024 | |

|* 2 | HASH JOIN | |
112K|
11M| 2154K| 13M (0)|

| 3 | TABLE ACCESS BY INDEX ROWID | F4INVOICE |
248K|
| | |

|* 4 | INDEX RANGE SCAN | F4INVOICE_BATCH_NBR_IX |
248K|
| | |


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 5 | HASH JOIN | |
90650 | 6
94K| 694K| 214K (0)|

| 6 | NESTED LOOPS |
| |
| | |

| 7 | NESTED LOOPS | |
90650 |
| | |

| 8 | TABLE ACCESS BY INDEX ROWID| SETDETAILS
| 25 |
| | |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

|* 9 | INDEX RANGE SCAN | SETDETAILS_SETID_IX
| 25 |
| | |

|* 10 | INDEX RANGE SCAN | X4FEEITEM_INVOICE_PK
| 1 |
| | |

|* 11 | TABLE ACCESS BY INDEX ROWID | X4FEEITEM_INVOICE |
3578 |
| | |

| 12 | TABLE ACCESS BY INDEX ROWID | F4FEEITEM |
1231K|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| | |

|* 13 | INDEX RANGE SCAN | F4FEEITEM_POS_IX |
1231K|
| | |

--------------------------------------------------------------------------------
-----------------------


Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

2 - access("FINV"."SERV_PROV_CODE"="XFI"."SERV_PROV_CODE" AND
"FINV"."INVOICE_NBR"="XFI"."INVOICE_NBR")
4 - access("FINV"."SERV_PROV_CODE"=:SPC)
5 - access("XFI"."SERV_PROV_CODE"="F4"."SERV_PROV_CODE" AND
"XFI"."B1_PER_ID1"="F4"."B1_PER_ID1" AND
"XFI"."B1_PER_ID2"="F4"."
B1_PER_ID2" AND

"XFI"."B1_PER_ID3"="F4"."B1_PER_ID3" AND
"XFI"."FEEITEM_SEQ_NBR"="
F4"."FEEITEM_SEQ_NBR")


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
9 - access("SD"."SERV_PROV_CODE"=:SPC AND "SD"."SYS_NC00017$"=:P1)
10 - access("XFI"."SERV_PROV_CODE"=:SPC AND
"SD"."B1_PER_ID1"="XFI"."B1_PER_ID
1" AND

"SD"."B1_PER_ID2"="XFI"."B1_PER_ID2" AND
"SD"."B1_PER_ID3"="XFI"."
B1_PER_ID3")

11 - filter(("XFI"."FEEITEM_INVOICE_STATUS"='INVOICED' AND
"XFI"."REC_STATUS"=
'A'))

13 - access("F4"."SERV_PROV_CODE"=:SPC)

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Note
-----
- Warning: basic plan statistics not available. These are only
collected when
:

* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or
system leve
l



60 rows selected.

Elapsed: 00:00:00.32
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release
11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing
options

E:\tools\syncdata>

------------------------case
3------------------------------------------------
3. combination ALL_ROWS + OICA (10) + OIC (90) + NO HINTS

Result: as below

| Id | Operation | Name | E-
Rows | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| | | | |
| 1 | SORT ORDER BY | |
112K| 1024 | 1024 | |
|* 2 | HASH JOIN | |
112K| 11M| 2154K| 13M (0)|
| 3 | TABLE ACCESS BY INDEX ROWID | F4INVOICE |
248K| | | |
|* 4 | INDEX RANGE SCAN | F4INVOICE_BATCH_NBR_IX |
248K| | | |
|* 5 | HASH JOIN | |
90650 | 694K| 694K| 214K (0)|
| 6 | NESTED LOOPS |
| | | | |
| 7 | NESTED LOOPS | |
90650 | | | |
| 8 | TABLE ACCESS BY INDEX ROWID| SETDETAILS
| 25 | | | |
|* 9 | INDEX RANGE SCAN | SETDETAILS_SETID_IX
| 25 | | | |
|* 10 | INDEX RANGE SCAN | X4FEEITEM_INVOICE_PK
| 1 | | | |
|* 11 | TABLE ACCESS BY INDEX ROWID | X4FEEITEM_INVOICE |
3578 | | | |
| 12 | TABLE ACCESS BY INDEX ROWID | F4FEEITEM |
1231K| | | |
|* 13 | INDEX RANGE SCAN | F4FEEITEM_POS_IX |
1231K| | | |

------------------------case
4------------------------------------------------
4. combination ALL_ROWS + OICA (10) + OIC (0) + NO HINTS

Result: as below

| Id | Operation | Name | E-
Rows | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| | | | |
| 1 | SORT ORDER BY | |
112K| 1024 | 1024 | |
|* 2 | HASH JOIN | |
112K| 11M| 2154K| 13M (0)|
| 3 | TABLE ACCESS BY INDEX ROWID | F4INVOICE |
248K| | | |
|* 4 | INDEX RANGE SCAN | F4INVOICE_BATCH_NBR_IX |
248K| | | |
|* 5 | HASH JOIN | |
90650 | 694K| 694K| 1047K (0)|
| 6 | NESTED LOOPS |
| | | | |
| 7 | NESTED LOOPS | |
90650 | | | |
| 8 | TABLE ACCESS BY INDEX ROWID| SETDETAILS
| 25 | | | |
|* 9 | INDEX RANGE SCAN | SETDETAILS_SETID_IX
| 25 | | | |
|* 10 | INDEX RANGE SCAN | X4FEEITEM_INVOICE_PK
| 1 | | | |
|* 11 | TABLE ACCESS BY INDEX ROWID | X4FEEITEM_INVOICE |
3578 | | | |
| 12 | TABLE ACCESS BY INDEX ROWID | F4FEEITEM |
1231K| | | |
|* 13 | INDEX RANGE SCAN | F4FEEITEM_POS_IX |
1231K| | | |
-------------------------------------------------------------------------------------------------------

------------------------case
5------------------------------------------------
5. combination ALL_ROWS + OICA (100) + OIC (0) + NO HINTS

Result: as below

| Id | Operation | Name | E-
Rows | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| | | | |
| 1 | SORT ORDER BY | |
112K| 1024 | 1024 | |
|* 2 | HASH JOIN | |
112K| 11M| 2154K| 13M (0)|
| 3 | TABLE ACCESS BY INDEX ROWID | F4INVOICE |
248K| | | |
|* 4 | INDEX RANGE SCAN | F4INVOICE_BATCH_NBR_IX |
248K| | | |
|* 5 | HASH JOIN | |
90650 | 694K| 694K| 1047K (0)|
| 6 | NESTED LOOPS |
| | | | |
| 7 | NESTED LOOPS | |
90650 | | | |
| 8 | TABLE ACCESS BY INDEX ROWID| SETDETAILS
| 25 | | | |
|* 9 | INDEX RANGE SCAN | SETDETAILS_SETID_IX
| 25 | | | |
|* 10 | INDEX RANGE SCAN | X4FEEITEM_INVOICE_PK
| 1 | | | |
|* 11 | TABLE ACCESS BY INDEX ROWID | X4FEEITEM_INVOICE |
3578 | | | |
| 12 | TABLE ACCESS BY INDEX ROWID | F4FEEITEM |
1231K| | | |
|* 13 | INDEX RANGE SCAN | F4FEEITEM_POS_IX |
1231K| | | |
-------------------------------------------------------------------------------------------------------

------------------------item
6------------------------------------------------
6. If query is expected to retrieve 100,000 rows, but the application
front-end will ONLY read just the first 100 rows and then throw away
the rest of the query results without bother fetching the remaining
rows.
[lsllcm] There are many queries like the case in our environment. So
we use FIRST_ROWS_100. I will test different queries in both
FIRST_ROWS_100 and ALL_ROWS in 10g db and see difference.

------------------------item
7------------------------------------------------
Is it a special case that no rows are returned due to some of the
bind
values used? How many rows does this kind of statement return
typically?

[lsllcm] The data in table SETDETAILS is skew, most of them have less
than 100 rows returned
based on SETDETAILS_SETID_IX. 10 of them have from 10,000 - 40,000
rows returned.

I use below command to gather statistics.

exec dbms_stats.gather_schema_stats
('TEST',estimate_percent=>100,cascade=> TRUE);

Below are histogram of SYS_NC00017$

COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
---------------------------------- --------------- --------------
SYS_NC00017$ 0 2.502076227359
SYS_NC00017$ 1 3.388853334909
SYS_NC00017$ 2 3.391679403148
SYS_NC00017$ 3 3.391679403478
SYS_NC00017$ 4 3.391679403525
SYS_NC00017$ 5 3.442991320807
SYS_NC00017$ 6 3.492087683254
SYS_NC00017$ 7 3.495116616164
SYS_NC00017$ 8 3.547450636185
SYS_NC00017$ 9 3.649479141472
SYS_NC00017$ 10 3.699779507944
SYS_NC00017$ 11 3.703217969722
SYS_NC00017$ 12 3.703622906366
SYS_NC00017$ 13 3.857771503121
SYS_NC00017$ 14 3.907648005162
SYS_NC00017$ 15 3.956124399555
SYS_NC00017$ 16 3.964259789323
SYS_NC00017$ 17 4.013711600766
SYS_NC00017$ 38 4.013711600766
SYS_NC00017$ 40 4.013711600766

COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
---------------------------------- --------------- --------------
SYS_NC00017$ 41 4.013737736751
SYS_NC00017$ 42 4.013737736908
SYS_NC00017$ 43 4.013739364774
SYS_NC00017$ 44 4.013741723170
SYS_NC00017$ 45 4.013741723170
SYS_NC00017$ 56 4.013748076424
SYS_NC00017$ 63 4.013748076644
SYS_NC00017$ 67 4.013748076645
SYS_NC00017$ 68 4.013748076656
SYS_NC00017$ 69 4.013748076657
SYS_NC00017$ 70 4.013748076669
SYS_NC00017$ 72 4.013748076681
SYS_NC00017$ 73 4.013748076718
SYS_NC00017$ 99 4.013748076765
SYS_NC00017$ 122 4.013748076766
SYS_NC00017$ 123 4.013748076798
SYS_NC00017$ 124 4.013748076801
SYS_NC00017$ 125 4.013748076802
SYS_NC00017$ 140 4.013748076826
SYS_NC00017$ 147 4.013748076826
SYS_NC00017$ 148 4.013748076838

COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
---------------------------------- --------------- --------------
SYS_NC00017$ 151 4.013748076838
SYS_NC00017$ 152 4.013748076919
SYS_NC00017$ 161 4.013748881378
SYS_NC00017$ 188 4.013748881378
SYS_NC00017$ 192 4.013748881378
SYS_NC00017$ 211 4.013748881378
SYS_NC00017$ 212 4.013749655139
SYS_NC00017$ 213 4.013751258162
SYS_NC00017$ 214 4.013752022627
SYS_NC00017$ 215 4.013761551658
SYS_NC00017$ 216 4.063638056887
SYS_NC00017$ 222 4.117383319540
SYS_NC00017$ 223 4.167086228207
SYS_NC00017$ 224 4.271108002299
SYS_NC00017$ 225 4.271744272749
SYS_NC00017$ 226 4.273769335395
SYS_NC00017$ 227 4.319380127847
SYS_NC00017$ 228 4.319380127847
SYS_NC00017$ 229 4.319380127847
SYS_NC00017$ 230 4.319380127847
SYS_NC00017$ 231 4.319380127847

COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
---------------------------------- --------------- --------------
SYS_NC00017$ 232 4.319380127847
SYS_NC00017$ 233 4.319380127847
SYS_NC00017$ 234 4.319380127847
SYS_NC00017$ 238 4.319380127847
SYS_NC00017$ 240 4.319380127847
SYS_NC00017$ 241 4.319380127847
SYS_NC00017$ 242 4.319380127847
SYS_NC00017$ 243 4.319380127847
SYS_NC00017$ 244 4.319380127847
SYS_NC00017$ 245 4.319380127847
SYS_NC00017$ 247 4.319380127847
SYS_NC00017$ 248 4.319380127847
SYS_NC00017$ 249 4.324469045995
SYS_NC00017$ 250 4.376187633557
SYS_NC00017$ 251 4.377607232242
SYS_NC00017$ 252 4.430327275963
SYS_NC00017$ 253 4.530723636900
SYS_NC00017$ 254 4.690372424101

------------------------item
8------------------------------------------------
[lsllcm] The reason to set "_optimizer_cost_based_transformation is to
check if
the issue is related to the setting. Now I have set it back to
default.

------------------------item
9------------------------------------------------
9. You might try collecting system statistics during a busy time
period.
To collect the system statistics with a 60 minute monitoring period,
enter the following in SQL*Plus (the SQL*Plus command prompt will
return immediately):
EXEC DBMS_STATS.GATHER_SYSTEM_STATS('interval',interval=>60)

[lsllcm] I did not gather system statistics, and I check again
scheduled job, no job
gather system staitstics.

At first I use below command to gather statistics.
exec dbms_stats.gather_schema_stats
('TEST',estimate_percent=>100,cascade=> TRUE);

11g has automatic statistics gathering job too.
BEGIN
DBMS_AUTO_TASK_ADMIN.disable( client_name => 'auto optimizer stats
collection',
operation => NULL,
window_name => NULL);
END;
/

------------------------item
10------------------------------------------------
10. As you suggested, I change the optimizer setting into default as
combination ALL_ROWS + OICA (100) + OIC (0) in test environment. The
result is
system looks like to become stable. Even if the query does not choose
best
execution plan, it chooses not worst execution plan. Like the test
case above,
it has 3 consistent reads in 10g db (it should be best execution
plan). In 11g
(ALL_ROWS), it has 3656 consistent reads (it is not best, but it is
not worst
too). In 11g (FIRST_ROWS_100), it has 2891740 consistent gets(like
worst one).

From: lsllcm on
On Nov 7, 5:31 pm, "Jonathan Lewis" <jonat...(a)jlcomp.demon.co.uk>
wrote:
> "lsllcm" <lsl...(a)gmail.com> wrote in message
>
> news:871566ee-22ef-4895-bcc6-753269a7bc3a(a)f18g2000prf.googlegroups.com...
>
> > The trace file is too long, I use three parts.
>
> >  *************************************
> >  PARAMETERS WITH ALTERED VALUES
> >  ******************************
> > Compilation Environment Dump
> > optimizer_features_enable           = 11.1.0.6
> > optimizer_mode                      = first_rows_100
> > optimizer_index_cost_adj            = 10
> > optimizer_index_caching             = 90
> > _optimizer_cost_based_transformation = off
>
> Sorry about picking this one up so late.
>
> I've had a quick read through the posts I can see at the moment.
> The critical issue is (as Charles and Randolf have pointed out)
> the use of first_rows_100, combined with Randolf's observation
> that the expected result set is 40 million rows.
>
> >> First K Rows: K = 100.00, N = 40640900.00
> >> First K Rows: Setup end
>
> When you don't have the order by clause, Oracle is working on a
> plan that will pick up any 100 rows as quickly as possible.  When
> you add the order by the optimizer "knows" it has to acquire 40M
> rows and sort them before returning the first 100. Consequently
> any path that avoids doing the sorting is likely to be a good path -
> hence the choice of index to drive the query.
>
> --
> Regards
>
> Jonathan Lewishttp://jonathanlewis.wordpress.com
>
> Author: Cost Based Oracle: Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> The Co-operative Oracle Users' FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.html

Thanks Jonathan,

From: Charles Hooper on
On Nov 7, 9:13 am, lsllcm <lsl...(a)gmail.com> wrote:
> Thanks Charles and Randolf for your comments:

Thanks for running all of the test cases. Many of the plans produced
are identical, but none show the same plan as you posted for 11.1.0.6
and 11.2.0.1.

> Below are test cases and results.
>
> ------------------------case
> 1------------------------------------------------
> 1. Use hint /*+ index(SD SETDETAILS_SETID_IX) */
>
> Result: the result is same as no index hint

It appears that the optimizer was originally using the
SETDETAILS_PID_IX index based on your first post in this thread, so it
does appear that the index hint did actually change the plan when
using your original OPTIMIZER_MODE, OPTIMIZER_INDEX_COST_ADJ and
OPTIMIZER_INDEX_CACHING parameter settings.

(snip)

> SQL> exec :efd:='2008-12-04 23:59:59';
> PL/SQL procedure successfully completed.
> Elapsed: 00:00:00.37
>
> SQL> rem exec :p1:='254413'
> SQL> exec :p1:='SET07'
> PL/SQL procedure successfully completed.
> Elapsed: 00:00:00.03
>
> SQL> exec :p2:='Contractor'
> PL/SQL procedure successfully completed.
> Elapsed: 00:00:00.18

It seems a little odd that some of these commands to set bind variable
values requires 370ms - about 1/2 as long as it takes to actually
execute the SQL statement with OPTIMIZER_MODE set to ALL_ROWS. I am
not sure if there is significance to this observation, or not.

(snip)

> 6------------------------------------------------
> 6. If query is expected to retrieve 100,000 rows, but the application
> front-end will ONLY read just the first 100 rows and then throw away
> the rest of the query results without bother fetching the remaining
> rows.
> [lsllcm] There are many queries like the case in our environment. So
> we use FIRST_ROWS_100. I will test different queries in both
> FIRST_ROWS_100 and ALL_ROWS in 10g db and see difference.
>
> ------------------------item
> 7------------------------------------------------
> Is it a special case that no rows are returned due to some of the
> bind
> values used? How many rows does this kind of statement return
> typically?
>
> [lsllcm] The data in table SETDETAILS is skew, most of them have less
> than 100 rows returned
> based on SETDETAILS_SETID_IX. 10 of them have from 10,000 - 40,000
> rows returned.

Skewed data and bind variables may lead to problems with the re-use of
plans, especially if histograms are present. Oracle 11.2.0.1 (with
OPTIMIZER_MODE = ALL_ROWS) might be trying to build a "safer" plan
which will work reasonably well for 10 or 40,000 rows, while 11.1.0.6
and 10.2.0.4 appear to be relying on a short-circuit in the plan which
required only 3 logical IOs. Any idea how the plan produced on the
older releases of Oracle might have handled the situations where the
plan returned 40,000 rows? In other words, while the one case you
have identified with returning 0 rows executes quickly, the case where
40,000 rows are returned might execute much slower than the 11.2.0.1
execution.

> I use below command to gather statistics.
>
> exec dbms_stats.gather_schema_stats
> ('TEST',estimate_percent=>100,cascade=> TRUE);

OK, the above collects table and index statistics, but I am not sure
if that will collect the statistics on the hidden columns (Randolf or
Jonathan should be able to answer this question).

> Below are histogram of SYS_NC00017$

What is the purpose of this index? This is the same index that I
mentioned previously that showed a strange IO cost in the 10053 trace
file:
cost_io
179769313486231570814527423731704356798070567525844996598917476803157260780­
028538760589558632766878171540458953514382464234321326889464182768467546703­
537516986049910576551282076245490090389328944075868508455133942304583236903­
222948165808559332123348274797826204144723168738177180919299881250404026184­
124858368.00

>
> ------------------------item
> 8------------------------------------------------
> [lsllcm] The reason to set "_optimizer_cost_based_transformation is to
> check if
> the issue is related to the setting. Now I have set it back to
> default.

OK, so you temporarily set _optimizer_cost_based_transformation to
false to check for changes in performance, and have returned the
parameter to its original value. I might be remembering incorrectly,
but I believe that there was a bug in 10.2.0.4, and the work-around
for the bug required this hidden parameter to be set to false. That
might explain why you saw that parameter set to false in 10.2.0.4.

> ------------------------item
> 9------------------------------------------------
> 9. You might try collecting system statistics during a busy time
> period.
> To collect the system statistics with a 60 minute monitoring period,
> enter the following in SQL*Plus (the SQL*Plus command prompt will
> return immediately):
> EXEC DBMS_STATS.GATHER_SYSTEM_STATS('interval',interval=>60)
>
> [lsllcm] I did not gather system statistics, and I check again
> scheduled job, no job
> gather system staitstics.

There is no automated job for *system* (CPU) statistics gathering -
the DBA must tell Oracle when to collect the statistics using
DBMS_STATS.GATHER_SYSTEM_STATS.
From:
http://download.oracle.com/docs/cd/E11882_01/server.112/e10821/stats.htm#i41496
"System statistics describe the system's hardware characteristics,
such as I/O and CPU performance and utilization, to the query
optimizer. When choosing an execution plan, the optimizer estimates
the I/O and CPU resources required for each query. System statistics
enable the query optimizer to more accurately estimate I/O and CPU
costs, enabling the query optimizer to choose a better execution
plan."

Jonathan has also written several articles on the topic, here are a
couple of those articles:
http://jonathanlewis.wordpress.com/2007/04/30/system-statistics/
http://jonathanlewis.wordpress.com/2007/05/20/system-stats-strategy/
http://jonathanlewis.wordpress.com/2007/10/17/system-statistics-3/

> 10------------------------------------------------
> 10. As you suggested, I change the optimizer setting into default as
> combination ALL_ROWS + OICA (100) + OIC (0) in test environment. The
> result is
> system looks like to become stable. Even if the query does not choose
> best
> execution plan, it chooses not worst execution plan. Like the test
> case above,
> it has 3 consistent reads in 10g db (it should be best execution
> plan). In 11g
> (ALL_ROWS), it has 3656 consistent reads (it is not best, but it is
> not worst
> too). In 11g (FIRST_ROWS_100), it has 2891740  consistent gets(like
> worst one).

It is good that you found a suitable plan, which might be suitable if
0 rows, 10 rows, or 40,000 rows are returned.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
From: lsllcm on
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

From: lsllcm on
On Nov 8, 9:36 pm, Charles Hooper <hooperc2...(a)yahoo.com> wrote:
> On Nov 7, 9:13 am, lsllcm <lsl...(a)gmail.com> wrote:
>
> > Thanks Charles and Randolf for your comments:
>
> Thanks for running all of the test cases.  Many of the plans produced
> are identical, but none show the same plan as you posted for 11.1.0.6
> and 11.2.0.1.
>
> > Below are test cases and results.
>
> > ------------------------case
> > 1------------------------------------------------
> > 1. Use hint /*+ index(SD SETDETAILS_SETID_IX) */
>
> > Result: the result is same as no index hint
>
> It appears that the optimizer was originally using the
> SETDETAILS_PID_IX index based on your first post in this thread, so it
> does appear that the index hint did actually change the plan when
> using your original OPTIMIZER_MODE, OPTIMIZER_INDEX_COST_ADJ and
> OPTIMIZER_INDEX_CACHING parameter settings.
>
> (snip)
>
> > SQL> exec :efd:='2008-12-04 23:59:59';
> > PL/SQL procedure successfully completed.
> > Elapsed: 00:00:00.37
>
> > SQL> rem exec :p1:='254413'
> > SQL> exec :p1:='SET07'
> > PL/SQL procedure successfully completed.
> > Elapsed: 00:00:00.03
>
> > SQL> exec :p2:='Contractor'
> > PL/SQL procedure successfully completed.
> > Elapsed: 00:00:00.18
>
> It seems a little odd that some of these commands to set bind variable
> values requires 370ms - about 1/2 as long as it takes to actually
> execute the SQL statement with OPTIMIZER_MODE set to ALL_ROWS.  I am
> not sure if there is significance to this observation, or not.
>
> (snip)
>
>
>
>
>
> > 6------------------------------------------------
> > 6. If query is expected to retrieve 100,000 rows, but the application
> > front-end will ONLY read just the first 100 rows and then throw away
> > the rest of the query results without bother fetching the remaining
> > rows.
> > [lsllcm] There are many queries like the case in our environment. So
> > we use FIRST_ROWS_100. I will test different queries in both
> > FIRST_ROWS_100 and ALL_ROWS in 10g db and see difference.
>
> > ------------------------item
> > 7------------------------------------------------
> > Is it a special case that no rows are returned due to some of the
> > bind
> > values used? How many rows does this kind of statement return
> > typically?
>
> > [lsllcm] The data in table SETDETAILS is skew, most of them have less
> > than 100 rows returned
> > based on SETDETAILS_SETID_IX. 10 of them have from 10,000 - 40,000
> > rows returned.
>
> Skewed data and bind variables may lead to problems with the re-use of
> plans, especially if histograms are present.  Oracle 11.2.0.1 (with
> OPTIMIZER_MODE = ALL_ROWS) might be trying to build a "safer" plan
> which will work reasonably well for 10 or 40,000 rows, while 11.1.0.6
> and 10.2.0.4 appear to be relying on a short-circuit in the plan which
> required only 3 logical IOs.  Any idea how the plan produced on the
> older releases of Oracle might have handled the situations where the
> plan returned 40,000 rows?  In other words, while the one case you
> have identified with returning 0 rows executes quickly, the case where
> 40,000 rows are returned might execute much slower than the 11.2.0.1
> execution.
>
> > I use below command to gather statistics.
>
> > exec dbms_stats.gather_schema_stats
> > ('TEST',estimate_percent=>100,cascade=> TRUE);
>
> OK, the above collects table and index statistics, but I am not sure
> if that will collect the statistics on the hidden columns (Randolf or
> Jonathan should be able to answer this question).
>
> > Below are histogram of SYS_NC00017$
>
> What is the purpose of this index?  This is the same index that I
> mentioned previously that showed a strange IO cost in the 10053 trace
> file:
> cost_io
> 179769313486231570814527423731704356798070567525844996598917476803157260780­­
> 028538760589558632766878171540458953514382464234321326889464182768467546703­­
> 537516986049910576551282076245490090389328944075868508455133942304583236903­­
> 222948165808559332123348274797826204144723168738177180919299881250404026184­­
> 124858368.00
>
>
>
> > ------------------------item
> > 8------------------------------------------------
> > [lsllcm] The reason to set "_optimizer_cost_based_transformation is to
> > check if
> > the issue is related to the setting. Now I have set it back to
> > default.
>
> OK, so you temporarily set _optimizer_cost_based_transformation to
> false to check for changes in performance, and have returned the
> parameter to its original value.  I might be remembering incorrectly,
> but I believe that there was a bug in 10.2.0.4, and the work-around
> for the bug required this hidden parameter to be set to false.  That
> might explain why you saw that parameter set to false in 10.2.0.4.
>
> > ------------------------item
> > 9------------------------------------------------
> > 9. You might try collecting system statistics during a busy time
> > period.
> > To collect the system statistics with a 60 minute monitoring period,
> > enter the following in SQL*Plus (the SQL*Plus command prompt will
> > return immediately):
> > EXEC DBMS_STATS.GATHER_SYSTEM_STATS('interval',interval=>60)
>
> > [lsllcm] I did not gather system statistics, and I check again
> > scheduled job, no job
> > gather system staitstics.
>
> There is no automated job for *system* (CPU) statistics gathering -
> the DBA must tell Oracle when to collect the statistics using
> DBMS_STATS.GATHER_SYSTEM_STATS.
> From:http://download.oracle.com/docs/cd/E11882_01/server.112/e10821/stats.....
> "System statistics describe the system's hardware characteristics,
> such as I/O and CPU performance and utilization, to the query
> optimizer. When choosing an execution plan, the optimizer estimates
> the I/O and CPU resources required for each query. System statistics
> enable the query optimizer to more accurately estimate I/O and CPU
> costs, enabling the query optimizer to choose a better execution
> plan."
>
> Jonathan has also written several articles on the topic, here are a
> couple of those articles:http://jonathanlewis.wordpress.com/2007/04/30/system-statistics/http://jonathanlewis.wordpress.com/2007/05/20/system-stats-strategy/http://jonathanlewis.wordpress.com/2007/10/17/system-statistics-3/
>
> > 10------------------------------------------------
> > 10. As you suggested, I change the optimizer setting into default as
> > combination ALL_ROWS + OICA (100) + OIC (0) in test environment. The
> > result is
> > system looks like to become stable. Even if the query does not choose
> > best
> > execution plan, it chooses not worst execution plan. Like the test
> > case above,
> > it has 3 consistent reads in 10g db (it should be best execution
> > plan). In 11g
> > (ALL_ROWS), it has 3656 consistent reads (it is not best, but it is
> > not worst
> > too). In 11g (FIRST_ROWS_100), it has 2891740  consistent gets(like
> > worst one).
>
> It is good that you found a suitable plan, which might be suitable if
> 0 rows, 10 rows, or 40,000 rows are returned.
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.- Hide quoted text -
>
> - Show quoted text -

Thanks Charles and Randolf, I learned a lot about optimizer from this
thread.

------------------------case
1------------------------------------------------
1. Use hint /*+ index(SD SETDETAILS_SETID_IX) */

Result: yes, the execution plan is different and use different index.
I did not check it carefully.

------------------------item
2------------------------------------------------
Below are histogram of SYS_NC00017$

Yes, SYS_NC00017$ is function index column UPPER("SD"."SET_ID") in
index SETDETAILS_SET_IX
It showed a strange IO cost in the 10053 trace file:
cost_io
179769313486231570814527423731704356798070567525844996598917476803157260780--
028538760589558632766878171540458953514382464234321326889464182768467546703--
537516986049910576551282076245490090389328944075868508455133942304583236903--
222948165808559332123348274797826204144723168738177180919299881250404026184--
124858368.00

------------------------item
3------------------------------------------------
Below sql still runs slow regardless optimzer mode is FIRST_ROWS_100
and ALL_ROWS

SELECT *
FROM (SELECT A.SERV_PROV_CODE AS SERV_PROV_CODE,
A.B1_PER_ID1 AS B1_PER_ID1,
A.B1_PER_ID2 AS B1_PER_ID2,
A.B1_PER_ID3 AS B1_PER_ID3,
A.B1_PER_GROUP,
A.B1_PER_TYPE,
A.B1_PER_SUB_TYPE,
A.B1_PER_CATEGORY,
A.B1_SPECIAL_TEXT,
A.B1_CREATED_BY_ACA,
A.R3_STD_TIME_CLASS_CODE,
A.B1_STANDARD_TIME,
A.B1_EVENT_CODE,
A.B1_REF_ID,
A.B1_APPL_STATUS,
A.REC_FUL_NAM,
A.REC_STATUS,
A.PROJECT_NBR,
A.B1_ALT_ID,
A.B1_TRACKING_NBR,
A.B1_APPL_STATUS_DATE,
A.B1_REF_ID1,
A.B1_REF_ID2,
A.B1_REF_ID3,
A.B1_Q_UD1,
A.B1_Q_UD2,
A.B1_Q_UD3,
A.B1_Q_UD4,
A.APP_STATUS_GROUP_CODE,
A.B1_MODULE_NAME,
A.B1_FILE_DD,
A.REC_DATE,
A.B1_REPORTED_DATE,
A.B1_APPL_CLASS,
A.B1_APP_TYPE_ALIAS,
A.B1_CREATED_BY,
A.B1_INITIATED_BY_PRODUCT,
B.APP_STATUS,
B.APP_STATUS_DATE,
B.DISPOSITION,
B.DISPOSITION_DATE,
B.TOTAL_FEE,
B.TOTAL_PAY,
B.PERCENT_COMPLETE,
B.BALANCE,
B.BALANCE_DATE,
B.HOUSE_COUNT,
B.BUILDING_COUNT,
B.PUBLIC_OWNED,
B.CONST_TYPE_CODE,
B.ACTION,
B.GA_AGENCY_CODE,
B.GA_BUREAU_CODE,
B.GA_DIVISION_CODE,
B.GA_OFFICE_CODE,
B.GA_SECTION_CODE,
B.GA_GROUP_CODE,
B.GA_FNAME,
B.GA_MNAME,
B.GA_LNAME,
B.B1_APPL_SUB_STATUS,
B.B1_SHORT_NOTES,
B.B1_CLOSED_DATE,
B.B1_CLOSEDBY,
B.B1_REPORTED_CHANNEL,
B.B1_ASGN_DEPT,
B.B1_ASGN_STAFF,
B.B1_PRIORITY,
B.B1_SEVERITY,
B.B1_ASGN_DATE,
B.TOTAL_JOB_COST,
B.B1_CLOSED_DEPT,
B.B1_COMPLETE_BY,
B.B1_COMPLETE_DEPT,
B.B1_COMPLETE_DATE,
B.B1_SCHEDULED_DATE,
B.B1_CREATOR_DEPT,
B.PM_SCHEDULE_SEQ,
B.B1_EST_PROD_UNITS,
B.B1_ACTUAL_PROD_UNITS,
B.B1_EST_COST_PER_UNIT,
B.B1_COST_PER_UNIT,
B.B1_EST_JOB_COST,
B.B1_PROD_UNIT_TYPE,
B.B1_CREATED_BY AS B1CREATEDBY,
B.B1_TRACK_START_DATE,
B.B1_ESTIMATED_DUE_DATE,
B.B1_IN_POSSESSION_TIME,
B.C6_ANONYMOUS_FLAG,
B.C6_REFERENCE_TYPE,
B.C6_APPEARANCE_DAYOFWEEK,
B.C6_APPEARANCE_DD,
B.C6_BOOKING_FLAG,
B.C6_DFNDT_SIGNATURE_FLAG,
B.C6_ENFORCE_OFFICER_ID,
B.C6_ENFORCE_OFFICER_NAME,
B.C6_INFRACTION_FLAG,
B.C6_INSPECTOR_ID,
B.C6_MISDEMEANOR_FLAG,
B.C6_OFFN_WITNESSED_FLAG,
B.C6_INSPECTOR_NAME,
B.C6_ENFORCE_DEPT,
B.C6_INSPECTOR_DEPT,
B.APP_STATUS_REASON,
B.FIRST_ISSUED_DATE,
B.UNDISTRIBUTED_JOB_COST,
B.B1_VAL_MULTIPLIER,
B.B1_VAL_EXTRA_AMT,
G.EXPIRATION_CODE,
G.EXPIRATION_STATUS,
G.EXPIRATION_DATE,
G.EXPIRATION_INTERVAL,
G.EXPIRATION_INTERVAL_UNITS,
G.GRACE_PERIOD_INTERVAL,
G.GRACE_PERIOD_INTERVAL_UNITS,
G.PENALTY_INTERVAL,
G.PENALTY_INTERVAL_UNITS,
G.NUM_PENALTY_PERIODS,
G.PENALTY_PERIOD,
G.RENEWAL_FEE_FUNCTION,
G.RENEWAL_FEE_CODE,
G.PENALTY_FEE_FUNCTION,
G.PENALTY_FEE_CODE,
G.PAY_PERIOD_GROUP,
G.UDF1,
G.UDF2,
G.UDF3,
G.UDF4,
C.B1_WORK_DESC
FROM B1PERMIT A
LEFT OUTER JOIN BPERMIT_DETAIL B ON A.SERV_PROV_CODE =
B.SERV_PROV_CODE
AND A.B1_PER_ID1 =
B.B1_PER_ID1
AND A.B1_PER_ID2 =
B.B1_PER_ID2
AND A.B1_PER_ID3 =
B.B1_PER_ID3
LEFT OUTER JOIN BWORKDES C ON A.SERV_PROV_CODE =
C.SERV_PROV_CODE
AND A.B1_PER_ID1 = C.B1_PER_ID1
AND A.B1_PER_ID2 = C.B1_PER_ID2
AND A.B1_PER_ID3 = C.B1_PER_ID3
LEFT OUTER JOIN B1_EXPIRATION G ON A.SERV_PROV_CODE =
G.SERV_PROV_CODE
AND A.B1_PER_ID1 =
G.B1_PER_ID1
AND A.B1_PER_ID2 =
G.B1_PER_ID2
AND A.B1_PER_ID3 =
G.B1_PER_ID3,
B3CONTRA L
WHERE A.REC_STATUS = 'A'
AND A.B1_PER_ID3 NOT LIKE '#%'
AND (A.B1_APPL_CLASS = 'COMPLETE' OR A.B1_APPL_CLASS IS
NULL)
AND A.SERV_PROV_CODE = L.SERV_PROV_CODE
AND A.B1_PER_ID1 = L.B1_PER_ID1
AND A.B1_PER_ID2 = L.B1_PER_ID2
AND A.B1_PER_ID3 = L.B1_PER_ID3
AND A.SERV_PROV_CODE = :spc
AND L.B1_LICENSE_NBR = :p1
AND L.B1_LICENSE_TYPE = :p2
ORDER BY A.B1_FILE_DD DESC)
WHERE ROWNUM < 101

-- in 10g and 11.1.0.6 db
The plan as below:
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name |
Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 | 4157 | 6 (17)| 00:00:01 |
|* 1 | COUNT STOPKEY |
| | | | |
| 2 | VIEW |
| 1 | 4157 | 6 (17)| 00:00:01 |
|* 3 | SORT ORDER BY STOPKEY |
| 1 | 616 | 6 (17)| 00:00:01 |
| 4 | NESTED LOOPS OUTER |
| 1 | 616 | 5 (0)| 00:00:01 |
| 5 | NESTED LOOPS OUTER |
| 1 | 447 | 3 (0)| 00:00:01 |
| 6 | NESTED LOOPS OUTER |
| 1 | 319 | 2 (0)| 00:00:01 |
| 7 | NESTED LOOPS |
| 1 | 235 | 1 (0)| 00:00:01 |
|* 8 | TABLE ACCESS BY INDEX ROWID| B3CONTRA
| 1 | 48 | 0 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | B3CONTRA_BUS_NAM2_IX
| 1 | | 0 (0)| 00:00:01 |
|* 10 | TABLE ACCESS BY INDEX ROWID| B1PERMIT
| 1 | 187 | 1 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | B1PERMIT_PK
| 1 | | 0 (0)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID | BWORKDES
| 4 | 336 | 1 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | BWORKDES_PK
| 1 | | 0 (0)| 00:00:01 |
| 14 | TABLE ACCESS BY INDEX ROWID | BPERMIT_DETAIL
| 1 | 128 | 1 (0)| 00:00:01 |
|* 15 | INDEX UNIQUE SCAN | BPERMIT_DETAIL_PK
| 1 | | 0 (0)| 00:00:01 |
| 16 | TABLE ACCESS BY INDEX ROWID | B1_EXPIRATION
| 2 | 338 | 2 (0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | B1_EXPIRATION_PK
| 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

-- in 11.2.0.1 db
The plan as below:
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name |
Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
75 | 304K| 246 (1)| 00:00:03 |
|* 1 | COUNT STOPKEY |
| | | | |
| 2 | VIEW | |
75 | 304K| 246 (1)| 00:00:03 |
|* 3 | SORT ORDER BY STOPKEY | |
75 | 44025 | 246 (1)| 00:00:03 |
| 4 | NESTED LOOPS OUTER | |
75 | 44025 | 245 (0)| 00:00:03 |
|* 5 | HASH JOIN RIGHT OUTER | |
75 | 32175 | 96 (2)| 00:00:02 |
|* 6 | TABLE ACCESS FULL | B1_EXPIRATION |
52 | 7592 | 3 (0)| 00:00:01 |
| 7 | NESTED LOOPS OUTER | |
75 | 21225 | 92 (0)| 00:00:02 |
| 8 | NESTED LOOPS | |
75 | 15300 | 6 (0)| 00:00:01 |
|* 9 | TABLE ACCESS BY INDEX ROWID| B3CONTRA
| 1 | 48 | 4 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | B3CONTRA_LIC_NBR1_IX
| 2 | | 3 (0)| 00:00:01 |
|* 11 | TABLE ACCESS BY INDEX ROWID| B1PERMIT |
1374 | 209K| 2 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | B1PERMIT_PK
| 1 | | 1 (0)| 00:00:01 |
| 13 | TABLE ACCESS BY INDEX ROWID | BWORKDES
| 1 | 79 | 2 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | BWORKDES_PK
| 1 | | 1 (0)| 00:00:01 |
| 15 | TABLE ACCESS BY INDEX ROWID | BPERMIT_DETAIL
| 1 | 158 | 2 (0)| 00:00:01 |
|* 16 | INDEX UNIQUE SCAN | BPERMIT_DETAIL_PK
| 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Thanks
lsllcm