By Shaunyl


2015-02-12 14:28:17 8 Comments

I am on Oracle 10.2.0.4.0. I have a big Index Organized Table (IOT) with 74 columns and over 345 million of rows. An external tool execute a batch where I cannot touch the source code. The critical query in the batch execute a SELECT against multiple columns of the table, filtering with three query predicates using three different equality operators. This three predicates are passed as Bind Variables, and during the batch they change their values. Sometimes, during a Bind Variable change, the CBO choose another execution plan, that is not good. The predicates are these:

.. WHERE ((COSTEVENT.ACC_NUM=:PREVALUE0) AND 
  (COSTEVENT.EVENT_SEQ=PREVALUE1)) AND (COSTEVENT.EVENT_REF=PREVALUE2);

The table has an UNIQUE INDEX on the column EVENT_REF. There is a composite PRIMARY KEY on five columns, two of which are EVENT_SEQ and ACC_NUM (the others are EVENT_TYPE_ID and EVENT_SOURCE and EVENT_REF). When the query performs well, the CBO chooses the following execution plan:

-------------------------------------------------------------------------------------
| Id  | Operation         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                 |       |       |     8 (100)|          |
|   1 |  INDEX UNIQUE SCAN| COSTEVENT_PK    |     1 |   265 |     8   (0)| 00:00:01 |
|   2 |   INDEX RANGE SCAN| COSTEVENT_UK1   |     1 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

   1 - access("COSTEVENT"."EVENT_REF"='00DE320000620E1')
       filter("COSTEVENT"."EVENT_SEQ"=11 AND
              "COSTEVENT"."ACC_NUM"='LA00032914')
   2 - access("COSTEVENT"."EVENT_REF"='00DE320000620E1')

But during the batch, sometimes, the CBO switch to another plan, the bad plan:

-----------------------------------------------------------------------------------
| Id  | Operation        | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                |     1 |   266 |     5   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| COSTEVENT_PK   |     1 |   266 |     5   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("COSTEVENT"."ACC_NUM"='LA00032914' AND
              "COSTEVENT"."EVENT_SEQ"=111 AND "COSTEVENT"."EVENT_REF"='00DE320000620E1')
       filter("COSTEVENT"."EVENT_REF"='00DE320000620E1')

Instead of accessing first to the EVENT_REF unique index key, and then performing the limited range scan, the CBO, for some strange reason, access first to ACC_NUM, and the applies the filter; but ACC_NUM is not UNIQUE so he needs to perform an INDEX RANGE SCAN through million and million of rows. If I add the hint /*+ index(COSTEVENT COSTEVENT_UK1) */ to the SELECT, the CBO chooses correctly for an INDEX UNIQUE SCAN, but I can't access to the source code.

Other useful info are here:

COLUMN_NAME                       DENSITY LOW_VALUE                                HIGH_VALUE                               NUM_DISTINCT HISTOGRAM
------------------------------ ---------- ---------------------------------------- ---------------------------------------- ------------ ---------------
ACC_NUM                        .001146789 30303030343037                           4E3030303331343634                              31011 HEIGHT BALANCED
EVENT_SEQ                      1.4550E-09 C102                                     C20204                                            100 FREQUENCY
EVENT_SOURCE                   .000808407 30303031383034                           7777772E7469656D706F2E6974                     123629 HEIGHT BALANCED
EVENT_TYPE_ID                  1.4550E-09 C102                                     C119                                               11 FREQUENCY
EVENT_REF                      2.9101E-09 303030364645303030343534343544           303044434644303030303032303634              343634290 NONE

and here:

INDEX_NAME                                                                                 CLUSTERING_FACTOR   NUM_ROWS 
------------------------------------------------------------------------------------------ ----------------- ---------- 
COSTEVENT_PK                                                                                               0  345257860 
COSTEVENT_UK1                                                                                       30346000  345738415 

The question are: 1) why Oracle chooses for and INDEX RANGE SCAN instead of an INDEX UNIQUE SCAN, even if the first is extremely slower than the second? 2) There is a way to lead Oracle to the right choice, without having to apply a SQL Profile, for example? (maybe manipulating histograms, and statistics, etc..)

Thanks in advance

1 comments

@Gandolf989 2015-02-12 15:22:58

I have a big Index Organized Table (IOT) with 74 columns

Why do you think that this table is a candidate for an IOT? IMHO, IOT's are meant to be narrow tables that don't change often. I have done testing with narrow tables and have found IOT's to do inserts at a slower rate than a traditional table with indexes. You may want to start by recreating this as a traditional table and then think about what indexes you want on the table. Also there is a note in this article on IOT's. Performance Tuning Guide

@Shaunyl 2015-02-12 16:09:36

I am not the developer. The table is just that. I am interested to know why Oracle doesn't use an INDEX UNIQUE SCAN.

@Gandolf989 2015-02-12 16:11:03

Then you should probably talk to the developer on why this table needs to be an IOT.

@Shaunyl 2015-02-12 16:13:01

They access the data always via the primary key, so with a IOT this action is quicker.

@Gandolf989 2015-02-12 16:30:05

You can find out for certain if the table returns rows faster than an IOT by creating a copy of the table as a regular table and then creating the same indexes. then query both tables and see which returns the rows faster. You will probably get similar or better results with a regular table. Don't take my word for it though, test it on your own.

@Shaunyl 2015-02-12 16:58:37

In this table they add multiple rows every time the batch is executed (about once a week). If it was an heap, these data would be added to the end in different blocks. The IOT, instead, force a physical clustering of data (less blocks to read). Also, cause they access only via PK, the remaining fields are present at that location. The IOT avoids the IO operation of following a rowid back to table data. Anyway, my question is not about a comparison between IOT and Heap, but why the optimizer does not use an Index Unique Scan in that situation. So please, don't go off on a tangent. Thanks.

@Gandolf989 2015-02-12 17:58:42

This is what Tom Kyte has to say about it, asktom.oracle.com/pls/asktom/…. If using a hint works, then you may want to use a sql profile in cases where the optimizer is choosing the wrong explain plan. It is easy to do that with grid. A little harder without grid. You might also have better tools for forcing explain plans if you go to 11.2.0.4. But that might also bring other issues as well.

@Philᵀᴹ 2015-02-12 19:57:17

In my experience, SQL Server devs will think that IOTs are sensible because that's what it tends to use

Related Questions

Sponsored Content

1 Answered Questions

[SOLVED] Filter predicates executing before Access predicates

1 Answered Questions

[SOLVED] Oracle Optimizer refuses to use my index

2 Answered Questions

[SOLVED] why optimizer in Oracle database does not use index?

2 Answered Questions

1 Answered Questions

1 Answered Questions

2 Answered Questions

[SOLVED] Non Clustered Index Scan Properties window - Predicate order different than query order

  • 2015-11-03 18:12:36
  • John G Hohengarten
  • 80 View
  • 0 Score
  • 2 Answer
  • Tags:   execution-plan

2 Answered Questions

[SOLVED] How can Oracle use a Range Scan here?

  • 2012-02-14 16:09:14
  • Matt
  • 2390 View
  • 2 Score
  • 2 Answer
  • Tags:   oracle index

Sponsored Content