Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Slow SQL, too many logical reads ?

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US