Re: Slow SQL, too many logical reads ?

"Charles Hooper" <hooperc2000_at_yahoo.com> wrote in message news:1178574518.539544.105860_at_u30g2000hsc.googlegroups.com...
>
> 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.
>

Charles,

I can't get the test case you sent to reproduce on either 10.2.0.1 or 10.2.0.3; I use the unique index in both cases.

I can send you a 10053 if you want to do a point by point comparison to see where the difference occurs.

Do you end up with a histogram on the ENTITY_ID when you do the test ? (I don't - and it does look like a histogram-related bug).

What are your setting for system stats, and do you have any special settings for any of the optimizer parameters.

(You could cut the parameter listing from the 10053 and post it).

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