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