| 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 10, 11:46 am, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk>
wrote:
> Charles,
>
> I couldn't see any obvious reason from the parameters
> why your 10.2.0.2 should behave differently.
>
> There isn't a histogram on entity_id in your output.
> A "histogram" with endpoint number (0,1) is simply
> recording the low/high - it's not a real histogram.
>
> --
> 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, I greatly appreciate your comments and explanation of what I am seeing regarding histograms.
I increased the size of the data set from 3,000 rows to 30,000 rows and was able to reproduce the problem where Oracle attempts to access the data using the wrong index on 32 bit and 64 bit Oracle 10.2.0.2 and 32 bit Oracle 10.2.0.3 - previously I was only able to reproduce the problem on 32 bit Oracle 10.2.0.2.
The full test run:
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 30,000 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<=30000;
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:
SELECT INVOICE_ID , INVOICE_DATE , ENTITY_ID FROM T1 WHERE
ENTITY_ID= '1' AND
INVOICE_ID= '00010' AND INVOICE_DATE<(SYSDATE-100)
Plan hash value: 426301786
| 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_C0013370 | 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);
SQL_ID 2287qj8u8scfq, child number 0
Plan hash value: 2911507501
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
|* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 1 | 1 |00:00:00.15 | 170 | |* 2 | INDEX RANGE SCAN | IND_T1_ENTITY_ID | 1 | 1 | 30000 |00:00:00.09 | 83 | ----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
1 - filter(("INVOICE_ID"=:2 AND "INVOICE_DATE"<SYSDATE@!-100)) 2 - access("ENTITY_ID"=:1)
>From the 10053 trace file:
SINGLE TABLE ACCESS PATH
Column (#3): ENTITY_ID(VARCHAR2)
AvgLen: 2.00 NDV: 1 Nulls: 0 Density: 1.6667e-005
Histogram: Freq #Bkts: 1 UncompBkts: 30000 EndPtVals: 1
Column (#1): INVOICE_ID(VARCHAR2)
AvgLen: 6.00 NDV: 30000 Nulls: 0 Density: 3.3333e-005
Column (#2): INVOICE_DATE(DATE)
AvgLen: 8.00 NDV: 30000 Nulls: 0 Density: 3.3333e-005 Min: 2451232
Max: 2481231
Table: T1 Alias: T1
Card: Original: 30000 Rounded: 1 Computed: 0.00 Non Adjusted:
0.00
Access Path: TableScan
Cost: 30.88 Resp: 30.88 Degree: 0
Cost_io: 29.00 Cost_cpu: 7826712
Resp_io: 29.00 Resp_cpu: 7826712
Access Path: index (UniqueScan)
Cost: 1.00 Degree: 1 Resp: 1.00 Card: 0.00 Bytes: 0
The above example is an abbreviated example of a problem in an ERP package that I am seeing. One report in particular that should take 45 seconds to generate (should be 1 seconds or less, but the program is database independent), is actually taking six to eight minutes to execute. My solution at the time is to generate a logon trigger that disables bind variable peeking for all connections related to that ERP package. Doing so drops the execution time back down to 45 seconds.
Thanks again for your comments.
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Thu May 10 2007 - 12:33:36 CDT
 |
 |