| 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 8, 7:35 am, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On May 8, 1:00 am, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk>
> wrote:
> > "Charles Hooper" <hooperc2..._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 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-Hide
>
> I was able to produce the problem on a test box with Oracle 10.2.0.2
> with Oct 2006 patch running on 32 bit Windows. I could not reproduce
> the problem on Oracle 10.2.0.2 with Oct 2006 patch running on 64 bit
> Windows, nor could I reproduce the problem on a test box (running
> Vista) on Oracle 10.2.0.3. The Oracle 10.2.0.2 test box (32 bit
> Windows) has query_rewrite_enabled set to false,
> optimizer_index_cost_adj set to 100, and optimizer_index_caching set
> to 0 - Oracle 10.2.0.2 on 64 bit Windows has parameters that are much
> more carefully tuned. I will be comparing the 10053 trace files from
> the three runs to see if anything jumps out.
Just an update to this issue. I was able to finally force Oracle to select the correct index on 32 bit Windows.
ALTER SYSTEM SET QUERY_REWRITE_ENABLED=TRUE SCOPE=BOTH;
ALTER SYSTEM SET QUERY_REWRITE_INTEGRITY=ENFORCED SCOPE=BOTH;
ALTER SYSTEM FLUSH SHARED_POOL;
Above did not change the plan, still using the wrong index.
ALTER SYSTEM SET OPTIMIZER_INDEX_COST_ADJ=20 SCOPE=BOTH;
ALTER SYSTEM FLUSH SHARED_POOL;
Above did not change the plan, still using the wrong index.
ALTER SYSTEM SET OPTIMIZER_INDEX_CACHING=100 SCOPE=BOTH;
ALTER SYSTEM FLUSH SHARED_POOL;
Above did not change the plan, still using the wrong index.
ALTER SYSTEM SET "_OPTIMIZER_UNDO_COST_CHANGE"='10.1.0.4' SCOPE=BOTH;
ALTER SYSTEM FLUSH SHARED_POOL;
Problem solved.
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_C0014623 | 1 | 1 | 1 |00:00:00.01 | 2 | ------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
1 - filter(("ENTITY_ID"=:1 AND "INVOICE_DATE"<SYSDATE@!-100)) 2 - access("INVOICE_ID"=:2)
This was an interesting experiment - now to see if the change positively affects the ERP package. Jonathan, thanks for the suggestions and confirming that it appears to be an isolated issue.
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Tue May 08 2007 - 08:39:40 CDT
 |
 |