Mohamed Houri’s Oracle Notes

June 24, 2016

Index Skip Scan : how many columns are skippable?

Filed under: Index — hourim @ 10:49 am

If you want to know what an index skip scan operation is then Richard Foote article and Jonathan Lewis one you can find here and here respectively are two reliable references.

This article deals about a particular query using an index skip scan with the following Real Time SQL Monitoring (RTSM) report

Global Information
------------------------------
 Status              :  EXECUTING  
 Instance ID         :  1                   
 Session             :  C##MHOURI (965:52281)  
 SQL ID              :  c2mah5hxau88v       
 SQL Execution ID    :  16777216            
 Execution Started   :  06/15/2016 13:03:15 
 First Refresh Time  :  06/15/2016 13:03:21 
 Last Refresh Time   :  06/15/2016 14:49:32 
 Duration            :  6378s                  
 Module/Action       :  SQL*Plus/-          
 Service             :  orcl                
 Program             :  sqlplus.exe         
 Fetch Calls         :  2280           


Global Stats
================================================================
| Elapsed |   Cpu   |    IO    | Fetch | Buffer | Read | Read  |
| Time(s) | Time(s) | Waits(s) | Calls |  Gets  | Reqs | Bytes |
================================================================
|    6385 |    193 |     6192 |   2280 |   2M   | 2M   |  14GB |
================================================================
  
SQL Plan Monitoring Details (Plan Hash Value=517922901)
======================================================================================
| Id |               Operation               | Name  |   Time    | Start  | Activity |
|    |                                       |       | Active(s) | Active |   (%)    |
======================================================================================
|  0 | SELECT STATEMENT                      |       |      6297 |     +67|          |
|  1 |   TABLE ACCESS BY INDEX ROWID BATCHED | T     |      6297 |     +67|      0.52|
|->2 |    INDEX SKIP SCAN                    | T_IDX |      6378 |     +2 |     99.48|
======================================================================================

As you can see after 6385 seconds the query is still running. The Start Active column when correlated with the Time Active one indicates that 99% of this time is spent on the index skip scan operation. We all know that an index skip scan is an indicator of a missing index or a pointer to a wrongly designed one but, what has retained my attention in this case is that this skipped index is a 7 columns composed index of which the first 6 columns have been skipped.

Now the question turns to: are those 6 leading columns very repetitive(contain few distinct values) so that they can be skipped? Let’s first see the size of this index and how its columns values are distributed respectively:

SQL> @sizeBysegName
Enter value for segment_name : IDX

SEGMENT_TYPE       TABLESPACE_NAME       SEGMENT_NAME     PARTITION    GB
------------------ --------------------- --------------   ----------  -------
INDEX              IDX_TBS                T_IDX                        28.44
                                                                       -------
Total Segment Size                                                     28.44

SQL> col column_name format a20
SQL> select
  2         i.column_name
  3        ,i.column_position
  4       ,s.num_distinct
  5      from
  6         user_ind_columns i
  7       , user_tab_col_statistics s
  8       where
  9        i.column_name = s.column_name
 10       and   index_name = 'T_IDX'
 11       order by column_position asc;

COLUMN_NAME          COLUMN_POSITION NUM_DISTINCT
-------------------- --------------- ------------
BKR_ID                             1         1337
PRD_ID                             2      3342592
PRD_QTY                            3       197008
IS_PROCESED                        4            2
OPR_PRD_QTY                        5       170576
PRD_FMLY                           6            9
PROCESS_ID                         7            1

7 rows selected.

Interestingly, except the first column which is relatively repetitive when compared to the index size, neither the second column which contains more than 3 million of distinct values nor the third and the fifth which count for about 2 hundreds of thousands of distinct values, can be considered as repetitive. So why they have been skipped?

Additionally, skipping an index,say idx(a,b,c) on its column c is somehow rewriting the original query by adding two predicates on the two first index columns. Something as follows:

From

select
    {lits of columns}
from table
where
   c = :1;

To

select
    {lits of columns}
from table
where
    a in {all distinct value of a}
and b in {all distinct value of b}
and c = :1;

The two added predicates explain why the number of distinct values of the index leading edge columns should be very small for the index skip scan path to be considered.

If you want to reproduce such a kind of index operation then here’s the model I have engineered

create table t
as select
      rownum   prd_id
	 ,rownum-1 sec_prd_id
	 ,trunc((rownum-1)/3) prd_qty
	 ,trunc((rownum-1)/3) opr_prd_qty
	 ,mod(rownum,100)     bkr_id
	 ,case mod(rownum,1000000) when  0  then 'x'
	                           when  1  then 'y'
							   when  2  then 'z'
							   when  3  then 'a'
							   when  4  then 'b'
							   when  5  then 'c'
							   else 'd' end  prd_fmly
     ,case rownum when 1 then 0 else 1 end is_procesed
     ,0                                    process_id
from dual
connect by level  'for all columns size 1');
end;

SQL> select
  2         i.column_name
  3        ,i.column_position
  4       ,s.num_distinct
  5       ,s.histogram
  6      from
  7         user_ind_columns i
  8       , user_tab_col_statistics s
  9       where
 10        i.column_name = s.column_name
 11       and   index_name = 'T_IDX'
 12       order by column_position asc;

COLUMN_NAME          COLUMN_POSITION NUM_DISTINCT HISTOGRAM
-------------------- --------------- ------------ ------------
BKR_ID                             1          100 NONE
PRD_ID                             2      1000000 NONE
PRD_QTY                            3       337344 NONE
IS_PROCESED                        4            2 NONE
OPR_PRD_QTY                        5       337344 NONE
PRD_FMLY                           6            7 NONE
PROCESS_ID                         7            1 NONE

7 rows selected.
SQL> set autotrace on explain stat
SQL>  select /*+ opt_param ('optimizer_index_cost_adj' 20) */
  2             t.*
  3          from t
  4          where
  5              t.prd_id in (5507203,78400086,359798,4565189,9495,12215)
  6          and t.process_id = 0
  7          and t.opr_prd_qty  0;

    PRD_ID SEC_PRD_ID    PRD_QTY OPR_PRD_QTY     BKR_ID P IS_PROCESED PROCESS_ID
---------- ---------- ---------- ----------- ---------- - ----------- ----------
     12215      12214       4071        4071         15 d           1          0
      9495       9494       3164        3164         95 d           1          0
    359798     359797     119932      119932         98 d           1          0

---------------------------------------------------------------------
| Id  | Operation                           | Name  | Rows  | Bytes |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |       |     4 |   120 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T     |     4 |   120 |
|*  2 |   INDEX SKIP SCAN                   | T_IDX |     4 |       |
---------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T"."PROCESS_ID"=0)
       filter(("T"."PRD_ID"=9495 OR "T"."PRD_ID"=12215 OR "T"."PRD_ID"=359798 OR
              "T"."PRD_ID"=4565189 OR "T"."PRD_ID"=5507203 OR "T"."PRD_ID"=78400086) AND
              "T"."OPR_PRD_QTY"0 AND "T"."PROCESS_ID"=0)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       5026  consistent gets
          0  physical reads
          0  redo size
       1167  bytes sent via SQL*Net to client
        544  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed

Not having a clear answer for why Oracle has considered this index operation, doesn’t pre-empt us from solving this issue. And as explained above, an index skip scan operation being an indicationg of a missing index I have then created the following index (bear in mind that the newly created index takes into account that the original text of query can’t be changed):

SQL> create index idx_t1_usr1 on t(process_id, prd_id, prd_qty) compress 3;

SQL> select /*+ opt_param ('optimizer_index_cost_adj' 20) */
  2         t.*
  3      from t
  4      where
  5          t.prd_id in (5507203,78400086,359798,4565189,9495,12215)
  6      and t.process_id = 0
  7      and t.opr_prd_qty  0;

    PRD_ID SEC_PRD_ID    PRD_QTY OPR_PRD_QTY     BKR_ID P IS_PROCESED PROCESS_ID
---------- ---------- ---------- ----------- ---------- - ----------- ----------
      9495       9494       3164        3164         95 d           1          0
     12215      12214       4071        4071         15 d           1          0
    359798     359797     119932      119932         98 d           1          0

----------------------------------------------------------------------------
| Id  | Operation                            | Name        | Rows  | Bytes |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |             |     4 |   120 |
|   1 |  INLIST ITERATOR                     |             |       |       |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T           |     4 |   120 |
|*  3 |    INDEX RANGE SCAN                  | IDX_T1_USR1 |     5 |       |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("T"."OPR_PRD_QTY"0)
   3 - access("T"."PROCESS_ID"=0 AND ("T"."PRD_ID"=9495 OR "T"."PRD_ID"=12215 OR
              "T"."PRD_ID"=359798 OR "T"."PRD_ID"=4565189 OR "T"."PRD_ID"=5507203 OR
              "T"."PRD_ID"=78400086))

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         18  consistent gets
          7  physical reads
          0  redo size
       1167  bytes sent via SQL*Net to client
        544  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed

May 12, 2015

Index Efficiency

Filed under: Index — hourim @ 7:03 am

I used Jonathan Lewis script to locate degenerated indexes –-or indexes that are occupying more space than they should–. Among those indexes I have isolated this one:

16:20:33:TABLE1 - PK_TAB1
Current Leaf blocks: 2,846,555 Target size:1,585,492

According to this SQL script the above index possesses 2.8 million worth of leaf blocks while it should normally occupy half this number of blocks.

The sys_op_lb_id function when applied on this index gives the following average leaf block per index key picture:

sys_op_lbid

ROWS_PER_BLOCK BLOCKS
-------------- ----------
 2              1
 7              1
 27             1
 32             1
 92             1
 94             1
 103            1
 107            1
 108            1
 111            1
 112            800
 113            1,627,529
……
 422            980,894
 423            40
 432            1
 434            1
 448            5496
 449            32803
 450            7
 456            3
 458            1
 466            1
 478            54
 479            200
 487            1
 ----------    -----------
sum             2,979,747

Spot that odd value of 1.6 million leaf blocks (out of a total of 2,9 million) we have to visit to get only 113 index keys. Add to this the other 980,984 leaf blocks we need to visit to get an extra 422 index keys and you might end up by approximatively having to visit the entire index leaf blocks to get only a couple of hundred of index keys. That is a completely degenerated index.

Let’s then rebuild it and check if we will get back the reclaimed space:

SQL> alter index PK_TAB1 rebuild parallel 8;

SQL> alter index PK_TAB1 noparallel;

SQL> break on report skip 1

SQL> compute sum of blocks on report

SQL> select
        rows_per_block,
        count(*) blocks
     from
       (
        select
            /*+
              cursor_sharing_exact
              dynamic_sampling(0)
              no_monitoring
              no_expand
              index_ffs(t1,t1_i1)
              noparallel_index(t,t1_i1)
             */
        sys_op_lbid( &m_ind_id ,'L',t1.rowid) as block_id,
        count(*) as rows_per_block
      from
        TABLE1 t1
      where
        tab_id is not null
      group by
       sys_op_lbid( &m_ind_id ,'L',t1.rowid)
     )
   group by rows_per_block
   order by rows_per_block
   ;
Enter value for m_ind_id: 53213
Enter value for m_ind_id: 53213

ROWS_PER_BLOCK BLOCKS
-------------- ----------
 26            1
 206           1
 208           1
 243           1
 249           1
 272           1
 316           1
 339           1
 422           1,558,800
 423           53
 432           1
 448           5496
 449           32803
 458           1
 478           54
 479           200
 487           1
 ----------------------
sum          1,597,417

Notice the new number of index leaf block we’ve got after rebuilding the index (1,597,417) and compare it with the number predicted by Jonathan Lewis script (1,585,492). That’s really very accurate. The initial estimation is almost 100% accurate. In passing the new index size has been reduced by at factor of 46%.

While rebuilding the index has reduced drastically the number of leaf blocks and the disk space they occupy, that odd value of 1,558,800 leaf blocks we have to visit to get only 422 index keys is still present. This has prompted me to try coalescing the index even though I was not very confident that such a high number of leaf blocks could be merged with adjacent leaf blocks making the index less smashed.

SQL> alter index PK_TAB1 coalesce;

ROWS_PER_BLOCK    BLOCKS
-------------- ----------
           26          1
          206          1
          208          1
          243          1
          249          1
          272          1
          316          1
          339          1
          422          1,558,800
          423          53
          432          1
          448          5496
          449          32803
          458          1
          478          54
          479          200
          487          1
              -------------
sum              1,597,417

Definitely this primary key index has a strange way of being filled up which I have to figure out with the Java developers.

The bottom line of this article is that Jonathan Lewis script locating degenerated index is amazingly precise.

March 9, 2015

Index compression: two birds with one stone

Filed under: Index — hourim @ 7:52 pm

Have you ever been asked to trouble shoot a performance issue of a complex query with the following restrictions:

  • You are not allowed to change the code of the query because it belongs to a third party software
  • You are not allowed to create a new index because of disk space stress
  • You are supposed to solve the issue without using  neither a SPM baseline nor a SQL Profile

What do you think you still have on your hands to tackle this issue?

I was quite confident that the performance issue was coming, as almost always, from a poor or not representative statistics which ultimately have biased the Optimizer choosing a wrong execution path. The row source execution plan taken this time from the Real Time Sql Monitoring report confirms my initial feeling about non representative statistics as shown by the several 1 estimated cardinality below (Rows Estim):

SQL Plan Monitoring Details (Plan Hash Value=2278065992)
==============================================================================================
| Id |                Operation                 |          Name |  Rows   | Execs |   Rows   |
|    |                                          |               | (Estim) |       | (Actual) |
==============================================================================================
|  0 | SELECT STATEMENT                         |               |         |     1 |     1059 |
|  1 |   HASH GROUP BY                          |               |       1 |     1 |     1059 |
|  2 |    FILTER                                |               |         |     1 |     135K |
|  3 |     NESTED LOOPS                         |               |       1 |     1 |     135K |
|  4 |      NESTED LOOPS                        |               |       1 |     1 |     135K |
|  5 |       NESTED LOOPS OUTER                 |               |       1 |     1 |     135K |
|  6 |        NESTED LOOPS                      |               |       1 |     1 |     135K |
|  7 |         HASH JOIN OUTER                  |               |       1 |     1 |     145K |
|  8 |          NESTED LOOPS                    |               |         |     1 |     145K |
|  9 |           NESTED LOOPS                   |               |       1 |     1 |     145K |
| 10 |            NESTED LOOPS                  |               |       1 |     1 |     146K |
| 11 |             NESTED LOOPS                 |               |       1 |     1 |     146K |
| 12 |              NESTED LOOPS                |               |       1 |     1 |        1 |
| 13 |               NESTED LOOPS               |               |       1 |     1 |        1 |
| 14 |                FAST DUAL                 |               |       1 |     1 |        1 |
| 15 |                FAST DUAL                 |               |       1 |     1 |        1 |
| 16 |               FAST DUAL                  |               |       1 |     1 |        1 |
| 17 |              TABLE ACCESS BY INDEX ROWID | TABLE_XY      |       1 |     1 |     146K |
| 18 |               INDEX RANGE SCAN           | IDX_TABLE_XY23|       1 |     1 |      12M |
==============================================================================================
17 - filter(("AU"."NEW_VALUE"=:SYS_B_119 AND "AU"."ATTRIBUTE_NAME"=:SYS_B_118))
18 - access("AU"."UPDATED_DATE">=TO_DATE(:SYS_B_001||TO_CHAR(EXTRACT(MONTH FROM
              ADD_MONTHS(CURRENT_DATE,(-:SYS_B_000))))||:SYS_B_002||TO_CHAR(EXTRACT(YEAR FROM
              ADD_MONTHS(CURRENT_DATE,(-:SYS_B_000))))||:SYS_B_003,:SYS_B_004)
              AND "AU"."COURSE_NAME"=:SYS_B_117
              AND "AU"."UPDATED_DATE"<=TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(CURRENT_DATE,(-
             :SYS_B_000))),:SYS_B_005)||:SYS_B_006,:SYS_B_007))
     filter("AU"."COURSE_NAME"=:SYS_B_117)

Collecting statistics adequately was of course the right path to follow; however looking at the above execution plan I have realized that the most consuming operation was an index range scan followed by a table access by index rowid. The index operation at line 18 was supplying its parent operation at line 17 with 12 million worth of rows from which the filter at this line has allowed only 2% (146K) of the rows to survive its elimination. This is a classical problem of imprecise index wasting a lot of time and energy in throwing away rows that should have been eliminated earlier.  There is also another indication about the imprecision of the index used at operation line 18. Its predicate part contains both an access and a filter operation. In order to make the picture clear here below is the index definition and the predicate part used in the problematic query:

IDX_TABLE_XY23 (UPDATED_DATE, COURSE_NAME)

FROM TABLE_XY     
WHERE AU.COURSE_NAME = ‘Point’
AND AU.UPDATED_DATE  >= PidDates.END_DATE

The index has been defined to start with the column on which an inequality is applied. We should instead always place at the leading edge of the index the column on which we have the intention to apply an equality predicate. One solution would be to reverse the above index columns. But I was not going to do that without checking the whole application looking for queries using the UPDATED_DATE column in equality predicate so that reversing the columns of that index will harm them. Hopefully there were no such queries and the way was already paved for me to proceed to that index columns order reversing proposition.

Few minutes before going ahead I remembered that this application is suffering from a disk space shortage so that compressing the index would certainly help. Moreover the “new” leading index column is the less repetitive one which hopefully will give a better level of compressibility:

SQL> select column_name, num_distinct
     from all_tab_col_statistics
     where table_name = 'TABLE_XY'
     and column_name in ('UPDATED_DATE','COURSE_NAME');

COLUMN_NAME                    NUM_DISTINCT
------------------------------ ------------
UPDATED_DATE                   1309016064
COURSE_NAME                    63
SQL> create index idx_TABLE_XYmh on TABLE_XY(COURSE_NAME, UPDATED_DATE) compress 1;

And here are the results

Before the new index

1059 rows selected.
Elapsed: 00:32:37.34

Global Stats
================================================================
| Elapsed |   Cpu   |    IO    | Fetch | Buffer | Read | Read  |
| Time(s) | Time(s) | Waits(s) | Calls |  Gets  | Reqs | Bytes |
================================================================
|    2020 |     154 |     1867 |    72 |    10M |   1M |   9GB |
================================================================

After the new index

1059 rows selected.
Elapsed: 00:19:56.08

Global Stats
========================================================
| Elapsed |   Cpu   |    IO    | Buffer | Read | Read  |
| Time(s) | Time(s) | Waits(s) |  Gets  | Reqs | Bytes |
========================================================
|    1204 |      70 |     1134 |     9M | 463K |   4GB |
========================================================

The query execution time dropped from 33 minutes to 19 minutes and so did the logical and physical I/O.

Of course I am still throwing the same amount of rows at the table level a shown below

SQL Plan Monitoring Details (Plan Hash Value=2577971998)
===================================================================
| Id    |  Operation                 |          Name   |   Rows   |
|       |                            |                 | (Actual) |
===================================================================
|    17 |TABLE ACCESS BY INDEX ROWID | TABLE_XY        |     146K |
|    18 | INDEX RANGE SCAN           | IDX_TABLE_XYMH  |      12M |
===================================================================
17 - filter(("AU"."NEW_VALUE"=:SYS_B_119 AND "AU"."ATTRIBUTE_NAME"=:SYS_B_118))
18 - access("AU"."COURSE_NAME"=:SYS_B_117 AND
           "AU"."UPDATED_DATE">=TO_DATE(:SYS_B_001||TO_CHAR(EXTRACT(MONTH FROM
            ADD_MONTHS(CURRENT_DATE,(-:SYS_B_000))))||:SYS_B_002||TO_CHAR(EXTRACT(YEAR FROM
            ADD_MONTHS(CURRENT_DATE,(-:SYS_B_000))))||:SYS_B_003,:SYS_B_004) AND
           "AU"."UPDATED_DATE"<=TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(CURRENT_DATE,(-:SYS_B_000)))
           ,:SYS_B_005)||:SYS_B_006,:SYS_B_007))

But I have drastically reduced the part of the index I was traversing before reversing the columns order and I have got rid of the filter at the index predicate.  And finally, because it is the title of this article, by compressing the index I have gained 67GB

SQL> select segment_name, trunc(bytes/1024/1024/1024) GB
    from dba_segments
    where segment_type = 'INDEX'
    and segment_name in ('IDX_TABLE_XYMH','IDX_TABLE_XY23');

SEGMENT_NAME                           GB
------------------------------ ----------
IDX_TABLE_XYMH                        103
IDX_TABLE_XY23                        170

 

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

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

March 24, 2014

Redundant Indexes

Filed under: Index — hourim @ 11:28 am

I am very often warning:  dropping redundant indexes in production is not 100% safe. I have instead always been advocating paying a careful attention during design time to avoid creating redundant indexes. In my professional experience I have realized that, it is very often when creating indexes to cover the lock threat of unindexed foreign key constraints, that developers are creating unintentionally redundant indexes. It has been irritating me so that I have created a script which checks if a given foreign key is already indexed or not before creating supplementary non wanted indexes damaging the DML part of the application.

Having said that I still have not defined what is redundant indexes

Indexes ind_1 and ind_2 are said redundant when leading columns of one index are a superset of the leading columns of the other one

For example

Ind_1 (a,b) and ind_2(a,b,c) are redundant because ind_2 contains index ind_1.

If you are at design time it is obvious that you should not create index ind_1. However, once in production, it is not 100% safe to drop index ind_1 without any impact. There are, for sure, occasions were the clustering factor of ind_2 is so dramatic when compared to index ind_1 so that if the later index is dropped the optimizer will opt for a full table scan traumatizing the queries that were perfectly happy with the dropped redundant index.

I can also show you another type of what people might consider redundant indexes while they aren’t. Consider the following model where I have created a range partitioned table (mho_date being the partition key and I have created 1493 partitions) and two indexes as shown below

desc partitioned_tab

Name                            Null?    Type
------------------------------- -------- ------------
1      MHO_ID                          NOT NULL NUMBER(10)
2      MHO_DATE                        NOT NULL DATE
3      MHO_CODE                        NOT NULL VARCHAR2(1)
4      MHO_TYP_ID                      NOT NULL NUMBER(10)

create index local_ind_1 on partitioned_tab (mho_typ_id,mho_code) local;

create index global_ind_1 on partitioned_tab (mho_typ_id);

I am going to execute a simple query against the above engineered partitioned table.

select * from partitioned_tab where mho_typ_id = 0;

Which, in the presence of the above two indexes is honored via the following execution plan

----------------------------------------------------------------------
Plan hash value: 3042058313
----------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name            | Rows  |Cost (%CPU)| Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                 |  1493 | 1496   (0)|       |       |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| PARTITIONED_TAB |  1493 | 1496   (0)| ROWID | ROWID |
|*  2 |   INDEX RANGE SCAN                         | GLOBAL_IND_1    |  1493 |    4   (0)|       |       |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("MHO_TYP_ID"=0)

Statistics
-------------------------------------------------------
48    recursive calls
0     db block gets
3201  consistent gets
0     physical reads
0     redo size
51244 bytes sent via SQL*Net to client
1632  bytes received via SQL*Net from client
101   SQL*Net roundtrips to/from client
7     sorts (memory)
0     sorts (disk)
1493  rows processed

As you might already know I am one of the fans of SQLT tool developed by Carlos Sierra. And here below what this tool said about redundant indexes in this particular case

Redundant_indexes

It is clearly suggesting considering dropping the redundant index global_ind_1 index

So let’s follow this advice and see what happens. Hopefully with the recent Oracle release I will first make the index invisible ( by the way that’s a good point to signal for Carlos Sierra and Mauro Pagano for them to suggest setting first the index invisible before considering dropping it)

alter index global_ind_1 invisible;

select * from partitioned_tab where mho_typ_id = 0;

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name            | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                 |  1493 | 23888 |  2985   (1)|       |       |
|   1 |  PARTITION RANGE ALL                       |                 |  1493 | 23888 |  2985   (1)|     1 |  1493 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| PARTITIONED_TAB |  1493 | 23888 |  2985   (1)|     1 |  1493 |
|*  3 |    INDEX RANGE SCAN                        | LOCAL_IND_1     |  1493 |       |  1492   (0)|     1 |  1493 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("MHO_TYP_ID"=0)

Statistics
--------------------------------------------------------
48    recursive calls
0     db block gets
4588  consistent gets
0     physical reads
0     redo size
47957 bytes sent via SQL*Net to client
1632  bytes received via SQL*Net from client
101   SQL*Net roundtrips to/from client
7     sorts (memory)
0     sorts (disk)
1493  rows processed

By making the ”redundant” index invisible we went from a smooth one global index range scan with a cost of 4 to 1493 index range scans with a cost of 1492 with an additional 1387 logical reads.

Bottom line: You should always consider dropping (avoiding) redundant index at design time. Once in production consider making them invisible first before thinking carefully about dropping them.

PS : if you want to create the table the script can be found partitioned table

March 2, 2014

Index design

Filed under: Index — hourim @ 8:35 am

Here it is a very brief discussion I have had with one of my colleagues about index design

Colleague: what kind of index would you suggest to cover the following query?

SELECT
     rowid
    ,a.*
FROM  message_out a
WHERE sms_status    in (700, 707)
AND  (scheduled_time is null
      OR scheduled_time   <= :1)
AND   provider_id     in (0,0)
ORDER BY
      priority_level desc,
      creation_time asc;

----------------------------------------------------------------------------
| Id | Operation         | Name        | Rows | Bytes |TempSpc| Cost (%CPU)|
----------------------------------------------------------------------------
| 0  | SELECT STATEMENT  |             | 5529 | 1376K |       | 4769 (1)   |
| 1  | SORT ORDER BY     |             | 5529 | 1376K | 1856K | 4769 (1)   |
|* 2 |  TABLE ACCESS FULL| MESSAGE_OUT | 5529 | 1376K |       | 4462 (1)   |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("SMS_STATUS"=700 OR "SMS_STATUS"=707)
AND ("SCHEDULED_TIME" IS NULL OR "SCHEDULED_TIME"<=:1)
AND "PROVIDER_ID"=0)

Me: and what have you ended up with until now?

Colleague: here my suggested index and the related execution plan

CREATE INDEX virtual_index ON MESSAGE_OUT(sms_status,scheduled_time,provider_id) ;

---------------------------------------------------------------------------------------
| Id | Operation                   | Name          | Rows | Bytes |TempSpc| Cost (%CPU)|
---------------------------------------------------------------------------------------
| 0  | SELECT STATEMENT            |               | 5529 | 1376K |       | 446 (1)    |
| 1  | SORT ORDER BY               |               | 5529 | 1376K | 1856K | 446 (1)    |
| 2  | INLIST ITERATOR             |               |      |       |       |            |
|* 3 |  TABLE ACCESS BY INDEX ROWID| MESSAGE_OUT   | 5529 | 1376K |       | 140 (0)    |
|* 4 |   INDEX RANGE SCAN          | VIRTUAL_INDEX | 5529 |       |       | 6 (0)      |
---------------------------------------------------------------------------------------

Me: I would not have created the same index

Me: here it is the index I would have created (after several questions regarding the data distribution, the table data volume, the use of bind variables, etc…)

create index mho_ind on MESSAGE_OUT (status, provider_id, scheduled_time);

Me: and if sms_status contains repetitive values then I would have added a compress command to that index creation

Colleague: there is no difference in the execution plan either by using my index or your index

------------------------------------------------------------------------------------------
| Id | Operation                     | Name          | Rows | Bytes |TempSpc| Cost (%CPU)|
------------------------------------------------------------------------------------------
| 0  | SELECT STATEMENT              |               | 5529 | 1376K |       | 446 (1)    |
| 1  | SORT ORDER BY                 |               | 5529 | 1376K | 1856K | 446 (1)    |
| 2  |  INLIST ITERATOR              |               |      |       |       |            |
|* 3 |   TABLE ACCESS BY INDEX ROWID | MESSAGE_OUT   | 5529 | 1376K |       | 140 (0)    |
|* 4 |    INDEX RANGE SCAN           | VIRTUAL_INDEX | 5529 |       |       | 6 (0)      |
------------------------------------------------------------------------------------------

Me: no, it is not the same plan. Please always consider the predicate part

Me: what is the predicate part of the plan using your index

Colleague: this is my index predicate part


Predicate Information (identified by operation id):
 ---------------------------------------------------
 3 - filter("SCHEDULED_TIME" IS NULL OR "SCHEDULED_TIME"<=:1)
 4 - access(("SMS_STATUS"=700 OR "SMS_STATUS"=707) AND "PROVIDER_ID"=0)
     filter("PROVIDER_ID"=0) --> additional filter operation

Colleague: and this is your index predicate part

Predicate Information (identified by operation id):
---------------------------------------------------
 3 - filter("SCHEDULED_TIME" IS NULL OR "SCHEDULED_TIME"<=:1)
 4 - access(("SMS_STATUS"=700 OR "SMS_STATUS"=707) AND "PROVIDER_ID"=0)
--> no additional filter operation

Me: and did you pointed out the difference or not yet?

Colleague: no, same plan, same cost and same execution time

Me: there is a fundamental difference between your plan and mine. In your plan there is a double operation on your engineered index:  “ACCESS + FILTER” operation while my engineered index needs only one precise operation :  “ACCESS

Me: and when it comes to performance you always prefers a precise index ACCESS operation to that double ACCESS and FILTER operations.

Me: your engineered index has a second columns on which an inequality predicate is applied

SCHEDULED_TIME <= :1

You should always start your index by the columns on which an equality predicate is applied. In my case, I put the SCHEDULED_TIME column at the trailing edge of my index and doing as such I have avoided a costly filter operation on my index while your engineered index has been subject to that costly filter operation

If you want to test this behaviour then below is an example to play with. I hope you will enjoy it

SQL> create table t1
     (id number,
     n_1000 number,
     n_5000 number,
     n_10000 number,
     small_vc varchar2(20),
     padding varchar2(100)
     );

Table created.

SQL> insert into t1
  with generator as (
  select --+ materialize
  rownum id
  from dual
  connect by
  rownum <= 10000
  )
  select
    rownum id,
    mod(rownum,1000) n_1000,
    mod(rownum,5000) n_5000,
    mod(rownum,10000) n_10000,
    lpad(rownum,10,'0') small_vc,
    rpad('x',100) padding
  from
    generator v1,
    generator v2
  where
  rownum <= 100000
  ;

SQL> create index my_ind on t1(id, n_5000, n_1000);

Index created.

SQL> create index colleague_ind on t1(id, n_1000, n_5000);

Index created.

SQL> alter index my_ind invisible;

Index altered.

SQL> exec dbms_stats.gather_table_stats(user, 't1');

PL/SQL procedure successfully completed.

SQL> select
        a.*
  from t1 a
  where id in (112,120)
  and (n_1000 is null
  or n_1000 <= 3000)
  and n_5000 in (120);

Statistics
------------------------------------------------------
 65 recursive calls
 0 db block gets
 95 consistent gets ---> spot this
 0 physical reads
 0 redo size
 1005 bytes sent via SQL*Net to client
 543 bytes received via SQL*Net from client
 2 SQL*Net roundtrips to/from client
 6 sorts (memory)
 0 sorts (disk)
 1 rows processed

SQL_ID 7d6ag1m1ztpgr, child number 1
-------------------------------------
select a.* from t1 a where id in (112,120) and (n_1000 is null
or n_1000 <= 3000) and n_5000 in (120)

Plan hash value: 3644584748
-------------------------------------------------------------------------------------------
| Id  | Operation                            | Name          | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |               |       |       |     4 (100)|
|   1 |  INLIST ITERATOR                     |               |       |       |            |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1            |     2 |   258 |     4   (0)|
|*  3 |    INDEX RANGE SCAN                  | COLLEAGUE_IND |     2 |       |     3   (0)|
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
 2 - filter(("N_1000"<=3000 OR "N_1000" IS NULL))
 3 - access((("ID"=112 OR "ID"=120)) AND "N_5000"=120)
 filter("N_5000"=120) ---> spot this

SQL> alter index colleague_ind invisible;

Index altered.

SQL> alter index my_ind visible;

Index altered.

SQL> select
       a.*
  from t1 a
  where id in (112,120)
  and (n_1000 is null
  or n_1000 <= 3000)
  and n_5000 in (120);

Statistics
------------------------------------------------------
 33 recursive calls
 0 db block gets
 49 consistent gets --> spot the reduction
 0 physical reads
 0 redo size
 1005 bytes sent via SQL*Net to client
 543 bytes received via SQL*Net from client
 2 SQL*Net roundtrips to/from client
 6 sorts (memory)
 0 sorts (disk)
 1 rows processed

SQL_ID 7d6ag1m1ztpgr, child number 1
-------------------------------------
select a.* from t1 a where id in (112,120) and (n_1000 is null
or n_1000 <= 3000) and n_5000 in (120)

Plan hash value: 4286547933</pre>
------------------------------------------------------------------------------------
| Id  | Operation                            | Name   | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |        |       |       |     4 (100)|
|   1 |  INLIST ITERATOR                     |        |       |       |            |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1     |     2 |   258 |     4   (0)|
|*  3 |    INDEX RANGE SCAN                  | MY_IND |     2 |       |     3   (0)|
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
 2 - filter(("N_1000"<=3000 OR "N_1000" IS NULL))
 3 - access((("ID"=112 OR "ID"=120)) AND "N_5000"=120)
     --> spot the absence of filter on index

June 14, 2013

ORA-08102: index key not found: Part II

Filed under: Index — hourim @ 6:52 pm

Last year I wrote a note about how I succeeded to overcome an index key not found error by moving the underlying table not without mentioning that an offline rebuild of the corrupted index reveals to be of no help.  Because, in contrast to an online index rebuild which is based on the table data, an offline index rebuild is based on the index data. And, as far as this data is corrupted in the index, rebuilding the same index with the same data will  produce the same error.

What prompted me to write this article is that, yesterday,  we have been confronted to the same error with the same index in the ACCEPTANCE (beta) database.

I was going to play with this case by tracing(10046 events) an offline rebuild first and then set the index in an unusable state before rebuilding  it offline when I received an e-mail from a DBA telling that he has successfully rebuilt the culprit index online. Too late.

Fortunately, yes you read it correctly, the day after, the same error occurred again but this time on another index of the same table.

The occasion was then given to me again to check the suggestion made by Jason Bucata (see comment 2) about putting the index in an unusable state and rebuild it offline. As such, i.e. when index is in an unusable state, even if this index is rebuilt offline, Oracle will use the underlying table to reconstruct the corrupted index in contrast to a “valid” index rebuilt offline where the underlying table is not used during this kind of rebuild.

And by the way, instead of rebuilding the newly corrupted index, I decide to consider all the table indexes (it is not very safe  but I could not take a risk of another day with a new different corrupted index)

select 'alter index ' ||index_name || ' unusable;' from user_indexes where table_name = 'TABLE_XXX';
select 'alter index ' ||index_name || ' rebuild;'  from user_indexes where table_name = 'TABLE_XXX';

The 10046 trace for the rebuild offline of an unusable index identified by its object_id (obj#=245082) belonging to a table identified by its object_id ( obj#=244832) when tkprofed show this:

alter index XXX_IND_NI rebuild

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       75      0.32       0.37          0         96          0           0
Execute      1    182.96     174.86     492687     368598      75682           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       76    183.28     175.23     492687     368694      75682           0

Misses in library cache during parse: 75
Optimizer mode: ALL_ROWS
Parsing user id: 47

Elapsed times include waiting on following events:

Event waited on                             Times   Max. Wait  Total Waited
----------------------------------------   Waited  ----------  ------------
SQL*Net break/reset to client                 150        0.00          0.17
SQL*Net message to client                      76        0.00          0.00
SQL*Net message from client                    76      146.07        169.65
db file scattered read                       6588        0.61          7.27
db file sequential read                       821        0.03          0.08
direct path write temp                        320        0.29          5.81
direct path read temp                        4498        0.06          1.17
log file sync                                   2        0.02          0.02
log file switch completion                      8        0.31          0.73
direct path write                               6        0.00          0.00
reliable message                                1        0.00          0.00
enq: RO - fast object reuse                     1        0.00          0.00
rdbms ipc reply                                 1        0.01          0.01
********************************************************************************

The presence of db file scattered read wait event is a clear indication of a full segment read; and their high number (6588) compared to the db file sequential read (821) suggests that this offline rebuild (of an unusable index) has been done using the underlying table.

Note, by the way, the unusual high number (47) of Misses in Library cache during parse and a cpu time (183 seconds) greater than the elapsed time (175 seconds)

The trace file shows also the following interesting information:

 =====================

PARSING IN CURSOR #2 len=43 dep=0 uid=47 oct=9 lid=47 tim=22486851823983 hv=2687996766 ad='920eda48'
alter index XXX_IND_NI rebuild
END OF STMT

PARSE #2:c=10000,e=6576,p=0,cr=9,cu=0,mis=1,r=0,dep=0,og=1,tim=22486851823977
BINDS #2:
WAIT #2: nam='db file scattered read' ela= 2526 file#=37 block#=21 blocks=58 obj#=244832 tim=22486851828916
WAIT #2: nam='db file scattered read' ela= 4839 file#=37 block#=79 blocks=58 obj#=244832 tim=22486851838736
WAIT #2: nam='db file scattered read' ela= 3139 file#=37 block#=137 blocks=58 obj#=244832 tim=22486851847085
WAIT #2: nam='db file scattered read' ela= 2603 file#=37 block#=195 blocks=58 obj#=244832 tim=22486851853977
WAIT #2: nam='db file scattered read' ela= 5218 file#=37 block#=253 blocks=58 obj#=244832 tim=22486851863154
WAIT #2: nam='db file scattered read' ela= 2313 file#=37 block#=311 blocks=58 obj#=244832 tim=22486851868008
WAIT #2: nam='db file scattered read' ela= 2611 file#=37 block#=369 blocks=58 obj#=244832 tim=22486851875983
WAIT #2: nam='db file scattered read' ela= 3098 file#=37 block#=427 blocks=58 obj#=244832 tim=22486851882593
WAIT #2: nam='db file scattered read' ela= 3194 file#=37 block#=485 blocks=58 obj#=244832 tim=22486851892313
WAIT #2: nam='db file scattered read' ela= 2763 file#=37 block#=543 blocks=58 obj#=244832 tim=22486851901798
WAIT #2: nam='db file scattered read' ela= 3374 file#=37 block#=601 blocks=48 obj#=244832 tim=22486851912129
WAIT #2: nam='db file scattered read' ela= 3214 file#=37 block#=1299 blocks=58 obj#=244832 tim=22486851918241
WAIT #2: nam='db file scattered read' ela= 3015 file#=37 block#=1357 blocks=58 obj#=244832 tim=22486851927379
WAIT #2: nam='db file scattered read' ela= 2787 file#=37 block#=1415 blocks=58 obj#=244832 tim=22486851936055
………..

WAIT #2: nam='db file sequential read' ela= 79 file#=45 block#=3152 blocks=1 obj#=244832 tim=22486853368773
WAIT #2: nam='db file scattered read' ela= 365 file#=45 block#=3154 blocks=55 obj#=244832 tim=22486853369742
WAIT #2: nam='db file scattered read' ela= 464 file#=45 block#=3852 blocks=58 obj#=244832 tim=22486853373874
WAIT #2: nam='db file scattered read' ela= 680 file#=45 block#=3910 blocks=58 obj#=244832 tim=22486853378189
WAIT #2: nam='db file scattered read' ela= 1957 file#=45 block#=3968 blocks=58 obj#=244832 tim=22486853385193
WAIT #2: nam='db file scattered read' ela= 846 file#=45 block#=4026 blocks=58 obj#=244832 tim=22486853390273
WAIT #2: nam='db file scattered read' ela= 456 file#=45 block#=4084 blocks=58 obj#=244832 tim=22486853395209
WAIT #2: nam='db file scattered read' ela= 455 file#=45 block#=4142 blocks=58 obj#=244832 tim=22486853399969
etc….

A very high number of db file scattered read on obj#=244832 which represents the object id of the table to which is attached to corrupted index.

Finally, the bottom line from this blog article is to show that when an index is corrupted (ora-08102) it is possible to rebuild it offline but you should first set it into an unusable state.

February 6, 2012

How to solve ORA-08102: index key not found

Filed under: Index — hourim @ 2:28 pm

In one of our test databases we encountered the following error during an overnight PL/SQL purge job:

ORA-08102: index key not found, obj# 124885, file 48, block 55492 (2) 01-FEB-12

The purpose of the job is to delete data introduced since more than 6 days respecting for that several hierarchical parent-child relationships.  It has been working without any relatives problems since more than 2 years until it started crashing out with the above ORA-08102 error. I aim via this blog article to show how I have resolved this problem.

The first thing I did is of course to identify the corrupted object via the following select:

mhouri.world> ed
    Wrote file afiedt.buf
  1  select substr(object_name,1,30), object_type
  2  from user_objects
  3* where object_id = 154711
;

 SUBSTR(OBJECT_NAME,1,30)       OBJECT_TYPE
------------------------------ ----------------
XXX_ZZZ_UK                        INDEX

The naming standard (_UK) is as such that I immediately identified this index as an automatic index created to enforce a unique constraint. I thought first that I need to rebuild this unique index:


mhouri.world> alter index XXX_ZZZ_UK rebuild;

Index altered.

Unfortunately, when I launched again the purge batch the same error has been raised again. So my second tentative was to drop and recreate the unique constraint and implicitly the unique index.


mhouri.world> alter table t1 drop constraint XXX_ZZZ_UK;

Table altered.

mhouri.world > alter table t1 add constraint XXX_ZZZ_UK unique (id1, id2, dat1);

Table altered.

Of course that you have first to get the DDL definition of the constraint before dropping it so that you will be able to re-created it again.

When the purge process has been re-started following this last intervention, it, unfortunately, raised again the same ORA-08102 on the same object. Finally I have decided to work on the underneath table instead of its unique constraint and unique index.

mhouri.world>> alter table t1 move;

 Command that has invalidated all attached indexes and which obliged me to re-build them:


mhouri.world > select 'alter index ' || index_name || ' rebuild;'
from user_indexes
where status != 'VALID';

Once all indexes rebuilt, I launched again the purge job which surprisingly completed successfully.

Frankly speaking I don’t know if I would have been able to work around this error if I have tried instead of the move command a rebuild or shrink command. But what I have learnt from this experience is that when an index is corrupted it could be repaired by moving table data instead of dropping and recreating the corrupted index as initially indicated by the ORA-08102 error. I have also to admit that I don’t know yet the side effect of the move command and will certainly write something about that if eventually I will come to learn something about that.
.

July 3, 2011

Using Function Based index appropriately

Filed under: Index — hourim @ 11:48 am

Very often requests to enhance performance of queries resembling to the following one:

SELECT col1
,col2
,col3
FROM t
WHERE flag != 'N';

populate forums like OTN. Requesters have already created an index on the flag column  and are complaining about the query which is not performing well and which is not using the index they created to cover the  flag where clause column. The main problem here is the “!=” operator.  The Oracle Cost based Optimizer (CBO) is unable to use an index to handle this operator. Fortunately, there is a possibility to enhance this kind of queries by taking the advantage of using a function based index. And this is what I aim to explain here via this blog article:

mhouri.world > create table t as select * from all_objects;
Table created.

mhouri.world > select count(1) from t;
COUNT(1)
----------
39114

mhouri.world > SELECT /*+ gather_plan_statistics */
2         t.*
3    FROM t
4   WHERE status != 'VALID'
5  ;

30 rows selected.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
SQL_ID  5xtutwubr5xqv, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */        t.* FROM t WHERE status !=
'VALID'
Plan hash value: 2153619298
------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| T    |      1 |      6 |     30 |00:00:00.01 |     551 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("STATUS"<>'VALID')
Note
-----
- dynamic sampling used for this statement

22 rows selected.

Let’s now create an index on the status column, compute statistics and repeat the same select

mhouri.world > create index ind_status on t (status);

Index created.

mhouri.world >BEGIN
2     DBMS_STATS.gather_table_stats (ownname          => USER,
3                                    tabname          =>'t1',
4                                    estimate_percent => 100,
5                                    method_opt       => 'FOR ALL COLUMNS SIZE 1'
6                                   );
7  END;
8  /

PL/SQL procedure successfully completed.

mhouri.world > SELECT /*+ gather_plan_statistics */
2     t.*
3  FROM t
4  WHERE status != 'VALID';

30 rows selected.

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
SQL_ID  5xtutwubr5xqv, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */        t.* FROM t WHERE status !=
'VALID'
Plan hash value: 2153619298
-----------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| T    |      1 |      6 |     30 |00:00:00.01 |     551 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("STATUS"<>'VALID')

Note
-----
- dynamic sampling used for this statement
22 rows selected.

The newly created index has not been of any help. It hasn’t been used at all by the CBO. This is of course due to the “!=” operator. Let’s then, now, create a function based index and requery again.

mhouri.world > create index ind_fbi_status on t (case when status = 'VALID' then NULL else 'X' end );

Index created.

mhouri.world > SELECT /*+ gather_plan_statistics */
2     t.*
3  FROM t
4  WHERE status != 'VALID';

30 rows selected.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
SQL_ID  5xtutwubr5xqv, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */        t.* FROM t WHERE status !=
'VALID'
Plan hash value: 2153619298
------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| T    |      1 |      6 |     30 |00:00:00.01 |     551 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("STATUS"<>'VALID')
Note
-----
- dynamic sampling used for this statement

22 rows selected.

Nothing noticeable happened; we are still full scanning our T table. You know why? This is because to take advantage of using the newly created function based (FBI) index, we have to rewrite the query to make the query and the index consistent with each other.

mhouri.world > SELECT /*+ gather_plan_statistics */
2         t.*
3    FROM t
4    WHERE CASE
5            WHEN status = 'VALID'
6               THEN NULL
7            ELSE 'X'
8         END = 'X';

30 rows selected.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
SQL_ID  32fcmm91ywcqf, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */        t.*   FROM t  WHERE CASE           WHEN status =
'VALID'              THEN NULL           ELSE 'X'        END = 'X'
Plan hash value: 3817300654
--------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID| T              |      1 |      6 |     30 |00:00:00.01 |      14 |
|*  2 |   INDEX RANGE SCAN          | IND_FBI_STATUS |      1 |    130 |     30 |00:00:00.01 |       3 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."SYS_NC00014$"='X')

Note
-----
- dynamic sampling used for this statement

23 rows selected.

And here we are. This is a perfect example of how to cover queries that are not very well performing because of “!=” operator which impeaches a normal index on the corresponding column to be used.

Warning: the above FBI index will not work correctly if the status column is allowed to be null.

mhouri.world> update t
2  set status = null
3  where status != 'VALID'
4  and rownum <= 4;

4 rows updated.

mhouri.world> commit;

Commit complete.

mhouri.world> SELECT count(1)
2    FROM t
3   WHERE status != 'VALID';

COUNT(1)
----------
26

mhouri.world>  SELECT count(1)
2       FROM t
3       WHERE CASE
4               WHEN status = 'VALID'
5                  THEN NULL
6               ELSE 'X'
7            END = 'X';

COUNT(1)
----------
30

The query using the FBI index is not returning the correct number of rows because, now, the status column has null values in it.  Both the query and the FBI index should be re-factored in order to take into account the NULL values of the status column:

mhouri.world> create index ind_fbi_status on t(case when nvl(status,'VALID') = 'VALID' then null else 'X' end);

Index created.

mhouri.world> select count(1)
2  from t
3  where case when nvl(status,'VALID') = 'VALID'
4                 then null
5             else 'X'
6             end = 'X';

COUNT(1)
----------
26

mhouri.world> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
SQL_ID  ffj2y4c2q8vup, child number 0
-------------------------------------
select count(1) from t where case when nvl(status,'VALID') = 'VALID'
then null            else 'X'            end = 'X'
Plan hash value: 3388779105
------------------------------------------------------------------------------------
| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE   |                |     1 |     5 |            |          |
|*  2 |   INDEX RANGE SCAN| IND_FBI_STATUS |     6 |    30 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."SYS_NC00014$"='X')

Note
-----
- dynamic sampling used for this statement

To be followed.

Next Page »

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)