Mohamed Houri’s Oracle Notes

November 12, 2014

Dynamic sampling: unlearning

Filed under: Statistics — hourim @ 3:45 pm

Here is an execution plan that served as a path for the Oracle SQL engine to execute a simple two table join

 SQL> SELECT
          count(t1.small_vc)
         ,count(t2.padding)
    FROM
          t1, t2
    WHERE
          t1.id1 = t2.id
        ;

SQL> start xstat

SQL_ID  b5yc4wnt59bxn, child number 0
-------------------------------------
--------------------------------------------------------------------------
| Id  | Operation                     | Name  | Starts | E-Rows | A-Rows |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |      1 |        |      1 |
|   1 |  SORT AGGREGATE               |       |      1 |      1 |      1 |
|   2 |   NESTED LOOPS                |       |      1 |        |     10 |
|   3 |    NESTED LOOPS               |       |      1 |     10 |     10 |
|   4 |     TABLE ACCESS FULL         | T2    |      1 |     10 |     10 |
|*  5 |     INDEX UNIQUE SCAN         | T1_PK |     10 |      1 |     10 |
|   6 |    TABLE ACCESS BY INDEX ROWID| T1    |     10 |      1 |     10 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("T1"."ID1"="T2"."ID")

Note
-----
   - dynamic sampling used for this statement (level=2)

What does this last Note about dynamic sampling imply?

That involved tables have stale statistics?
No it doesn’t imply such a conclusion

That involved tables have no statistics at all?
No it doesn’t exactly imply such a conclusion

The above Note indicates that at least one (particularly for the default level 2) of the involved tables in the above query has no statistics at all. It doesn’t mean as well that all tables in the join query have no statistics. It means that at least one of the two tables has been NOT ANALYZED using the 10053 trace file terminology as shown below:


***************************************
BASE STATISTICAL INFORMATION
***************************************
Table Stats::
  Table: T2  Alias: T2  (NOT ANALYZED) --- spot this
    #Rows: 327  #Blks:  4  AvgRowLen:  100.00  ChainCnt:  0.00
  Column (#1): ID(  NO STATISTICS (using defaults)
    AvgLen: 13 NDV: 10 Nulls: 0 Density: 0.097859

Index Stats::
  Index: T2_I_FK  Col#: 1
    LVLS: 0  #LB: 1  #DK: 10  LB/K: 1.00  DB/K: 1.00  CLUF: 1.00
***********************

In fact, what I did not show you is the model I’ve used which is

create table t1
    as select
              rownum                     id1,
              trunc(dbms_random.value(1,1000)) id2,
              lpad(rownum,10,'0')              small_vc,
              rpad('x',1000)                   padding
              from dual
    connect by level <= 1e4;

alter table t1 add constraint t1_pk primary key (id1);

create index t1_i1 on t1(id2);

create table t2
   as select *
      from ( select
               rownum                              as id
              ,mod(t1.id1,5) + mod(rownum,10)* 10  as id1
              ,lpad(rownum,10,'0')                 as small_vc
              ,rpad('x',70)                        as padding
            from t1
            where rownum <= 10
           )
            order by id1;

alter table t2 add constraint t2_fk foreign key(id) references t1(id1);

create index t2_i_fk on t2(id);

And most importantly the statistics I have collected for table t1 only

 BEGIN
  dbms_stats.gather_table_stats(USER
                           ,'t1'
                           ,method_opt => 'FOR ALL COLUMNS SIZE 1'
                           ,estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE
                           ,CASCADE => true);  
END;                          
/

The CBO 10053 trace file echoes for table t1, in contrast to t2 table on which I haven’t collected statistics, the following lines which indicate that the CBO has used table t1 corresponding dictionary statistics during the execution plan compilation time

***********************
Table Stats::
  Table: T1  Alias: T1
    #Rows: 10000  #Blks:  1460  AvgRowLen:  1020.00  ChainCnt:  0.00
  Column (#1): ID1(
    AvgLen: 4 NDV: 10000 Nulls: 0 Density: 0.000100 Min: 1 Max: 10000
Index Stats::
  Index: T1_I1  Col#: 2
    LVLS: 1  #LB: 21  #DK: 999  LB/K: 1.00  DB/K: 9.00  CLUF: 9968.00
  Index: T1_PK  Col#: 1
    LVLS: 1  #LB: 20  #DK: 10000  LB/K: 1.00  DB/K: 1.00  CLUF: 1429.00
Access path analysis for T1
***************************************
SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for T1[T1]
  Table: T1  Alias: T1
    Card: Original: 10000.000000  Rounded: 10000  Computed: 10000.00  Non Adjusted: 10000.00
  Access Path: TableScan
    Cost:  447.51  Resp: 447.51  Degree: 0
      Cost_io: 439.00  Cost_cpu: 12297302
      Resp_io: 439.00  Resp_cpu: 12297302
  Best:: AccessPath: TableScan
         Cost: 447.51  Degree: 1  Resp: 447.51  Card: 10000.00  Bytes: 0

Bottom line: when you see a Note about dynamic sampling at level 2 in an execution plan then bear in mind that this Note doesn’t imply that all tables involved in the underlying query have no statistics. It indicates that there is at least one table without statistics.

I’ve also attached to this blog the corresponding documentation about dynamic (statistics) sampling

November 8, 2014

Bind Aware – Part II

Filed under: cursor sharing — hourim @ 1:49 pm

In part I we saw how the count of the bucket_id in the v$cs_histogram view is incremented according to the number of rows their corresponding child cursor execution has processed depending on the bind variable value. We saw that this count-bucket_id relationship follows the following algorithm

  • If the number of processed rows < 1,000 then increment count of bucket_id n°0
  • If the number of processed rows < 1,000,000 then increment count of bucket_id n°1
  • If the number of processed rows > 1,000,000 then increment count of bucket_id n°2

In part II we will see how the decision to mark a cursor bind-aware is made. I have found three situations in which the CBO is triggered to compile a new execution plan:

    • increment the count of two adjacent bucket_id (0-1 or 1-2)
    • increment the count of the two distant bucket_id (0-2)
    • increment the count of the three bucket_id (0-1-2)

1. Two neighbour bucket_ids

Let’s start with the first situation. For this I will be using ‘j100’ and ‘j10000’ bind variable values as far as these two values increment two adjacent bucket_id which are 0 and 1. I will be using the same table and the same query as in part I.

SQL> exec :lvc := 'j100'

SQL> select count(1) from t_acs where vc2 = :lvc;

SQL> @cntbukt 6f6wzmu3yzm43 -- see script at the end

CHILD_NUMBER  BUCKET_ID      COUNT
------------ ---------- ----------
           0          0          1
           0          1          0
           0          2          0

Next, I will execute the same query 6 extra times so that I will get the following cursor monitoring picture:

SQL> @cntbukt 6f6wzmu3yzm43

CHILD_NUMBER  BUCKET_ID      COUNT
------------ ---------- ----------
           0          0          7
           0          1          0
           0          2          0

7 executions using the same bind variable value (‘j100’) which has incremented the count of bucket_id n° 0 as expected.
Now, I will change the bind variable value so that it is the bucket_id n°1 that will be incremented and execute the same query 7 times

SQL> exec :lvc := 'j10000'

SQL>select count(1) from t_acs where vc2 = :lvc; -- repeat this 7 times

SQL> @cntbukt 6f6wzmu3yzm43 

CHILD_NUMBER  BUCKET_ID      COUNT
------------ ---------- ----------
           0          0          7
           0          1          7
           0          2          0

After 7 executions using the new bind variable we are still sharing the same child cursor n°0. However,Oracle has, as expected too, incremented the bucket_id n°1 7 times. Let’s add an extra run of the same query and see what happens:

SQL> select count(1) from t_acs where vc2 = :lvc;

SQL> @cntbukt 6f6wzmu3yzm43

CHILD_NUMBER  BUCKET_ID      COUNT
------------ ---------- ----------
           1          0          0
           1          1          1
           1          2          0
           0          0          7 -- equals its neighbour
           0          1          7 -- equals its neighbour
           0          2          0

Finally after at the 8th execution Oracle has decided that it is now time to compile a new plan (new child cursor n°1). This allows me to write the first below observation:

For the same child cursor, when the count of a bucket_id reaches the count of its neighbour bucket_id, the next execution will mark the original cursor as bind aware and a new child cursor is compiled.

This conclusion is of course valid only before the apparition of the first bind aware cursor in the cursor cache.

2. Two distant bucket_ids

In order to investigate the second case I will start by flushing the shared pool and repeat the same experiment with two distant (bucket_id) bind variables, ‘j100’ and ‘j>million’ starting by 6 cursor executions using the first bind variable.

SQL> alter system flush shared_pool;
SQL> exec :lvc := 'j100';
SQL> select count(1) from t_acs where vc2 = :lvc;

SQL> @cntbukt 6f6wzmu3yzm43

CHILD_NUMBER  BUCKET_ID      COUNT
------------ ---------- ----------
           0          0          6
           0          1          0
           0          2          0

As expected, the  count of bucket_id number 0 has been incremented 6 times. Now, I will execute the same query two times using the ‘j>million’ bind variable which favours the non-adjacent bucket_id n°2

SQL> exec :lvc := 'j>million'
SQL> select count(1) from t_acs where vc2 = :lvc;

SQL> @cntbukt 6f6wzmu3yzm43
CHILD_NUMBER  BUCKET_ID      COUNT
------------ ---------- ----------
           0          0          6
           0          1          0
           0          2          2

As expected too, the count of bucket_id number 2 has been incremented 2 times. But I am still sharing the same child cursor n°0. Let’s try an extra execution with the same bind variable

SQL> select count(1) from t_acs where vc2 = :lvc;

SQL> @cntbukt 6f6wzmu3yzm43

CHILD_NUMBER  BUCKET_ID      COUNT
------------ ---------- ----------
           1          0          0
           1          1          0
           1          2          1
           0          0          6
           0          1          0
           0          2          2

And finally a new child cursor n°1 has been created indicating that the cursor has been marked bind aware.

At this step I can make the second conclusion:

For the same child cursor, when the count of a bucket_id is greater or equal to  (trunc(count/2) – 1) of its distant non-adjacent bucket_id, the next execution will mark the original cursor as bind aware and a new child cursor is compiled.

We have 6 executions at bucket_id 0 and 2 executions at bucket_id n° 2 of the same child cursor n°0. We reaches the situation where 2>= (trunc(6/2)-1). This has the tendency to indicate that the the next execution (the 3rd one) will compile a new execution plan (child cursor n°1)

What happens when the number of executions is odd?

By flushing the shared pool and setting again the bind variable to ‘j100’ and executing the same query 11 times I obtained the following picture:

CHILD_NUMBER  BUCKET_ID      COUNT
------------ ---------- ----------
           0          0         11
           0          1          0
           0          2          0

Then by setting the bind variable value to ‘j>million’ and executing the same query 4 times I arrived to the following picture:

CHILD_NUMBER  BUCKET_ID      COUNT
------------ ---------- ----------
           0          0         11
           0          1          0
           0          2          4

The above pictures indicates that  4 >= trunc(11/2) – 1 = 4. This means that the next execution(5th one)  will compile a new plan:

SQL> select count(1) from t_acs where vc2 = :lvc;

SQL> @cntbukt 6f6wzmu3yzm43

CHILD_NUMBER  BUCKET_ID      COUNT
------------ ---------- ----------
           1          0          0
           1          1          0
           1          2          1
           0          0         11
           0          1          0
           0          2          4

The two observations seems to be valid in both directions.i.e. the initial compiled cursor can belong to the bucket_id n°0 or 1 or 2. I have experimented the above tests starting by bind variable ‘j>million’ (or ‘j10000′) and have came up with the same conclusions.

In my incessant desire to make my article short and simple, I’ve decided to treat the last case (mixture bucket_ids) in Part III

PS:  cntbukt script

select
        child_number
        ,bucket_id
        ,count
    from
         v$sql_cs_histogram
    where sql_id = '&amp;1' ;

Update 11/11/2014

I did today an extra bunch of test with two distant bucket id and have updated the corresponding conclusion. When the number of cursor executions at bucket_id 2 reaches ceil(number of execution at bucket_id 0)/3) then the next execution at bucket_id 2 will mark the cursor as bind aware and compile a new execution plan

I have also attached here a Bind Aware test file on which I gathered my tests related to two distant bucket_ids (0 and 2)

November 6, 2014

Bind Aware – Part I

Filed under: cursor sharing — hourim @ 4:14 pm

If you don’t want to use the /*+ bind-aware */ hint to make your cursor immediately bind aware, you may find that your initial cursor needs a certain number of executions to reach the bind awareness status. How many of such initial executions your cursor needs in its warming up period? And how this number of executions is monitored? Those are the two questions I aim to answer via a two parts articles

In my answering path I will start by creating the following t_cs table with a special data pattern

create table t_acs(n1  number, vc2  varchar2(10));

BEGIN
     for j in 1..1200150 loop
      if j = 1 then
       insert into t_acs values (j, 'j1');
      elsif j>1 and j<=101 then
       insert into t_acs values(j, 'j100');
      elsif j>101 and j<=1101 then
       insert into t_acs values (j, 'j1000');
      elsif j>10001 and j<= 110001 then
      insert into t_acs values(j,'j10000');
     else
      insert into t_acs values(j, 'j>million');
     end if;
    end loop;
   commit;
END;
/

create index t_acs_i1 on t_acs(vc2);

select vc2, count(1) from t_acs group by vc2 order by 2;

VC2          COUNT(1)
---------- ----------
j1                  1
j100              100
j1000            1000
j10000         100000
j>million     1099049

As you can notice above, the values of the vc2 column indicate the number of their occurrence in the t_cs table:

	‘j1’        means   select count(1) from t_acs where vc2 = ‘j1’         generates  1 rows
	‘j100’      means   select count(1) from t_acs where vc2 = ‘j100’       generates 100 rows
	‘j1000’     means   select count(1) from t_acs where vc2 = ‘j1000’      generates 1000 rows
	‘j10000’    means   select count(1) from t_acs where vc2 = ‘j10000’     generates 10000 rows
	‘j>million’ means   select count(1) from t_acs where vc2 = ‘j>million’  generates more than a million of rows

I will explain later why I built such a data skew set and such a naming of the vc2 column values.
There are three views that are used to monitor a cursor candidate to the adaptive cursor sharing feature:

SQL> desc v$sql_cs_statistics

           Name                            Null?    Type
           ------------------------------- -------- --------------------
    1      ADDRESS                                  RAW(8)
    2      HASH_VALUE                               NUMBER
    3      SQL_ID                                   VARCHAR2(13)
    4      CHILD_NUMBER                             NUMBER        --> emphasize this
    5      BIND_SET_HASH_VALUE                      NUMBER
    6      PEEKED                                   VARCHAR2(1)
    7      EXECUTIONS                               NUMBER
    8      ROWS_PROCESSED                           NUMBER        --> emphasize this
    9      BUFFER_GETS                              NUMBER
   10      CPU_TIME                                 NUMBER

SQL> desc v$sql_cs_histogram

           Name                            Null?    Type
           ------------------------------- -------- --------------------
    1      ADDRESS                                  RAW(8)
    2      HASH_VALUE                               NUMBER
    3      SQL_ID                                   VARCHAR2(13)
    4      CHILD_NUMBER                             NUMBER        --> emphasize this
    5      BUCKET_ID                                NUMBER        --> emphasize this
    6      COUNT                                    NUMBER        --> emphasize this

SQL> desc v$sql_cs_selectivity

           Name                            Null?    Type
           ------------------------------- -------- --------------------
    1      ADDRESS                                  RAW(8)
    2      HASH_VALUE                               NUMBER
    3      SQL_ID                                   VARCHAR2(13)
    4      CHILD_NUMBER                             NUMBER
    5      PREDICATE                                VARCHAR2(40)
    6      RANGE_ID                                 NUMBER
    7      LOW                                      VARCHAR2(10)
    8      HIGH                                     VARCHAR2(10)

The third view starts to be useful only when the cursor becomes bind aware. At this step of the article, let’s concentrate only on the rows_processed column of the first view and on the couple (bucket_id, count) columns of the second one. They go hand-in-hand in the monitoring process of the same child cursor

Let’s investigate how these two views interact with each other. I will be using, all over this article, the below query which is:

SQL> select count(1) from t_acs where vc2 = :lvc;

Where :lvc is a bind variable which I will declare and set (the first time) as follows:

SQL> var lvc varchar2(10);
SQL> exec :lvc := 'j100';

To be bind aware, a cursor requires from the column used in its predicate part to possess a histogram:

BEGIN
       dbms_stats.gather_table_stats
                   (user
                   ,'t_acs'
                   ,method_opt       => 'for all columns size skewonly'
                   ,estimate_percent => dbms_stats.auto_sample_size
                   ,cascade          => true
                   ,no_invalidate    => false
                   );
END;
/

I used ‘’size skewonly’’ for the method_opt parameter to collect histogram on the vc2 column. There was no previous query using the vc2 column in a where clause so that using the ‘’size auto’’ parameter in the above dbms_stats package call would have been more appropriate.

As mentioned above ‘j100’ means that my initial cursor will be processing 100 rows. This is what we can see in the two monitoring views when we execute the above query:

SQL> select
         child_number
         ,executions
         ,rows_processed
    from v$sql_cs_statistics
    where sql_id = '6f6wzmu3yzm43' ;

SQL> select
        child_number
        ,bucket_id
        ,count
    from
         v$sql_cs_histogram
    where  sql_id = '6f6wzmu3yzm43' ;

CHILD_NUMBER EXECUTIONS ROWS_PROCESSED
------------ ---------- --------------
           0          1            101

CHILD_NUMBER  BUCKET_ID      COUNT
------------ ---------- ----------
           0          0          1 --> incremented
           0          1          0
           0          2          0

The child cursor n° 0 processed 101 rows and Oracle incremented the count of its bucket_id n° 0.

What happens now if I execute the same query using a different bind variable value (‘j10000’)?

SQL> exec :lvc := 'j10000';

CHILD_NUMBER EXECUTIONS ROWS_PROCESSED
------------ ---------- --------------
           0          1         101

I know that ‘j10000’ bind variable produces 10,000 rows; so why my shared cursor n°0 is showing only 101 rows?

This is not completely true. We will see below, that in contrast to v$sql_cs_histogram view, the rows_processed column of the v$sql_cs_statistics view is updated only when a new execution plan is compiled. This means that, the rows_processed column is not updated when a child cursor is shared. At this step we are still sharing the child cursor n°0 and this is why the number of processed rows of the last second (last) execution is not corect.

Hopefully, we have the v$sql_cs_histogram:

CHILD_NUMBER  BUCKET_ID      COUNT
------------ ---------- ----------
           0          0          1
           0          1          1 --> incremented
           0          2          0

Oracle is showing, via this view, that we are still sharing the same child cursor n° 0, but, in contrast to the first execution where it was the bucket_id n° 0 that has seen its count incremented to 1, for the second query execution, it is actually the bucket_id n°1 that has been incremented.

What does this mean?

Oracle is telling us that the second query execution has actually processed more than the indicated 101 rows. We will see at the end of this article the proof of that claim.

Now, let’s use a new bind variable value (‘j>million’) and re-execute the same query?

SQL> exec :lvc := 'j>million';

CHILD_NUMBER EXECUTIONS ROWS_PROCESSED
----------- ---------- --------------
           1          1        1099050
           0          1         101

CHILD_NUMBER  BUCKET_ID      COUNT
------------ ---------- ----------
           1          0          0
           1          1          0
           1          2          1 --> incremented
           0          0          1
           0          1          1
           0          2          0

Notice this time that Oracle produces a new child cursor n°1, which is a sign that our cursor is now bind aware. It has processed 1,099,050 rows and it is the bucket_id number 2 of this new compiled child cursor n° 1 that has seen its count incremented to 1.

In order to show you that the bind variable ‘j10000’ has effectively generated more than 10,000 rows, let’s re-execute the initial query using this bind variable and observe what the monitoring views will show us:

SQL> exec :lvc := 'j10000';

CHILD_NUMBER EXECUTIONS ROWS_PROCESSED
------------ ---------- --------------
           2          1         100001
           1          1        1099050
           0          1            101

CHILD_NUMBER  BUCKET_ID      COUNT
------------ ---------- ----------
           2          0          0
           2          1          1 --> incremented
           2          2          0
           1          0          0
           1          1          0
           1          2          1
           0          0          1
           0          1          1
           0          2          0

A new child cursor n°2 has been compiled for the ‘j10000’ bind variable value and this is why we see the correct number of rows_processed (10,001) in the v$sql_cs_statistics view. Note also that for this child cursor n°2, it is the bucket_id n° 1 that has been incremented to 1

Keep away, for this moment, the fact that cursor child n°1 and 2 are bind aware and that cursor child n°0 became obsolete (not shareable anymore) as shown below:

select  sql_id
       ,child_number
       ,is_bind_aware
       ,is_bind_sensitive
       ,is_shareable
       ,executions
from   v$sql
where  sql_id ='6f6wzmu3yzm43';

SQL_ID        CHILD_NUMBER I I I EXECUTIONS
------------- ------------ - - - ----------
6f6wzmu3yzm43            0 N Y N          2
6f6wzmu3yzm43            1 Y Y Y          1
6f6wzmu3yzm43            2 Y Y Y          1

And notice that, with what preceds, I hope that I have demonstrated the relationship between the child cursor, the number of rows it has processed and the corresponding incremented bucket_id. This relationship obeys to the following algorithm:

             0   < ROWS_PROCESSED <= 1000  --> COUNT of BUCKET_ID  0 will be incremented
	       1000 < ROWS_PROCESSED <= 1e6   --> COUNT of BUCKET_ID  1 will be incremented
	              ROWS_PROCESSED > 1e6   --> COUNT of BUCKET_ID  2  will be incremented

In part II of this series we will see how the couple (bucket_id, count) influences the bind aware status of the initial child cursor. This couple of column ceases to play their initial role when the cursor becomes bind aware; in which case the third v$view (v$sql_cs_selectivity) enters the bind awareness scene and starts using the selectivity of the bind variable to decide whether to share an existing child cursor or optimize a new one.

October 26, 2014

Index organized table: high logical I/O during delete

Filed under: Index — hourim @ 3:46 pm

I was trouble shooting an application wide performance issue via a 60 minutes AWR report and here below is what I’ve pointed out in the SQL ordered by Gets part:

SQL ordered by Gets

Buffer Gets Executions Gets per Exec %Total Elapsed Time (s) %CPU %IO SQL Id SQL Module SQL Text
24,131,799 784 30,780.36 11.36 273.33 54.5 45 6m881uq1j6yr6 DBMS_SCHEDULER INSERT INTO TABLE_IOT(…
17,339,802 784 22,117.09 8.16 557.84 13.8 86.4 2abf33xkq9ypd DBMS_SCHEDULER DELETE FROM TABLE_IOT…

It is an insert and a delete from an index organized table consuming 30,780 and 22,117 Buffer Gets per execution respectively.

You are going to say why an IOT table on which there is such a high number of delete and insert operations. This is what prompted my attention as well. But let’s suppose that they want to keep this table as it is. I started trouble shooting the delete part first, and, the next step I did was to get its corresponding execution plan taken from AWR using the corresponding sql_id

DELETE FROM TABLE_IOT WHERE IOT_ID = :B1

---------------------------------------------------------------------------------------
| Id  | Operation         | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT  |                   |       |       |     7 (100)|          |
|   1 |  DELETE           | TABLE_IOT         |       |       |            |          |
|   2 |   INDEX RANGE SCAN| CHILD_IOT_FK_I    |   808 | 21008 |     7   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

I wished, at this step, that Oracle has already managed within its last release, to add the predicate part of the above execution plan taken from AWR (and for real time monitored SQL). But It still doesn’t include them.

The TABLE_IOT has 5 columns

SQL&gt; desc TABLE_IOT

           Name                            Null?    Type
           ------------------------------- -------- --------------------
    1      IOT_ID                           NOT NULL NUMBER(10)
    2      IOT_DATE                        NOT NULL DATE
    3      IOT_IDB                         NOT NULL NUMBER(10)
    4      IOT_PUBL_DATE                   NOT NULL DATE
    5      IOT_CRE_DATE                    NOT NULL DATE
    6      IOT_ID_TYPE                              VARCHAR2(3 CHAR)

With a primary key on (iot_publ_date, iot_id, iot_date, iot_idb) and index on foreign key child_iot_fk_i(iot_id)

When deleting from the TABLE_IOT, Oracle is not using the primary key. It is, instead, visiting the secondary index which has been created to cover the deadlock threat when deleting from a parent table this index organized table is referencing.

Well, just by looking at the above index definition I was tended to think that if the developer has read my Indexing Strategy – Part I article he would have certainly not created that index on the foreign key and would have reversed the primary key index columns by putting the IOT_ID column at the leading edge of this primary key index. As such, he will have succeeded to cover the FK lock threat and would have save disk space and DML overhead on the underlying table.

In trouble shooting this issue I started by replaying the delete in an equivalent TEST environment:


SQL&gt; delete from TABLE_IOT where IOT_id = 94149;

251 rows deleted.

---------------------------------------------------------------------------------------
| Id  | Operation         | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT  |                   |   431 | 11206 |     7   (0)| 00:00:01 |
|   1 |  DELETE           | TABLE_IOT         |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| CHILD_IOT_FK_I    |   431 | 11206 |     7   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access(&quot;IOT_ID&quot;=94149)

Statistics
----------------------------------------------------------
         54  recursive calls
       1033  db block gets
        101  consistent gets
          8  physical reads
     131468  redo size
        567  bytes sent via SQL*Net to client
        493  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          9  sorts (memory)
          0  sorts (disk)
        251  rows processed

Then, I made invisible the foreign key index (don’t do that in production without reading the article until the end)

SQL&gt; alter index CHILD_IOT_FK_I invisible;

SQL&gt; delete from TABLE_IOT where IOT_id = 94149;

251 rows deleted.

--------------------------------------------------------------------------------------
| Id  | Operation        | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT |                   |   431 | 11206 |  1467   (1)| 00:00:03 |
|   1 |  DELETE          | TABLE_IOT         |       |       |            |          |
|*  2 |   INDEX SKIP SCAN| IOT_PK            |   431 | 11206 |  1467   (1)| 00:00:03 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access(&quot;IOT_ID&quot;=94149)
       filter(&quot;IOT_ID&quot;=94149)

Statistics
----------------------------------------------------------
         54  recursive calls
       1782  db block gets
        126  consistent gets
         24  physical reads
     178716  redo size
        567  bytes sent via SQL*Net to client
        493  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          8  sorts (memory)
          0  sorts (disk)
        251  rows processed

Scanning the primary key index necessitated more consistent gets than it was the case with the previous secondary index child_iot_fk_i. This is due to the skip scan path. Very often when I see an index skip scan access I am pretty sure that there is a place for a better index to be designed. And particularly in this case, where, if I was the developer of this application at design time, I would have started the primary key index with the IOT_ID column and hence would have not created the redundant child_iot_fk_i.

SQL&gt; create unique index mho_iot_pk on TABLE_IOT (IOT_ID, IOT_PUBL_DATE,IOT_DATE, IOT_IDB);

SQL&gt; delete from TABLE_IOT where IOT_id = 94149;

---------------------------------------------------------------------------------------
| Id  | Operation         | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT  |                   |   431 | 11206 |     5   (0)| 00:00:01 |
|   1 |  DELETE           | TABLE_IOT         |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| MHO_IOT_PK        |   431 | 11206 |     5   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access(&quot;IOT_ID&quot;=94149)

Statistics
----------------------------------------------------------
          1  recursive calls
       1046  db block gets
          5  consistent gets
          4  physical reads
     141512  redo size
        575  bytes sent via SQL*Net to client
        493  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
        251  rows processed                  

The number of consistent gets and the number of recursive calls has been drastically reduced when using the new appropriately designed unique index.

However I would have preferred, in this particluar case, a solution in which the type of table would have been changed from an index organized table into a heap table. A proposition that was not been accepted by the customer.

I tried to model this customer size using the below table model but did not end up with the exact same situation.

 create table 
  index_org_tab
  ( n1  number
   ,d1  date
   ,n2  number
   ,n3  number
   ,n4  number
   ,svc varchar2(10)
   ,constraint iot_pk primary key (d1,n1,n2,n3)
   )
  organization index;

insert into index_org_tab
 select
      rownum
     ,date '2013-01-01' + ((Level - 1) * 2)
     ,trunc((rownum-1)/5)
     ,mod(rownum,10)
     ,dbms_random.value(1,50)
     ,lpad('x',10)
 from dual
 connect by level &lt;= 1e6;

 create index iot_fk_i on index_org_tab(n3);

 exec dbms_stats.gather_table_stats(user, 'index_org_tab', cascade =&gt; true, method_opt =&gt; 'for all columns size 1');

 delete from
 index_org_tab
 where n3 = 6;

100000 rows deleted.

-----------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | DELETE STATEMENT  |               |   100K|  2050K|   458   (2)| 00:00:01 |
|   1 |  DELETE           | INDEX_ORG_TAB |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| IOT_FK_I      |   100K|  2050K|   458   (2)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access(&quot;N3&quot;=6)

Statistics
--------------------------------------------------------
        340  recursive calls
     106296  db block gets
        905  consistent gets
          0  physical reads
   29930420  redo size
        563  bytes sent via SQL*Net to client
        482  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
     100000  rows processed

SQL&gt; alter index IOT_FK_I invisible;

SQL&gt;  delete from
           index_org_tab
           where n3 = 6;

100000 rows deleted.

---------------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT      |               |   100K|  2050K|  2897   (3)| 00:00:05 |
|   1 |  DELETE               | INDEX_ORG_TAB |       |       |            |          |
|*  2 |   INDEX FAST FULL SCAN| IOT_PK        |   100K|  2050K|  2897   (3)| 00:00:05 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(&quot;N3&quot;=6)

Statistics
--------------------------------------------------------
        518  recursive calls
     406700  db block gets
       8397  consistent gets
          0  physical reads
   49543928  redo size
        569  bytes sent via SQL*Net to client
        497  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          8  sorts (memory)
          0  sorts (disk)
     100000  rows processed  

create unique index mho_iot_uk on index_org_tab(n3,d1,n1,n2);

SQL&gt; delete from
     index_org_tab
     where n3 = 6;

100000 rows deleted.
-----------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | DELETE STATEMENT  |               |   100K|  2050K|   468   (2)| 00:00:01 |
|   1 |  DELETE           | INDEX_ORG_TAB |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| MHO_IOT_UK    |   100K|  2050K|   468   (2)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access(&quot;N3&quot;=6)

Statistics

----------------------------------------------------------
        400  recursive calls
     109095  db block gets
        983  consistent gets
          0  physical reads
   33591824  redo size
        569  bytes sent via SQL*Net to client
        504  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
     100000  rows processed

Bottom line : think carefully when you have the intention to create an index organized table on how you are going to access it. If you are going to visit this table without its primary key then you might encounter a high number of logical I/O consumption particularly when you visit this IOT table using a secondary index (as it is the case in the current issue).

October 25, 2014

Algeria and Oracle

Filed under: Oracle — hourim @ 12:04 pm

As you might have already guessed I am deploying a lot of efforts to help Algerian universities and companies using the Oracle technology in the same way as the way I’ve used to learn from world Oracle experts like Jonathan Lewis. In this context, I spent the last couple of weeks in Algeria giving one day seminar to the Oracle DBA team of Ooredoo – Algeria, a private mobile telephone group and one of the PSG football team sponsors, about interpreting execution plans, adaptive cursor sharing and gathering adequate statistics. I managed also to initiate students of the “Mathematique-Informatique” department of Université de Khemis-Miliana to the Oracle world via a brief and very quick training on how Oracle manage internally to answer a simple select * from employee table (library cache, buffer cache, logical read, physical read, bind variable, soft parse, hard parse, parent cursor, child cursor).

I was very happy to see students interested by this presentation which they have never been given in the way I have managed to do it. I made a big effort in order to use words that are close to the student’s actual knowledge of the Oracle Database technology. Strange enough why academic teachers can’t give attractive and elegant training with which I am sure they will initiate so many excellent Oracle carriers. Am I insinuating that to be a good Oracle teacher you should have a good Oracle professional experience? I am afraid that the answer is YES.

OoredooKhemisUniv2

October 24, 2014

100

Filed under: Information — hourim @ 3:25 pm

During the last week I reached the number 100. There are 100 persons following my blogging activities on October the 24th. It was, and it still is, a personal documentation purpose that paved the way to this writing activity. My initial motivation has nothing to do with that number while it becomes now, I have to confess, a magnificent boost.

Unfortunately, between the desire of learning and that of writing in general and blogging in particular, there is a gap that I have to fill very quickly if I want to have a Jonathan Lewis rate of publications (number of blog article per month) :-)

Publications

Filed under: Publication — hourim @ 10:02 am

Here it is a list of recent articles I wrote for RedGate Software, Dell Software and Oracle Otn. I included a reminder for French readers that they have a free “French” downloadable chapter of the always excellent Jonathan Lewis book Oracle Cost Based Fundamentals.

Allthings Oracle

Dell Software Solution

Oracle Otn

 Oracle French Developer group

 Books Translation

 

I will be very happy to receive your critics and corrections.

October 1, 2014

Partition by virtual column

Filed under: Oracle — hourim @ 3:19 pm

Almost a year ago I trouble-shooted a query performance issue which was mainly due to a high number of logical I/O consumed by a local non prefixed index accessing a range partitioned table without eliminating partitions (partition range all). This trouble shooting issue paved the way to the partitioned index: global or local article via which the designer of the “culprit” index has perfectly learnt the lesson.

Everyone was back to its daily work until last week when I was handled a complex query performing badly. Here below, reduced to its problematic part only, is the corresponding execution plan taken from memory

----------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                        | Rows  | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                             |       |       |       |
|   1 |  TEMP TABLE TRANSFORMATION           |                             |       |       |       |
|   2 |   LOAD AS SELECT                     |                             |       |       |       |
|   3 |    PARTITION LIST ALL                |                             |     1 |     1 |   367 |
|*  4 |     TABLE ACCESS BY LOCAL INDEX ROWID| TAB_LOG_ENTRY               |     1 |     1 |   367 |
|*  5 |      INDEX RANGE SCAN                | LOCAL_N_PREFIX_INDEX        |     2 |     1 |   367 |
----------------------------------------------------------------------------------------------------
 
 Predicate Information (identified by operation id):
---------------------------------------------------
 4 - filter((COL1='T' AND INTERNAL_FUNCTION(COL2)))
 5 - access(COL3>=TIMESTAMP' 2014-09-25 00:00:00' AND COL3 <=TIMESTAMP' 2014-09-26 00:00:00')

Don’t be disturbed by this internal_function which pops up because of a particular use of an ‘or’ clause in the current query (this is not the goal of this article). However, spot the number of partitions scanned by Oracle when visiting the index local_n_prefix_index: 367 partitions. The whole partitions have been scanned. The Real time SQL monitoring of the corresponding sql_id shows also that 80% of the wait activity was due to the operation with id n° 4 above.

This is exactly the same issue as the last year one. Isn’t it?

I hurried up to the developer desk and got the following Q&A with him

Me: do you remember our last year discussion about non-prefixed index used to drive a query which is not eliminating partitions?

Developer:  Of course I remember and this is why the query is using the partition key and the index is a locally prefixed (contains the partition key)

Me: So where does this partition list all operation is coming from?

A careful look at the table and index definition reveal that  the table is indeed partitioned but this time the developer decided to use a virtual column (derived from col3) as the partition key. He also managed to create a local prefixed (at least he was correctly thinking that this is a prefixed) index using col3 column.

At this stage of the investigation I remembered that Jonathan Lewis has blogged about partitioning using virtual column where I have already presented (see comment n°3) a similar case to what I have been, coincidentally, asked to look at the last week.

The positive answer in the Jonathan Lewis article to the following question

Quiz: if you create a virtual column as trunc(date_col,’W’) and partition on it – will a query on date_col result in partition elimination?

Is of course valid for the trunc SQL function, but it seems, however, not extensible to other SQL functions.

It’s time now to the set up the model. I will create a simple table list partitioned by a virtual column having 365 partitions (one partition per day). Due to the high number of partitions, I will create this table using dynamic SQL

declare
       v_sql_statment   varchar2(32000);
begin
   v_sql_statment := 'create table mho_log_entry (ln_id  number primary key,ln_date date not null,ln_type_code  number not null,';
   v_sql_statment := v_sql_statment||'ln_event_typ varchar2(32 char) not null,day_in_year number(3,0) generated always';
   v_sql_statment := v_sql_statment||q'# as (to_number(to_char(ln_date,'DDD'))) virtual) partition by list (day_in_year)(#';   
   for n in 1..365 loop
     if n != 365 then
         v_sql_statment   := v_sql_statment||' partition y_dd_'||n||' values ('||n||'),';
     else
         v_sql_statment   := v_sql_statment||' partition y_dd_'||n||' values ('||n||'))';
    end if;
   end loop;     
      execute immediate v_sql_statment;
end;
/

Execute the above script and you will have the following table description

SQL> desc mho_log_entry
          Name            Null?    Type
          -------------- --------- -----------------
   1      LN_ID           NOT NULL NUMBER
   2      LN_DATE         NOT NULL DATE              -- used to derive the virtual column
   3      LN_TYPE_CODE    NOT NULL NUMBER
   4      LN_EVENT_TYP    NOT NULL VARCHAR2(32 CHAR)
   5      DAY_IN_YEAR              NUMBER(3)         -- partition key

The partition key(day_in_year) is a virtual column responding to the following formula

  to_number(to_char(ln_date,'DDD')

And the number of partitions I have generated is given by the following select

 SQL> select count(1)
    from user_tab_partitions
    where table_name = 'MHO_LOG_ENTRY';

  COUNT(1)
----------
       365

I still have to create a local “prefixed” index as the developer did to be in the exact situation as the query I have asked to tune

create index mho_ln_date on mho_log_entry (ln_date) local;

If you want to push data into this table then here is an example on how to proceed

SQL> insert into mho_log_entry
          (ln_id
          ,ln_date
          ,ln_type_code
          ,ln_event_typ
          )
     select
          *
     from
        (
       select
          rownum
         ,date '2014-01-01' + ((level - 1) * 2) dd
         ,trunc(rownum-1)
         ,case
           when mod(rownum,100) = 0 then 'I'
           when mod(rownum,10) = 0 then 'A'
           when mod (rownum,1000) = 0 then 'B'
           when mod(rownum,1e4) = 0 then 'R'
          else 'L'
          end aa
       from dual
       connect by level <=3e2
       )
     where dd <= to_date('31122014','ddmmyyyy') ;

SQL> exec dbms_stats.gather_table_stats(user, 'mho_log_entry', cascade => true);

And finally this is the query and its corresponding execution plan in 11.2.0.3.0

SQL> select *
        from
          mho_log_entry
        where
           ln_date between to_date('20082014','ddmmyyyy')
           and to_date('22082014','ddmmyyyy') ;

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

------------------------------------------------------------------------------------
| Id  | Operation                          | Name          | Rows  | Pstart| Pstop |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |               |       |       |       |
|   1 |  PARTITION LIST ALL                |               |     3 |     1 |365    |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| MHO_LOG_ENTRY |     3 |     1 |365    |
|*  3 |    INDEX RANGE SCAN                | MHO_LN_DATE   |     3 |     1 |365    |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("LN_DATE">=TO_DATE(' 2014-08-20 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
       AND "LN_DATE"<=TO_DATE('2014-08-22 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

No partition elimination and 365 index range scans.

However, if I change the query to use the virtual column (day_in_year) instead of its underlying real column (ln_date), partition pruning occurs:

 SQL> select *
        from
           mho_log_entry
        where
           day_in_year between to_number(to_char(to_date('20082014','ddmmyyyy'),'DDD'))
           and to_number(to_char(to_date('22082014','ddmmyyyy'),'DDD')) ;

SQL> select * from table(dbms_xplan.display_display);

-------------------------------------------------------------------------
| Id  | Operation               | Name          | Rows  | Pstart| Pstop |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |               |       |       |       |
|   1 |  PARTITION LIST ITERATOR|               |     3 |   232 |   234 |
|   2 |   TABLE ACCESS FULL     | MHO_LOG_ENTRY |     3 |   232 |   234 |
-------------------------------------------------------------------------

Only two partitions have been scanned. Note in passing the absence of the predicate part in this case.  This predicate part is absent also when using the explain plan for command:

 SQL> explain plan for
     select *
        from
           mho_log_entry
        where
           day_in_year between to_number(to_char(to_date('20082014','ddmmyyyy'),'DDD'))
           and to_number(to_char(to_date('22082014','ddmmyyyy'),'DDD')) ;

SQL> select * from table(dbms_xplan.display);

-------------------------------------------------------------------------
| Id  | Operation               | Name          | Rows  | Pstart| Pstop |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |               |     3 |       |       |
|   1 |  PARTITION LIST ITERATOR|               |     3 |   232 |   234 |
|   2 |   TABLE ACCESS FULL     | MHO_LOG_ENTRY |     3 |   232 |   234 |
------------------------------------------------------------------------- 

Since Oracle is iteratively eliminating partitions it might be correct to do not expect to see the predicate part applied on the mho_log_entry table. However, see what happens when I create an index (first global and then local) on this table using the virtual column:

SQL> create index mho_day_in_year on mho_log_entry (day_in_year); 

SQL> select *
     from
      mho_log_entry
     where
       day_in_year between to_number(to_char(to_date('20082014','ddmmyyyy'),'DDD'))
       and to_number(to_char(to_date('22082014','ddmmyyyy'),'DDD')) ;

-------------------------------------------------------------------------------------
| Id  | Operation                          | Name            | Rows  | Pstart|Pstop |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                 |       |       |      |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| MHO_LOG_ENTRY   |     3 | ROWID |ROWID |
|*  2 |   INDEX RANGE SCAN                 | MHO_DAY_IN_YEAR |     3 |       |      |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DAY_IN_YEAR">=232 AND "DAY_IN_YEAR"<=234

And this is the corresponding execution plan for a local index

 drop index mho_day_in_year ;
 create index mho_day_in_year on mho_log_entry (day_in_year) local;

 select /*+ index(mho_log_entry) */ -- I don't know why I am obliged to hint the index
   *                                -- I will be back to it later
    from
      mho_log_entry
    where
       day_in_year between to_number(to_char(to_date('20082014','ddmmyyyy'),'DDD'))
       and to_number(to_char(to_date('22082014','ddmmyyyy'),'DDD')) ;

-------------------------------------------------------------------------------------
| Id  | Operation                          | Name            | Rows  | Pstart|Pstop |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                 |       |       |      |
|   1 |  PARTITION LIST ITERATOR           |                 |     3 |   232 |234   |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| MHO_LOG_ENTRY   |     3 |   232 |234   |
|*  3 |    INDEX RANGE SCAN                | MHO_DAY_IN_YEAR |     1 |   232 |234   |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("DAY_IN_YEAR">=232 AND "DAY_IN_YEAR"<=234)

It is interesting to note that when using an index, Oracle applied the predicate part while when full scanning the mho_log_entry table is didn’t . And both explain plan and actual execution are showing the same behaviour.

That’s said let’s be back to what motivated this article: partitioning by a virtual column cannot guaranty a partition pruning when querying the partitioned table using the physical column your partition key is based on

PS: rewriting the original query using the virtual column might not be always correct. It strongly depends on the data and on the virtual column definition. In the above designed model I have only one distinct year of data which makes the refactoring of the query possible

September 23, 2014

TABLE ACCESS BY INDEX ROWID BATCHED

Filed under: Index — hourim @ 7:05 pm

I was writing an article for Allthings Oracle about Indexing strategy: discard and sort and testing the model supporting this article in different oracle database releases 10gR2, 11gR2 and 12cR1 until my attention has been kept by an interesting detail in 12cR1.

Observe the following execution plans taken from 12cR1 in response to the following query:

select * from t1 where id2 = 42 order by id1 desc;
------------------------------------------------------------------------
| Id  | Operation                   | Name  | Starts | E-Rows | A-Rows |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |      1 |        |   1000 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T1    |      1 |    499K|   1000 |
|   2 |   INDEX FULL SCAN DESCENDING| T1_PK |      1 |    998K|   1000K|
------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID2"=42)

---------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Starts | E-Rows | A-Rows |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |      1 |        |   1000 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1             |      1 |    499K|   1000 |
|*  2 |   INDEX RANGE SCAN          | T1_IND_ID1_FBI |      1 |    499K|   1000 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID2"=42)

------------------------------------------------------------------------------
| Id | Operation                   | Name          | Starts | E-Rows | A-Rows |
------------------------------------------------------------------------------
| 0  | SELECT STATEMENT            |               |      1 |        | 1000   |
| 1  | TABLE ACCESS BY INDEX ROWID | T1            |      1 |    499K| 1000   |
|* 2 | INDEX RANGE SCAN DESCENDING | T1_IND_ID1_NI |      1 |    499K| 1000   |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
--------------------------------------------------
2 - access("ID2"=42)

Question: What have you already pointed out from the above execution plans?

Answer     : I have managed to design indexes so that Oracle succeeded to avoid the order by operation for each of the above query executions (there is no order by operation in the above execution plan).

But this is not the reason which paved the way to this article.  Wait a minute and will you know what motivated this article.

In my incessant desire to help the CBO doing good estimations, I created a virtual column(derived_id2), singularly indexed it with a b-tree index, collected statistics for this virtual column and executed a new but equivalent query:

SQL> select * from t1 where derived_id2 = 42 order by id1 desc;

Which has been honored via the following execution plan

--------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                   | Starts | E-Rows | A-Rows |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                        |      1 |        |   1000 |
|   1 |  SORT ORDER BY                       |                        |      1 |    511 |   1000 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1                     |      1 |    511 |   1000 |
|*  3 |    INDEX RANGE SCAN                  | T1_DERIVED_ID2_IND_BIS |      1 |    511 |   1000 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("DERIVED_ID2"=42)

Question : Have you already noticed something?

Answer   : The appearance of  a SORT ORDER BY operation above TABLE ACCESS BY INDEX ROWID BATCHED

It seems that the new 12c TABLE ACCESS BY INDEX ROWID BATCHED cannot take place when Oracle uses the index access child operation to avoid the order by operation. In the first three execution plans above, Oracle uses an index access path to avoid the order by operation and in these cases the parent index table has been visited via the classical table access by index rowid. While when Oracle has been unable to eliminate the order by operation, the parent index child table has been accessed via the new 12c table access by index rowid batched followed by, what seems to be inevitable in this case, an order by operation.

Here below is a simple model you can play with to check this impossible to separate couple (order by, table access by index rowid batched)

 create table t1 as
 select rownum n1
      ,trunc((rownum-1)/3) n2
      ,rpad('x',100) v1
 from dual
connect by level <= 1e4; 

create index t1_ind1 on t1(n2, n1);

select * from t1 where n2 = 3329 order by n1 desc;

select * from table(dbms_xplan.display_cursor);

alter index t1_ind1 invisible;

create index t1_ind2 on t1(n2);

select * from t1 where n2 = 3329 order by n1 desc;

select * from table(dbms_xplan.display_cursor);

September 3, 2014

Index design I

Filed under: Uncategorized — hourim @ 2:56 pm

Very often questions about the best position a column should be put in within a composite index come out into forums and Oracle discussions. The last question I have contributed to and tried to answer has been raised up in a French Forum. The original poster was wondering whether it is a good idea to place the very often repeated column (contains duplicates) at the leading edge of the index or not.

First of all, in contrast to my usual style of blogging I am not going to provide a SQL model on which I am going to expand my knowledge of index design. However, for those who want to learn and master indexes I would encourage them to read the world expert person in this field, Richard Foote. He has an excellent blog with several articles about almost all what one has to know about indexes and not only the widely used b-tree indexes but on all other types of indexes including bitmap indexes, function based indexes, partitioned indexes, exadata storage indexes etc..

The second reference is as always Jonathan Lewis blog in which you can find several articles about  index design, index efficiency and index maintenance. In addition, it is not sufficient to know how to design precise index; you need to know as well how your index will evolve with the delete, insert or update operations their underlined tables will undergo during the lifecycle of the application they participate to its normal functioning.

The third reference is the book Relational Database Index Design and the Optimizers which extends the index design to several databases including DB2, Oracle and SQL Server

I, from time to time, come to read very interesting articles about indexes in this web site that I am following via twitter. It contains valuable index design information which, according to what I have read up to now, is pertinent, correct and back up all what I have learned from Jonathan Lewis, Richard Foote and from my own professional experience.

That’s said, I will post here below few of my answers and articles(and Jonathan Lewis articles) about index design as a small answer to a lot of questions about index design

  1. On the importance of the leading index columns that should be the ones on which an equality predicate is applied
  2. Indexing Foreign keys
  3. Redundant Indexes
  4. Global or Local Partitioned Index
  5. Compressing indexes basic part and cost of index compression

I am planning to write several other articles on indexes and I will be completing the above list as far as I will go with this publishing task

My answer to the original poster question about the importance of the number of distinct values property of an index candidate column is that the starting index column decision is not driven by its number of distinct values. It is instead driven by:

  • The nature of the query where clause he has to cover
  • The nature of the predicate (equality, inequality, etc..) applied on the starting column
  • The constant desire to cover with the same index one or a couple of other queries
  • The constant desire to cover with the same index a foreign key deadlock threat : sometime just by reversing the columns order we succeed to cover the current query and an existing foreign key
  • The constant desire to avoid redundant indexes

And finally comes up the reason for which one has to consider placing the column with the small number of distinct values at the leading edge of the index: Compression. If you start your index with the more often duplicated column you will make a good index compression reducing, efficiently, the size of that index which means it will be very quickly put into the buffer cache and will be kept there more often due to its small size.

Next Page »

Theme: Rubric. Get a free blog at WordPress.com

Oracle Diagnostician

Performance troubleshooting as exact science

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Database 11g

Oracle Database

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

EU Careers info

Your career in the European Union

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

Tanel Poder's blog: Responsible data management

Linux, Oracle, Exadata and Hadoop.

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

Follow

Get every new post delivered to your Inbox.

Join 105 other followers