Mohamed Houri’s Oracle Notes

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> 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> 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("IOT_ID"=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> alter index CHILD_IOT_FK_I invisible;

SQL> 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("IOT_ID"=94149)
       filter("IOT_ID"=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> create unique index mho_iot_pk on TABLE_IOT (IOT_ID, IOT_PUBL_DATE,IOT_DATE, IOT_IDB);

SQL> 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("IOT_ID"=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 <= 1e6;

 create index iot_fk_i on index_org_tab(n3);

 exec dbms_stats.gather_table_stats(user, 'index_org_tab', cascade => true, method_opt => '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("N3"=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> alter index IOT_FK_I invisible;

SQL>  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("N3"=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> 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("N3"=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.

July 30, 2014

On table constraint execution order

Filed under: Oracle — hourim @ 8:03 pm

Introduction

Before to be left behind due to my infrequent blogging activity these days, here it is a simple note about the order of execution of constraints and triggers placed on a given table. Basically, I am inclined, according to my very recent tests done on 11.2.0.3 and shown below, to claimn that that order of execution obeys the following steps:

  1. Before (insert) trigger fires first
  2. Then CHECK or UNIQUE constraint follows. We will see herein after in what order
  3. Then it will be the turn of after (insert) trigger
  4.  And finally checking Foreign Key integrity constraint comes at the trailing edge of the process

The execution order of the check and unique constraints seems to be closely related to the order of the columns (in the table) with which the constraints has been implemented, as shown by Karthick_App in the above mentioned otn thread.

 Details

This is the model on which I have made the above observations:


drop table c purge;
drop table p purge;

create table p as select rownum n1, trunc((rownum-1)/3) n2
from dual connect by level <= 10;

alter table p add constraint p_pk primary key (n1);

create table c (m1 number not null, m2 number, m3 number);

alter table c add constraint child_fk foreign key (m2) references p(n1);

alter table c add constraint child_ck check (m3 in (1,2));

alter table c add constraint child_uk unique (m1);
  
create or replace trigger c_bfi_trg before insert on c
for each row
begin
    dbms_output.put_line('Trigger before insert fired');
end;
/

create or replace trigger c_afi_trg after insert on c
for each row
begin
    dbms_output.put_line('Trigger after insert fired');
end;
/       

The following select shows the order of the column within the table


select table_name, column_name, column_id
from user_tab_columns
where table_name = 'C'
order
by 1, 3;

TABLE_NAME                     COLUMN_NAME                     COLUMN_ID
------------------------------ ------------------------------ ----------
C                              M1                                      1
C                              M2                                      2
C                              M3                                      3

Note particularly that I have:

  • A not null CHECK constraint on the first column (m1) because it has been declared as the primary key of the child table c
  • A UNIQUE constraint on the first column (m1) because it has been declared as the primary key of the child table c
  • A FOREIGN Key constraint on the second column (m2) because it has been declared as a foreign key to the parent table p
  • A CHECK constraint on the third column(m3) to allow only 1 and 2 as possible values

A check constraint (being it unique or check on possible values) other than a foreign key constraint is executed according to the column order in the table. For example, the not null CHECK constraint will be the first to be verified because it has been implemented on column m1 which is the leading column of the table

SQL> set serveroutput on

SQL> insert into c values (null, 11, 3);

Trigger before insert fired
insert into c values (null, 11, 3)
                      *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("XXX"."C"."M1")

Note that in the above insert, I have 3 reasons for which the insert ought to be refused

  • Inserting a null value into the primary key (m1)
  • Inserting a non-existing parent key (m2 = 11)
  • Inserting a non-acceptable value into m3 (m3 in (1,2))

Among all those refusal reasons, it is the ORA-01400 that has fired up because the NOT NULL constraint has been implemented on the first column of the table (m1). The same observation can be made when the unique constraint placed on the first column is violated as shown below:

SQL> insert into c values (1, 10,1);
Trigger before insert fired
Trigger after insert fired
1 row created.

SQL> insert into c values (1, 11,3);
Trigger before insert fired
insert into c values (1, 11,3)
*
ERROR at line 1:
ORA-00001: unique constraint (XXX.CHILD_UK) violated

When we work around the not null refusal reason by supplying a not null value to the m1 column and re-insert the same statement, this time it is the check constraint m3 in (1,2) that raises up

SQL> insert into c values (1, 11,3);
Trigger before insert fired
insert into c values (1, 11,3)
*
ERROR at line 1:
ORA-02290: check constraint (XXX.CHILD_CK) violated

Despite the Foreign key is placed on the second column of the table, it is the check constraint on the third column that fires up.

And when all constraints are valid, the foreign key integrity constraint is finally checked as shown below:

SQL> insert into c values (1, 11,1);

Trigger before insert fired
Trigger after insert fired

insert into c values (1, 11,1)
*
ERROR at line 1:
ORA-02291: integrity constraint (XXX.CHILD_FK) violated - parent key not found

Conclusion

I have been very often asking myself (or being asked) the same question when confronted to such a kind of situation: what is the order of the constraint/trigger execution? And for each time I was obliged to spend few minutes creating a simple model in order to come up with a reliable answer. From now and on, I am sure that I will not redo this work as far as I definitely know that I wrote the answer into an article that has been published somewhere on the net.

In passing, spot why I am encouraging people to write:

  •  They will definitely not redo the same work again
  •  They will definitely find easily what they have done in this context
  • They  will definitely let other readers validate or correct their answer

July 2, 2014

Execution plan of a cancelled query

Filed under: explain plan — hourim @ 2:47 pm

I have been reminded by this otn thread that I have to write something about the usefulness of an execution plan pulled from memory of a query that has been cancelled due to its long running execution time. Several months ago I have been confronted to the same issue where one of my queries was desperately running sufficiently slow so that I couldn’t resist the temptation to cancel it.

It is of course legitimate to ask in such a situation about the validity of the corresponding execution plan? Should we wait until the query has finished before starting investigating where things went wrong? What if the query takes more than 20 minutes to complete? Will we wait 20 minutes each time before getting an exploitable execution plan?

The answer of course is no. You shouldn’t wait until the end of the query. Once the query has started you could get its execution plan from memory using its sql_id. And you will be surprised that you might already be able to found a clue of what is wrong with your query even in this ‘’partial’’ execution plan.

Let me model an example and show you what I am meaning.

    create table t1
       as
       with generator as (
           select  --+ materialize
               rownum id
           from dual
           connect by
               level <= 10000)
       select
          rownum                  id,
          trunc(dbms_random.value(1,1000))    n1,
          lpad(rownum,10,'0') small_vc,
          rpad('x',100)       padding
      from
          generator   v1,
          generator   v2
      where
          rownum <= 1000000;

   create index t1_n1 on t1(id, n1);

   create table t2
       as
       with generator as (
           select  --+ materialize
               rownum id
           from dual
           connect by
               level <= 10000)
       select
          rownum                  id,
          trunc(dbms_random.value(10001,20001))   x1,
          lpad(rownum,10,'0') small_vc,
          rpad('x',100)       padding
      from
          generator   v1,
          generator   v2
      where
          rownum <= 1000000;
    
   create index t2_i1 on t2(x1);

   exec dbms_stats.gather_table_stats(user, 't2', method_opt => 'FOR ALL COLUMNS SIZE 1');
   exec dbms_stats.gather_table_stats(user, 't1', method_opt => 'FOR ALL COLUMNS SIZE 1');
   

I have two simple indexed tables t1 and t2 against which I engineered the following query

    select *
       from t1
        where id in
                (Select id from t2 where x1 = 17335)
       order by id ;
   

The above query contains an order by clause on an indexed column (id). In order to make this query running sufficiently slow so that I can cancel it I will change the optimizer mode from all_rows to first_rows so that the CBO will prefer doing an index full scan and avoid the order by operation whatever the cost of the index full scan operation is.

   SQL> alter session set statistics_level=all;

   SQL> alter session set optimizer_mode=first_rows;

   SQL> select *
       from t1
        where id in
                (Select id from t2 where x1 = 17335)
       order by id ;

   107 rows selected.

   Elapsed: 00:02:14.70 -- more than 2 minutes

   SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

   SQL_ID  9gv186ag1mz0c, child number 0
  -------------------------------------
  Plan hash value: 1518369540
  ------------------------------------------------------------------------------------------------
  | Id  | Operation                    | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
  ------------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT             |       |      1 |        |    107 |00:02:39.85 |     108M|
  |   1 |  NESTED LOOPS SEMI           |       |      1 |    100 |    107 |00:02:39.85 |     108M|
  |   2 |   TABLE ACCESS BY INDEX ROWID| T1    |      1 |   1000K|   1000K|00:00:01.03 |   20319 |
  |   3 |    INDEX FULL SCAN           | T1_N1 |      1 |   1000K|   1000K|00:00:00.31 |    2774 |
  |*  4 |   TABLE ACCESS BY INDEX ROWID| T2    |   1000K|      1 |    107 |00:02:37.70 |     108M|
  |*  5 |    INDEX RANGE SCAN          | T2_I1 |   1000K|    100 |    106M|00:00:30.73 |    1056K|
  ------------------------------------------------------------------------------------------------

  Predicate Information (identified by operation id):
  ---------------------------------------------------
     4 - filter("ID"="ID")
     5 - access("X1"=17335)
 

And the plan_hash_value2 of this query is:

  SQL> @phv2 9gv186ag1mz0c

  SQL_ID        PLAN_HASH_VALUE CHILD_NUMBER       PHV2
  ------------- --------------- ------------ ----------
  9gv186ag1mz0c      1518369540            0 2288661314
  

The query took more than 2 minutes to complete and for the purpose of this blog I didn’t cancelled it in order to get its final execution plan shown above. Now I am going to re-execute it again and cancel it after few seconds.

    SQL> select *
       from t1
        where id in
                (Select id from t2 where x1 = 17335)
       order by id ;

   ^C
   C:\>
   

And here below is the corresponding partial plan pulled from memory

  SQL> select * from table(dbms_xplan.display_cursor('9gv186ag1mz0c',0,'ALLSTATS LAST'));

  SQL_ID  9gv186ag1mz0c, child number 0
  -------------------------------------
  Plan hash value: 1518369540

  ------------------------------------------------------------------------------------------------
  | Id  | Operation                    | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
  ------------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT             |       |      1 |        |     17 |00:00:18.13 |      13M|
  |   1 |  NESTED LOOPS SEMI           |       |      1 |    100 |     17 |00:00:18.13 |      13M|
  |   2 |   TABLE ACCESS BY INDEX ROWID| T1    |      1 |   1000K|    129K|00:00:00.12 |    2631 |
  |   3 |    INDEX FULL SCAN           | T1_N1 |      1 |   1000K|    129K|00:00:00.04 |     361 |
  |*  4 |   TABLE ACCESS BY INDEX ROWID| T2    |    129K|      1 |     17 |00:00:18.26 |      13M|
  |*  5 |    INDEX RANGE SCAN          | T2_I1 |    129K|    100 |     13M|00:00:03.63 |     136K|
  ------------------------------------------------------------------------------------------------

  Predicate Information (identified by operation id):
  ---------------------------------------------------
     4 - filter("ID"="ID")
     5 - access("X1"=17335)

  SQL>  @phv2 9gv186ag1mz0c

  SQL_ID        PLAN_HASH_VALUE CHILD_NUMBER       PHV2
  ------------- --------------- ------------ ----------
  9gv186ag1mz0c      1518369540            0 2288661314
  

The main observations are:

  1.   Both queries followed the same plan hash value
  2.   Both queries have the same phv2
  3.   Both execution plans contain the same operations at the same order of execution
  4.   Both execution plans contain the same Estimations (E-Rows) which is a clear indication that this is obtained at hard parse time

Either from the complete plan or from the ”partial” one we can have the same clue of what is going wrong here (even thought that in this particular case the performance penalty is due to the first_rows mode which has the tendency to avoid an order by on an indexed column at any price). Indeed it is the operation number 4 which is the most time consuming operation and from where there is the most important deviation between the CBO estimations and the Actual rows

So there is nothing that impeaches hurried developers from cancelling a query and getting its execution plan from memory. They can end up with valuable information from the cancelled query plan as well as from the complete plan.

This article is also a reminder for experienced Oracle DBA performance specialists that, there are cases where a difference between estimations done by the CBO and the actual rows obtained during execution time, does not necessary imply an absence of fresh and representative statistics. This difference could be due to a plan coming from a cancelled query.

By the way, I know another situation where an excellent optimal plan is showing a value of Starts*E-Rows diverging greatly from A-Rows and where statistics are perfect.  That will be explained, I hope,  in a different blog article.

July 1, 2014

DDL optimization : french version

Filed under: Oracle — hourim @ 7:16 pm

I wrote an article about DDL optimization which I have submitted to Oracle otn and which will be hopefully published very soon. As you might also know I have been invited to be a member of the Oracle World team in order to spread the use of the Oracle technology in Latin America. This is why the English (original) article has been translated into Portuguese and published in the otn Latin America. The Spanish version will be published very soon as well.

I have also translated the original article to French and have been looking unsuccessfully for a known place where I would have published it so that it will have reached many persons in several countries like France, Belgium, Luxembourg, Switzerland,  North Africa and several other countries too. Having been unable to find that place, I decided to publish it here in my blog. So here it is  DDL Optimization

Next Page »

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

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 102 other followers