Optimizer – Part IV (12c Enhancements)

This is the final part of my 4 part series on Optimizer. The previous 3 parts can be viewed clicking the following links:

In this blog, I will go through the 12c enhancements. Before we go through the 12c enhancement, let me also briefly cover the real life example that motivated me to write this series. I covered this in Part III. In that post, I missed to paste the relevant output of 10053 trace file, so let me take this up again in this post.

The problem query and it’s run time plan is pasted below.

select count(*) from nca.s_p_attributes a1
WHERE   a1.value='olwassenen';

    COUNT(*)
------------
      591168

SQL> select plan_table_output from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------
SQL_ID	79dfpvydpk710, child number 0
-------------------------------------
select count(*) from nca.s_p_attributes a1 WHERE
a1.value='olwassenen’
----------------------------------------------------------
| Id  | Operation	        | Name 	        | Rows	| 
----------------------------------------------------------
|   0 | SELECT STATEMENT 	|		|	|
|   1 |  SORT AGGREGATE  	|		|      1| 
|*  2 |   INDEX SKIP SCAN	| SP_P_IND3     |      8| 
----------------------------------------------------------

As can be seen, the optimizer calculation is way out (Actuals = 591168 v/s Assumptions=8). Believe me, the table and the column used in the WHERE predicate has a height balanced histogram on it. This will be clearly visible in the 10053 trace. The relevant portion of the 10053 trace is as under.

SINGLE TABLE ACCESS PATH 
  Single Table Cardinality Estimation for S_P_ATTRIBUTES[A1] 
  Column (#3): 
    NewDensity:0.000000, OldDensity:0.001202 BktCnt:254, PopBktCnt:122, PopValCnt:20, NDV:35078144
  Column (#3): VALUE(
    AvgLen: 11 NDV: 35078144 Nulls: 0 Density: 0.000000
    Histogram: HtBal  #Bkts: 254  UncompBkts: 254  EndPtVals: 153
  Table: S_P_ATTRIBUTES  Alias: A1
    Card: Original: 541600373.000000  Rounded: 8  Computed: 8.02  Non Adjusted: 8.02

This was a Non-Popular value and therefore, the selectivity calculation for Non-Popular value (NewDensity in 10053) is as under:


[(NPBKTCNT)/(BKTCNT * (NDV – POPVALCNT))]
[(254-122)/(254 * (35078144-20))] = 132/(254 * 35078124) = 132/8909843496 = 0.0000000148

The calculated selectivity multiplied by the number of rows in the table is the expected cardinality i.e. 0.0000000148*541600373=8.

Now, let’s briefly discuss the 12c Enhancements. Oracle Database 12c introduced additional histograms. These are : Top-n Frequency and Hybrid Histograms. There are few criteria’s for these histograms to be generated. The criteria is :

Following variables are used -
n - Number of Buckets either explicitly specified or left to default (254)
NDV - Number of Distinct Values in the Column
p - Internal Percentage, which is calculated as (1-(1/n))*100

If Data Skew Observed ?
   If NDV > n ?
      If estimate_percent => auto_sample_size ?
         if %of rows for topn frequent values >= p ?
            generate topn frequency histogram;
         else if
            generate hybrid histogram;
         end if;
      else if
         generate height balanced histogram;
      end if;
   else if
      generate frequency histogram;
   end if;
end if;

For the simplicity, I have color coded each IF-ELSE-ENDIF statement. From the preceding pseudo-code, it is clear that to generate 12c specific histograms, we need to specify estimate_percent as auto_sample_size, which is a default. So, if your statistics gathering policy states manual percentage for estimate, then it won’t gather these new histograms. The new auto_sample_size algorithm has improved a lot and therefore, you can safely change the manual estimate to auto.

The value of p requires some explanation. It is calculated as (1-(1/n))*100, where n is the number of buckets specified during stats gathering. Consider following 4 examples :

Example 1 : PROD_ID has 72 Distinct Values. As the bucket size specified is 100, which is more than the number of distinct values, Optimizer will generate Frequency Histogram.

execute dbms_stats.gather_table_stats(ownname=>'SH',tabname=>'SALES',method_opt=>'for columns PROD_ID size 100');

Example 2 : PROD_ID has 72 Distinct Values. In this case, the number of bucket (n) is less than the number of distinct values (72). However, the script explicitly estimates the percent as 30 and therefore, it will generate Height Balanced Histogram.

execute dbms_stats.gather_table_stats(ownname=>'SH',tabname=>'SALES',method_opt=>'for columns PROD_ID size 50', estimate_percent=>30);

Example 3 : PROD_ID has 72 Distinct Values. In this case, the number of bucket (n) is less than the number of distinct values (72). No estimate_percent specified means, it is left to default, which is AUTO_SAMPLE_SIZE. In this case, the decision on Hybrid or Top-N Frequency will be basis the value of p. The calcution for p is (1-(1/50))*100 = 98%. This means, if the top 50 distinct PROD_ID occupy more than 98% rows then, it will be a TopN Frequency, else it will be a Hybrid Histogram. We shall see this in action.

execute dbms_stats.gather_table_stats(ownname=>'SH',tabname=>'SALES',method_opt=>'for columns PROD_ID size 50');

Example : Our own table (TEST_SELECTIVITY) that we created for testing purpose in Optimizer – Part I. In this case, I am generating statistics using method_opt=>’for all columns size AUTO’. The columns will be considered basis the data available in COL_USAGE$, which is populated post execution of any query against a table and column. Column AMOUNT_SOLD has 636 Distinct values, which is higher than 254 (default buckets). The value of n is 254 and p will be (1-(1/254))*100 = 99.61. This means, if the number of rows occupied by top 254 distinct values is more than 99.61%, then it will be TopN Frequency, else it will be Hybrid.

execute dbms_stats.gather_table_stats(user,'TEST_SELECTIVITY',method_opt=>'FOR ALL COLUMNS SIZE AUTO');

select column_id, column_name, num_distinct, num_nulls,
	density, histogram
from	dba_tab_columns
where	owner='SCOTT'
and	table_name='TEST_SELECTIVITY'
and	column_name='AMOUNT_SOLD'
order by 1;

 COLUMN_ID COLUMN_NAME                    NUM_DISTINCT  NUM_NULLS    DENSITY HISTOGRAM
---------- ------------------------------ ------------ ---------- ---------- -------------------------
         6 AMOUNT_SOLD                             636          0    .000361 HYBRID

SQL> select round((1-(1/254))*100,2) value_p from dual;

   VALUE_P
----------
     99.61

## I have 800000 Rows in the table. So, 99.61% of 800000 is 796850.
## My Statistics Gathering command generated HYBRID, which means, 
## the top 254 distinct values occupy less than 99.61% or less than 796850 rows

SQL> select round((1-(1/254))*800000,0) value_p from dual;

   VALUE_P
----------
    796850

## Query to check Running Count and %Age 
## My Top 254 values occupy 776524, which is less than 796850
## My Top 254 values occupy 97.09%, which is less than 99.61%
## Therefore, the script generated HYBRID Histogram.

SQL> select rownum, amount_sold, cnt, running_cnt, running_perc from (
  2  select amount_sold, cnt, running_cnt,
  3                  sum(r_perc) over(order by cnt desc) running_perc from (
  4  select amount_sold, cnt, sum(cnt) over(order by cnt desc) running_cnt,
  5                  round(ratio_to_report(cnt) over()*100,2) r_perc from (
  6  select amount_sold, count(*) cnt from test_selectivity group by amount_sold order by 2 desc))
  7  order by cnt desc);

    ROWNUM AMOUNT_SOLD        CNT RUNNING_CNT RUNNING_PERC
---------- ----------- ---------- ----------- ------------
         1          10      33600       33600          4.2
         2           9      31964       65564          8.2
         3          11      28850       94414        11.81
         4          13      26968      121382        15.18
         5          47      26934      148316        18.55
         6          48      26114      174430        21.81
         7           8      24906      199336        24.92
         8          49      24239      223575        27.95
         9          46      18784      242359         30.3
        10          23      17014      259373        32.43
        11          21      16965      276338        34.55
        12          24      16246      292584        36.58
        13          22      15954      308538        38.57
        14          25      15782      324320        40.54
        15          51      14906      339226         42.4
        16          30      12932      352158        44.02
        17           7      12030      364188        45.52
        18          26      11958      376146        47.01
        19          17      11585      387731        48.46
        20          12      11547      399278         49.9
        21          52      11344      410622        51.32
        22          28      11227      421849        52.72
        23          29      10728      432577        54.06
        24          14      10180      442757        55.33
        25          53      10059      452816        56.59
        26          31       9907      462723        57.83
        27          19       9682      472405        59.04
        28          39       9571      481976        60.24
        29          33       9145      491121        61.38
        30          38       9027      500148        62.51
        31          54       8468      508616        63.57
        32          42       8177      516793        64.59
        33          32       8017      524810        65.59
        34          27       7651      532461        66.55
        35          34       7617      540078         67.5
        36          63       6607      546685        68.33
        37          20       6582      553267        69.15
        38          64       6575      559842        69.97
        39          56       6204      566046        70.75
        40          40       6060      572106        71.51
        41          57       5723      577829        72.23
        42          50       5511      583340        72.92
        43          35       5388      588728        73.59
        44          15       5219      593947        74.24
        45          60       5195      599142        74.89
        46          36       5062      604204        75.52
        47          58       5031      609235        76.15
        48          16       4903      614138        76.76
        49          41       4831      618969        77.36
        50          18       4562      623531        77.93
        51          62       4542      628073         78.5
        52          59       4421      632494        79.05
        53          45       4311      636805        79.59
        54          43       3946      640751        80.08
        55          65       3625      644376        80.53
        56          55       3372      647748        80.95
        57         123       2852      650600        81.31
        58          44       2327      652927         81.6
        59         126       2296      655223        81.89
        60         203       2286      657509        82.18
        61         115       2200      659709        82.46
        62          61       2187      661896        82.73
        63         211       2105      664001        82.99
        64         213       2032      666033        83.24
        65          91       2022      668055        83.49
        66         128       1953      670008        83.73
        67         113       1837      671845        83.96
        68         629       1799      673644        84.18
        69          73       1656      675300        84.39
        70         100       1641      676941         84.6
        71          72       1596      678537         84.8
        72         602       1577      680114           85
        73          37       1547      681661        85.19
        74         101       1533      683194        85.38
        75          66       1450      684644        85.56
        76         127       1402      686046        85.74
        77          71       1333      687379        85.91
        78          74       1314      688693        86.07
        79         163       1243      689936        86.23
        80          94       1237      691173        86.38
        81         210       1214      692387        86.53
        82         121       1139      693526        86.67
        83         160       1127      694653        86.81
        84          69       1111      695764        86.95
        85          70       1106      696870        87.09
        86         225       1101      697971        87.23
        87         168       1097      699068        87.37
        88         116       1088      700156        87.51
        89         307       1069      701225        87.64
        90         152       1063      702288        87.77
        91         134       1055      703343         87.9
        92         170       1034      704377        88.03
        93          77       1020      705397        88.16
        94          97       1012      706409        88.29
        95         151       1011      707420        88.42
        96         594        992      708412        88.54
        97         630        919      709331        88.65
        98          67        906      711143        88.87
        99         214        906      711143        88.87
       100          76        905      712048        88.98
       101          96        897      712945        89.09
       102         180        864      713809         89.2
       103         136        855      714664        89.31
       104         208        847      715511        89.42
       105         114        835      716346        89.52
       106         135        829      717175        89.62
       107         610        809      717984        89.72
       108         167        803      718787        89.82
       109         202        787      719574        89.92
       110         133        784      720358        90.02
       111         600        779      721137        90.12
       112         120        774      721911        90.22
       113          84        771      722682        90.32
       114         178        769      723451        90.42
       115         117        764      724215        90.52
       116          95        761      724976        90.62
       117        1000        758      725734        90.71
       118         306        735      726469         90.8
       119         199        734      727203        90.89
       120         125        718      727921        90.98
       121         303        688      728609        91.07
       122         112        686      729295        91.16
       123         639        679      729974        91.24
       124         179        670      730644        91.32
       125         228        665      731309         91.4
       126         177        663      731972        91.48
       127        1577        646      732618        91.56
       128         159        630      733248        91.64
       129         539        628      733876        91.72
       130         205        623      734499         91.8
       131          89        614      735113        91.88
       132          90        599      735712        91.95
       133         216        582      736294        92.02
       134         547        579      736873        92.09
       135        1050        564      737437        92.16
       136         557        557      737994        92.23
       137         119        552      738546         92.3
       138          99        536      739082        92.37
       139        1016        528      739610        92.44
       140          78        521      740131        92.51
       141          93        510      740641        92.57
       142         217        485      741126        92.63
       143         304        481      741607        92.69
       144        1053        480      742087        92.75
       145        1068        477      742564        92.81
       146        1496        476      743040        92.87
       147        1014        475      743515        92.93
       148         206        474      743989        92.99
       149        1004        468      744457        93.05
       150        1065        462      744919        93.11
       151        1566        461      745380        93.17
       152         156        458      745838        93.23
       153         207        457      746295        93.29
       154         140        452      746747        93.35
       155         142        451      747198        93.41
       156        1260        448      747646        93.47
       157         552        447      748093        93.53
       158        1599        442      748535        93.59
       159         222        433      748968        93.64
       160         212        424      749392        93.69
       161        1698        422      749814        93.74
       162         215        416      750230        93.79
       163        1240        415      750645        93.84
       164          85        408      751053        93.89
       165         155        403      751456        93.94
       166         148        401      751857        93.99
       167         554        399      752256        94.04
       168         900        395      752651        94.09
       169         556        391      753042        94.14
       170        1118        388      753430        94.19
       171          75        384      753814        94.24
       172          88        382      754196        94.29
       173         302        375      754571        94.34
       174        1057        372      754943        94.39
       175        1029        368      755311        94.44
       176        1321        366      755677        94.49
       177        1109        365      756042        94.54
       178          79        356      756398        94.58
       179         102        354      756752        94.62
       180        1729        344      757096        94.66
       181         204        341      757437         94.7
       182         659        339      758454        94.82
       183         195        339      758454        94.82
       184        1551        339      758454        94.82
       185          98        337      758791        94.86
       186         169        323      759114         94.9
       187         562        322      759436        94.94
       188        1195        320      759756        94.98
       189        1215        318      760074        95.02
       190         936        317      760391        95.06
       191          68        313      760704         95.1
       192         187        310      761014        95.14
       193         548        309      761632        95.22
       194        1714        309      761632        95.22
       195        1217        308      761940        95.26
       196         468        307      762554        95.34
       197        1297        307      762554        95.34
       198        1656        305      762859        95.38
       199         608        303      763162        95.42
       200         124        301      763463        95.46
       201         150        299      763762         95.5
       202        1206        294      764056        95.54
       203         181        293      764642        95.62
       204         106        293      764642        95.62
       205         288        288      764930        95.66
       206        1078        286      765216         95.7
       207         158        283      765499        95.74
       208        1633        282      765781        95.78
       209         154        277      766058        95.81
       210        1556        274      766606        95.87
       211        1061        274      766606        95.87
       212         161        273      766879         95.9
       213        1176        270      767149        95.93
       214         914        268      767417        95.96
       215         490        267      767684        95.99
       216         289        261      767945        96.02
       217        1758        256      768713        96.11
       218        1353        256      768713        96.11
       219        1045        256      768713        96.11
       220        1508        255      768968        96.14
       221         196        249      769217        96.17
       222         183        248      769465         96.2
       223        1674        246      769711        96.23
       224        1237        244      769955        96.26
       225        1531        243      770198        96.29
       226        1421        242      771166        96.41
       227         118        242      771166        96.41
       228         544        242      771166        96.41
       229        1501        242      771166        96.41
       230        1304        235      771636        96.47
       231        1076        235      771636        96.47
       232         842        232      771868         96.5
       233         561        230      772098        96.53
       234         143        228      772326        96.56
       235         632        222      772548        96.59
       236         193        216      772980        96.65
       237         305        216      772980        96.65
       238         596        215      773195        96.68
       239         194        209      773613        96.74
       240        1003        209      773613        96.74
       241         131        207      773820        96.77
       242        1553        206      774026         96.8
       243         937        202      774430        96.86
       244         580        202      774430        96.86
       245        1125        201      774631        96.89
       246         200        194      774825        96.91
       247         524        193      775018        96.93
       248         947        192      775594        96.99
       249         141        192      775594        96.99
       250        1709        192      775594        96.99
       251        1488        189      775783        97.01
       252        1334        187      775970        97.03
       253         281        186      776156        97.05
       254        1227        184      776524        97.09 <-- Top 254 Distinct Values
       255        1703        184      776524        97.09
.....
.....
.....
.....
       624        1231          1      800000        99.94
       625        1177          1      800000        99.94
       626        1638          1      800000        99.94
       627        1189          1      800000        99.94
       628         162          1      800000        99.94
       629          81          1      800000        99.94
       630         647          1      800000        99.94
       631         584          1      800000        99.94
       632         619          1      800000        99.94
       633        1036          1      800000        99.94
       634         708          1      800000        99.94
       635         518          1      800000        99.94
       636         522          1      800000        99.94

Next, I gather statistics on PRODUCTS table under SH Schema. This table has 72 rows with 22 Distinct values in PROD_SUBCATEGORY_ID column. First, I gather statistics using 10 Buckets. Optimizer will generate HYBRID Histogram, as the number of rows occupied by Top 10 Distinct values is less than than the value of p (1-(1/10)) = 90%. Then, I will generate using 18 Buckets and this time, it will be TopN Frequency, as the number of rows occupied by Top 18 Distinct Values is more than the value of p (1-(1/18)) = 94%.

SQL> select table_name, num_rows from dba_tables where table_name='PRODUCTS';

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
PRODUCTS                               72

SQL> select column_name, num_distinct from dba_tab_columns
  2  where      table_name='PRODUCTS'
  3  and        owner='SH'
  4  and        column_name='PROD_SUBCATEGORY_ID';

COLUMN_NAME                    NUM_DISTINCT
------------------------------ ------------
PROD_SUBCATEGORY_ID                      22


SQL> select 1-(1/10) from dual;

  1-(1/10)
----------
        .9

SQL> select round((1-(1/10))*72,0) from dual;

ROUND((1-(1/10))*72,0)
----------------------
                    65


SQL> compute sum of running_sum on report
SQL> break on report
SQL> select rownum, prod_subcategory_id, cnt, running_cnt, running_perc from (
  2  select prod_subcategory_id, cnt,
  3                  sum(cnt) over(order by cnt desc) running_cnt,
  4                  sum(r_perc) over(order by cnt desc) running_perc from (
  5  select prod_subcategory_id, cnt, round(ratio_to_report(cnt) over()*100,2) r_perc from (
  6  select prod_subcategory_id, count(*) cnt from products group by prod_subcategory_id order by 2 desc))
  7  order by cnt desc);

    ROWNUM PROD_SUBCATEGORY_ID        CNT RUNNING_CNT RUNNING_PERC
---------- ------------------- ---------- ----------- ------------
         1                2014          8           8        11.11
         2                2055          7          15        20.83
         3                2032          6          27        37.49
         4                2054          6          27        37.49
         5                2056          5          47        65.25
         6                2051          5          47        65.25
         7                2031          5          47        65.25
         8                2042          5          47        65.25
         9                2036          4          51        70.81
        10                2043          3          54        74.98 <-- Less than 90% or 65 Rows (HYBRID)
        11                2033          2          66        91.66
        12                2035          2          66        91.66
        13                2053          2          66        91.66
        14                2012          2          66        91.66
        15                2013          2          66        91.66
        16                2034          2          66        91.66
        17                2021          1          72          100
        18                2011          1          72          100
        19                2044          1          72          100
        20                2041          1          72          100
        21                2022          1          72          100
        22                2052          1          72          100

22 rows selected.

## STATS WITH BUCKET 10
exec dbms_stats.gather_table_stats(ownname=>'SH',tabname=>'PRODUCTS',method_opt=>'for columns prod_subcategory_id size 10');



SQL> select column_id, column_name, num_distinct, num_nulls,
  2                  density, histogram
  3  from       dba_tab_columns
  4  where      owner='SH'
  5  and        table_name='PRODUCTS'
  6  and             column_name='PROD_SUBCATEGORY_ID';

 COLUMN_ID COLUMN_NAME                    NUM_DISTINCT  NUM_NULLS    DENSITY HISTOGRAM
---------- ------------------------------ ------------ ---------- ---------- -------------------------
         5 PROD_SUBCATEGORY_ID                      22          0    .044976 HYBRID


SQL> select (1-(1/18)) from dual;

(1-(1/18))
----------
.944444444


SQL> select round((1-(1/18))*72,0) from dual;

ROUND((1-(1/18))*72,0)
----------------------
                    68

SQL> select rownum, prod_subcategory_id, cnt, running_cnt, running_perc from (
  2  select prod_subcategory_id, cnt,
  3                  sum(cnt) over(order by cnt desc) running_cnt,
  4                  sum(r_perc) over(order by cnt desc) running_perc from (
  5  select prod_subcategory_id, cnt, round(ratio_to_report(cnt) over()*100,2) r_perc from (
  6  select prod_subcategory_id, count(*) cnt from products group by prod_subcategory_id order by 2 desc))
  7  order by cnt desc);

    ROWNUM PROD_SUBCATEGORY_ID        CNT RUNNING_CNT RUNNING_PERC
---------- ------------------- ---------- ----------- ------------
         1                2014          8           8        11.11
         2                2055          7          15        20.83
         3                2032          6          27        37.49
         4                2054          6          27        37.49
         5                2056          5          47        65.25
         6                2051          5          47        65.25
         7                2031          5          47        65.25
         8                2042          5          47        65.25
         9                2036          4          51        70.81
        10                2043          3          54        74.98
        11                2033          2          66        91.66
        12                2035          2          66        91.66
        13                2053          2          66        91.66
        14                2012          2          66        91.66
        15                2013          2          66        91.66
        16                2034          2          66        91.66
        17                2021          1          72          100
        18                2011          1          72          100 <-- More than 94% or 68 Rows
        19                2044          1          72          100
        20                2041          1          72          100
        21                2022          1          72          100
        22                2052          1          72          100

22 rows selected.

SQL> exec dbms_stats.gather_table_stats(ownname=>'SH',tabname=>'PRODUCTS',method_opt=>'for columns prod_subcategory_id size 18');

PL/SQL procedure successfully completed.

SQL> select column_id, column_name, num_distinct, num_nulls,
  2                  density, histogram
  3  from       dba_tab_columns
  4  where      owner='SH'
  5  and        table_name='PRODUCTS'
  6  and             column_name='PROD_SUBCATEGORY_ID';

 COLUMN_ID COLUMN_NAME                    NUM_DISTINCT  NUM_NULLS    DENSITY HISTOGRAM
---------- ------------------------------ ------------ ---------- ---------- -------------------------
         5 PROD_SUBCATEGORY_ID                      22          0 .006944444 TOP-FREQUENCY

In this blog, I have tried to explain the underlying calculation by way of which 12c introduced histograms are generated. Hope, the explanation were clear. As always, comments are welcome.

Advertisements

Optimizer – Part III (Frequency & Height Balanced)

Finally, got some time to write the third post of this series. The Optimizer – Part I and Optimizer – Part II are the best reference before reading this post. From the Part II, we inferred that :

  • TIME_ID – Assumptions v/s Actuals 
  • AMOUNT_SOLD – Assumptions v/s Actuals Χ
  • PROMO_ID – Assumptions v/s Actuals Χ

However, with a minor change, which was on a copy of TEST_SELECTIVITY table, the equation changed to:

  • TIME_ID – Assumptions v/s Actuals 
  • TIME_ID – Assumptions v/s Actuals (minor change) Χ
  • AMOUNT_SOLD – Assumptions v/s Actuals Χ
  • PROMO_ID – Assumptions v/s Actuals Χ

A small change triggered a mismatch in the cardinality calculations of TIME_ID column, which was otherwise nearly accurate. For a Query Performance, optimal execution plan is very critical and for an optimal execution plan, it is very important that the Optimizer comes out with an accurate cardinality. As we have seen, in our previous blogs, SELECTIVITY is another significant factor and is the starting point for the Optimizer. While Cardinality is calculated by the Optimizer, Selectivity is (in most of the cases) stored in the data dictionary, by way of Statistics gathered using dbms_stats (or any other method provided by some Application Vendors).

Optimizer is a piece of code. The default behaviour (at least for a newly created table) of the optimizer is that it considers the data distribution as UNIFORM. For example, in our case (before the minor change), the data in TIME_ID column was Uniform and therefore, the optimizer calculation was nearly accurate. However, the other two columns (AMOUNT_SOLD & PROMO_ID), the data was non-uniform and therefore, Optimizer assumption v/s the actual data distribution were way out. After the table creation, the statistics were gathered automatically (as a part new feature of 12c). In 11g or earlier versions, you will have to gather the statistics manually. You should see the same results. The initial statistics were fed to the optimizer as a Uniform data. See below :

COLUMN_NAME        NUM_DISTINCT  NUM_NULLS    DENSITY
------------------ ------------ ---------- ----------
AMOUNT_SOLD                 636          0 .001572327
CUST_ID                    7056          0 .000141723
PROD_ID                      72          0 .013888889
PROMO_ID                      4          0        .25
QUANTITY_SOLD                 1          0          1
TIME_ID                    1096          0 .000912409

How do we fix the problem of Mis-Estimates? In this case, the DENSITY column was used as a SELECTIVITY and for each of the columns, it is calculated as if the data is Uniform. This mis-calculation resulted in errorneous optimizer calculation. How do we fix it? As mentioned, optimizer is a piece of code and it has to come out with it’s calculation based on the input provided. In the absence of additional statistics or accurate statistics, Optimizer will assume UNIFORM distribution and will mis-calculate the SELECTIVITY and the CARDINALITY, as we have seen with our test cases. We have to provide accurate inputs for the optimizer to come up with nearly accurate statistics and one approach to provide these additional and accurate statistics are Histograms.

Let us regather statistics on the table again and check the change in the DENSITY value for each of the columns.

exec dbms_stats.gather_table_stats(user,'TEST_SELECTIVITY', method_opt=>'for all columns size auto', estimate_percent=>100);

The resultant output is as below:

select column_id, column_name, num_distinct, num_nulls,
	density, histogram
from	dba_tab_columns
where	owner='SCOTT'
and	table_name='TEST_SELECTIVITY'
order by 1;

 COLUMN_ID COLUMN_NAME       NUM_DISTINCT  NUM_NULLS    DENSITY HISTOGRAM
---------- ----------------- ------------ ---------- ---------- --------------------
         1 PROD_ID                     72          0 .013888889 NONE
         2 CUST_ID                   7056          0 .000141723 NONE
         3 TIME_ID                   1096          0 .000912409 NONE
         4 PROMO_ID                     4          0 .000000625 FREQUENCY
         5 QUANTITY_SOLD                1          0          1 NONE
         6 AMOUNT_SOLD                636          0   .0018217 HEIGHT BALANCED

The Density for the two out of the three columns is changed and the HISTOGRAM column gives an additional information that we have some additional statistics on the two columns.

There are 2 questions here

  • Why the subsequent gathering of statistics gathered additional statistics (HISTOGRAM)?
  • Why there are no Additional Statistics (HISTOGRAMS) on other Columns?

The answer to the first question is that the queries on each of the tables and each of the columns are tracked in SYS.COL_USAGE$. The subsequent stats gathering job will refer to this table to get the column details on which the additional statistics are required. See below :

exec dbms_stats.flush_database_monitoring_info();

select intcol#, column_name, equality_preds, RANGE_PREDS
from	sys.col_usage$ cu, dba_tab_columns tc
where	obj# = (select data_object_id from dba_objects
		where object_name='TEST_SELECTIVITY')
and	cu.intcol# = tc.column_id
and	tc.table_name='TEST_SELECTIVITY';

   INTCOL# COLUMN_NAME       EQUALITY_PREDS RANGE_PREDS
---------- ----------------- -------------- -----------
         6 AMOUNT_SOLD                    1           1
         4 PROMO_ID                       1           0
         3 TIME_ID                        1           1

The answer to the second question is for the other columns (except TIME_ID), there were no queries executed, thus there were no information collected in COL_USAGE$. For the TIME_ID, there are no HISTOGRAMS even though we executed few queries (and COL_USAGE$ has an entry). The data in this column is UNIFORM and this is the additional check, that is internally made at the time of gathering statistics. During statistics generation, sample data for each of the column is computed and data is validated. If the data is found to be UNIFORM, no histograms are generated as it is a resource intensive process and generating histogram will not make any sense (at least not worth the resources required to generate histograms).

If you recollect from our Part II, the minor changes on the TIME_ID column was on another table TEST_SELECTIVITY_M, which was an exact replica of TEST_SELECTIVITY. If we gather statistics on TEST_SELECTIVITY_M, let’s see the results.

select column_id, column_name, num_distinct, num_nulls,
	density, histogram
from	dba_tab_columns
where	owner='SCOTT'
and	table_name='TEST_SELECTIVITY_M'
order by 1;

 COLUMN_ID COLUMN_NAME          NUM_DISTINCT  NUM_NULLS    DENSITY HISTOGRAM
---------- -------------------- ------------ ---------- ---------- ---------------
         1 PROD_ID                        72          0 .013888889 NONE
         2 CUST_ID                      7056          0 .000141723 NONE
         3 TIME_ID                      1097          0 .000914025 HEIGHT BALANCED
         4 PROMO_ID                        4          0        .25 NONE
         5 QUANTITY_SOLD                   1          0          1 NONE
         6 AMOUNT_SOLD                   636          0 .001572327 NONE

On this table, the query executed was only on TIME_ID column and therefore, the additional statistics were on TIME_ID column.

Coming back to TEST_SELECTIVITY. Now, we have a Frequency Histograms on PROMO_ID Column and Height Balanced Histogram on AMOUNT_SOLD column. Until 11g, we had these 2 types of Histograms. 12c introduced TopN Frequency and Hybrid Histograms, which I will cover in the last part of this series. I am on 12c and therefore, to generate Frequency and Height Balanced Histograms, I had to use estimate_percent as 100 (more on this in the next blog).

Frequency Histograms are generated if the number of distinct values are less than the number of Buckets. These Buckets, if not specified during statistics gathering, defaults to 254. PROMO_ID column has 4 distinct values, whereas, AMOUNT_SOLD has 636, which is more than 254 and hence Height Balanced Histograms. Lets execute our queries on these 2 columns and check the CARDINALITY estimates.

select column_id, column_name, num_distinct, num_nulls,
	density, histogram
from	dba_tab_columns
where	owner='SCOTT'
and	table_name='TEST_SELECTIVITY'
order by 1;

 COLUMN_ID COLUMN_NAME       NUM_DISTINCT  NUM_NULLS    DENSITY HISTOGRAM
---------- ----------------- ------------ ---------- ---------- --------------------
         1 PROD_ID                     72          0 .013888889 NONE
         2 CUST_ID                   7056          0 .000141723 NONE
         3 TIME_ID                   1096          0 .000912409 NONE
         4 PROMO_ID                     4          0 .000000625 FREQUENCY
         5 QUANTITY_SOLD                1          0          1 NONE
         6 AMOUNT_SOLD                636          0   .0018217 HEIGHT BALANCED

Since we have additional statistics, lets check the details from DBA_TAB_HISTOGRAMS for this column.

SQL> select ENDPOINT_NUMBER, ENDPOINT_VALUE
  2  from dba_tab_histograms
  3  where table_name='TEST_SELECTIVITY'
  4  and   column_name='PROMO_ID'
  5  order by 1;

ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
           2074             33
          20052            350
          22297            351
         800000            999

For the Frequency Histogram, the data is stored in a cumulative manner. The Endpoint_number stores the cumulative number of rows and the Endpoint_value stores the actual column value. For example, for PROMO_ID=33, we expect 2074 rows, for PROMO_ID=350, we expect 20052-2074=17981 rows, for PROMO_ID=351, we expect 22297-20052=2245 rows and so on.. Lets run the queries for each of these PROMO_ID’s.

SQL> set autot trace
SQL> select cust_id, amount_sold, promo_id from test_selectivity where promo_id=999;

777703 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 4083831454

--------------------------------------------------------------------------------------
| Id  | Operation         | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                  |   777K|  9873K|   960   (2)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_SELECTIVITY |   777K|  9873K|   960   (2)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   1 - filter("PROMO_ID"=999)

The Optimizer Calculation for cardinality matches the actual number of rows fetched. For other values too, these were perfectly matching (see below).

SQL> select cust_id, amount_sold, promo_id from test_selectivity where promo_id=350;

17978 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 4083831454

--------------------------------------------------------------------------------------
| Id  | Operation         | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                  | 17978 |   228K|   958   (2)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_SELECTIVITY | 17978 |   228K|   958   (2)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   1 - filter("PROMO_ID"=350)

SQL> select cust_id, amount_sold, promo_id from test_selectivity where promo_id=33;

2074 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 4083831454

--------------------------------------------------------------------------------------
| Id  | Operation         | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                  |  2074 | 26962 |   958   (2)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_SELECTIVITY |  2074 | 26962 |   958   (2)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   1 - filter("PROMO_ID"=33)

SQL> select cust_id, amount_sold, promo_id from test_selectivity where promo_id=351;

2245 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 4083831454

--------------------------------------------------------------------------------------
| Id  | Operation         | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                  |  2245 | 29185 |   958   (2)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_SELECTIVITY |  2245 | 29185 |   958   (2)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   1 - filter("PROMO_ID"=351)

Perfect. The calculation in this case is very simple. Take the values from DBA_TAB_HISTOGRAMS and get the accurate CARDINALITY. However, this stands good for the values that exists and are part of the histograms. What if we run a query against a value that doesn’t exists in the table or had no rows when the stats were gathered, but have few or more rows when the queries are executed against this value ? This value will have no cumulative data into DBA_TAB_HISTOGRAMS. In such cases, will Optimizer fall back to CARDINALITY = SELECTIVITY x NUM_ROWS, where SELECTIVITY is DENSITY ? Lets check.

select column_id, column_name, num_distinct, num_nulls,
	density, histogram
from	dba_tab_columns
where	owner='SCOTT'
and	table_name='TEST_SELECTIVITY'
and	column_name='PROMO_ID';

 COLUMN_ID COLUMN_NAME       NUM_DISTINCT  NUM_NULLS    DENSITY HISTOGRAM
---------- ----------------- ------------ ---------- ---------- --------------------
         4 PROMO_ID                     4          0 .000000625 FREQUENCY

SQL> select &&optdensity * 800000 Cardinality from dual;
old   1: select &&optdensity * 800000 Cardinality from dual
new   1: select .000000625 * 800000 Cardinality from dual

CARDINALITY
-----------
         .5

If the Density is considered as a SELECTIVITY, the expected CARDINALITY will be 1 (ceil of 0.5). I will run a query with PROMO_ID=500, which doesn’t exists.

SQL> set autot trace
SQL> select * from test_selectivity where promo_id=500;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 4083831454

--------------------------------------------------------------------------------------
| Id  | Operation         | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                  |  1037 | 25925 |   958   (2)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_SELECTIVITY |  1037 | 25925 |   958   (2)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   1 - filter("PROMO_ID"=500)

Actual Number of Rows are ZERO, Optimizer Estimated as 1037 and SELECTIVITY (density) based expected was 1. ZERO v/s 1037, a huge mis-estimate. Also, we can see that with histograms, optimizer does not consider DENSITY column. How do we get the calculation ? Here, 10053 trace file comes handy. Lets generate a 10053 trace for a non-existent value and see the relevant portion that contains the calculation.

SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for TEST_SELECTIVITY[A]
  SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE
  Column (#4):
    NewDensity:0.001296, OldDensity:0.000001 BktCnt:800000.000000, PopBktCnt:800000.000000, PopValCnt:4, NDV:4
  Column (#4): PROMO_ID(NUMBER)
    AvgLen: 4 NDV: 4 Nulls: 0 <b?Density: 0.001296 Min: 33.000000 Max: 999.000000
    Histogram: Freq  #Bkts: 4  UncompBkts: 800000  EndPtVals: 4  ActualVal: yes
  Table: TEST_SELECTIVITY  Alias: A
    Card: Original: 800000.000000  Rounded: 1037  Computed: 1037.000000  Non Adjusted: 1037.000000

As per 10053, the Rounded and Computed Cardinality is 1037. The Density is 0.001296. However, the Density from DBA_TAB_COLUMNS is .000000625. There are two additional statistics : NewDensity and OldDensity. OldDensity is 0.000001, which is the rounded off value for the actual Density stored in DBA_TAB_COLUMNS i.e .000000625. What is NewDensity ? The value against this is used as a final Density to calculate the Cardinality i.e.0.001296*800000 = 1037. It seems, for a non-existent value, Optimizer computes this NewDensity and uses this as a SELECTIVITY to come out with the Expected Cardinality.

The calculation for NewDensity, in case of Frequency Histogram is 50% of the lowest number of rows in DBA_TAB_HISTOGRAMS, which is 0.5 x 2074/NUM_ROWS = 0.00129625. So, NewDensity becomes the SELECTIVITY and CARDINALITY is SELECTIVITY x NUM_ROWS, 0.00129625 x 800000 = 1037(see below).

select promo_Id, count(*) from test_selectivity group by promo_id order by 2;

  PROMO_ID   COUNT(*)
---------- ----------
        33       2074  select 0.5*2074/800000 NewDensity from dual;

NEWDENSITY
----------
 .00129625
SQL> select round(&&new_density*800000,0) from dual;
old   1: select round(&&new_density*800000,0) from dual
new   1: select round( .00129625*800000,0) from dual

ROUND(.00129625*800000,0)
-------------------------
                     1037

Before we get into more details, let us check the Height Balanced Histograms. We have a Height Balanced Histogram on Amount_Sold Column.

SQL> select column_id, column_name, num_distinct, num_nulls,
  2                  density, histogram
  3  from       dba_tab_columns
  4  where      owner='SCOTT'
  5  and        table_name='TEST_SELECTIVITY'
  6  and             column_name='AMOUNT_SOLD'
  7  order by 1;

 COLUMN_ID COLUMN_NAME       NUM_DISTINCT  NUM_NULLS    DENSITY HISTOGRAM
---------- ----------------- ------------ ---------- ---------- --------------------
         6 AMOUNT_SOLD                636          0   .0018217 HEIGHT BALANCED

We have 636 Distinct Values for this column and the maximum number of Buckets are 254. The way these histograms are generated is that the number of rows in the table is equally divided into 254 buckets. The Maximum value for each of the bucket is calculated and then the buckets are compressed, if a value spans across more than 1 Bucket. I executed a query, which is similar to the query executed by the Optimizer during the statistics gathering (see below).

SQL> select bucket, count(*), min(amount_sold) min_amt, max(amount_sold) max_amt from (
  2  select amount_sold, ntile(254) over (order by amount_sold) bucket
  3  from       test_selectivity
  4  order by amount_sold)
  5  group by bucket
  6  order by 1;

    BUCKET   COUNT(*)    MIN_AMT    MAX_AMT
---------- ---------- ---------- ----------
         1       3150          6          7
         2       3150          7          7
         3       3150          7          7 <-- Popular Value (3 Buckets)
         4       3150          7          8
         5       3150          8          8
         6       3150          8          8
         7       3150          8          8
         8       3150          8          8
         9       3150          8          8
        10       3150          8          8
        11       3150          8          8 <-- Popular Value (8 Buckets)
        12       3150          8          9
        13       3150          9          9
        14       3150          9          9
        15       3150          9          9
        16       3150          9          9
        17       3150          9          9
        18       3150          9          9
        19       3150          9          9
        20       3150          9          9
        21       3150          9          9 <-- Popular Value (10 Buckets)
        22       3150          9         10
        23       3150         10         10
        24       3150         10         10
        25       3150         10         10
        26       3150         10         10
        27       3150         10         10
        28       3150         10         10
        29       3150         10         10
        30       3150         10         10
        31       3150         10         10
        32       3150         10         10
        33       3150         10         11
        34       3150         11         11
        35       3150         11         11
        36       3150         11         11
        37       3150         11         11
        38       3150         11         11
        39       3150         11         11
        40       3150         11         11
        41       3150         11         11
        42       3150         11         12
        43       3150         12         12
        44       3150         12         12
        45       3150         12         12
        46       3150         12         13
        47       3150         13         13
        48       3150         13         13
        49       3150         13         13
        50       3150         13         13
        51       3150         13         13
        52       3150         13         13
        53       3150         13         13
        54       3150         13         14
        55       3150         14         14
        56       3150         14         14
        57       3150         14         14
        58       3150         14         15 <-- Non-Popular (Only 1 Bucket)
        59       3150         15         16
        60       3150         16         16
        61       3150         16         17
        62       3150         17         17
        63       3150         17         17
        64       3150         17         17
        65       3150         17         18 <-- Non-Popular (1 Bucket)
        66       3150         18         19
        67       3150         19         19
        68       3150         19         19
        69       3150         19         20
        70       3150         20         20
        71       3150         20         21
        72       3150         21         21
        73       3150         21         21
        74       3150         21         21
        75       3150         21         21
        76       3150         21         21
        77       3150         21         22
        78       3150         22         22
        79       3150         22         22
        80       3150         22         22
        81       3150         22         22
        82       3150         22         23
        83       3150         23         23
        84       3150         23         23
        85       3150         23         23
        86       3150         23         23
        87       3150         23         24
        88       3150         24         24
        89       3150         24         24
        90       3150         24         24
        91       3150         24         24
        92       3150         24         25
        93       3150         25         25
        94       3150         25         25
        95       3150         25         25
        96       3150         25         25
        97       3150         25         26
        98       3150         26         26
        99       3150         26         26
       100       3150         26         26
       101       3150         26         27
       102       3150         27         27
       103       3150         27         28
       104       3150         28         28
       105       3150         28         28
       106       3150         28         28
       107       3150         28         29
       108       3150         29         29
       109       3150         29         29
       110       3150         29         30
       111       3150         30         30
       112       3150         30         30
       113       3150         30         30
       114       3150         30         30
       115       3150         30         31
       116       3150         31         31
       117       3150         31         31
       118       3150         31         32
       119       3150         32         32
       120       3150         32         33
       121       3150         33         33
       122       3150         33         33
       123       3150         33         34
       124       3150         34         34
       125       3150         34         34
       126       3150         34         35
       127       3150         35         36
       128       3150         36         36
       129       3150         36         38
       130       3150         38         38
       131       3150         38         38
       132       3150         38         39
       133       3150         39         39
       134       3150         39         39
       135       3150         39         40
       136       3150         40         40
       137       3150         40         41
       138       3150         41         41
       139       3150         41         42
       140       3150         42         42
       141       3150         42         43
       142       3150         43         43
       143       3150         43         45
       144       3150         45         45
       145       3150         45         46
       146       3150         46         46
       147       3150         46         46
       148       3150         46         46
       149       3150         46         46
       150       3150         46         46
       151       3150         46         47
       152       3150         47         47
       153       3150         47         47
       154       3150         47         47
       155       3149         47         47
       156       3149         47         47
       157       3149         47         47
       158       3149         47         47
       159       3149         47         48
       160       3149         48         48
       161       3149         48         48
       162       3149         48         48
       163       3149         48         48
       164       3149         48         48
       165       3149         48         48
       166       3149         48         48
       167       3149         48         49
       168       3149         49         49
       169       3149         49         49
       170       3149         49         49
       171       3149         49         49
       172       3149         49         49
       173       3149         49         49
       174       3149         49         49
       175       3149         49         50
       176       3149         50         50
       177       3149         50         51
       178       3149         51         51
       179       3149         51         51
       180       3149         51         51
       181       3149         51         51
       182       3149         51         52
       183       3149         52         52
       184       3149         52         52
       185       3149         52         53
       186       3149         53         53
       187       3149         53         53
       188       3149         53         54
       189       3149         54         54
       190       3149         54         54
       191       3149         54         55
       192       3149         55         56
       193       3149         56         56
       194       3149         56         57
       195       3149         57         57
       196       3149         57         58
       197       3149         58         58
       198       3149         58         59
       199       3149         59         60
       200       3149         60         60
       201       3149         60         62
       202       3149         62         62
       203       3149         62         63
       204       3149         63         63
       205       3149         63         64
       206       3149         64         64
       207       3149         64         65
       208       3149         65         66
       209       3149         66         70
       210       3149         70         72
       211       3149         72         74
       212       3149         74         79
       213       3149         79         90
       214       3149         90         94
       215       3149         94         97
       216       3149         97        101
       217       3149        101        113
       218       3149        113        115
       219       3149        115        117
       220       3149        117        123
       221       3149        123        125
       222       3149        125        127
       223       3149        127        131
       224       3149        131        136
       225       3149        136        151
       226       3149        151        158
       227       3149        158        163
       228       3149        163        170
       229       3149        170        180
       230       3149        180        199
       231       3149        199        203
       232       3149        203        208
       233       3149        208        211
       234       3149        211        214
       235       3149        214        225
       236       3149        225        302
       237       3149        302        307
       238       3149        307        531
       239       3149        531        552
       240       3149        552        594
       241       3149        594        602
       242       3149        602        629
       243       3149        629        900
       244       3149        900        973
       245       3149        973       1016
       246       3149       1016       1054
       247       3149       1054       1093
       248       3149       1093       1192
       249       3149       1192       1237
       250       3149       1237       1301
       251       3149       1301       1463
       252       3149       1463       1546
       253       3149       1546       1639
       254       3149       1639       1783

254 rows selected.

Total Number of rows in this table is 800000 divided by 254 Buckets is 3149 Rows. From the output above, it can be seen that each bucket has 3149 rows and there are some popular and non-popular values. For example : 7.8.9 are Popular (there are other popular values as well) and 15,18 are Non-Popular (there are other non-popular values as well). Popular values are values spanning across 2 or more Buckets. Non-Popular Values are values with 1 or less bucket. Finally, when the histogram is generated, the popular buckets are compressed to save dictionary space and the resultant output from DBA_TAB_HISTOGRAM is as under.

SQL> select ENDPOINT_NUMBER, ENDPOINT_VALUE
  2  from dba_tab_histograms
  3  where table_name='TEST_SELECTIVITY'
  4  and   column_name='AMOUNT_SOLD'
  5  order by 1;

ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
              0              6 <-- Popular Value
              3              7 <-- Popular Value (3-0=3 Buckets)
             11              8 <-- Popular Value (11-3=8 Buckets)
             21              9 <-- Popular Value (21-11=10 Buckets)
             32             10
             41             11
             45             12
             53             13
             57             14
             58             15 <-- Non-Popular Value (58-57=1 Bucket)
             60             16
             64             17
             65             18
             68             19
             70             20
             76             21
             81             22
             86             23
             91             24
             96             25
            100             26
            102             27
            106             28
            109             29
            114             30
            117             31
            119             32
            122             33
            125             34
            126             35
            128             36
            131             38
            134             39
            136             40
            138             41
            140             42
            142             43
            144             45
            150             46
            158             47
            166             48
            174             49
            176             50
            181             51
            184             52
            187             53
            190             54
            191             55
            193             56
            195             57
            197             58
            198             59
            200             60
            202             62
            204             63
            206             64
            207             65
            208             66
            209             70
            210             72
            211             74
            212             79
            213             90
            214             94
            215             97
            216            101
            217            113
            218            115
            219            117
            220            123
            221            125
            222            127
            223            131
            224            136
            225            151
            226            158
            227            163
            228            170
            229            180
            230            199
            231            203
            232            208
            233            211
            234            214
            235            225
            236            302
            237            307
            238            531
            239            552
            240            594
            241            602
            242            629
            243            895
            244            973
            245           1016
            246           1054
            247           1093
            248           1192
            249           1237
            250           1301
            251           1463
            252           1546
            253           1639
            254           1783

104 rows selected.

254 Buckets are compressed into 104 Buckets. The CARDINALITY calculations, in these cases are very simple. For Popular Value, it is 3149 (number of rows in each bucket) multiplied by number of Buckets. Let us run the queries and see the results.

## For 2 Buckets

SQL> select * from test_selectivity where amount_sold=56;

6204 rows selected.

Elapsed: 00:00:00.16

Execution Plan
----------------------------------------------------------
Plan hash value: 4083831454

--------------------------------------------------------------------------------------
| Id  | Operation         | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                  |  6299 |   153K|   960   (2)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_SELECTIVITY |  6299 |   153K|   960   (2)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   1 - filter("AMOUNT_SOLD"=56)

## For 10 Buckets

SQL> select * from test_selectivity where amount_sold=9;

31964 rows selected.

Elapsed: 00:00:00.64

Execution Plan
----------------------------------------------------------
Plan hash value: 4083831454

--------------------------------------------------------------------------------------
| Id  | Operation         | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                  | 31496 |   768K|   960   (2)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_SELECTIVITY | 31496 |   768K|   960   (2)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   1 - filter("AMOUNT_SOLD"=9)

For Non-Popular or Non-Existent values.
Will it be DENSITY x NUM_ROWS ? i.e. 0.0018217 x 800000 = 1457. Lets run the query to check this.

SQL> select column_id, column_name, num_distinct, num_nulls,
  2                  density, histogram
  3  from       dba_tab_columns
  4  where      owner='SCOTT'
  5  and        table_name='TEST_SELECTIVITY'
  6  and             column_name='AMOUNT_SOLD'
  7  order by 1;

 COLUMN_ID COLUMN_NAME       NUM_DISTINCT  NUM_NULLS    DENSITY HISTOGRAM
---------- ----------------- ------------ ---------- ---------- --------------------
         6 AMOUNT_SOLD                636          0   .0018217 HEIGHT BALANCED

SQL> select &&densit*800000 from dual;
old   1: select &&densit*800000 from dual
new   1: select   .0018217*800000 from dual

.0018217*800000
---------------
        1457.36

The Cardinality for non-popular values, as can be seen after executing the queries is as under.

SQL> select * from test_selectivity where amount_sold=55;

3372 rows selected.

Elapsed: 00:00:00.11

Execution Plan
----------------------------------------------------------
Plan hash value: 4083831454

--------------------------------------------------------------------------------------
| Id  | Operation         | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                  |   285 |  7125 |   960   (2)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_SELECTIVITY |   285 |  7125 |   960   (2)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   1 - filter("AMOUNT_SOLD"=55)

Value 55 is a Non-Popular Value. We expected the expected cardinality as 1457, but it is 285. Let us generate a 10053 trace for this and check the trace.

SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for TEST_SELECTIVITY[A]
  SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE
  Column (#6):
    NewDensity:0.000356, OldDensity:0.001822 BktCnt:254.000000, PopBktCnt:201.000000, PopValCnt:50, NDV:636
  Column (#6): AMOUNT_SOLD(NUMBER)
    AvgLen: 4 NDV: 636 Nulls: 0 Density: 0.000356 Min: 6.000000 Max: 1783.000000
    Histogram: HtBal  #Bkts: 254  UncompBkts: 254  EndPtVals: 104  ActualVal: yes
  Table: TEST_SELECTIVITY  Alias: A
    Card: Original: 800000.000000  Rounded: 285  Computed: 284.862003  Non Adjusted: 284.862003

We see a similar pattern here. NewDensity is used as a SELECTIVITY to compute the CARDINALITY (0.000356×800000=285). How is this NewDensity calculated for Height Balanced Histograms ? It is computed as :

[(NPBKTCNT)/(BKTCNT * (NDV – POPVALCNT))]

From the 10053 trace, we can get the values of each of these. BKTCNT (Bucket Count) is 254, POPBKCNT (Popular Bucket Count) are 201. This makes NPBKCNT as 254-201=53. NDV (Number of Distinct Values is 636 and POPVALCNT (Popular Value Counts) are 50. Applying these values, we get [53/(254 *(636-50))] = .000356078

SQL> select (53/(254*(636-50))) newdensity from dual;

NEWDENSITY
----------
.000356078

SQL> select ceil(&&ndensit * 800000) from dual;
old   1: select ceil(&&ndensit * 800000) from dual
new   1: select ceil(.000356078 * 800000) from dual

CEIL(.000356078*800000)
-----------------------
                    285

NewDensity, I assume, was introduced in 11g, but is backported in 10204 as well. This was introduced as a Bug Fix. However, in our case, this is actually causing a mis-estimation. How do we disable this fix? The solution is disabling the fix_control 5483301 and setting _optimizer_enable_density_improvements to FALSE. Both these needs to be set together. We will set this at the session level and see the results for a Non-Existent value in a Frequency Histogram and a Non-Popular value in a Height Balanced Histogram.

SQL> alter session set "_fix_control"='5483301:off';
SQL> alter session set "_optimizer_enable_density_improvements"=false;

SQL> set autot trace
SQL> select * from test_selectivity where promo_id=500;
no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 4083831454

--------------------------------------------------------------------------------------
| Id  | Operation         | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                  |     1 |    25 |   958   (2)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_SELECTIVITY |     1 |    25 |   958   (2)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   1 - filter("PROMO_ID"=500)

SQL> select * from test_selectivity where amount_sold=55;

3372 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 4083831454

--------------------------------------------------------------------------------------
| Id  | Operation         | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                  |  1457 | 36425 |   960   (2)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_SELECTIVITY |  1457 | 36425 |   960   (2)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   1 - filter("AMOUNT_SOLD"=55)

With these 2 settings, the Optimizer falls back to its Original Calculation of DENSITY x NUM_ROWS for Cardinality calculation.

It had been a long posting, however, I felt this to be necessary because many people still don’t know about this NewDensity. I was myself surprised when I was working on a real life issue and came across this mis-estimation. A 10053 trace revealed NewDensity, which was new for me as well. For the real life example, see below.

select count(*) from nca.s_p_attributes a1
WHERE   a1.value='olwassenen';

    COUNT(*)
------------
      591168

SQL> select plan_table_output from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------
SQL_ID	79dfpvydpk710, child number 0
-------------------------------------
select count(*) from nca.s_p_attributes a1 WHERE
a1.value='olwassenen’
----------------------------------------------------------
| Id  | Operation	        | Name 	        | Rows	| 
----------------------------------------------------------
|   0 | SELECT STATEMENT 	|		|	|
|   1 |  SORT AGGREGATE  	|		|      1| 
|*  2 |   INDEX SKIP SCAN	| SP_P_IND3     |      8| 
----------------------------------------------------------

The estimated and actual is way out. 8 Rows v/s 591168 Rows. At this point, I requested a 10053 trace, which pointed me to NewDensity value. The issue was resolved by way of disabling the fix_control and setting _optimizer_enable_density_improvements to FALSE.

Optimizer – Part II (Cardinality – Actuals v/s Assumed)

This is in continuation to my previous post, Optimizer – Part I of this series. In Part I, we covered the mathematical formulas used by the Optimizer. In this post, we shall see these calculations in action. For this, we will create a sample table and use this through out to see optimizer behaviour. So, lets create our table TEST_SELECTIVITY from SALES table under SH Schema. It is very critical to know your data. Therefore, while creating the table, I have manipulated the data to demonstrate the behaviour against the different data distribution.

exec dbms_random.seed(0);

create table test_selectivity as
select 	a.prod_id, 
	a.cust_id,
        trunc(sysdate)-round(dbms_random.value(0,1095),0) time_id,
        a.promo_id, 
        a.quantity_sold,
        round(a.amount_sold,0) amount_sold
from 	sh.sales a
where 	rownum<=8e5;

The table has 800k rows. The columns of interest for our demonstrations are TIME_ID, which is populated with 3 years of data, PROMO_ID and AMOUNT_SOLD. Once the table is created, Optimizer Statistics are automatically gathered on the table (Oracle 11g and above). Let’s query all the relevant statistics.

select table_name, num_rows, blocks from dba_tables
where table_name='TEST_SELECTIVITY';

TABLE_NAME                       NUM_ROWS     BLOCKS
------------------------------ ---------- ----------
TEST_SELECTIVITY                   800000       3478

select 	column_name, 
	num_distinct, 
	num_nulls, 
	density,
	histogram
from	dba_tab_columns
where	owner='SCOTT'
and	table_name='TEST_SELECTIVITY'
order by 1;

COLUMN_NAME        NUM_DISTINCT  NUM_NULLS    DENSITY HISTOGRAM
------------------ ------------ ---------- ---------- --------------------
AMOUNT_SOLD                 636          0 .001572327 NONE
CUST_ID                    7056          0 .000141723 NONE
PROD_ID                      72          0 .013888889 NONE
PROMO_ID                      4          0        .25 NONE
QUANTITY_SOLD                 1          0          1 NONE
TIME_ID                    1096          0 .000912409 NONE

6 rows selected.

As mentioned earlier, for our demonstration, we will query the table on the three columns. AMOUNT_SOLD has 636 Distinct Values, PROMO_ID has 4 distinct values and TIME_ID has 1096 Distinct Values. In my previous blog (Part I), we discussed about SELECTIVITY, which in this case is 1/NDV for each of the columns in the table. Selectivity is very critical, as it drives the Access Path and is used to calculate the Cardinality, which drives the Access Order. Therefore, accurate calculation of Selectivity is very critical for the Optimizer.

Now, let us run our queries against each of these three columns and check the Optimizer calculation of Expected Rows against the Actual Rows. The queries will be on EQUALITY, LESS THAN and GREATER THAN predicated. Please refer to my previous blog for the calculation of SELECTIVITY for each of these predicate types. The effective CARDINALITY = SELECTIVITY X NUM_ROWS. Here we go with the first column (TIME_ID).

First lets check the Low_Value and High_Value for the TIME_ID column. These values are used for Range Predicate queries to calculate the Available Range (High_Value – Low_Value).

with function get_date(n_raw in raw) return date
as
	l_date        date;
begin
	dbms_stats.convert_raw_value(n_raw,l_date);
	return l_date;
end;
select	column_name,
	get_date(low_value) lo_value,
	get_date(high_value) hi_value
from	dba_tab_columns
where	owner='SCOTT'
and	table_name='TEST_SELECTIVITY'
and	data_type='DATE'
order by 1;
/

COLUMN_NAME        LO_VALUE             HI_VALUE
------------------ -------------------- --------------------
TIME_ID            13-AUG-2013 00:00:00 12-AUG-2016 00:00:00

Function in WITH clause is a 12c new feature. For Oracle Database versions prior to 12c, create the function using CREATE FUNCTION clause and then used it in the query.

For the Equality Predicate, SELECTIVITY is 1/Num_Distinct and CARDINALITY = SELECTIVITY X NUM_ROWS. After calculating these, we will then run the query on this table to validate the actual number or rows.

## EQUALITY PREDICATE
SQL> select 1/&&ndv Selectivity from dual;
old   1: select 1/&&ndv Selectivity from dual
new   1: select 1/      1096 Selectivity from dual

SELECTIVITY
-----------
 .000912409

SQL> select round(&&selective*800000,0) cardinality from dual;
old   1: select round(&&selective*800000,0) cardinality from dual
new   1: select round(.000912409*800000,0) cardinality from dual

CARDINALITY
-----------
        730

SQL> set autot trace
SQL> select cust_id, amount_sold, promo_id from test_selectivity
  2  where time_id=to_date('11-DEC-2015','DD-MON-YYYY');

704 rows selected.

Elapsed: 00:00:00.99

Execution Plan
----------------------------------------------------------
Plan hash value: 4083831454

--------------------------------------------------------------------------------------
| Id  | Operation         | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                  |   730 | 15330 |  1088  (20)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_SELECTIVITY |   730 | 15330 |  1088  (20)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   1 - filter("TIME_ID"=TO_DATE(' 2015-12-11 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))


SQL> set autot off

Assumption – 730 Rows and Actual – 704 Rows (Nearly Accurate).

Next, we run a query with Less Than Predicate. The SELECTIVITY in this case will be Required_Range/Available_Range (see part I for the exact formula – Required_Range will be computed as Required_Date – Low_Value). CARDINALITY is again SELECTIVITY x NUM_ROWS

## LESS THAN PREDICATE

## Required_Range - 850 Days
SQL> select to_date('11-DEC-2015','DD-MON-YYYY')-to_date('&&min_value','DD-MON-YYYY HH24:MI:SS') req_range
  2  from       dual;
old   1: select to_date('11-DEC-2015','DD-MON-YYYY')-to_date('&&min_value','DD-MON-YYYY HH24:MI:SS') req_range
new   1: select to_date('11-DEC-2015','DD-MON-YYYY')-to_date('13-AUG-2013 00:00:00','DD-MON-YYYY HH24:MI:SS') req_range

 REQ_RANGE
----------
       850

## Available_Range - 1095 Days
SQL> select to_date('&&max_value','DD-MON-YYYY HH24:MI:SS')-to_date('&&min_value','DD-MON-YYYY HH24:MI:SS') avl_range
  2  from       dual;
old   1: select to_date('&&max_value','DD-MON-YYYY HH24:MI:SS')-to_date('&&min_value','DD-MON-YYYY HH24:MI:SS') avl_range
new   1: select to_date('12-AUG-2016 00:00:00','DD-MON-YYYY HH24:MI:SS')-to_date('13-AUG-2013 00:00:00','DD-MON-YYYY HH24:MI:SS') avl_range

 AVL_RANGE
----------
      1095


## Selectivity - Required_Range/Available_Range
SQL> select &&r_range/&&a_range Selectivity from dual;
old   1: select &&r_range/&&a_range Selectivity from dual
new   1: select        850/      1095 Selectivity from dual

SELECTIVITY
-----------
 .776255708

## Assumed Cardinality
SQL> select round(&&selective*800000,0) cardinality from dual;
old   1: select round(&&selective*800000,0) cardinality from dual
new   1: select round(.776255708*800000,0) cardinality from dual

CARDINALITY
-----------
     621005


SQL> select cust_id, amount_sold, promo_id from test_selectivity
  2  where time_id<to_date('11-DEC-2015','DD-MON-YYYY');

620764 rows selected.

Elapsed: 00:00:06.06

Execution Plan
----------------------------------------------------------
Plan hash value: 4083831454

--------------------------------------------------------------------------------------
| Id  | Operation         | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                  |   621K|    12M|  1125  (23)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_SELECTIVITY |   621K|    12M|  1125  (23)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   1 - filter("TIME_ID"<TO_DATE(' 2015-12-11 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))

Assumption – 621k Rows and Actual – 620k Rows (Nearly Accurate).

Next, the query with Greater Than Predicate and the SELECTIVITY will be again Required_Range/Available_Range. The difference, in this case, will be that High_Value will be used to calculate the Required_Range.

## GREATER THAN PREDICATE

## Required_Range - 245 Days
SQL> select to_date('&&max_value','DD-MON-YYYY HH24:MI:SS')-to_date('11-DEC-2015','DD-MON-YYYY') req_range
  2  from       dual;
old   1: select to_date('&&max_value','DD-MON-YYYY HH24:MI:SS')-to_date('11-DEC-2015','DD-MON-YYYY') req_range
new   1: select to_date('12-AUG-2016 00:00:00','DD-MON-YYYY HH24:MI:SS')-to_date('11-DEC-2015','DD-MON-YYYY') req_range

 REQ_RANGE
----------
       245

## Available_Range - 1095 Days
SQL> select to_date('&&max_value','DD-MON-YYYY HH24:MI:SS')-to_date('&&min_value','DD-MON-YYYY HH24:MI:SS') avl_range
  2  from       dual;
old   1: select to_date('&&max_value','DD-MON-YYYY HH24:MI:SS')-to_date('&&min_value','DD-MON-YYYY HH24:MI:SS') avl_range
new   1: select to_date('12-AUG-2016 00:00:00','DD-MON-YYYY HH24:MI:SS')-to_date('13-AUG-2013 00:00:00','DD-MON-YYYY HH24:MI:SS') avl_range

 AVL_RANGE
----------
      1095

## Selectivity - Required_Range/Available_Range
SQL> select &&r_range/&&a_range Selectivity from dual;
old   1: select &&r_range/&&a_range Selectivity from dual
new   1: select        245/      1095 Selectivity from dual

SELECTIVITY
-----------
 .223744292

## Assumed Cardinality 
SQL> select round(&&selective*800000,0) cardinality from dual;
old   1: select round(&&selective*800000,0) cardinality from dual
new   1: select round(.223744292*800000,0) cardinality from dual

CARDINALITY
-----------
     178995

SQL> set autot trace
SQL> select cust_id, amount_sold, promo_id from test_selectivity
  2  where time_id>to_date('11-DEC-2015','DD-MON-YYYY');

178532 rows selected.

Elapsed: 00:00:02.01

Execution Plan
----------------------------------------------------------
Plan hash value: 4083831454

--------------------------------------------------------------------------------------
| Id  | Operation         | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                  |   178K|  3670K|  1099  (21)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_SELECTIVITY |   178K|  3670K|  1099  (21)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   1 - filter("TIME_ID">TO_DATE(' 2015-12-11 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))

Assumption – 178k Rows and Actual – 178k Rows (Accurate).

For Time_ID Column, the expected and actual cardinality were nearly accurate. Let’s shift our focus to the other column – AMOUNT_SOLD. We will run similar three queries – Equality, Less Than and Greater Than.

Before we execute the queries against this column, lets check the statistics (Density, Low_Value and High_Value).

## FOR AMOUNT_SOLD COLUMN

select	column_name, 
		num_distinct, 
		num_nulls,
		density, 
		histogram
from	dba_tab_columns
where	owner='SCOTT'
and		table_name='TEST_SELECTIVITY'
and		column_name='AMOUNT_SOLD';

COLUMN_NAME        NUM_DISTINCT  NUM_NULLS    DENSITY HISTOGRAM
------------------ ------------ ---------- ---------- --------------------
AMOUNT_SOLD                 636          0 .001572327 NONE

with function get_number(n_raw in raw) return number
as
	l_number        number;
begin
	dbms_stats.convert_raw_value(n_raw,l_number);
	return l_number;
end;
select	column_name,
	get_number(low_value) lo_value,
	get_number(high_value) hi_value
from	dba_tab_columns
where	owner='SCOTT'
and	table_name='TEST_SELECTIVITY'
and	column_name='AMOUNT_SOLD';
/

COLUMN_NAME          LO_VALUE   HI_VALUE
------------------ ---------- ----------
AMOUNT_SOLD                 6       1783

All the calculation are same for this column as well.

## EQUALITY PREDICATE

SQL> select 1/&&ndv Selectivity from dual;
old   1: select 1/&&ndv Selectivity from dual
new   1: select 1/       636 Selectivity from dual

SELECTIVITY
-----------
 .001572327

SQL> select round(&&selective*800000,0) cardinality from dual;
old   1: select round(&&selective*800000,0) cardinality from dual
new   1: select round(.001572327*800000,0) cardinality from dual

CARDINALITY
-----------
       1258

SQL> set autot trace
SQL> select cust_id, amount_sold, promo_id from test_selectivity
  2  where amount_sold=1500;

122 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 4083831454

--------------------------------------------------------------------------------------
| Id  | Operation         | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                  |  1258 | 16354 |  1136  (24)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_SELECTIVITY |  1258 | 16354 |  1136  (24)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   1 - filter("AMOUNT_SOLD"=1500)

Assumption – 1258 Rows and Actual – 122 Rows (Out by 10 times).

## LESS THAN PREDICATE

## Required_Range
SQL> select (1500-&&min_value) req_range from dual;
old   1: select (1500-&&min_value) req_range from dual
new   1: select (1500-         6) req_range from dual

 REQ_RANGE
----------
      1494

## Available_Range
SQL> select (&&max_value-&&min_value) avl_range from dual;
old   1: select (&&max_value-&&min_value) avl_range from dual
new   1: select (      1783-         6) avl_range from dual

 AVL_RANGE
----------
      1777


## Selectivity - Required_Range/Available_Range
SQL> select &&r_range/&&a_range Selectivity from dual;
old   1: select &&r_range/&&a_range Selectivity from dual
new   1: select       1494/      1777 Selectivity from dual

SELECTIVITY
-----------
 .840742825

## Assumed Cardinality
SQL> select round(&&selective*800000,0) Cardinality from dual;
old   1: select round(&&selective*800000,0) Cardinality from dual
new   1: select round(.840742825*800000,0) Cardinality from dual

CARDINALITY
-----------
     672594

SQL> set autot trace
SQL> select cust_id, amount_sold, promo_id from test_selectivity
  2  where amount_sold<1500;

791950 rows selected.

Elapsed: 00:00:07.57

Execution Plan
----------------------------------------------------------
Plan hash value: 4083831454

--------------------------------------------------------------------------------------
| Id  | Operation         | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                  |   672K|  8538K|  1136  (24)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_SELECTIVITY |   672K|  8538K|  1136  (24)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   1 - filter("AMOUNT_SOLD"<1500)

Assumption – 672k Rows and Actual – 791k Rows (Inaccurate).

## GREATER THAN PREDICATE


## Required_Range
SQL> select (&&max_value-1500) req_range from dual;
old   1: select (&&max_value-1500) req_range from dual
new   1: select (      1783-1500) req_range from dual

 REQ_RANGE
----------
       283

## Available_range
SQL> select (&&max_value-&&min_value) avl_range from dual;
old   1: select (&&max_value-&&min_value) avl_range from dual
new   1: select (      1783-         6) avl_range from dual

 AVL_RANGE
----------
      1777

## Selectivity - Required_Range/Available_Range
SQL> select &&r_range/&&a_range Selectivity from dual;
old   1: select &&r_range/&&a_range Selectivity from dual
new   1: select        283/      1777 Selectivity from dual

SELECTIVITY
-----------
 .159257175

## Assumed Cardinality
SQL> select round(&&selective*800000,0) Cardinality from dual;
old   1: select round(&&selective*800000,0) Cardinality from dual
new   1: select round(.159257175*800000,0) Cardinality from dual

CARDINALITY
-----------
     127406

SQL> select cust_id, amount_sold, promo_id from test_selectivity
  2  where amount_sold>1500;

7928 rows selected.

Elapsed: 00:00:00.12

Execution Plan
----------------------------------------------------------
Plan hash value: 4083831454

--------------------------------------------------------------------------------------
| Id  | Operation         | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                  |   127K|  1617K|  1136  (24)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_SELECTIVITY |   127K|  1617K|  1136  (24)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   1 - filter("AMOUNT_SOLD">1500)

Assumption – 127k Rows and Actual – 7928 Rows (Significantly Out).

Finally, we move to our last column of interest i.e. PROMO_ID. The distinct values in this column are 4 and the data distribution is as under:

## FOR PROMO_ID Column

select	promo_id, 
	count(*) cnt,
	round(ratio_to_report(count(*)) over()*100,2) "%age" 
from	test_selectivity
group by promo_id
order by 2;

  PROMO_ID        CNT       %age
---------- ---------- ----------
        33       2074        .26
       351       2245        .28
       350      17978       2.25
       999     777703      97.21

select	column_name, 
	num_distinct, 
	num_nulls, 
	density,
	histogram
from	dba_tab_columns
where	owner='SCOTT'
and	table_name='TEST_SELECTIVITY'
and	column_name='PROMO_ID'
order by 1;

COLUMN_NAME        NUM_DISTINCT  NUM_NULLS    DENSITY HISTOGRAM
------------------ ------------ ---------- ---------- --------------------
PROMO_ID                      4          0        .25 NONE

For this column, the we will execute 4 queries and each of these will be EQUALITY Predicates. For Equality Predicates, the calculation for SELECTIVITY is simple, which is 1/NDV or DENSITY. From DBA_TAB_COLUMNS, we can see that the DENSITY for this column is 0.25 (1/4).

## FOR PROMO_ID

## Selectivity
SQL> select 1/&&ndv selectivity from dual;
old   1: select 1/&&ndv selectivity from dual
new   1: select 1/         4 selectivity from dual

SELECTIVITY
-----------
        .25

## Assumed Cardinality
SQL> select round(&&selective*800000,0) cardinality from dual;
old   1: select round(&&selective*800000,0) cardinality from dual
new   1: select round(       .25*800000,0) cardinality from dual

CARDINALITY
-----------
     200000

## VALUE 999
SQL> select cust_id, amount_sold, promo_id from test_selectivity where promo_id=999;

777703 rows selected.

Elapsed: 00:00:06.89

Execution Plan
----------------------------------------------------------
Plan hash value: 4083831454

--------------------------------------------------------------------------------------
| Id  | Operation         | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                  |   200K|  2539K|  1112  (22)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_SELECTIVITY |   200K|  2539K|  1112  (22)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   1 - filter("PROMO_ID"=999)

Assumption – 200k Rows and Actual – 777k Rows (Out by 4 time).

## Value 350
SQL> select cust_id, amount_sold, promo_id from test_selectivity where promo_id=350;

17978 rows selected.

Elapsed: 00:00:00.27

Execution Plan
----------------------------------------------------------
Plan hash value: 4083831454

--------------------------------------------------------------------------------------
| Id  | Operation         | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                  |   200K|  2539K|  1112  (22)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_SELECTIVITY |   200K|  2539K|  1112  (22)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   1 - filter("PROMO_ID"=350)

Assumption – 200k Rows and Actual – 17978 Rows (Significantly Out).

## VALUE 33
SQL> select cust_id, amount_sold, promo_id from test_selectivity where promo_id=33;

2074 rows selected.

Elapsed: 00:00:00.22

Execution Plan
----------------------------------------------------------
Plan hash value: 4083831454

--------------------------------------------------------------------------------------
| Id  | Operation         | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                  |   200K|  2539K|  1112  (22)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_SELECTIVITY |   200K|  2539K|  1112  (22)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   1 - filter("PROMO_ID"=33)

Assumption – 200k Rows and Actual – 2074 Rows (Significantly Out).

## VALUE 351
SQL> select cust_id, amount_sold, promo_id from test_selectivity where promo_id=351;

2245 rows selected.

Elapsed: 00:00:00.11

Execution Plan
----------------------------------------------------------
Plan hash value: 4083831454

--------------------------------------------------------------------------------------
| Id  | Operation         | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                  |   200K|  2539K|  1112  (22)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_SELECTIVITY |   200K|  2539K|  1112  (22)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   1 - filter("PROMO_ID"=351)

Assumption – 200k Rows and Actual – 2245 Rows (Significantly Out).

Summary – So far

  • TIME_ID – Assumptions v/s Actuals 
  • AMOUNT_SOLD – Assumptions v/s Actuals Χ
  • PROMO_ID – Assumptions v/s Actuals Χ

Let’s make a simple change in the TIME_ID column. For this, I will create another table, which will be a replica of TEST_SELECTIVITY. We will make this change in the new table, so that, we do not disturb the Original Table.

create table test_selectivity_m as
select * from test_selectivity;

update test_selectivity_m set time_id=to_date('31-Dec-2050','DD-MON-YYYY')
where rownum=1;

exec dbms_stats.gather_table_stats(user,'TEST_SELECTIVITY_M');

select table_name, num_rows, blocks, partitioned from dba_tables
where table_name in ('TEST_SELECTIVITY','TEST_SELECTIVITY_M');

TABLE_NAME                       NUM_ROWS     BLOCKS PAR
------------------------------ ---------- ---------- ---
TEST_SELECTIVITY_M                 800000       3478 NO
TEST_SELECTIVITY                   800000       3478 NO

I created another table TEST_SELECTIVITY_M and updated a single row with a future date i.e.31st December 2050. Lets see, whether this minor change has any impact on the Optimizer Assumptions v/s Actuals.

with function get_date(n_raw in raw) return date
as
	l_date        date;
begin
	dbms_stats.convert_raw_value(n_raw,l_date);
	return l_date;
end;
select	column_name,
	num_distinct,
	num_nulls,
	density,
	histogram,
	get_date(low_value) lo_value,
	get_date(high_value) hi_value
from	dba_tab_columns
where	owner='SCOTT'
and	table_name='TEST_SELECTIVITY_M'
and	data_type='DATE'
order by 1;
/
COLUMN_NAME                    NUM_DISTINCT  NUM_NULLS    DENSITY HISTOGRAM            LO_VALUE             HI_VALUE
------------------------------ ------------ ---------- ---------- -------------------- -------------------- --------------------
TIME_ID                                1097          0 .000911577 NONE                 13-AUG-2013 00:00:00 31-DEC-2050 00:00:00

Now, lets run the three queries on TIME_ID column against this table and see the results.

## EQUALITY PREDICATE

## Selectivity
SQL> select 1/&&ndv Selectivity from dual;
old   1: select 1/&&ndv Selectivity from dual
new   1: select 1/      1097 Selectivity from dual

SELECTIVITY
-----------
 .000911577

## Assumed Cardinality
SQL> select round(&&selective*800000,0) cardinality from dual;
old   1: select round(&&selective*800000,0) cardinality from dual
new   1: select round(.000911577*800000,0) cardinality from dual

CARDINALITY
-----------
        729

SQL> set autot trace
SQL> select cust_id, promo_id, amount_sold from test_selectivity_m
  2  where time_id=to_date('11-DEC-2015','DD-MON-YYYY');

704 rows selected.

Elapsed: 00:00:00.57

Execution Plan
----------------------------------------------------------
Plan hash value: 3843949181

----------------------------------------------------------------------------------------
| Id  | Operation         | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                    |   729 | 15309 |  1088  (20)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_SELECTIVITY_M |   729 | 15309 |  1088  (20)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   1 - filter("TIME_ID"=TO_DATE(' 2015-12-11 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))

Assumption – 729 Rows and Actual – 704 Rows (Nearly Accurate).

## LESS THAN PREDICATE

## Required_Range - 850 Days
SQL> select to_date('11-DEC-2015','DD-MON-YYYY')-to_date('&&min_value','DD-MON-YYYY HH24:MI:SS') req_range
  2  from       dual;
old   1: select to_date('11-DEC-2015','DD-MON-YYYY')-to_date('&&min_value','DD-MON-YYYY HH24:MI:SS') req_range
new   1: select to_date('11-DEC-2015','DD-MON-YYYY')-to_date('13-AUG-2013 00:00:00','DD-MON-YYYY HH24:MI:SS') req_range

 REQ_RANGE
----------
       850

## Available_Range - 13654 Days
SQL> select to_date('&&max_value','DD-MON-YYYY HH24:MI:SS')-to_date('&&min_value','DD-MON-YYYY HH24:MI:SS') avl_range
  2  from       dual;
old   1: select to_date('&&max_value','DD-MON-YYYY HH24:MI:SS')-to_date('&&min_value','DD-MON-YYYY HH24:MI:SS') avl_range
new   1: select to_date('31-DEC-2050 00:00:00','DD-MON-YYYY HH24:MI:SS')-to_date('13-AUG-2013 00:00:00','DD-MON-YYYY HH24:MI:SS') avl_range

 AVL_RANGE
----------
     13654

## Selectivity - Required_Range/Available_Range
SQL> select &&r_range/&&a_range Selectivity from dual;
old   1: select &&r_range/&&a_range Selectivity from dual
new   1: select        850/     13654 Selectivity from dual

SELECTIVITY
-----------
  .06225282

## Assumed Cardinality
SQL> select round(&&selective*800000,0) cardinality from dual;
old   1: select round(&&selective*800000,0) cardinality from dual
new   1: select round( .06225282*800000,0) cardinality from dual

CARDINALITY
-----------
      49802

SQL> select cust_id, promo_id, amount_sold from test_selectivity_m
  2  where time_id<to_date('11-DEC-2015','DD-MON-YYYY');

620764 rows selected.

Elapsed: 00:00:06.41

Execution Plan
----------------------------------------------------------
Plan hash value: 3843949181

----------------------------------------------------------------------------------------
| Id  | Operation         | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                    | 49802 |  1021K|  1091  (21)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_SELECTIVITY_M | 49802 |  1021K|  1091  (21)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   1 - filter("TIME_ID"<TO_DATE(' 2015-12-11 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))

Assumption – 49k Rows and Actual – 620k Rows (Significantly Out).

## GREATER THAN PREDICATE

## Required_Range - 12804 Days
SQL> select to_date('&&max_value','DD-MON-YYYY HH24:MI:SS')-to_date('11-DEC-2015','DD-MON-YYYY') req_range
  2  from       dual;
old   1: select to_date('&&max_value','DD-MON-YYYY HH24:MI:SS')-to_date('11-DEC-2015','DD-MON-YYYY') req_range
new   1: select to_date('31-DEC-2050 00:00:00','DD-MON-YYYY HH24:MI:SS')-to_date('11-DEC-2015','DD-MON-YYYY') req_range

 REQ_RANGE
----------
     12804

## Available_range - 13654 Days
SQL> select to_date('&&max_value','DD-MON-YYYY HH24:MI:SS')-to_date('&&min_value','DD-MON-YYYY HH24:MI:SS') avl_range
  2  from       dual;
old   1: select to_date('&&max_value','DD-MON-YYYY HH24:MI:SS')-to_date('&&min_value','DD-MON-YYYY HH24:MI:SS') avl_range
new   1: select to_date('31-DEC-2050 00:00:00','DD-MON-YYYY HH24:MI:SS')-to_date('13-AUG-2013 00:00:00','DD-MON-YYYY HH24:MI:SS') avl_range

 AVL_RANGE
----------
     13654

## Selectivity - Required_range/Available_range
SQL> select &&r_range/&&a_range Selectivity from dual;
old   1: select &&r_range/&&a_range Selectivity from dual
new   1: select      12804/     13654 Selectivity from dual

SELECTIVITY
-----------
  .93774718

## Assumed Cardinality
SQL> select round(&&selective*800000,0) cardinality from dual;
old   1: select round(&&selective*800000,0) cardinality from dual
new   1: select round( .93774718*800000,0) cardinality from dual

CARDINALITY
-----------
     750198

SQL> select cust_id, promo_id, amount_sold from test_selectivity_m
  2  where time_id>to_date('11-DEC-2015','DD-MON-YYYY');

178532 rows selected.

Elapsed: 00:00:02.15

Execution Plan
----------------------------------------------------------
Plan hash value: 3843949181

----------------------------------------------------------------------------------------
| Id  | Operation         | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                    |   750K|    15M|  1133  (24)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_SELECTIVITY_M |   750K|    15M|  1133  (24)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   1 - filter("TIME_ID">TO_DATE(' 2015-12-11 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))       

Assumption – 750k Rows and Actual – 178k Rows (Out by 4 Times).

Final Summary:

  • TIME_ID – Assumptions v/s Actuals 
  • TIME_ID – Assumptions v/s Actuals (minor change) Χ
  • AMOUNT_SOLD – Assumptions v/s Actuals Χ
  • PROMO_ID – Assumptions v/s Actuals Χ

A minor change in the data had changed our Summary on Time_ID column. In case of our TEST_SELECTIVITY Table, the Optimizer estimation for Time_ID was nearly accurate, whereas, for other 2 columns, it was way out. What could be the reason ? Remember, accurate Selectivity and Cardinality is critical as it can have an impact on Access Path and Access Order respectively. Any discrepancy can cause a sub-optimal plan. Optimizer is a piece of code and completely depends on the Statistics that we gather and provide to it as an input. Therefore, the solution to these discrepancies has to be with us. In the next blog, which will be Part III of this series, we will cover the problem and the solution to fix this discrepancy, but that fix will cause problems for few cases and we shall cover those as well.

Optimizer – Part I

Just concluded a Full Day Event on Performance in Chandigarh for the North India Chapter of “All India Oracle User Group”. As committed in my earlier user group update blog, I thought for the benefit of the readers, posting the technical details on Optimizer, especially histograms, would help. Another reason, this is important is these sessions are attended by Developer communities as well and grasping everything in one session is very difficult. writeup will help them understand this critical piece.

I usually blog on real life challenges. The motivation behind this blog as well is an issue that I was working on and the fix that I applied to resolve it. This will be discussed at a relevant time and in a relevant part of this series.

I will publish a 4 part series, starting with the basics of Optimizer, the formulaes and then few examples with its various calculations. The series will be divided into 4 parts, such as :

  1. Optimizer Basics & Calculations
  2. Cardinality – Actuals v/s Assumed
  3. Histograms – Frequency & Height Balanced
  4. 12c Enhancements – Hybrid and TopN Frequency Histograms

In the context of the Optimizer, the two terminologies commonly used are SELECTIVITY and CARDINALITY.

SELECTVITY :It is measured as a percentage of rows that would be returned from or filtered out of a row set. Thus the Selectivity of a predicate indicates how many rows pass a predicate test. Selectivity ranges from 0.0 to 1.0. A Selectivity of 0.0 means no rows are selected from a row set, whereas a selectivity of 1.0 means all the rows are selected. A predicate become more Selective as the values approaches 0.0 and less selective if it approaches 1.0. It drives the Access Path for example, tablescan or an Index Scan. With No Histograms, Selectivity for a column is computed as 1/NUM_DISTINCT or DENSITY.

CARDINALITY :Cardinality is the estimated number of rows returned by each operation in an Execution Plan. The Optimizer determines cardinality for each operation based on complex set of formulas that use both, the table and column level statistics or dynamic statistics. Cardinality estimates must be as accurate as possible because they influence all aspects of an execution plan. Cardinality is important when the Optimizer determines the cost of a Join. For example, in a Nested Loop Join between an EMP and DEPT table, the number of rows returned by EMP table determines how often the DEPT table will be probed. Cardinality drives the Access Order.

Just to simplify, for a Gender column with M & F (2 Distinct Values), the selectivity will be 1/2 or 0.5. If this table has around 100 rows, then the Cardinality will be Selectivity X Num_Rows, which is 0.5 x 100 = 50.

For a 100 row table with 2 columns, each with distinct values as 4 and 2, the combined selectivity (for AND predicate) will be 0.5 X 0.25 = 0.125 and the Cardinality will be 0.125 X 100 = 12.5 rounded off to 13.

Selectivity Calculation

Assume Column C
NDV is the Number of Distinct Values
minv is the Minumim Value for C
maxv is the Maximum Value for C

The formulae for Selectivity Calculation would be as under :

  • SEL(=C) = 1/NDV or DENSITY
  • SEL(<C) = (C-minv)/(maxv-minv)
  • SEL(<=C) = SEL(=C) + SEL(<C)
  • SEL(>C) = (maxv-C)/(maxv-minv)
  • SEL(>=C) = SEL(=C) + SEL(>C)

In case of a Range Predicate (<, , >=), the Numerator part is called as the Required Range and the Denominator Part is called as an Available Range.

Once the Selectivity is derived, Cardinality will be Selectivity multiplied by the Number of Rows. For multiple predicates involved, the Selectivity of each of these is derived based on above formulas and then used based on AND or OR predicates. For example :

  • WHERE A=:b1 and B=:b2 = SEL(=A) X SEL(=B)
  • WHERE A=:b1 and B>=:b2 = SEL(=A) X (SEL(=B) + SEL(>B))
  • WHERE A=:b1 or B=:b2 = SEL(=A) + SEL(=B) – (SEL(=A) X SEL(=B))

This was the first part of this series. In the next part, we will create a sample table and run through each of these formulas.

AIOUG Performance Tuning Day ! Hyderabad…

AIOUG is hosting a full day Performance Tuning day on 30th May 2015 in Hyderabad. I will be presenting on Oracle Query Optimizer and Performance. These sessions will be a mix of technical demos and real life examples. Hope to see a large gathering. Registrations are open at Performance Tuning Day.

Issue with Autotrace ! Criticality of Using a Right tool…..

As mentioned in my previous blog, my visit to Oracle Open World was very exciting and enriching. Every session I attended, I got to know something new and therefore thought of sharing this with my readers. I actually wanted to demonstrate this during my Sangam 2013 presentation. However, due to some official commitments, I had to pull out from the event.

In many of my presentations, especially for Developers, I had covered some concepts (along with the Demonstration) on Bind Peeking and issues with “Explain Plan” or Autotrace, as these are not bind-aware. This means, if a query contains a bind variable and if the execution plan of that query is dependant on the value provided to the bind, then the execution plan displayed (or generated) by “explain plan” / “autotrace” is not guaranted to be the same as that of runtime optimizer. Even today, I see many developers (and DBA’s as well) use either “explain plan for” or “autotrace” utilities to check for the execution plan of the queries they are working on.

I attended a very good session that was jointly presented by Maria Colgan and Jonathan Lewis. Maria mentioned about an issue with “set autot trace explain”, which has a potential of creating a (sub-optimal) plan and can cause a performance bottleneck as this sub-optimal plan can then be shared by other users.

Randolf Geist has already published a very good note on this issue and should help my readers understand this as it has been explained with good examples. However, since I was working on the demonstration to be presented during Sangam 2013, thought of sharing this here.

The demonstration is as under :


## Table Creation

drop table t1;

create table t1 as
select a.* from all_objects a, all_objects b
where rownum<=1e6;
create index t1_idx on t1(temporary);

select temporary, count(*) from t1 group by temporary;

exec dbms_stats.gather_table_stats(user,'T1',method_opt=>'for all columns size 1, for columns temporary size 100');

We created a table t1 with 1 Million Rows and an Index on Temporary Column. This Index has been created to demonstrate the difference in the plan when we query data on Temporary Column. Further, in order for the optimizer to generate different plan, we gather histogram on this column. This additional statistics will ensure that optimizer generates / computes the cardinality and the plan based on the input value.

Next, we execute two queries, one each with a Literal Value of ‘Y’ and a Bind Variable with Y passed as a value to the Bind. In both the case, the Optimizer computes nearly accurate cardinality and Index Scan Access Path. For the execution with Bind, the optimizer peeked into Bind to come out with the cardinality and Index Access Path.

select /*+ vivek_y */ OWNER, OBJECT_NAME from t1 where temporary='Y';

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
SQL_ID  7zfa7mstt63gv, child number 0
-------------------------------------
select /*+ vivek_y */ OWNER, OBJECT_NAME from t1 where temporary='Y'

Plan hash value: 546753835

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |       |       |    47 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |  2680 | 88440 |    47   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_IDX |  2680 |       |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   2 - access("TEMPORARY"='Y')

variable b1 varchar2(32);
exec :b1:='Y';

select /*+ vivek_bind_y */ OWNER, OBJECT_NAME from t1 where temporary=:b1;

SQL_ID  9c5pp1gt64s7q, child number 0
-------------------------------------
select /*+ vivek_bind_y */ OWNER, OBJECT_NAME from t1 where
temporary=:b1

Plan hash value: 546753835

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |       |       |    47 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |  2680 | 88440 |    47   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_IDX |  2680 |       |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - :B1 (VARCHAR2(30), CSID=178): 'Y'

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

   2 - access("TEMPORARY"=:B1)


Let us check for the behaviour of “explain plan for” and “autotrace” utilities. Since these are not bind-aware, the plan displayed & generated will be a Full Table Scan. The computed cardinality is based on #Rows/NDV, which is 1000000/2 = 500000. Also, note the I/O’s (consistent read) as generated by autotrace.

explain plan for
select /*+ vivek_bind_y */ OWNER, OBJECT_NAME from t1 where temporary=:b1;

SQL> @utlxpls

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   500K|    15M|  3968   (2)| 00:00:48 |
|*  1 |  TABLE ACCESS FULL| T1   |   500K|    15M|  3968   (2)| 00:00:48 |
--------------------------------------------------------------------------

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

   1 - filter("TEMPORARY"=:B1)

set autot trace
select /*+ vivek_bind_y */ OWNER, OBJECT_NAME from t1 where temporary=:b1;

2327 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   500K|    15M|  3968   (2)| 00:00:48 |
|*  1 |  TABLE ACCESS FULL| T1   |   500K|    15M|  3968   (2)| 00:00:48 |
--------------------------------------------------------------------------

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

   1 - filter("TEMPORARY"=:B1)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        841  consistent gets <--- Note the I/O
          0  physical reads
          0  redo size

set autot trace

Last, we execute the query with “set autot trace explain” and the same query with “set autot off”. In this case, while the value passed to the bind is ‘Y’, the optimizer will re-use the plan (Full Table Scan) generated by “set autot trace explain” and this is where the problem starts. With “set autot trace explain”, the plan generated is stored in the Shared Pool and is shared by the same query, if run from an application and this can be a problem. Assuming the query executed next is a part of the application query and a developer runs the exactly same query using “set autot trace explain”, the plan generated will be without bind peek and will be sub-optimal.

set autot trace explain
select /*+ with_y */ OWNER, OBJECT_NAME from t1 where temporary=:b1;


SQL> @ap

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  cbf51h7v1276h, child number 0
-------------------------------------
select /*+ with_y */ OWNER, OBJECT_NAME from t1 where temporary=:b1

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |  3968 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   |   500K|    15M|  3968   (2)| 00:00:48 |
--------------------------------------------------------------------------

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

   1 - filter("TEMPORARY"=:B1)


18 rows selected.

set autot off
select /*+ with_y */ OWNER, OBJECT_NAME from t1 where temporary=:b1;

select plan_table_output from table(dbms_xplan.display_cursor(format=>'typical +peeked_binds'));

SQL_ID  cbf51h7v1276h, child number 0
-------------------------------------
select /*+ with_y */ OWNER, OBJECT_NAME from t1 where temporary=:b1

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |  3967 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   |   500K|    15M|  3967   (2)| 00:00:48 |
--------------------------------------------------------------------------

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

   1 - filter("TEMPORARY"=:B1)

new   1: select sql_id, sql_text, executions, buffer_gets, elapsed_time, rows_processed from v$sqlarea where sql_id='cbf51h7v1276h'

SQL_ID        SQL_TEXT                                           EXECUTIONS BUFFER_GETS ELAPSED_TIME ROWS_PROCESSED
------------- -------------------------------------------------- ---------- ----------- ------------ --------------
cbf51h7v1276h select /*+ with_y */ OWNER, OBJECT_NAME from t1 wh          1       14393      3563761           2327
              ere temporary=:b1

The last query is exactly same to the one that was executed with “set autot trace explain” and shared the plan generated by “set autotrace explain”, which is a Full table scan and is not the one that should have been generated. Interestingly, check for the I/O’s of the last Full Table Scan plan query. The query has done 14393 Logical reads, whereas, in one of the previous execution with “autotrace on”, the I/O’s shown was 891 (and the plan says full table scan). Why is there a difference between the two I/O’s of Full Table Scans ? For an explanation, read the blog from Randolf Geist.

Oracle Open World 2013 : An Enriching Experience….

First of all, let me apologies for a long delay in posting a Blog. This was due to some critical escalations and the work pressure. Work comes first and therefore, it was very critical for me to finish off all the pending in-hand customer tasks.

This year, I had an opportunity to attend Oracle Open World 2013. Being my first visit for this Event in US, I was all excited as I was sure that this will be an opportunity to meet some of the great experts from different parts of the world and also to meet some old friends. The trip met all my expectations. During the event, while I attended many of the technical events (I will talk about these later), I met some of my old friends, like K. Gopalakrishnan (co-author of Oracle Wait Interface).

On the technical front, I attended many sessions and some of these were on Optimizer and Oracle 12c. However, the sessions that kept me glued towards the technical content and speaker presenting style was on “Top 12 Features of Oracle Database 12c” by Thomas Kyte and “Oracle Optimizer Bootcamp” presented jointly by Maria Colgan (Oracle Optimizer Group) and Jonathan Lewis. A session by Connor Mcdonald on “Optimizer Statistics : A Fresh Approach” was also well presented and the attendees enjoyed the session a lot.

While I do not want to write everything about the sessions, the take away that I want the readers to know are :

1. Multitenant Feature of Oracle 12c – This is one of the best feature that is introduced and will facilitate many of the organizations in database consolidation.
2. Adaptive Optimization – This is to ensure that a right (and optimal) execution plan is chosen (or switched) at run time based on the number of rows fetched.
3. Identity Datatype – Oracle Sequences can be attached to this data type to generate repetitive numbers.

All these years, during my presentations, I have discussed on using a right tool for optimization. Developers still use “explain plan for” or “autotrace” to check for the execution plan of their queries. I have demonstrated some of the issues around “explain plan” and this was revisited by Maria Colgan in “Oracle Optimizer Bootcamp”. Every session you attend, you are bound to learn something new and surely enough, I learned something interesting in this session. I am sure not many of my friends and readers know about this and therefore, I will include this in my Sangam 2013 presentation with a thanks to Maria.

I will be presenting a session during this year annual User Group Event “Sangam 2013”. The topic of my presentation is “Real Life Optimization Scenarios and Solutions”. Looking forward for this great event.

Query Optimization ! An example of a better rewrite…

In January 2012, I wrote about a Query Performance Issue due to NVL(:b1,column_name) predicates in the WHERE clause. Due to these predicates, the Optimizer computed Incorrect Cardinality and came out with a Sub-Optimal Plan. In Sangam 2012, I demostrated an issue because of these predicates. This blog is again on one of the issue that a customer reported and was solved by way of a workaround. Since, this query is a seeded query, the permanent fix (suggested in this blog) is expected by way of an Application Patch.

I will demonstrate this on my SCOTT Schema and a Query on EMP table. This will be easier for me to explain as well. Based on the EMP table, the requirement is to write a report that takes 2 Inputs. These are EMPNO and DEPTNO. The users can run this report for any of the following conditions :

  1. EMPNO and DEPTNO are NOT NULL
  2. EMPNO IS NULL and DEPTNO IS NOT NULL
  3. EMPNO IS NOT NULL AND DEPTNO IS NULL
  4. EMPNO and DEPTNO are both NULL

The way Original Query is written, I assumed the Developers had above 4 requirements in mind. However, at the Customer place, the customer confirmed that only the 1st two conditions are applicable. Out of the total execution, 1st condition contributes to around 70% and 2nd contributes to 30%.

Back to our example on EMP table. With the 4 conditions in mind, any Developer would write a query as mentioned below.

select empno, ename, deptno, sal
from	emp
where	deptno = nvl(:deptno,deptno)
and	empno = nvl(:empno,empno)
order by empno;

## Execution of this query for each of the combination
## Both are NOT NULL (For a Deptno & for an Employee)
exec :deptno:=30; :empno:=7499; 

     EMPNO ENAME          DEPTNO        SAL
---------- ---------- ---------- ----------
      7499 ALLEN              30       1600

## EMPNO IS NULL (For a Deptno and all Employees)
exec :deptno:=30; :empno:=null;

     EMPNO ENAME          DEPTNO        SAL
---------- ---------- ---------- ----------
      7499 ALLEN              30       1600
      7521 WARD               30       1250
      7654 MARTIN             30       1250
      7698 BLAKE              30       2850
      7844 TURNER             30       1500
      7900 JAMES              30        950

## DEPTNO IS NULL (For an Employee)
exec :deptno:=null; :empno:=7499;

     EMPNO ENAME          DEPTNO        SAL
---------- ---------- ---------- ----------
      7499 ALLEN              30       1600

## Both are NULL (for all Deptno and all Employees)
exec :deptno:=null; :empno:=null;

     EMPNO ENAME          DEPTNO        SAL
---------- ---------- ---------- ----------
      7369 SMITH              20        800
      7499 ALLEN              30       1600
      7521 WARD               30       1250
      7566 JONES              20       2975
      7654 MARTIN             30       1250
      7698 BLAKE              30       2850
      7782 CLARK              10       2450
      7788 SCOTT              20       3000
      7839 KING               10       5000
      7844 TURNER             30       1500
      7876 ADAMS              20       1100
      7900 JAMES              30        950
      7902 FORD               20       3000
      7934 MILLER             10       1300

A single query meets the requirement for all the 4 combinations. The Developer, in this case, has done his job. However, they have not considered the fact that 2 out of 4 of the above combinations would end up doing a Full Table Scan of EMP table. In case of the customer case, since only the 1st two combinations are applicable and with 30% of the executions on combination 2, 30% of the time, the Optimizer would opt for a Full Table Scan. Before, we get into the Original case, let us check the runtime execution plan for the Query on EMP Table.

select empno, ename, deptno, sal from emp where deptno =
nvl(:deptno,deptno) and empno = nvl(:empno,empno) order by empno

Plan hash value: 3923409422

-----------------------------------------------------------------------------------------
| Id  | Operation                      | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |        |       |       |     6 (100)|          |
|   1 |  SORT ORDER BY                 |        |     7 |   322 |     6  (17)| 00:00:01 |
|   2 |   CONCATENATION                |        |       |       |            |          |
|*  3 |    FILTER                      |        |       |       |            |          |
|*  4 |     TABLE ACCESS FULL          | EMP    |     6 |   276 |     3   (0)| 00:00:01 |
|*  5 |    FILTER                      |        |       |       |            |          |
|*  6 |     TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    46 |     2   (0)| 00:00:01 |
|*  7 |      INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   3 - filter(:EMPNO IS NULL)
   4 - filter(("EMPNO" IS NOT NULL AND "DEPTNO"=NVL(:DEPTNO,"DEPTNO")))
   5 - filter(:EMPNO IS NOT NULL)
   6 - filter("DEPTNO"=NVL(:DEPTNO,"DEPTNO"))
   7 - access("EMPNO"=:EMPNO)

From the predicate information, the Optimizer choice becomes very clear, which is “IF :EMPNO is NULL then FTS of EMP and IF :EMPNO is NOT NULL then Table Access by Index Rowid and a Unique Scan of PK_EMP”. This means, if a User Inputs the value for an EMPNO (irrespective of what has been passed for DEPNO), then the Optimizer will always do an Index Scan.

Now the Original Case
The Original Query and the Plan is pasted below, before we discuss on this.

SELECT TRANSACTION_ID, TRANSACTION_TYPE, TRANSACTION_DATE, TAX_TRANSACTION_ID, PARENT_TRANSACTION_TYPE, 
	CURRENCY_CONVERSION_RATE, QUANTITY, 
	DECODE(TRANSACTION_TYPE, 'RECEIVE', 1, 'RETURN TO VENDOR', -1, 'CORRECT', 
	DECODE(PARENT_TRANSACTION_TYPE, 'RECEIVE', SIGN(QUANTITY), 'RETURN TO VENDOR', 
	SIGN(QUANTITY)*-1)) QUANTITY_MULTIPLIER 
FROM 	JAI_RCV_TRANSACTIONS 
WHERE 	SHIPMENT_LINE_ID = NVL(:B2 , SHIPMENT_LINE_ID) 
AND 	TRANSACTION_ID = NVL(:B1 , TRANSACTION_ID) 
AND 	( TRANSACTION_TYPE IN ('RECEIVE', 'RETURN TO VENDOR') OR ( TRANSACTION_TYPE = 'CORRECT' AND PARENT_TRANSACTION_TYPE IN ('RECEIVE', 'RETURN TO VENDOR') ) ) 
ORDER BY TRANSACTION_ID;

Plan hash value: 4081860556

----------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                         |       |       |   621K(100)|          |
|   1 |  SORT ORDER BY                 |                         |     2 |    94 |   621K (11)| 00:02:24 |
|   2 |   CONCATENATION                |                         |       |       |            |          |
|*  3 |    FILTER                      |                         |       |       |            |          |
|*  4 |     TABLE ACCESS FULL          | JAI_RCV_TRANSACTIONS    |     1 |    47 |   621K (11)| 00:02:24 |
|*  5 |    FILTER                      |                         |       |       |            |          |
|*  6 |     TABLE ACCESS BY INDEX ROWID| JAI_RCV_TRANSACTIONS    |     1 |    47 |     3   (0)| 00:00:01 |
|*  7 |      INDEX UNIQUE SCAN         | JAI_RCV_TRANSACTIONS_PK |     1 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

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

   3 - filter(:B1 IS NULL)
   4 - filter(((INTERNAL_FUNCTION("TRANSACTION_TYPE") OR ("TRANSACTION_TYPE"='CORRECT' AND
              INTERNAL_FUNCTION("PARENT_TRANSACTION_TYPE"))) AND "SHIPMENT_LINE_ID"=NVL(:B2,"SHIPMENT_LINE_ID")
              AND "TRANSACTION_ID" IS NOT NULL))
   5 - filter(:B1 IS NOT NULL)
   6 - filter(((INTERNAL_FUNCTION("TRANSACTION_TYPE") OR ("TRANSACTION_TYPE"='CORRECT' AND
              INTERNAL_FUNCTION("PARENT_TRANSACTION_TYPE"))) AND
              "SHIPMENT_LINE_ID"=NVL(:B2,"SHIPMENT_LINE_ID")))
   7 - access("TRANSACTION_ID"=:B1)

As mentioned earlier, in case of the Customer case, only the 1st two combinations are applicable. These are (columns from Original Query) :

  1. TRANSACTION_ID and SHIPMENT_LINE_ID are NOT NULL
  2. TRANSACTION_ID IS NULL and SHIPMENT_LINE_ID IS NOT NULL

There is a Unique Index on TRANSACTION_ID and another Unique Index on (SHIPMENT_LINE_ID,TRANSACTION_ID). This query is a part of a Transaction and for the cases, where the value to :B1 (Transaction_Id) is null, the transaction takes around 20 minutes to complete. The table has around 9 Million Rows and does a Full Table Scan for such cases.

Since the customer mentioned that in their case, value for TRANSACTION_ID column can only be passed as NULL, an option of changing “:B1 is NULL” to “:B2 is NULL” (see PREDICATE INFORMATION) seemed to be a better choice. Remember, :B1 is for TRANSACTION_ID and :B2 is for SHIPMENT_LINE_ID. This change will check, if :B2 is NULL then FTS on the Table and if :B2 is NOT NULL then an Index Scan on SHIPMENT_LINE_ID. The Uniqueness of TRANSACTION_ID is more than SHIPMENT_LINE_ID, and therefore, optimizer picks TRANSACTION_ID as more cheaper and opts for an Index Scan on this column. Below are the column statistics for the two columns.

COLUMN_NAME                    NUM_DISTINCT  NUM_NULLS    DENSITY HISTOGRAM
------------------------------ ------------ ---------- ---------- ---------------
SHIPMENT_LINE_ID                    4351257          0 2.2982E-07 NONE
TRANSACTION_ID                      9092033          0 1.0999E-07 NONE

As a temporary workaround, the column statistics for the two columns were swapped. Before doing that, it was critically important to actually check the number of queries with TRANSACTION_ID column in the WHERE predicate. This is ensure that this temporary fix should not impact other queries. Once it was confirmed that this change will not impact, the column stats were swapped. This swapping was intended to see below mentioned change in the plan :

Original – if :B1 is NULL then FTS. If :B1 is NOT NULL then Unique Index Scan on TRANSACTION_ID
Changed – if :B2 is NULL then FTS. If :B2 is NOT NULL then Range Scan on SHIPMENT_LINE_ID

Since the value to :B2 is never passed as NULL, this change will also make sure this 9 Million Row table is never Full Scanned. The swapping of statistics worked and the plan changed for better. Some of the transaction that took around 20 Minutes started completing in a second.

exec dbms_stats.set_column_stats(ownname=>'JA',tabname=>'JAI_RCV_TRANSACTIONS',colname=>'TRANSACTION_ID',DISTCNT=>4351257);
exec dbms_stats.set_column_stats(ownname=>'JA',tabname=>'JAI_RCV_TRANSACTIONS',colname=>'SHIPMENT_LINE_ID',DISTCNT=>9092033);

Plan hash value: 3580471929

----------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                           |       |       |   621K(100)|          |
|   1 |  SORT ORDER BY                 |                           |     2 |    94 |   621K (11)| 00:02:24 |
|   2 |   CONCATENATION                |                           |       |       |            |          |
|*  3 |    FILTER                      |                           |       |       |            |          |
|*  4 |     TABLE ACCESS FULL          | JAI_RCV_TRANSACTIONS      |     1 |    47 |   621K (11)| 00:02:24 |
|*  5 |    FILTER                      |                           |       |       |            |          |
|*  6 |     TABLE ACCESS BY INDEX ROWID| JAI_RCV_TRANSACTIONS      |     1 |    47 |     4   (0)| 00:00:01 |
|*  7 |      INDEX RANGE SCAN          | JA_IN_RCV_TRANSACTIONS_N5 |     1 |       |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

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

   3 - filter(:B2 IS NULL)
   4 - filter(((INTERNAL_FUNCTION("TRANSACTION_TYPE") OR ("TRANSACTION_TYPE"='CORRECT' AND
              INTERNAL_FUNCTION("PARENT_TRANSACTION_TYPE"))) AND "TRANSACTION_ID"=NVL(:B1,"TRANSACTION_ID")
              "SHIPMENT_LINE_ID" IS NOT NULL))
   5 - filter(:B2 IS NOT NULL)
   6 - filter(((INTERNAL_FUNCTION("TRANSACTION_TYPE") OR ("TRANSACTION_TYPE"='CORRECT' AND
              INTERNAL_FUNCTION("PARENT_TRANSACTION_TYPE"))) AND "TRANSACTION_ID"=NVL(:B1,"TRANSACTION_ID")
   7 - access("SHIPMENT_LINE_ID"=:B2)

While this was a temporary workaround to fix the problem, a better strategy would have been to write a query that take the advantage of the available indexes and works optimally for any of the 4 combinations mentioned above. A better query is as under :

select * from (
SELECT /*+ WITH BOTH BINDS AS NOT NULL */ TRANSACTION_ID, TRANSACTION_TYPE, TRANSACTION_DATE, TAX_TRANSACTION_ID, PARENT_TRANSACTION_TYPE, 
	CURRENCY_CONVERSION_RATE, QUANTITY, 
	DECODE(TRANSACTION_TYPE, 'RECEIVE', 1, 'RETURN TO VENDOR', -1, 'CORRECT', 
	DECODE(PARENT_TRANSACTION_TYPE, 'RECEIVE', SIGN(QUANTITY), 'RETURN TO VENDOR', 
	SIGN(QUANTITY)*-1)) QUANTITY_MULTIPLIER 
FROM 	JAI_RCV_TRANSACTIONS 
WHERE 	SHIPMENT_LINE_ID = :B2
AND 	TRANSACTION_ID = :B1
AND 	( TRANSACTION_TYPE IN ('RECEIVE', 'RETURN TO VENDOR') OR ( TRANSACTION_TYPE = 'CORRECT' AND PARENT_TRANSACTION_TYPE IN ('RECEIVE', 'RETURN TO VENDOR') ) ) 
union all
SELECT /*+ WITH B1 AS NULL */ TRANSACTION_ID, TRANSACTION_TYPE, TRANSACTION_DATE, TAX_TRANSACTION_ID, PARENT_TRANSACTION_TYPE, 
	CURRENCY_CONVERSION_RATE, QUANTITY, 
	DECODE(TRANSACTION_TYPE, 'RECEIVE', 1, 'RETURN TO VENDOR', -1, 'CORRECT', 
	DECODE(PARENT_TRANSACTION_TYPE, 'RECEIVE', SIGN(QUANTITY), 'RETURN TO VENDOR', 
	SIGN(QUANTITY)*-1)) QUANTITY_MULTIPLIER 
FROM 	JAI_RCV_TRANSACTIONS 
WHERE 	SHIPMENT_LINE_ID = :B2 AND :B1 is NULL
AND 	( TRANSACTION_TYPE IN ('RECEIVE', 'RETURN TO VENDOR') OR ( TRANSACTION_TYPE = 'CORRECT' AND PARENT_TRANSACTION_TYPE IN ('RECEIVE', 'RETURN TO VENDOR') ) ) 
union all
SELECT /*+ WITH B2 AS NULL */ TRANSACTION_ID, TRANSACTION_TYPE, TRANSACTION_DATE, TAX_TRANSACTION_ID, PARENT_TRANSACTION_TYPE, 
	CURRENCY_CONVERSION_RATE, QUANTITY, 
	DECODE(TRANSACTION_TYPE, 'RECEIVE', 1, 'RETURN TO VENDOR', -1, 'CORRECT', 
	DECODE(PARENT_TRANSACTION_TYPE, 'RECEIVE', SIGN(QUANTITY), 'RETURN TO VENDOR', 
	SIGN(QUANTITY)*-1)) QUANTITY_MULTIPLIER 
FROM 	JAI_RCV_TRANSACTIONS 
WHERE 	TRANSACTION_ID = :B1 AND :B2 IS NULL
AND 	( TRANSACTION_TYPE IN ('RECEIVE', 'RETURN TO VENDOR') OR ( TRANSACTION_TYPE = 'CORRECT' AND PARENT_TRANSACTION_TYPE IN ('RECEIVE', 'RETURN TO VENDOR') ) )
UNION ALL
SELECT /*+ WITH BOTH BINDS AS NULL */ TRANSACTION_ID, TRANSACTION_TYPE, TRANSACTION_DATE, TAX_TRANSACTION_ID, PARENT_TRANSACTION_TYPE, 
	CURRENCY_CONVERSION_RATE, QUANTITY, 
	DECODE(TRANSACTION_TYPE, 'RECEIVE', 1, 'RETURN TO VENDOR', -1, 'CORRECT', 
	DECODE(PARENT_TRANSACTION_TYPE, 'RECEIVE', SIGN(QUANTITY), 'RETURN TO VENDOR', 
	SIGN(QUANTITY)*-1)) QUANTITY_MULTIPLIER 
FROM 	JAI_RCV_TRANSACTIONS 
WHERE 	:B1 IS NULL AND :B2 IS NULL
AND 	( TRANSACTION_TYPE IN ('RECEIVE', 'RETURN TO VENDOR') OR ( TRANSACTION_TYPE = 'CORRECT' AND PARENT_TRANSACTION_TYPE IN ('RECEIVE', 'RETURN TO VENDOR') ) ) )
ORDER BY TRANSACTION_ID;

The benefit of this modified query is :<

  1. If both BINDS are NOT NULL, use a Composite Unique Index on SHIPMENT_LINE_ID, TRANSACTION_ID
  2. If BIND :B1 on TRANSACTION_ID is NULL then a Range Scan on SHIPMENT_LINE_ID
  3. If Bind :B2 on SHIPMENT_LINE_ID is NULL then a Unique Scan on TRANSACTION_ID
  4. If Both Binds are NULL, then a Full Table Scan (this is unavoidable).

Query Performance Issue ! SQL*Plus v/s Oracle Reports…

Have editted on 1st March to include the Execution Plan of the Modified Query. See at the end.

Recently, got a mail from one of my customer on a Query Performance Issue. The Customer mentioned that the Query, when executed from SQL*Plus, takes around 10 Seconds, whereas, when the pasted in Oracle Reports and run through Concurrent Manager, it runs from around 30-40 minutes and is finally killed. The Customer also confirmed that the Query is exactly same i.e it is purely a Copy-Paste of the Query that was run from SQL*Plus and that the Bind Type & Length too are same. The run time plan for both the executions are totally different, with the plan of SQL*Plus better than the plan generated by Oracle Reports. The Question was, “Why is the plan different between SQL*Plus and Oracle Reports ?”

As usual, I asked for a 10053 trace for both the executions, which is one of the best way in investigating Optimizer Calculations. The 10053 trace revealed some important facts about the Query Performance, and therefore helped resolve the issue. Before we dive into the investigation, let me paste the Query and both the Execution Plans.

This is the Query.

SELECT /*+ INDEX(WDD WSH_DELIVERY_DETAILS_TI4) */
             WDD.ORGANIZATION_ID, 
             COUNT(*) LPNS,
             TRP.TRIP_ID,
             RC.CUSTOMER_NUMBER,
             RC.CUSTOMER_NAME, 
             RAA.ADDRESS1 TO_ADDRESS1, 
             RAA.CITY RAA.STATE TO_CITY_STATE, 
             HLA.DESCRIPTION FROM_DESC, 
             HLA.ADDRESS_LINE_1||' '||HLA.TOWN_OR_CITY||' '||HLA.TOWN_OR_CITY)  FROM_ADDRESS1_CITY, 
             WDA.DELIVERY_ID, 
             WDA.PARENT_DELIVERY_DETAIL_ID,
             WDD.DELIVERY_DETAIL_ID, 
             WDD1.CONTAINER_NAME    CONTAINER_NAME, 
             SUM(WDD.REQUESTED_QUANTITY) REQUESTED_QUANTITY, 
             WDD.REQUESTED_QUANTITY_UOM, 
             SUM(ROUND(WDD.GROSS_WEIGHT,0)) GROSS_WEIGHT, 
             WDD.WEIGHT_UOM_CODE,
             WT.VEHICLE_NUM_PREFIX ||' '||WT.VEHICLE_NUMBER VEHICLE_NUMBER,
             MSI.SEGMENT1 ITEM_CODE, 
	     MSI.ATTRIBUTE10 PRT_DESC 
FROM 	apps.WSH_DELIVERY_DETAILS WDD,
     	apps.WSH_DELIVERY_DETAILS WDD1,
        apps.WSH_DELIVERY_ASSIGNMENTS WDA ,
        apps.WSH_DELIVERY_ASSIGNMENTS WDA1,    
        apps.WSH_TRIPS WT, 
        apps.MTL_SYSTEM_ITEMS MSI, 
	TI_RA_ADDRESSES_V RAA,
	TI_RA_CUSTOMERS_V RC ,
        apps.HR_LOCATIONS_ALL HLA,
    	(SELECT DISTINCT  T.TRIP_ID, WDA.DELIVERY_ID 
    	FROM	apps.WSH_DELIVERY_ASSIGNMENTS WDA, 
          	apps.WSH_DELIVERY_LEGS DL,
          	apps.WSH_TRIP_STOPS PICKUP_STOP,  
             	apps.WSH_TRIPS T
	WHERE	WDA.DELIVERY_ID     = DL.DELIVERY_ID 
        AND     DL.PICK_UP_STOP_ID  = PICKUP_STOP.STOP_ID 
        AND     PICKUP_STOP.TRIP_ID = T.TRIP_ID
        AND     T.TRIP_ID  >=  : P_TRIPID_FR
        AND     T.TRIP_ID  = : P_TRIPID_FR 
AND 	TRP.TRIP_ID <= : P_TRIPID_TO 
GROUP BY WDD.ORGANIZATION_ID , TRP.TRIP_ID , RC.CUSTOMER_NUMBER , RC.CUSTOMER_NAME , RAA.ADDRESS1 , 
RAA.CITY , RAA.STATE , HLA.DESCRIPTION , HLA.ADDRESS_LINE_1 , HLA.TOWN_OR_CITY , WDA.DELIVERY_ID , 
WT.VEHICLE_NUM_PREFIX , WT.VEHICLE_NUMBER , WDA.PARENT_DELIVERY_DETAIL_ID , WDD.DELIVERY_DETAIL_ID , 
WDD1.CONTAINER_NAME , WDD.REQUESTED_QUANTITY_UOM , WDD.WEIGHT_UOM_CODE , MSI.SEGMENT1 , MSI.ATTRIBUTE10

The Runtime Plan of the Query when Executed from SQL*Plus.

-----------------------------------------------------------------------------------------------
| Id  | Operation                                       | Name                        | Rows  |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                |                             |       |
|   1 |  SORT GROUP BY                                  |                             |     1 |
|   2 |   VIEW                                          | VM_NWVW_1                   |     1 |
|   3 |    SORT UNIQUE                                  |                             |     1 |
|*  4 |     FILTER                                      |                             |       |
|   5 |      NESTED LOOPS                               |                             |       |
|   6 |       NESTED LOOPS                              |                             |     1 |
|   7 |        NESTED LOOPS                             |                             |     1 |
|   8 |         NESTED LOOPS                            |                             |     1 |
|   9 |          NESTED LOOPS                           |                             |     5 |
|  10 |           NESTED LOOPS                          |                             |     5 |
|  11 |            NESTED LOOPS                         |                             |     5 |
|  12 |             NESTED LOOPS                        |                             |     5 |
|  13 |              NESTED LOOPS OUTER                 |                             |    27 |
|  14 |               NESTED LOOPS OUTER                |                             |    27 |
|  15 |                NESTED LOOPS                     |                             |    27 |
|  16 |                 NESTED LOOPS                    |                             |  1908 |
|  17 |                  NESTED LOOPS                   |                             |   427 |
|  18 |                   NESTED LOOPS                  |                             |   127 |
|  19 |                    MERGE JOIN                   |                             |    72 |
|  20 |                     TABLE ACCESS BY INDEX ROWID | WSH_TRIP_STOPS              |   187 |
|* 21 |                      INDEX RANGE SCAN           | WSH_TRIP_STOPS_N1           |   187 |
|* 22 |                     SORT JOIN                   |                             |    72 |
|  23 |                      TABLE ACCESS BY INDEX ROWID| WSH_TRIPS                   |    72 |
|* 24 |                       INDEX RANGE SCAN          | WSH_TRIPS_U1                |    72 |
|  25 |                    TABLE ACCESS BY INDEX ROWID  | WSH_DELIVERY_LEGS           |     2 |
|* 26 |                     INDEX RANGE SCAN            | WSH_DELIVERY_LEGS_N2        |     2 |
|* 27 |                   INDEX RANGE SCAN              | WSH_DELIVERY_ASSIGNMENTS_N1 |     3 |
|  28 |                  TABLE ACCESS BY INDEX ROWID    | WSH_DELIVERY_ASSIGNMENTS    |     4 |
|* 29 |                   INDEX RANGE SCAN              | WSH_DELIVERY_ASSIGNMENTS_N1 |     4 |
|* 30 |                 TABLE ACCESS BY INDEX ROWID     | WSH_DELIVERY_DETAILS        |     1 |
|* 31 |                  INDEX RANGE SCAN               | WSH_DELIVERY_DETAILS_TI4    |     1 |
|* 32 |                INDEX RANGE SCAN                 | WSH_DELIVERY_ASSIGNMENTS_N3 |     1 |
|  33 |               TABLE ACCESS BY INDEX ROWID       | WSH_DELIVERY_DETAILS        |     1 |
|* 34 |                INDEX UNIQUE SCAN                | WSH_DELIVERY_DETAILS_U1     |     1 |
|  35 |              TABLE ACCESS BY INDEX ROWID        | MTL_SYSTEM_ITEMS_B          |     1 |
|* 36 |               INDEX UNIQUE SCAN                 | MTL_SYSTEM_ITEMS_B_U1       |     1 |
|  37 |             TABLE ACCESS BY INDEX ROWID         | HZ_CUST_ACCOUNTS            |     1 |
|* 38 |              INDEX UNIQUE SCAN                  | HZ_CUST_ACCOUNTS_U1         |     1 |
|  39 |            TABLE ACCESS BY INDEX ROWID          | HZ_LOCATIONS                |     1 |
|* 40 |             INDEX UNIQUE SCAN                   | HZ_LOCATIONS_U1             |     1 |
|  41 |           TABLE ACCESS BY INDEX ROWID           | HZ_PARTY_SITES              |     1 |
|* 42 |            INDEX RANGE SCAN                     | HZ_PARTY_SITES_N2           |     1 |
|* 43 |          TABLE ACCESS BY INDEX ROWID            | HZ_CUST_ACCT_SITES_ALL      |     1 |
|* 44 |           INDEX RANGE SCAN                      | HZ_CUST_ACCT_SITES_N1       |     1 |
|  45 |         TABLE ACCESS BY INDEX ROWID             | HZ_PARTIES                  |     1 |
|* 46 |          INDEX UNIQUE SCAN                      | HZ_PARTIES_U1               |     1 |
|* 47 |        INDEX UNIQUE SCAN                        | HR_LOCATIONS_PK             |     1 |
|  48 |       TABLE ACCESS BY INDEX ROWID               | HR_LOCATIONS_ALL            |     1 |
-----------------------------------------------------------------------------------------------

Runtime Plan of the Query when Executed from Oracle Reports.

  
  -----------------------------------------------------------------------------------------------
  | Id  | Operation                                       | Name                        | Rows  |
  -----------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT                                |                             |       |
  |   1 |  SORT ORDER BY                                  |                             |     1 |
  |   2 |   SORT GROUP BY                                 |                             |     1 |
  |   3 |    VIEW                                         | VM_NWVW_1                   |     1 |
  |   4 |     SORT UNIQUE                                 |                             |     1 |
  |*  5 |      FILTER                                     |                             |       |
  |   6 |       NESTED LOOPS                              |                             |       |
  |   7 |        NESTED LOOPS                             |                             |     1 |
  |   8 |         NESTED LOOPS                            |                             |     1 |
  |   9 |          NESTED LOOPS                           |                             |     4 |
  |  10 |           NESTED LOOPS                          |                             |     4 |
  |  11 |            NESTED LOOPS                         |                             |     1 |
  |  12 |             NESTED LOOPS OUTER                  |                             |     1 |
  |  13 |              NESTED LOOPS OUTER                 |                             |     1 |
  |  14 |               NESTED LOOPS                      |                             |     1 |
  |* 15 |                HASH JOIN                        |                             |     1 |
  |  16 |                 TABLE ACCESS FULL               | HZ_CUST_ACCT_SITES_ALL      | 36983 |
  |* 17 |                 HASH JOIN                       |                             | 23952 |
  |  18 |                  TABLE ACCESS FULL              | HZ_PARTY_SITES              | 76710 |
  |* 19 |                  HASH JOIN                      |                             | 22674 |
  |  20 |                   TABLE ACCESS FULL             | HZ_LOCATIONS                | 72598 |
  |* 21 |                   HASH JOIN                     |                             | 22737 |
  |  22 |                    TABLE ACCESS FULL            | HZ_CUST_ACCOUNTS            | 14291 |
  |* 23 |                    HASH JOIN                    |                             | 22801 |
  |  24 |                     TABLE ACCESS BY INDEX ROWID | MTL_SYSTEM_ITEMS_B          | 11073 |
  |* 25 |                      INDEX RANGE SCAN           | MTL_SYSTEM_ITEMS_B_N16      | 11073 |
  |* 26 |                     HASH JOIN                   |                             |   130K|
  |  27 |                      TABLE ACCESS FULL          | HR_LOCATIONS_ALL            |   919 |
  |* 28 |                      TABLE ACCESS BY INDEX ROWID| WSH_DELIVERY_DETAILS        |   130K|
  |* 29 |                       INDEX RANGE SCAN          | WSH_DELIVERY_DETAILS_TI4    |   143K|
  |* 30 |                TABLE ACCESS BY INDEX ROWID      | WSH_DELIVERY_ASSIGNMENTS    |     1 |
  |* 31 |                 INDEX RANGE SCAN                | WSH_DELIVERY_ASSIGNMENTS_N3 |     1 |
  |* 32 |               INDEX RANGE SCAN                  | WSH_DELIVERY_ASSIGNMENTS_N3 |     1 |
  |  33 |              TABLE ACCESS BY INDEX ROWID        | WSH_DELIVERY_DETAILS        |     1 |
  |* 34 |               INDEX UNIQUE SCAN                 | WSH_DELIVERY_DETAILS_U1     |     1 |
  |  35 |             TABLE ACCESS BY INDEX ROWID         | HZ_PARTIES                  |     1 |
  |* 36 |              INDEX UNIQUE SCAN                  | HZ_PARTIES_U1               |     1 |
  |* 37 |            INDEX RANGE SCAN                     | WSH_DELIVERY_ASSIGNMENTS_N1 |     4 |
  |  38 |           TABLE ACCESS BY INDEX ROWID           | WSH_DELIVERY_LEGS           |     1 |
  |* 39 |            INDEX RANGE SCAN                     | WSH_DELIVERY_LEGS_N1        |     1 |
  |* 40 |          TABLE ACCESS BY INDEX ROWID            | WSH_TRIP_STOPS              |     1 |
  |* 41 |           INDEX UNIQUE SCAN                     | WSH_TRIP_STOPS_U1           |     1 |
  |* 42 |         INDEX UNIQUE SCAN                       | WSH_TRIPS_U1                |     1 |
  |  43 |        TABLE ACCESS BY INDEX ROWID              | WSH_TRIPS                   |     1 |
  -----------------------------------------------------------------------------------------------

Since the investigation is around the Cardinality of each of the row source, I have removed other information (irrelevant for this discussion) from the execution plan. The Query contains a Subquery with alias as TRP. The Tables queried in the Subquery Drives the Good Plan, whereas, the Subquery is unnested and joined at a later stage in the Problematic Plan (Step 37-43). In the Problematic Plan, the Driving table is HR_LOCATIONS_ALL, which is hash joined to WSH_DELIVERY_DETAILS. The Investigation was around the change in the Execution Path. One of the primary goal of Optimizer is to calculate the Number of rows to be returned by each of the Filter Predicate and then evaluate the Access Patch, including the Driving Table, based on the predicate that fetches the least number of rows. It then also evaluates the Join Method, which is to consider Nested Loop Join for small set of rows or Hash Join / Sort Merge Join for larger set of rows.

The text of the TRP Subquery is pasted below

    	(SELECT DISTINCT  T.TRIP_ID, WDA.DELIVERY_ID 
    	FROM	apps.WSH_DELIVERY_ASSIGNMENTS WDA, 
          	apps.WSH_DELIVERY_LEGS DL,
          	apps.WSH_TRIP_STOPS PICKUP_STOP,  
             	apps.WSH_TRIPS T
	WHERE	WDA.DELIVERY_ID     = DL.DELIVERY_ID 
        AND     DL.PICK_UP_STOP_ID  = PICKUP_STOP.STOP_ID 
        AND     PICKUP_STOP.TRIP_ID = T.TRIP_ID
        AND     T.TRIP_ID  >=  : P_TRIPID_FR
        AND     T.TRIP_ID  <=  : P_TRIPID_TO )  TRP

The Good Plan for this subquery is a Sort Merge Join on WSH_TRIPS & WSH_TRIP_STOPS. Both the tables are accessed via an Index on TRIP_ID. Note that though the range predicate is on TRIP_ID of WSH_TRIPS, the column is joined to TRIP_ID of WSH_TRIP_STOPS and therefore, the optimizer scans an Index on these columns to get the relevant rows from the table. The Optimizer calculation says 72 rows from WSH_TRIPS and 187 rows from WSH_TRIP_STOPS, which are merged join to get final 72 rows from the two tables. This calculation is based on the Input values that Optimized peeked into during the hard parse. For the same set of input values, we have a 10053 trace of the Bad Plan and therefore, I immediately concentrated on the cardinality calculation for the two tables mentioned here. Surprisingly, the first caveat that I observed in the 10053 trace was missing Bind Peeked Data and this was enough to get into the Optimizer calculations.

The Peeked Bind Values section of the 10053 trace shows following information.

*******************************************
Peeked values of the binds in SQL statement
*******************************************
----- Bind Info (kkscoacd) -----
 Bind#0
  oacdty=02 mxl=22(01) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000000 frm=00 csi=00 siz=72 off=0
  No bind buffers allocated
 Bind#1
  oacdty=02 mxl=22(01) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000000 frm=00 csi=00 siz=0 off=24
  No bind buffers allocated
 Bind#2
  oacdty=02 mxl=22(01) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000000 frm=00 csi=00 siz=0 off=48
  No bind buffers allocated
 Bind#3
  No oacdef for this bind.
 Bind#4
  No oacdef for this bind.

When the Query was executed from SQL*Plus, the values for the Binds were populated and were visible in the Peeked Bind values section of 10053 trace. The missing information means, the Optimizer would fall back to the default calculation for a range predicate. From the trace, the computed cardinality for the two tables, WSH_TRIPS & WSH_TRIP_STOPS, are as under :

Access path analysis for WSH_TRIP_STOPS
***************************************
Access path analysis for WSH_TRIP_STOPS
***************************************
SINGLE TABLE ACCESS PATH 
  Single Table Cardinality Estimation for WSH_TRIP_STOPS[PICKUP_STOP] 

  Table: WSH_TRIP_STOPS  Alias: PICKUP_STOP
    Card: Original: 4666397.000000  Rounded: 11666  Computed: 11665.99  Non Adjusted: 11665.99

Access path analysis for WSH_TRIPS
***************************************
SINGLE TABLE ACCESS PATH 
  Single Table Cardinality Estimation for WSH_TRIPS[T] 

  Table: WSH_TRIPS  Alias: T
    Card: Original: 1802653.000000  Rounded: 4507  Computed: 4506.63  Non Adjusted: 4506.63

In the absence of the actual values provided to the Bind Variables, the Optimizer computed the Cardinality based on defaults, which in this case is 0.25% (5% for >= and 5% of = : P_TRIPID_FR and TRIP_ID <= : P_TRIPID_TO is too high. Another predicate in the Outer query is ORGANIZATION_ID and based on this predicate, optimizer generates a plan with the smallest rowsource first. The smallest rowsource is HR_LOCATIONS_ALL with 919 rows and is joined to WSH_DELIVERY_DETAILS table, which is accessed via hinted index WSH_DELIVERY_DETAILS_TI4.

To check and confirm this behaviour, I executed an EXPLAIN PLAN FOR for this query, without setting the values for the bind variables, from SQL*Plus. With no values to the Bind, the EXPLAIN PLAN FOR computed the BAD plan from SQL*Plus. Therefore, now I was sure that the problem occurs when the peeked values to the bind are not available. The next concern was “Why Oracle Report does not peek into the Bind Variables before actually generating the plan ?”.

I assume this to be a default behaviour, where the query is parsed first and then binded. Unfortunately, there is not much written on this. A better option is to write a query in an efficient way or hint the query. To resolve the issue, I modified the query and this modified version worked perfectly well on both the environments i.e. SQL*Plus and Oracle Reports. The modification was in the Subquery TRP. The change is pasted below.

    (SELECT TRIP_ID, DELIVERY_ID FROM 
    (SELECT T.TRIP_ID, WDA.DELIVERY_ID, row_number() over(partition by t.trip_id, wda.delivery_id) rnum 
                FROM   apps.WSH_DELIVERY_ASSIGNMENTS WDA, 
                                apps.WSH_DELIVERY_LEGS DL,
                                apps.WSH_TRIP_STOPS PICKUP_STOP,  
                                apps.WSH_TRIPS T
                WHERE   WDA.DELIVERY_ID     = DL.DELIVERY_ID 
        AND     DL.PICK_UP_STOP_ID  = PICKUP_STOP.STOP_ID 
        AND     PICKUP_STOP.TRIP_ID = T.TRIP_ID
        AND     T.TRIP_ID  >=  : P_TRIPID_FR
        AND     T.TRIP_ID  <=  : P_TRIPID_TO)
     WHERE RNUM=1)  TRP

The modification made sure that the TRP subquery becomes the Driving Query. The relevant portion of the execution plan post modification is attached below. The calculation for WSH_TRIPS is still at 0.25% and is therefore computed as 4507, even then, the Optimizer takes this as a Driving Table, which is what we wanted. This again leads to a common argument, which is, Optimizer is a piece of Code that works on Statistics. It is the way we write a query that dominates the Optimizer.


|* 19 |                  WINDOW SORT PUSHED RANK         |                             | 26689 |
|* 20 |                   FILTER                         |                             |       |
|  21 |                    NESTED LOOPS                  |                             | 26689 |
|  22 |                     NESTED LOOPS                 |                             |  7968 |
|  23 |                      NESTED LOOPS                |                             |  4516 |
|* 24 |                       INDEX RANGE SCAN           | WSH_TRIPS_U1                |  4507 |
|  25 |                       TABLE ACCESS BY INDEX ROWID| WSH_TRIP_STOPS              |     1 |
|* 26 |                        INDEX RANGE SCAN          | WSH_TRIP_STOPS_N1           |     1 |
|  27 |                      TABLE ACCESS BY INDEX ROWID | WSH_DELIVERY_LEGS           |     2 |
|* 28 |                       INDEX RANGE SCAN           | WSH_DELIVERY_LEGS_N2        |     2 |
|* 29 |                     INDEX RANGE SCAN             | WSH_DELIVERY_ASSIGNMENTS_N1 |     3 |

  20 - filter(:P_TRIPID_FR=:P_TRIPID_FR AND "T"."TRIP_ID"=:P_TRIPID_FR AND "PICKUP_STOP"."TRIP_ID"<=:P_TRIPID_TO))
  28 - access("DL"."PICK_UP_STOP_ID"="PICKUP_STOP"."STOP_ID")
  29 - access("WDA"."DELIVERY_ID"="DL"."DELIVERY_ID")
       filter("WDA"."DELIVERY_ID" IS NOT NULL)

India Localization ! R12 Upgrade Performance Issues…..

First, let me wish my readers a very Happy & Prosperous New Year.

While the heading of this blog relates to India Localization and Performance Issues post R12 Upgrade, the solution that I talk about actually translates to Effective Query Writing. One of my customer is in a final phase of Apps R12 Upgrade and is facing some slowness in a Test Environment. While I am working on all of these, I thought of writing on One Issue at a time, as and when, these are resolved. The database is 11gR2. Slowness in Matched Receipt Application was reported and an 10046 Trace file was sent to me for analysis. From the trace file, I could see 2 Queries that were consuming most of the CPU time and resolving these would bring down the execution time. The Queries are mentioned below :

SELECT wda.delivery_id, count(wda.delivery_detail_id) delivery_detail_count
FROM	wsh_new_deliveries wnd, 
	wsh_delivery_assignments wda, 
	wsh_delivery_details wdd, 
	jai_om_oe_so_lines jsl 
WHERE 	wnd.status_code NOT IN ( 'CO', 'CL', 'IT') 
AND 	wnd.organization_id = :1 
AND 	wda.delivery_id = wnd.delivery_id 
AND  	wdd.delivery_detail_id = wda.delivery_detail_id 
AND 	wdd.source_code = 'OE'  
AND 	wdd.organization_id = :2 
AND 	wdd.source_header_id = jsl.header_id 
AND  	wdd.source_line_id = jsl.line_id 
AND 	((:3 = 'UN MATCH' 
AND 	EXISTS (select ref_line_id 
		from 	JAI_CMN_MATCH_RECEIPTS 
		where 	ref_line_id = wda.delivery_detail_id 
		AND 	order_invoice ='O' 
		and 	ship_status IS NULL) ) OR
  	(:4 = 'MATCH' 
  	AND NOT EXISTS (select ref_line_id 
  			from 	JAI_CMN_MATCH_RECEIPTS 
  			where 	ref_line_id = wda.delivery_detail_id 
  			AND  	order_invoice ='O' 
  			and 	ship_status IS NULL) ) ) 
  AND 	wdd.customer_id = nvl(:5, wdd.customer_id ) 
  AND 	wdd.source_header_id = nvl(:6,wdd.source_header_id ) 
  GROUP BY wda.delivery_id ;

SELECT ott.name, ott.transaction_type_id
FROM 	oe_transaction_types_tl ott, 
	oe_order_headers_all oh, 
	oe_order_lines_all ol,
  	jai_om_oe_so_lines jsl 
WHERE 	(NAME LIKE :b1) 
AND 	( ott.transaction_type_id = oh.order_type_id 
AND 	oh.header_id = ol.header_id 
AND 	ol.header_id = jsl.header_id 
AND 	ol.line_id = jsl.line_id 
AND 	ol.ship_from_org_id = :2 
AND	ol.sold_to_org_id = nvl(:3, ol.sold_to_org_id ) ) 
GROUP BY ott.name, ott.transaction_type_id

Since the solution for both the Queries are same, to make this smaller, a detailed explanation on Query 1 wll dominate the blog. The Execution Plan of Query 1 is pasted below and the problematic part is marked as bold (and arrow marked, in case, some browser does not display characters in BOLD). I have also mentioned the run time values of the Bind Variables from v$sql_bind_capture. These values usually help to run the query from SQL*Plus and validate the working of a query.

Bind Capture Value
------------------
SQL_ID        CHILD_ADDRESS    NAME         POSITION DATATYPE_STRING      VALUE_STRING
------------- ---------------- ---------- ---------- -------------------- --------------------
byzqm6cf3j226 07000009D7614120 :1                  1 NUMBER               52
byzqm6cf3j226 07000009D7614120 :2                  2 NUMBER               52
byzqm6cf3j226 07000009D7614120 :3                  3 CHAR(32)             MATCH
byzqm6cf3j226 07000009D7614120 :4                  4 CHAR(32)             MATCH
byzqm6cf3j226 07000009D7614120 :5                  5 CHAR(32)             NULL
byzqm6cf3j226 07000009D7614120 :6                  6 NUMBER               NULL

Plan hash value: 2965431451

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                             |       |       | 64721 (100)|          |
|   1 |  SORT GROUP BY                     |                             |     1 |    67 |            |          |
|   2 |   CONCATENATION                    |                             |       |       |            |          |
|*  3 |    FILTER                          |                             |       |       |            |          |
|*  4 |     FILTER                         |                             |       |       |            |          |
|   5 |      NESTED LOOPS                  |                             |     1 |    67 | 64704  (61)| 00:00:01 |
|   6 |       NESTED LOOPS                 |                             |     1 |    55 | 64704  (61)| 00:00:01 |
|   7 |        NESTED LOOPS                |                             |     7 |   294 | 64691  (61)| 00:00:01 |
|*  8 |         TABLE ACCESS BY INDEX ROWID| WSH_DELIVERY_DETAILS        |     7 |   217 | 64677  (61)| 00:00:01 |<--------
|*  9 |          INDEX RANGE SCAN          | WSH_DELIVERY_DETAILS_TI6    |   142K|       |  4194  (91)| 00:00:01 |<--------
|* 10 |         TABLE ACCESS BY INDEX ROWID| WSH_DELIVERY_ASSIGNMENTS    |     1 |    11 |     2  (50)| 00:00:01 |
|* 11 |          INDEX RANGE SCAN          | WSH_DELIVERY_ASSIGNMENTS_N3 |     1 |       |     0   (0)|          |
|* 12 |        INDEX RANGE SCAN            | WSH_NEW_DELIVERIES_TI1      |     1 |    13 |     2  (50)| 00:00:01 |
|* 13 |       INDEX RANGE SCAN             | JAI_OM_OE_SO_LINES_N1       |     1 |    12 |     0   (0)|          |
|* 14 |     TABLE ACCESS BY INDEX ROWID    | JAI_CMN_MATCH_RECEIPTS      |     1 |    15 |     8  (50)| 00:00:01 |
|* 15 |      INDEX RANGE SCAN              | JAI_CMN_MATCH_RECEIPTS_N1   |     1 |       |     6  (50)| 00:00:01 |
|* 16 |     TABLE ACCESS BY INDEX ROWID    | JAI_CMN_MATCH_RECEIPTS      |     1 |    15 |     8  (50)| 00:00:01 |
|* 17 |      INDEX RANGE SCAN              | JAI_CMN_MATCH_RECEIPTS_N1   |     1 |       |     6  (50)| 00:00:01 |
|* 18 |    FILTER                          |                             |       |       |            |          |
|* 19 |     FILTER                         |                             |       |       |            |          |
|  20 |      NESTED LOOPS                  |                             |     1 |    67 |    16  (50)| 00:00:01 |
|  21 |       NESTED LOOPS                 |                             |     1 |    54 |    14  (50)| 00:00:01 |
|  22 |        NESTED LOOPS                |                             |     1 |    43 |    12  (50)| 00:00:01 |
|* 23 |         TABLE ACCESS BY INDEX ROWID| WSH_DELIVERY_DETAILS        |     1 |    31 |    12  (50)| 00:00:01 |
|* 24 |          INDEX RANGE SCAN          | WSH_DELIVERY_DETAILS_N2     |     9 |       |     6  (50)| 00:00:01 |
|* 25 |         INDEX RANGE SCAN           | JAI_OM_OE_SO_LINES_N1       |     1 |    12 |     0   (0)|          |
|* 26 |        TABLE ACCESS BY INDEX ROWID | WSH_DELIVERY_ASSIGNMENTS    |     1 |    11 |     2  (50)| 00:00:01 |
|* 27 |         INDEX RANGE SCAN           | WSH_DELIVERY_ASSIGNMENTS_N3 |     1 |       |     0   (0)|          |
|* 28 |       INDEX RANGE SCAN             | WSH_NEW_DELIVERIES_TI1      |     1 |    13 |     2  (50)| 00:00:01 |
|* 29 |     TABLE ACCESS BY INDEX ROWID    | JAI_CMN_MATCH_RECEIPTS      |     1 |    15 |     8  (50)| 00:00:01 |
|* 30 |      INDEX RANGE SCAN              | JAI_CMN_MATCH_RECEIPTS_N1   |     1 |       |     6  (50)| 00:00:01 |
|* 31 |     TABLE ACCESS BY INDEX ROWID    | JAI_CMN_MATCH_RECEIPTS      |     1 |    15 |     8  (50)| 00:00:01 |
|* 32 |      INDEX RANGE SCAN              | JAI_CMN_MATCH_RECEIPTS_N1   |     1 |       |     6  (50)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------

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

   3 - filter(((:B4='UN MATCH' AND  IS NOT NULL) OR (:B3='MATCH' AND  IS NULL)))
   4 - filter(:B1 IS NULL)
   8 - filter(("WDD"."SOURCE_CODE"='OE' AND "WDD"."SOURCE_HEADER_ID" IS NOT NULL AND
              "WDD"."CUSTOMER_ID"=NVL(:B2,"WDD"."CUSTOMER_ID")))
   9 - access("WDD"."ORGANIZATION_ID"=:B5)
  10 - filter("WDA"."DELIVERY_ID" IS NOT NULL)
  11 - access("WDD"."DELIVERY_DETAIL_ID"="WDA"."DELIVERY_DETAIL_ID")
  12 - access("WND"."ORGANIZATION_ID"=:B6 AND "WDA"."DELIVERY_ID"="WND"."DELIVERY_ID")
       filter(("WND"."STATUS_CODE"'IT' AND "WND"."STATUS_CODE"'CL' AND "WND"."STATUS_CODE"'CO'))
  13 - access("WDD"."SOURCE_LINE_ID"="JSL"."LINE_ID" AND "WDD"."SOURCE_HEADER_ID"="JSL"."HEADER_ID")
  14 - filter(("SHIP_STATUS" IS NULL AND "ORDER_INVOICE"='O'))
  15 - access("REF_LINE_ID"=:B1)
  16 - filter(("SHIP_STATUS" IS NULL AND "ORDER_INVOICE"='O'))
  17 - access("REF_LINE_ID"=:B1)
  18 - filter(((:B4='UN MATCH' AND  IS NOT NULL) OR (:B3='MATCH' AND  IS NULL)))
  19 - filter(:B1 IS NOT NULL)
  23 - filter(("WDD"."ORGANIZATION_ID"=:B5 AND "WDD"."SOURCE_CODE"='OE' AND
              "WDD"."CUSTOMER_ID"=NVL(:B2,"WDD"."CUSTOMER_ID")))
  24 - access("WDD"."SOURCE_HEADER_ID"=:B1)
  25 - access("WDD"."SOURCE_LINE_ID"="JSL"."LINE_ID" AND "WDD"."SOURCE_HEADER_ID"="JSL"."HEADER_ID")
  26 - filter("WDA"."DELIVERY_ID" IS NOT NULL)
  27 - access("WDD"."DELIVERY_DETAIL_ID"="WDA"."DELIVERY_DETAIL_ID")
  28 - access("WND"."ORGANIZATION_ID"=:B6 AND "WDA"."DELIVERY_ID"="WND"."DELIVERY_ID")
       filter(("WND"."STATUS_CODE"'IT' AND "WND"."STATUS_CODE"'CL' AND "WND"."STATUS_CODE"'CO'))
  29 - filter(("SHIP_STATUS" IS NULL AND "ORDER_INVOICE"='O'))
  30 - access("REF_LINE_ID"=:B1)
  31 - filter(("SHIP_STATUS" IS NULL AND "ORDER_INVOICE"='O'))
  32 - access("REF_LINE_ID"=:B1)

WSH_DELIVERY_DETAIL is the diriving Table, which is scanned via an Index on ORGANIZATION_ID (Index WSH_DELIVERY_DETAILS_T16). See Step 9 from the Predicate Information Section.

Analysis

The Logical Reads of this query is around 0.23 Million per Execution. For Analysis, it was important to validate whether the Driving Table and Access Path chosen by the optimizer is Optimal. Therefore, I first targetted Step 8 & 9 of the Execution plan, which is the driving step. Step 9 says an Index Scan and the estimated rows from this step is around 142k. This means, after scanning and filtering the data for an Organization_ID, the optimizer estimates around 142k rows will be returned and passed to the next step, which is to visit the table to filter additional non-indexed predicates. From Step 8, it can be seen that these non-indexed predicates are SOURCE_CODE, CUSTOMER_ID and SOURCE_HEADER_ID. Once the non-indexed predicates are applied, the optimizer estimates 7 rows to be returned by the final step 8. Based on this, the next action, which is to come out with a Optimal Access Path. In this case, it is a Nested Loop Join. Only 7 rows out of 142K seemed to be a problem and therefore, the next step was to validate the optimizer calculation. Following table shows the Statistics that Optimizer takes into consideration and next we shall manually run the formula to check the calculation.

## Number of Rows in the Table

SQL> @table_stats
Enter value for 1: WSH_DELIVERY_DETAILS
old   2: where table_name=upper('&1')
new   2: where table_name=upper('WSH_DELIVERY_DETAILS')

OWNER                          PAR   NUM_ROWS     BLOCKS LAST_ANAL GLO DEGREE
------------------------------ --- ---------- ---------- --------- --- ----------
WSH                            NO    11148400     673435 14-DEC-11 YES          1

## Column Statistics (Only relevant columns specified in the Query are displayed)

SQL> @column_stats
Enter value for 1: WSH
old   3: where owner='&1'
new   3: where owner='WSH'
Enter value for 2: WSH_DELIVERY_DETAILS
old   4: and   table_name='&2'
new   4: and   table_name='WSH_DELIVERY_DETAILS'

COLUMN_NAME                    NUM_DISTINCT  NUM_NULLS    DENSITY HISTOGRAM
------------------------------ ------------ ---------- ---------- ---------------
CUSTOMER_ID                            9250      31395 .000108108 NONE
ORGANIZATION_ID                          78          0 .012820513 NONE
SOURCE_CODE                               2          0         .5 NONE
SOURCE_HEADER_ID                    1171510    1090944 8.5360E-07 NONE

The table has around 11 Million Rows. The Optimizer Calculation for Number of Rows expected from Index Scan is = 11148400*1/78 (Num_Distinct of Organization_Id) = 142928.905 = 142k. This matches the calculation. Step 8 is a filter from non-indexed columns used in the WHERE clause. Therefore, it is 142928*1/9250*1/2 = 7.72 = 7 (9250 for Customer_id and 2 for Source_code). Optimizer, in this case, has not considered source_header_id in the computation, as this column is also used in the JOIN Predicate, else the expected rows would have gone down further. This miscalculation of 7 Rows is enough to generate a wrong execution plan, particularly if the values against the bind variables :5 and :6 is NULL. Again, let us revisit the WHERE predicate of the query that FILTERS a condition on Customer_id & Source_code.


AND 	wdd.source_code = 'OE'  <-- Non Indexed Column 
AND 	wdd.organization_id = :2 <--- Indexed Column (Bind Value passed as 52)
  AND 	wdd.customer_id = nvl(:5, wdd.customer_id ) <--- Non Indexed Column (Bind Value passed as NULL)

As mentioned in my previous paragraph, the NULL values means a a condition wdd.customer_id = wdd.customer_id, but Optimizer takes this predicate into calculation and therefore, misbehaves. While better indexing strategy or more detailed statistics would help Optimizer calculate nearly accurate cardinality, this can have an impact on other queries as well. Therefore, a better strategy would be to write the query, as per best practices, which would generate an Optimal Execution Plan.

The change would be, since the values for :5 & :6, which is for customer_id and source_header_id respectively, is passed as NULL, these predicates are unwanted in the query. When the Query was executed, with these 2 columns removed, the execution time and the I/O’s reduced. However, we cannot always assume that these will be NULL and therefore, the query needs to be written based on the runtime choice. In this case, since there are 2 columns, which could be either NULL or NOT NULL, there would be 4 Combinations by way of which this query can be written. These Combination will be :>/p>

   1. Customer_ID is NULL and Source_header_id is NULL
   2. Customer_id is NULL and Source_header_id is NOT NULL
   3. Customer_Id is NOT NULL and Source_header_id is NULL
   4. Customer_Id is NOT NULL and Source_header_id is NOT NULL

Since the choice is between 2 values (NULL & NOT NULL), the number of permutation and combination would depend on the number of columns. In this case, 2^2 = 4. If there are 3 columns, it will be 2^3=8. Following table shows the change in the WHERE clause to accommodate the 4 Combinations mentioned above.

## MODIFICATION 1 both customer_id and source_header_id is null

SELECT /*+ MODIFICATION 1 REMOVED BOTH NULL COLUMNS */ wda.delivery_id, count(wda.delivery_detail_id) delivery_detail_count
		FROM	wsh_new_deliveries wnd, 
		wsh_delivery_assignments wda, 
		wsh_delivery_details wdd, 
		jai_om_oe_so_lines jsl 
	WHERE 	wnd.status_code NOT IN ( 'CO', 'CL', 'IT') 
	AND 	wnd.organization_id = :1 
	AND 	wda.delivery_id = wnd.delivery_id 
	AND  	wdd.delivery_detail_id = wda.delivery_detail_id 
	AND 	wdd.source_code = 'OE'  
	AND 	wdd.organization_id = :2 
	AND 	wdd.source_header_id = jsl.header_id 
	AND  	wdd.source_line_id = jsl.line_id 
	AND 	((:3 = 'UN MATCH' 
	AND 	EXISTS (select ref_line_id 
			from 	JAI_CMN_MATCH_RECEIPTS 
			where 	ref_line_id = wda.delivery_detail_id 
			AND 	order_invoice ='O' 
			and 	ship_status IS NULL) ) OR
	  	(:4 = 'MATCH' 
	  	AND NOT EXISTS (select ref_line_id 
	  			from 	JAI_CMN_MATCH_RECEIPTS 
	  			where 	ref_line_id = wda.delivery_detail_id 
	  			AND  	order_invoice ='O' 
	  			and 	ship_status IS NULL) ) ) 
--	  AND 	wdd.customer_id = nvl(:5, wdd.customer_id ) 
--	  AND 	wdd.source_header_id = nvl(:6,wdd.source_header_id ) 
	  GROUP BY wda.delivery_id;

## MODIFICATION 2 customer_id is null and source_header_id is not null

SELECT /*+ MODIFICATION 2 CUSTOMER ID IS NULL AND SOURCE ID IS NOT NULL */ wda.delivery_id, count(wda.delivery_detail_id) delivery_detail_count
		FROM	wsh_new_deliveries wnd, 
		wsh_delivery_assignments wda, 
		wsh_delivery_details wdd, 
		jai_om_oe_so_lines jsl 
	WHERE 	wnd.status_code NOT IN ( 'CO', 'CL', 'IT') 
	AND 	wnd.organization_id = :1 
	AND 	wda.delivery_id = wnd.delivery_id 
	AND  	wdd.delivery_detail_id = wda.delivery_detail_id 
	AND 	wdd.source_code = 'OE'  
	AND 	wdd.organization_id = :2 
	AND 	wdd.source_header_id = jsl.header_id 
	AND  	wdd.source_line_id = jsl.line_id 
	AND 	((:3 = 'UN MATCH' 
	AND 	EXISTS (select ref_line_id 
			from 	JAI_CMN_MATCH_RECEIPTS 
			where 	ref_line_id = wda.delivery_detail_id 
			AND 	order_invoice ='O' 
			and 	ship_status IS NULL) ) OR
	  	(:4 = 'MATCH' 
	  	AND NOT EXISTS (select ref_line_id 
	  			from 	JAI_CMN_MATCH_RECEIPTS 
	  			where 	ref_line_id = wda.delivery_detail_id 
	  			AND  	order_invoice ='O' 
	  			and 	ship_status IS NULL) ) ) 
--	  AND 	wdd.customer_id = nvl(:5, wdd.customer_id ) 
	  AND 	wdd.source_header_id = :5 
	  GROUP BY wda.delivery_id;

## MODIFICATION 4 customer_id is not null and source_header_id is not null

SELECT /*+ MODIFICATION 3 BOTH ARE NOT NULL COLUMNS */ wda.delivery_id, count(wda.delivery_detail_id) delivery_detail_count
		FROM	wsh_new_deliveries wnd, 
		wsh_delivery_assignments wda, 
		wsh_delivery_details wdd, 
		jai_om_oe_so_lines jsl 
	WHERE 	wnd.status_code NOT IN ( 'CO', 'CL', 'IT') 
	AND 	wnd.organization_id = :1 
	AND 	wda.delivery_id = wnd.delivery_id 
	AND  	wdd.delivery_detail_id = wda.delivery_detail_id 
	AND 	wdd.source_code = 'OE'  
	AND 	wdd.organization_id = :2 
	AND 	wdd.source_header_id = jsl.header_id 
	AND  	wdd.source_line_id = jsl.line_id 
	AND 	((:3 = 'UN MATCH' 
	AND 	EXISTS (select ref_line_id 
			from 	JAI_CMN_MATCH_RECEIPTS 
			where 	ref_line_id = wda.delivery_detail_id 
			AND 	order_invoice ='O' 
			and 	ship_status IS NULL) ) OR
	  	(:4 = 'MATCH' 
	  	AND NOT EXISTS (select ref_line_id 
	  			from 	JAI_CMN_MATCH_RECEIPTS 
	  			where 	ref_line_id = wda.delivery_detail_id 
	  			AND  	order_invoice ='O' 
	  			and 	ship_status IS NULL) ) ) 
	  AND 	wdd.customer_id = :5 
	  AND 	wdd.source_header_id = :6 
	  GROUP BY wda.delivery_id;

## MODIFICATION 4 customer_id is not null and source_header_id is null

SELECT /*+ MODIFICATION 4 CUSTOMER_ID is NOT NULL */ wda.delivery_id, count(wda.delivery_detail_id) delivery_detail_count
		FROM	wsh_new_deliveries wnd, 
		wsh_delivery_assignments wda, 
		wsh_delivery_details wdd, 
		jai_om_oe_so_lines jsl 
	WHERE 	wnd.status_code NOT IN ( 'CO', 'CL', 'IT') 
	AND 	wnd.organization_id = :1 
	AND 	wda.delivery_id = wnd.delivery_id 
	AND  	wdd.delivery_detail_id = wda.delivery_detail_id 
	AND 	wdd.source_code = 'OE'  
	AND 	wdd.organization_id = :2 
	AND 	wdd.source_header_id = jsl.header_id 
	AND  	wdd.source_line_id = jsl.line_id 
	AND 	((:3 = 'UN MATCH' 
	AND 	EXISTS (select ref_line_id 
			from 	JAI_CMN_MATCH_RECEIPTS 
			where 	ref_line_id = wda.delivery_detail_id 
			AND 	order_invoice ='O' 
			and 	ship_status IS NULL) ) OR
	  	(:4 = 'MATCH' 
	  	AND NOT EXISTS (select ref_line_id 
	  			from 	JAI_CMN_MATCH_RECEIPTS 
	  			where 	ref_line_id = wda.delivery_detail_id 
	  			AND  	order_invoice ='O' 
	  			and 	ship_status IS NULL) ) ) 
	  AND 	wdd.customer_id = :5
--	  AND 	wdd.source_header_id = nvl(:6,wdd.source_header_id ) 
	  GROUP BY wda.delivery_id;

The change require an IF..THEN..ELSE loop to check for the values of the Bind Variables at Run Time and execute the query that is relevant. With this change, the Optimizer calculated correct cardinality based on the predicates actually used by the User and came up with a perfect execution plan. Below is the plan, when the query was run at the customer site for both values as null.

Plan hash value: 466680131

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                             |       |       |       |   114K(100)|          |
|   1 |  SORT GROUP BY                   |                             |     1 |    62 |       |   114K (59)| 00:00:01 |
|*  2 |   FILTER                         |                             |       |       |       |            |          |
|   3 |    NESTED LOOPS                  |                             |   511 | 31682 |       |   114K (59)| 00:00:01 |
|*  4 |     HASH JOIN                    |                             |   511 | 25550 |  2400K|   114K (59)| 00:00:01 |
|*  5 |      TABLE ACCESS BY INDEX ROWID | WSH_DELIVERY_DETAILS        | 64471 |  1636K|       | 62327  (59)| 00:00:01 |
|*  6 |       INDEX RANGE SCAN           | WSH_DELIVERY_DETAILS_TI6    |   142K|       |       |  4194  (91)| 00:00:01 |
|   7 |      NESTED LOOPS                |                             |       |       |       |            |          |
|   8 |       NESTED LOOPS               |                             | 72604 |  1701K|       | 48471  (55)| 00:00:01 |
|*  9 |        INDEX RANGE SCAN          | WSH_NEW_DELIVERIES_TI1      | 16146 |   204K|       |  2197  (91)| 00:00:01 |
|* 10 |        INDEX RANGE SCAN          | WSH_DELIVERY_ASSIGNMENTS_N1 |     5 |       |       |     0   (0)|          |
|  11 |       TABLE ACCESS BY INDEX ROWID| WSH_DELIVERY_ASSIGNMENTS    |     4 |    44 |       |     4  (50)| 00:00:01 |
|* 12 |     INDEX RANGE SCAN             | JAI_OM_OE_SO_LINES_N1       |     1 |    12 |       |     0   (0)|          |
|* 13 |    TABLE ACCESS BY INDEX ROWID   | JAI_CMN_MATCH_RECEIPTS      |     1 |    15 |       |     8  (50)| 00:00:01 |
|* 14 |     INDEX RANGE SCAN             | JAI_CMN_MATCH_RECEIPTS_N1   |     1 |       |       |     6  (50)| 00:00:01 |
|* 15 |    TABLE ACCESS BY INDEX ROWID   | JAI_CMN_MATCH_RECEIPTS      |     1 |    15 |       |     8  (50)| 00:00:01 |
|* 16 |     INDEX RANGE SCAN             | JAI_CMN_MATCH_RECEIPTS_N1   |     1 |       |       |     6  (50)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------------

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

   2 - filter(((:B3='UN MATCH' AND  IS NOT NULL) OR (:B4='MATCH' AND  IS NULL)))
   4 - access("WDD"."DELIVERY_DETAIL_ID"="WDA"."DELIVERY_DETAIL_ID")
   5 - filter(("WDD"."SOURCE_CODE"='OE' AND "WDD"."SOURCE_HEADER_ID" IS NOT NULL))
   6 - access("WDD"."ORGANIZATION_ID"=:B2)
   9 - access("WND"."ORGANIZATION_ID"=:B1)
       filter(("WND"."STATUS_CODE"'IT' AND "WND"."STATUS_CODE"'CL' AND "WND"."STATUS_CODE"'CO'))
  10 - access("WDA"."DELIVERY_ID"="WND"."DELIVERY_ID")
       filter("WDA"."DELIVERY_ID" IS NOT NULL)
  12 - access("WDD"."SOURCE_LINE_ID"="JSL"."LINE_ID" AND "WDD"."SOURCE_HEADER_ID"="JSL"."HEADER_ID")
  13 - filter(("SHIP_STATUS" IS NULL AND "ORDER_INVOICE"='O'))
  14 - access("REF_LINE_ID"=:B1)
  15 - filter(("SHIP_STATUS" IS NULL AND "ORDER_INVOICE"='O'))
  16 - access("REF_LINE_ID"=:B1)

With the same indexes, but slight modification in the Query, the optimizer has come up with a plan that does a HASH Join (as against Nested Loop Join) based on the number of rows expected from WSH_DELIVERY_DETAILS Table.

This modification was applied via an application patch, which resolved the Match Receipt Application Performance Issues. The benefit was due to the reduced number of Logical Reads.