| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Slow SQL, too many logical reads ?
On May 7, 4:44 pm, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk>
wrote:
> "Charles Hooper" <hooperc2..._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.
>
> (There is a note about this change in the book, by the way).
>
> --
> Regards
>
> Jonathan Lewishttp://jonathanlewis.wordpress.com
>
> Author: Cost Based Oracle: Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> The Co-operative Oracle Users' FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.html
Jonathan,
Thanks for the feedback. I recall reading about the potential problems of values outside min/max in your book, so I was aware of that possibility. I do not believe that that is what is happening.
On Oracle 10.2.0.2, if I execute the SQL statement that I previously referenced using constants (or with binds without providing initial values, or with binds when bind peeking is disabled), one plan is developed that is efficient, and if I execute the same SQL statement with bind variable values specified the plan is very inefficient (this is in a packaged application, where I can't touch the source code). A quick demonstration that should be easily repeatable:
Create a three column table, with the first column being the primary
key:
CREATE TABLE T1 (
INVOICE_ID VARCHAR2(15),
INVOICE_DATE DATE,
ENTITY_ID VARCHAR2(5) NOT NULL,
PRIMARY KEY(INVOICE_ID));
Create an index on the third column:
CREATE INDEX IND_T1_ENTITY_ID ON T1(ENTITY_ID);
Insert 3000 rows into the table, all with the same value for
ENTITY_ID:
INSERT INTO
T1
SELECT
TRIM(TO_CHAR(ROWNUM,'00000')) INVOICE_ID,
TRUNC((SYSDATE-3000)+ROWNUM) INVOICE_DATE,
'1' ENTITY_ID
FROM
DUAL
CONNECT BY
LEVEL<=3000;
COMMIT; Make certain that the statistics are up to date: EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'table owner here',TABNAME=>'T1',CASCADE=>TRUE)
The first test:
SELECT
INVOICE_ID,
INVOICE_DATE,
ENTITY_ID
FROM
T1
WHERE
ENTITY_ID='1'
AND INVOICE_ID='00010'
AND INVOICE_DATE<(SYSDATE-100);
The ENTITY_ID column is very unselective, as there is only one distinct value in that column, so Oracle should not use that index, but should use the index on the primary key (INVOICE_ID column). The DBMS XPLAN:
| Id | Operation | Name | Starts | E-Rows |A-Rows | A-Time | Buffers |
|* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 1 | 1 |00:00:00.01 | 3 | |* 2 | INDEX UNIQUE SCAN | SYS_C0014551 | 1 | 1 | 1 |00:00:00.01 | 2 | ------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
1 - filter(("INVOICE_DATE"<SYSDATE@!-100 AND "ENTITY_ID"='1')) 2 - access("INVOICE_ID"='00010')
The above DBMS XPLAN shows that it behaved as expected. Now,
introduce bind variable values and the unexpected happens:
SELECT
INVOICE_ID,
INVOICE_DATE,
ENTITY_ID
FROM
T1
WHERE
ENTITY_ID= :1
AND INVOICE_ID= :2
AND INVOICE_DATE<(SYSDATE-100);
The DBMS XPLAN - note that the access at step #2 has changed to the very unselective index:
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
|* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 1 | 1 |00:00:00.01 | 15 | |* 2 | INDEX RANGE SCAN | IND_T1_ENTITY_ID | 1 | 1 | 3000 |00:00:00.01 | 6 | ----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
1 - filter(("INVOICE_ID"=:2 AND "INVOICE_DATE"<SYSDATE@!-100)) 2 - access("ENTITY_ID"=:1)
Just to make certain that I did not submit the bind variables
incorrectly, from the 10046 trace:
Bind#0
oacdty=96 mxl=32(02) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=1000010 frm=01 csi=178 siz=64 off=0
kxsbbbfp=0ed45e3c bln=32 avl=01 flg=05
value="1"
Bind#1
oacdty=96 mxl=32(10) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=1000010 frm=01 csi=178 siz=0 off=32
kxsbbbfp=0ed45e5c bln=32 avl=05 flg=01
value="00010"
It appears that the cost based optimizer becomes confused when examining the selectivity of the IND_T1_ENTITY_ID index when bind variable values are used.
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Mon May 07 2007 - 16:48:38 CDT
 |
 |