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).

5 Comments »

  1. Mohammed, what would be great, would be to see a database diagram before attempting to fix this issue, as I suspect that that is where the problem truly lies. Those column names look kind of bizarre for them all to be part of a logical PK.

    Also, IOTs can do more harm than good when not used in correct circumstances. I would like to know why the database designer chose to use an IOT here, (assuming there was an actual designer, and not somebody who just thought “Hey, I haven’t used IOTs in a while. I’ll stick one in here for a laugh!”).

    To me though, it all points to bad DB design, so let’s see the design before attempting to correct the problem.

    Comment by Martin Rose — October 26, 2014 @ 4:53 pm | Reply

  2. Hi Martin,

    It’s always a pleasure to read your comments.

    I totally agree with you when you say that “IOTs can do more harm than good when not used in correct circumstances” and I have the same impression as well when you say that the developer/designer has probably designed this IOT without knowing exactly the impact it will have on the application. I take the occasion with this answer
    to your comment to add a starting link for those who want to know when and where IOT might help

    http://jonathanlewis.wordpress.com/2011/11/22/iots/

    Best regards

    Comment by hourim — October 26, 2014 @ 5:47 pm | Reply

  3. Hi Mohammed,

    it seems you renamed the objects and especially columns in order to anonymize client information. I agree with Martin that this makes the use case really hard to understand. Self-explanatory names would have been great.
    That being said, I think “MHO_GVLA_PK” should read MHO_IOT_PK in one plan.

    Best regards,
    Salek

    Comment by Salek Talangi — October 27, 2014 @ 12:34 pm | Reply

  4. Salek

    Now corrected.

    Thanks

    Comment by hourim — October 27, 2014 @ 12:44 pm | Reply

  5. Still waiting to see the database design. 😉

    Comment by Martin Rose — October 27, 2014 @ 12:50 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

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

OraStory

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

%d bloggers like this: