Re: Slow SQL, too many logical reads ?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 7 May 2007 21:44:41 +0100
Message-ID: <dIqdnbmeG_yADaLbnZ2dnUVZ8sKlnZ2d@bt.com>

"Charles Hooper" <hooperc2000_at_yahoo.com> wrote in message news:1178560537.058879.28140_at_p77g2000hsh.googlegroups.com...

>                 What is a bit hard to understand is why the cost based
> optimizer stops using a highly selective index, and instead uses an
> index that contains one distinct value.  If the SQL statement is
> rewritten to not use bind variables, but instead use constants in
> place of the bind variables, with the same values specified during the
> second parse call, Oracle continues to use them highly selective
> index, as it did when bind variable values were not specified during

> the parse.
>

This MAY be related to the way in which 10g handles predicates that are outside the known low/high range.

The following outputs will no doubt be a mess, but they demonstrate the point (running on 10.2.0.1 - things may change in 10.2.0.3).

I have an index on id which is declared non-unique (even though the data is unique). Id ranges from 1 to 10,000.

I have an index on n1, which holds 50 rows of each of the values from 0 to 199. A point comes where my predicate on N1 tests for a value which is so far outside the range that the optimizer estimates it will return fewer rows than the one row expected from using the ID index.

SQL> select * from t1 where n1 = 394 and id = 99;

Execution Plan

Plan hash value: 965905564

| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| 
Time     |


-------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |       |     1 |   119 |     2   (0)| 
00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |   119 |     2   (0)| 
00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_I2 |     1 |       |     1   (0)|

00:00:01 |

Predicate Information (identified by operation id):

1 - filter("N1"=394)
2 - access("ID"=99)

SQL> select * from t1 where n1 = 395 and id = 99;

Execution Plan

Plan hash value: 1429545322

| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| 
Time     |


-------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |       |     1 |   119 |     2   (0)| 
00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |   119 |     2   (0)| 
00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_I1 |     1 |       |     1   (0)|

00:00:01 |

Predicate Information (identified by operation id):

1 - filter("ID"=99)
2 - access("N1"=395)

SQL> spool off

Looking at it another way - drop the predicate on id, and see what happens to the ROWS as the predicate on N1 moves outside the low/high

SQL> select * from t1 where n1 = 150 ;

Execution Plan

Plan hash value: 1429545322

| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| 
Time     |


-------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |       |    50 |  5950 |     2   (0)| 
00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |    50 |  5950 |     2   (0)| 
00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_I1 |    50 |       |     1   (0)|

00:00:01 |

Predicate Information (identified by operation id):

2 - access("N1"=150)

SQL> select * from t1 where n1 = 250 ;

Execution Plan

Plan hash value: 1429545322

| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| 
Time     |


-------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |       |    37 |  4403 |     2   (0)| 
00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |    37 |  4403 |     2   (0)| 
00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_I1 |    37 |       |     1   (0)|

00:00:01 |

Predicate Information (identified by operation id):

2 - access("N1"=250)

SQL> select * from t1 where n1 = 300 ;

Execution Plan

Plan hash value: 1429545322

| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| 
Time     |


-------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |       |    25 |  2975 |     2   (0)| 
00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |    25 |  2975 |     2   (0)| 
00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_I1 |    25 |       |     1   (0)|

00:00:01 |

Predicate Information (identified by operation id):

2 - access("N1"=300)

SQL> select * from t1 where n1 = 350 ;

Execution Plan

Plan hash value: 1429545322

| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| 
Time     |


-------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |       |    12 |  1428 |     2   (0)| 
00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |    12 |  1428 |     2   (0)| 
00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_I1 |    12 |       |     1   (0)|

00:00:01 |

Predicate Information (identified by operation id):

2 - access("N1"=350)

SQL> spool off

(There is a note about this change in the book, by the way).

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Received on Mon May 07 2007 - 15:44:41 CDT