Sangam 2017 ! Largest Annual Event of All India Oracle User Group

Sangam, India’s largest All India Oracle User Group Event is back and this time in Hyderabad. Oracle experts across the globe would be speaking and therefore, this is the best opportunity to learn from them and collaborate with other Oracle professionals.

I will be presenting 2 sessions and will be hosting a “Fireside Chat with Performance Tuning Experts” round table discussions. Along side me, there would be Tirthankar Lahiri, VP-Product Management for In-Memory Technologies, Oracle, Karan Dodwal and Kishore Surve.

Oracle Autonomous Database is one of the most talked about technology and many of you would be eager to know more about it. I will be speaking on Autonomous Database on 9th December 2017. At present, it is scheduled for 2 pm India Time. Please do check the schedule, as it can change. Seats are limited. Therefore, to avoid disappointment, kindly book your seats in advance.

Another session of mine is on “Indexing : Facts and Myths”. This session will walk you through some of the common myths and few facts. A must attend session for Technical Folks. Again, due to the limited capacity, it is better to book your seat in advance. This is scheduled for 8th December 2017.

On 9th December 2017, join me and other Oracle Experts for a fireside chat on performance. Bring your questions and get response from the experts. Performance queries are difficult to address without some data or artifacts. If you can carry some of these, that would help us further and the chat would me more meaningful.

See you in Hyderabad on 8th December.

Advertisements

Index – Ordering of Columns

Post my previous Blog on Consistent Gets for an Index Scan, there were few other queries that I received over the chat. The queries were related to Clustering Factor of an Index and therefore, the recommendation or comment was that the Columns should be ordered to keep the value of a Clustering Factor as low as possible. The other comment was that High Cardinality or Low Cardinality plays a critical role with an exception for Unique or Primary Key Indexes. Again, Clustering Factor was cited as the reason behind this comment. Thought of clarifying this as well. In my opinion, this is again a MYTH.

The myth that I am going to address here is – Indexes and Column Ordering should be designed such that the Clustering Factor is as low as possible. This means, an Index on (B,A) should be preferred than (A,B) if the clustering factor of (A,B) is higher than (B,A).

Assuming, I have application queries, like :

select C, D from TABLE_A where A=:b1;

select C, D from TABLE_A where B=:b1 and A=:b2;

If I go by the above myth, shall I then create an Index on (B,A) ? Will this index be used for Query#1 ? Remember, Index Skip Scan is an Optimizer decision and is chosen if the leading column is a low cardinality column. The definition of low cardinality would be an Optimizer decision based on the costing. If an index on (B,A) is not used by Query#1, then shall I go ahead and create another Index on A ? Indexes improve query performance but are overhead for DML’s and therefore, less the number of Indexes, better the DML performance would be. Further, if a column is badly clustered, whether it is a leading column or a trailing column, it doesn’t matter and we will see this with an example. Therefore, as I mentioned in earlier and in my previous blog, Column Ordering of an Index should be basis your application queries and should not be designed basis any other table or column level statistics.

In this case, I am creating a table VIVEK_TEST with 100K rows. Column BC stands for BAD_CLUSTERED and will dominate the clustering factor. There are other 2 columns of interest – Object_ID which is a non unique column and RNUM_UNQ which is a Unique Column.


exec dbms_random.seed(0);

create table vivek_test as
with test as
(select * from all_objects where rownum between 1 and 10000)
select  trunc((rownum-1)/1000) object_id,
	rownum	rnum_unq,
        round(dbms_random.value(1,100),0) bc,
        a.object_name,
        a.temporary,
        a.created
from    test a,
        test b
where  rownum between 1 and 10000;

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

column owner for a30
select owner, num_rows, blocks from dba_tables where table_name='VIVEK_TEST';

OWNER                  NUM_ROWS     BLOCKS
-------------------- ---------- ----------
SCOTT                     10000         62

First, I will create 4 Indexes – 2 of these are Single Column Indexes and 2 are Multi-Column.

create index vivek_test_oid on vivek_test(object_id);
create index vivek_test_oid_bc on vivek_test(object_id, bc);
create index vivek_test_bc on vivek_test(bc);
create index vivek_test_bc_oid on vivek_test(bc,object_id);

column index_name for a30
select index_name, num_rows, blevel, leaf_blocks, distinct_keys, clustering_factor
from    dba_indexes
where   table_name='VIVEK_TEST'
order by 1;

INDEX_NAME                       NUM_ROWS     BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR
------------------------------ ---------- ---------- ----------- ------------- -----------------
VIVEK_TEST_BC                       10000          1          20           100              4642 <-- Single Column on BC (Bad_Clustered)
VIVEK_TEST_BC_OID                   10000          1          24          1000              4642 <-- BC is the Leading Column
VIVEK_TEST_OID                      10000          1          20            10                56
VIVEK_TEST_OID_BC                   10000          1          24          1000              4856 <-- BC is the Trailing Column

As seen in the example above, column ordering doesn’t matter when it comes to the Clustering Factor. My choice of Indexing would be :

VIVEK_TEST_BC_OID if most of my queries are on 
	BC=:b1;
	BC =:b1 and object_id=:b2;

VIVEK_TEST_OID_BC if most of my queries are on 
	object_id=:b1;
	BC=:b1 and object_id=:b2;

Now, I will drop these indexes and create new indexes on the combination of BC and RNUM_UNQ. RNUM_UNQ is 100% Unique Column.

create index vivek_test_rnum on vivek_test(rnum_unq);
create index vivek_test_rnum_bc on vivek_test(rnum_unq, bc);
create index vivek_test_bc on vivek_test(bc);
create index vivek_test_bc_rnum on vivek_test(bc, rnum_unq);


column index_name for a30
select index_name, num_rows, blevel, leaf_blocks, distinct_keys, clustering_factor
from    dba_indexes
where   table_name='VIVEK_TEST'
order by 1;

INDEX_NAME                       NUM_ROWS     BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR
------------------------------ ---------- ---------- ----------- ------------- -----------------
VIVEK_TEST_BC                       10000          1          20           100              4642 <-- Single Column on BC (Bad_Clustered)
VIVEK_TEST_BC_RNUM                  10000          1          26         10000              4642 <-- Leading Column dominates CF
VIVEK_TEST_RNUM                     10000          1          21         10000                56
VIVEK_TEST_RNUM_BC                  10000          1          26         10000                56


For an Index with a Column, either leading or trailing, with 100% Unique values, the leading column would dictate the calculation of Clustering Factor. This is obvious as for the Clustering factor calculation, the values are sorted on the first_column then on Second_Column. For a better idea of how is it calculated, you may run the following queries :

## CF Calculation for BC, RNUM_UNQ (it matches the value as per DBA_INDEXES)

select  sum(block_change) from (
select  block_fno, bc, RNUM_UNQ, prev_bfno,
        (case when nvl(prev_bfno,0)!=block_fno then 1 else 0 end) block_change from (
        select  block_fno, bc, RNUM_UNQ, lag(block_fno) over (order by bc, rnum_unq) prev_bfno from (
                select  dbms_rowid.rowid_block_number(rowid)||'.'||
                        dbms_rowid.ROWID_TO_ABSOLUTE_FNO(rowid,'&Schema','&table_name') block_fno,
                        bc, RNUM_UNQ
                from  VIVEK_TEST
                order by bc, RNUM_UNQ, block_fno)
        )
);

SUM(BLOCK_CHANGE)
-----------------
             4642


## CF Calculation for RNUM_UNQ, BC (it matches the value as per DBA_INDEXES)

select  sum(block_change) from (
select  block_fno, RNUM_UNQ, bc, prev_bfno,
        (case when nvl(prev_bfno,0)!=block_fno then 1 else 0 end) block_change from (
        select  block_fno, RNUM_UNQ, bc, lag(block_fno) over (order by rnum_unq, bc) prev_bfno from (
                select  dbms_rowid.rowid_block_number(rowid)||'.'||
                        dbms_rowid.ROWID_TO_ABSOLUTE_FNO(rowid,'&Schema','&table_name') block_fno,
                        bc, RNUM_UNQ
                from  VIVEK_TEST
                order by RNUM_UNQ, bc, block_fno)
        )
);

SUM(BLOCK_CHANGE)
-----------------
               56

This query can be further modified to get the calculation. Remove the SUM(BLOCK_CHANGE) SELECT from the query and you could generate the output with the calculation. For every Index Entry, if the table block is changed, the BLOCK_CHANGE value is set to 1. This query will help you understand the calculation. This holds true for a combination of Non-Unique columns as well. It is not that for column involving Unique Column, the calculation is different and this will be clear if you deep dive into the queries pasted above.

During my session, I also mentioned a FLAW in Clustering Factor. It is that it doesn’t take into consideration the caching effect and to address this, Oracle introduced TABLE_CACHED_BLOCKS preference. I am against setting manually setting clustering_factor to a lower value as it will be overwritten by the next statistics gathering process. Also, I am STRONGLY against CTAS using ORDER BY to improve the clustering_factor. By using CTAS, you can improve the clustering_factor of one index, but it will impact this for other indexes. The best way is to use the TABLE_CACHED_BLOCKS preference as depicted below.

I will use the queries pasted above to come out with the actual clustering_factor and will then set the TABLE_CACHED_BLOCKS preference. For demonstration, I will work on an Index on BC, RNUM_UNQ. This process can be used for any of the indexes.

## I will create a temporary table to hold the clustering_factor calculation


create table cluf_factor as
select bc, blkno,
        lag(blkno,1,blkno) over(order by bc) prev_blkno,
        case when blkno!=lag(blkno,1,blkno) over(order by bc) or rownum=1
           then 1 else null end cluf_ft from
(select bc, rnum_unq, dbms_rowid.rowid_block_number(rowid) blkno
from    VIVEK_TEST
where   BC is not null
order by bc);

compute sum of cnt on report
break on report
select blkno, count(*) cnt from cluf_factor group by blkno order by 1;

     BLKNO        CNT
---------- ----------
    255883        188
    255884        185
    255885        185
    255886        185
    255887        185
    255936        182
    255937        180
    255938        180
    255939        180
    255940        180
    255941        180
    255942        180
    255943        180
    255945        180
    255946        180
    255947        180
    255948        180
    255949        180
    255950        180
    255951        180
    258896        180
    258897        180
    258898        180
    258899        180
    258900        180
    258901        180
    258902        180
    258903        180
    258905        180
    258906        180
    258907        180
    258908        180
    258909        180
    258910        180
    258911        180
    260480        180
    260481        180
    260482        180
    260483        180
    260484        180
    260485        180
    260486        180
    260487        180
    260489        180
    260490        180
    260491        180
    260492        180
    260493        180
    260494        180
    260495        180
    260496        180
    260497        180
    260498        180
    260499        180
    260500        180
    260501         70
           ----------
sum             10000

56 rows selected.

As per the output, each of the block was touched around 180 times. Each of these touch were considered as it these were read from the Disk. However, Oracle purely works in Memory (Buffer_Cache, Shared_Pool etc) and once the block is read from disk, it is cached in the Buffer_cache thus avoiding the disk i/o. This flaw causes unwanted high clustering_factor and therefore, is addressed by way of TABLE_CACHED_BLOCKS.

TABLE_CACHED_BLOCKS value defaults to 1. Increasing this value (to, say 250) means, clustering_factor for an Index will not be incremented, if the table block being referenced by the current Index entry has been referenced by any of the previous 250 Index Entries. In our case, each of the table blocks were referenced by previous 180 index entries. Therefore, I will change this value to 180.

select index_name, leaf_blocks, clustering_factor from dba_indexes
where table_name = 'VIVEK_TEST';

INDEX_NAME                     LEAF_BLOCKS CLUSTERING_FACTOR
------------------------------ ----------- -----------------
VIVEK_TEST_RNUM                         21                56
VIVEK_TEST_RNUM_BC                      26                56
VIVEK_TEST_BC                           20              4642
VIVEK_TEST_BC_RNUM                      26              4642

select dbms_stats.get_prefs(pname=>'TABLE_CACHED_BLOCKS',ownname=>'SCOTT',tabname=>'VIVEK_TEST') preference from dual;

PREFERENCE
-----------------------------------------------------------
1

## Setting TABLE_CACHED_BLOCKS to 180
exec dbms_stats.set_table_prefs(ownname=>'SCOTT',tabname=>'VIVEK_TEST',pname=>'TABLE_CACHED_BLOCKS',PVALUE=>180);

select dbms_stats.get_prefs(pname=>'TABLE_CACHED_BLOCKS',ownname=>'SCOTT',tabname=>'VIVEK_TEST') preference from dual;

PREFERENCE
------------------------------------------------------------
180

exec dbms_stats.gather_table_stats(user,'VIVEK_TEST',method_opt=>'for all columns size 1', no_invalidate=>false);

select index_name, leaf_blocks, clustering_factor from dba_indexes
where table_name = 'VIVEK_TEST';

INDEX_NAME                     LEAF_BLOCKS CLUSTERING_FACTOR
------------------------------ ----------- -----------------
VIVEK_TEST_RNUM                         21                56
VIVEK_TEST_RNUM_BC                      26                56
VIVEK_TEST_BC                           20                56
VIVEK_TEST_BC_RNUM                      26                56

Clustering Factor is calculated for Unique Indexes as well and is critical for costing. The only time when clustering_factos is not considered or not relevant for Unique or Primary Keys is when querying for all the columns part of the Unique or Primary Key i.e. INDEX UNIQUE SCAN. However, clustering_factor will play a critical role when accessing the Unique or Primary Key using INDEX RANGE SCAN.

With this blog, I tried addressing following Myth’s –

- 	Indexes and Column Ordering should be designed such that the Clustering Factor is as low as possible. 
- 	Clustering_Factor is irrelevant for Unique and Primary Key Indexes.
-	High Cardinality or Low Cardinality play important roles with an exception for Unique & Primary Key.

Be it a Unique Index / Primary Key Index or any other Index, column ordering has to be basis your application queries.

One interesting fact on TABLE_CACHED_BLOCKS : While testing this, I actually created a table with 100k rows. The number of blocks in the table were around 469 and the table block were referenced by around 230 previous index entries. Therefore, I modified this preference to 230 and regathered the statistics. It didn’t work. The same table creation script with 10k rows, everything worked as expected. I am investigating this and will update the blog, once I have something to share.

Consistent Gets for an Index Scan

This question was raised by a participant during my User Group Session on “Indexing : Fact & Myth Session”. Therefore, I thought of writing about it with the same example that I demonstrated..

I was demonstrating on a Myth that “High Cardinality Column should be a Leading Column of an Index” and for this, I created following table with 2 Indexes.

create table t1 as
select * from all_objects;

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

SQL> select owner, num_rows, blocks from dba_tables where table_name='T1' and owner='SCOTT';

OWNER                  NUM_ROWS     BLOCKS
-------------------- ---------- ----------
SCOTT                     68605       1377

SQL> select column_name, num_distinct, num_nulls from dba_tab_columns
where   owner='SCOTT'
and     table_name='T1'
and     column_name in ('OBJECT_ID','TEMPORARY')
order by 1;

COLUMN_NAME                    NUM_DISTINCT  NUM_NULLS
------------------------------ ------------ ----------
OBJECT_ID                             68605          0
TEMPORARY                                 2          0

SQL> create index t1_ot on t1(object_id, temporary);

Index created.

SQL> create index t1_to on t1(temporary,object_id);

Index created.

SQL> select index_name, blevel, leaf_blocks, clustering_factor from dba_indexes
where   table_name='T1'
order by 1;

INDEX_NAME                         BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
------------------------------ ---------- ----------- -----------------
T1_OT                                   1         171              1458
T1_TO                                   1         171              1494

So, I have a table with 68605 rows. Object_ID is 100% Distinct and Temporary has 2 Distinct Values. I have 2 indexes on it, which are on object_id & temporary. T1_OT is on (Object_ID, Temporary) and T1_TO is on (Temporary, Object_ID). The naming convention stands for the first letter of the columns in the order they are in the Index. So, for T1_OT O->Object_id and T->Temporary.

The Index Statistics shows that the two indexes are almost same, in terms of Height (BLEVEL), number of Leaf Blocks. A minor different in the Clustering_factor though.

I than executed the queries to demonstrate that the I/O’s done by queries using any of the 2 indexes is exactly same. For this, I executed the query and it used Index T1_OT and and then hinted the query to make use of T1_TO Index. The Cost and IO’s for both the queries are exactly same, which leads to a conclusion that cardinality doesn’t matter when designing a Index Strategy. Index Columns should be based on Application Queries and the Columns. The queries were executed twice to ensure that the consistent gets that we are post the hard parsing.


## IO's for the Query using an Index T1_OT

select owner, object_name from t1
where      object_id = 58
and        temporary='N';

OWNER                OBJECT_NAME
-------------------- ------------------------------
SYS                  I_CCOL2

set autot trace
select owner, object_name from t1
where      object_id = 58
and        temporary='N';

Execution Plan
----------------------------------------------------------
Plan hash value: 3109227855

---------------------------------------------------------------------------------------------
| Id  | Operation                           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |       |     1 |    48 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1    |     1 |    48 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | T1_OT |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"=58 AND "TEMPORARY"='N')

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets 
          0  physical reads
          0  redo size
...
...
          1  rows processed

## IO's with Index T1_TO

SQL> select /*+ index(t1,t1_to) */ owner, object_name from t1
     where      object_id = 58
     and        temporary='N';

OWNER                OBJECT_NAME
-------------------- ------------------------------
SYS                  I_CCOL2

Elapsed: 00:00:00.00
SQL> pause;

set autot trace
select /*+ index(t1,t1_to) */ owner, object_name from t1
where      object_id = 58
and        temporary='N';


Execution Plan
----------------------------------------------------------
Plan hash value: 1129381402

---------------------------------------------------------------------------------------------
| Id  | Operation                           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |       |     1 |    48 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1    |     1 |    48 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | T1_TO |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   2 - access("TEMPORARY"='N' AND "OBJECT_ID"=58)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
...
...
          1  rows processed

While I have 2 Indexes on the same columns only the ordered changed, optimizer chose an Index on Object_ID and Temporary. As my sessions are interactive (and this sometimes mean that my sessions take more time than alloted :)), I asked the participants the reason behind this optimizer decision and there were lot many assumptions. Anju Garg came out with the correct guess. However, will disclose this later.

At this point, the question raised was, why there are 4 Consistent I/O’s? The assumption here was that it should be 3 (BLEVEL + LEAF BLOCK Access + Table Block).

Next, I dropped any one of the Index and re-created it as a Unique Index. Remember, Object_ID is 100% Distinct. So, I will drop and re-create T1_OT.

SQL> drop index t1_ot;

Index dropped.

Elapsed: 00:00:00.04
SQL> create unique index t1_ot_uq on t1(object_id, temporary);

Index created.

SQL> select index_name, blevel, leaf_blocks, clustering_factor, uniqueness from dba_indexes
where   table_name='T1'
order by 1;
  2    3
INDEX_NAME                         BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR UNIQUENES
------------------------------ ---------- ----------- ----------------- ---------
T1_OT_UQ                                1         162              1457 UNIQUE
T1_TO                                   1         171              1493 NONUNIQUE

I will then execute the queries to check for the consistent gets. This time, the consistent gets for the execution plan with Unique Index is 3 (as against 4 for the same non-unique index).

select owner, object_name from t1
where      object_id = 58
and        temporary='N';

Execution Plan
----------------------------------------------------------
Plan hash value: 1959391432

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |    48 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1       |     1 |    48 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | T1_OT_UQ |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"=58 AND "TEMPORARY"='N')


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads

There is a difference in the Consistent Gets with Unique and Non-Unique Index. The height of both these Indexes are exactly same. This difference was important to get to original question. So, I generated a 10046 trace for the 2 Queries (with Unique and Without Unique Scan) and the relevant portion from the trace is as under, which will explain the reason behind 4 Consistent Gets.

## 10046 portion for Non-Unique index. Please see the BOLD and UNDERLINED portion. The extra cr=1 for FETCH#18446604434610142176.
## In this case, once the Blocks are fetched from an Index (cr=2) and Table (cr=1) Total cr=3, there is an extra cr immediately after 
## SQL*Net message to client. So, the total cr = 2 + 1 + 1 (extra) = 4.

PARSE #18446604434610142176:c=118,e=119,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1259244381,tim=7093069466
EXEC #18446604434610142176:c=88,e=93,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1259244381,tim=7093070117
WAIT #18446604434610142176: nam='SQL*Net message to client' ela= 8 driver id=1413697536 #bytes=1 p3=0 obj#=0 tim=7093070421
WAIT #18446604434610142176: nam='db file scattered read' ela= 2292 file#=12 block#=275312 blocks=8 obj#=77545 tim=7093073241
WAIT #18446604434610142176: nam='db file sequential read' ela= 49 file#=12 block#=255875 blocks=1 obj#=77543 tim=7093073719
FETCH #18446604434610142176:c=2088,e=3043,p=9,cr=3,cu=0,mis=0,r=1,dep=0,og=1,plh=1259244381,tim=7093073891
WAIT #18446604434610142176: nam='SQL*Net message from client' ela= 624 driver id=1413697536 #bytes=1 p3=0 obj#=77543 tim=7093074878
FETCH #18446604434610142176:c=104,e=104,p=0,cr=1,cu=0,mis=0,r=0,dep=0,og=1,plh=1259244381,tim=7093075164
STAT #18446604434610142176 id=1 cnt=1 pid=0 pos=1 obj=77543 op='TABLE ACCESS BY INDEX ROWID T1 (cr=4 pr=9 pw=0 str=1 time=3109 us cost=2 size=48 card=1)'
STAT #18446604434610142176 id=2 cnt=1 pid=1 pos=1 obj=77545 op='INDEX RANGE SCAN T1_TO (cr=3 pr=8 pw=0 str=1 time=2835 us cost=1 size=0 card=1)'
WAIT #18446604434610142176: nam='SQL*Net message to client' ela= 6 driver id=1413697536 #bytes=1 p3=0 obj#=77543 tim=7093076331

## 10046 portion for Unique index. In this case, once the Blocks are fetched from an Index (cr=2) and Table (cr=1), there is no extra cr.

PARSE #18446604434610123376:c=134,e=135,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1959391432,tim=7120639467
EXEC #18446604434610123376:c=89,e=90,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1959391432,tim=7120640009
WAIT #18446604434610123376: nam='SQL*Net message to client' ela= 8 driver id=1413697536 #bytes=1 p3=0 obj#=0 tim=7120640248
WAIT #18446604434610123376: nam='db file scattered read' ela= 5875 file#=12 block#=260496 blocks=8 obj#=77546 tim=7120646397
WAIT #18446604434610123376: nam='db file sequential read' ela= 64 file#=12 block#=255875 blocks=1 obj#=77543 tim=7120646786
FETCH #18446604434610123376:c=1407,e=6569,p=9,cr=3,cu=0,mis=0,r=1,dep=0,og=1,plh=1959391432,tim=7120646947
STAT #18446604434610123376 id=1 cnt=1 pid=0 pos=1 obj=77543 op='TABLE ACCESS BY INDEX ROWID T1 (cr=3 pr=9 pw=0 str=1 time=6549 us cost=2 size=48 card=1)'
STAT #18446604434610123376 id=2 cnt=1 pid=1 pos=1 obj=77546 op='INDEX UNIQUE SCAN T1_OT_UQ (cr=2 pr=8 pw=0 str=1 time=6240 us cost=1 size=0 card=1)'
WAIT #18446604434610123376: nam='SQL*Net message from client' ela= 583 driver id=1413697536 #bytes=1 p3=0 obj#=77543 tim=7120652705
FETCH #18446604434610123376:c=15,e=15,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=1959391432,tim=7120652925
WAIT #18446604434610123376: nam='SQL*Net message to client' ela= 5 driver id=1413697536 #bytes=1 p3=0 obj#=77543 tim=7120653074

From this, I can assume that the steps carried out for a Non-Unique Index is as under (as it an Index-Range Scan).

-	Read the Root Block to get the address of the Leaf Block (IO = 1). 
- 	Read the Leaf Block to check for the matching Object_ID and Temporary Column. Get the ROWID for the table block. (IO = 2).
-	Go to the Table Block to read the other required columns listed in the SELECT list. (IO=3).
-	Go back to the Index Block to check for any other Object_ID and Temporary Values. (IO=4).
-	It is here that it gets to know that there are no more rows.

Bullet Point 4 is not required for a Unique Scan as Oracle is aware that it is a Unique Index and therefore, there will be no additional read required. Further, to confirm this, I executed a query on OBJECT_ID using an Unique Index. Remember, while Object_Id is 100% Unique, but the Unique Index is on the 2 columns and I am not referring the 2nd column in the query, which will change the plan from UNIQUE SCAN to RANGE SCAN.

select owner, object_name from t1
where      object_id = 58;

Execution Plan
----------------------------------------------------------
Plan hash value: 1834913555

------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |          |     1 |    46 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1       |     1 |    46 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | T1_OT_UQ |     1 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"=58)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets 

So, this answers the question raised during the session on the rationale behind 4 Consistent Reads. Unique and Non-Unique Indexes are internally same with only a difference in the way these are accessed. With Index Range Scan, the behaviour of both the indexes are exactly same.

Now, for the another question on why the optimizer used T1_OT (by default) and not T1_TO ? The reason was a TIE between the 2 indexes, which is usually very rare with Cost Based Optimization and due to the TIE, optimizer preferred an Index in an alphabetical order. TI_OT comes before TI_TO. To demonstrate this, I dropped and recreated T1_TO as T1_1TO and optimized started using T1_1TO by default.

Performance Tuning Day – Mumbai Chapter

Presenting a Full Day session on Performance Optimisation for Mumbai Chapter of All India Oracle User Group. This is on 5th August 2017. For registration, click on this Performance Tuning Day. I have revised the content of this session, to incorporate the latest Optimiser behaviour and changes.

See you all there…

Oracle OpenWorld 2017 Delhi – My Session

I will be presenting at the Oracle OpenWorld 2017, Delhi scheduled for 9th and 10th May 2017. My session detail :

Session ID: CON1092
Session Title: High-Performance Database in Oracle Bare Metal Cloud Services

Hope to see you all at the OOW.

Effective SQL: 61 specific ways of writing effective SQL

Few months back, I completed a technical review of an upcoming book titled “Effective SQL: 61 specific ways of writing effective SQL”. This book focuses on widely used Relational Databases and, no doubt, Oracle is one of these. My review was specifically on Oracle SQL. Got to know from the publisher that this book is scheduled to be out in the market by Friday, 23rd December 2016. The book can be ordered from the following link:

effective-sql-61-specific-ways-to-write-better-sql-9780134578897

 effectivesql

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.

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.

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.

%d bloggers like this: