| 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: Oracle Optmizer does not use the right index (Oracle 9.2.0.6)
On Mar 15, 3:45 pm, alano..._at_invera.com wrote:
> Hello,
>
> Not sure whether this problem has been discussed earlier in this
> forum. Can someone suggest a workaround to the following problem which
> can be replicated by following the sequence of detailed steps I have
> given below. In summary, if you have a table with two composite
> indexes, both with the same fields, but one of them has a field in
> descending order, then the Optimizer loses track of the correct index
> to use for a specific query.
>
> Thanks.
>
> A.J.Andrews
>
> See details below:
> ----------------
>
> SQL*Plus: Release 9.2.0.6.0 - Production on Mon Feb 27 08:29:06 2006
>
> Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
>
> Connected to:
> Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
> With the Partitioning, OLAP and Oracle Data Mining options
> JServer Release 9.2.0.6.0 - Production
>
> SQL> create table tab1 as select * from all_objects;
>
> Table created.
>
> SQL> set autotrace traceonly explain;
> SQL> create index tab1ind1 on tab1(owner, object_name);
>
> Index created.
>
> SQL> analyze table tab1 compute statistics;
>
> Table analyzed.
>
> SQL> alter session set optimizer_mode=first_rows;
> Session altered.
>
> SQL> select * from tab1 order by owner, object_name;
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=172 Card=4899
> Bytes=372324)
>
> 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TAB1' (Cost=172
> Card=4899 Bytes=372324)
>
> 2 1 INDEX (FULL SCAN) OF 'TAB1IND1' (NON-UNIQUE) (Cost=46
> Card=4899)
>
> ===> Note here that the correct index tab1ind1 is being used for the
> query.
>
> SQL> create index tab1ind2 on tab1(owner, object_name desc);
>
> Index created.
>
> SQL> analyze table tab1 compute statistics;
>
> Table analyzed.
>
> SQL> select * from tab1 order by owner, object_name desc;
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=172 Card=4899
> Bytes=372324)
>
> 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TAB1' (Cost=172
> Card=4899 Bytes=372324)
>
> 2 1 INDEX (FULL SCAN) OF 'TAB1IND2' (NON-UNIQUE) (Cost=48
> Card=4899)
>
> ===> Again, the correct index tab1ind2 is being used.
>
> SQL> select * from tab1 order by owner, object_name;
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=288 Card=4899
> Bytes=372324)
>
> 1 0 SORT (ORDER BY) (Cost=288 Card=4899 Bytes=372324)
> 2 1 TABLE ACCESS (FULL) OF 'TAB1' (Cost=3 Card=4899
> Bytes=372324)
>
> ===> But, at this time the Optimizer has forgotten about the existence
> of tab1ind1 and is going for a full table scan and a sort! Wrong
> choice! Should be using index tab1ind1.
>
> SQL> drop index tab1ind2;
>
> Index dropped.
>
> SQL> select * from tab1 order by owner, object_name;
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=172 Card=4899
> Bytes=372324)
>
> 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TAB1' (Cost=172
> Card=4899 Bytes=372324)
>
> 2 1 INDEX (FULL SCAN) OF 'TAB1IND1' (NON-UNIQUE) (Cost=46
> Card=4899)
>
> ===> Once tab1ind2 is dropped, the Optimizer remembers the existence
> of tab1ind1 and chooses the index scan for the very same query for
> which it used a table scan earlier.
>
> This problem is easily reproducible with any two indexes on a table,
> with both indexes being on the same set of columns, but with one of
> the indexes having a "desc" requirement on at least one of the columns.
FIRST_ROWS has a tendency to prefer index access, even if the index access that is selected is less than ideal. You should be using DBMS_STATS. With that said, a test on Oracle 10.2.0.2:
CREATE TABLE T2 AS
SELECT
*
FROM
ALL_OBJECTS;
Table created.
COMMIT; Commit complete.
CREATE INDEX T2_IND1 ON T2(TABLE_OWNER_HERE, OBJECT_NAME); Index created.
EXEC
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'TABLE_OWNER_HERE',TABNAME=>'T2',CASCADE=>TRUE);
PL/SQL procedure successfully completed.
ALTER SESSION SET OPTIMIZER_MODE=FIRST_ROWS; Session altered.
SELECT
*
FROM
T2
ORDER BY
OWNER,
OBJECT_NAME;
SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS'));
SQL_ID 9jf1t5g0sgzz4, child number 0
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 2 | 13945 | 27890 |00:00:01.12 | 12286 | 210 | | 2 | INDEX FULL SCAN | T2_IND1 | 2 | 13945 | 27890 |00:00:00.55 | 410 | 136 | ----------------------------------------------------------------------------------------------------------
CREATE INDEX T2_IND2 ON T2(TABLE_OWNER_HERE, OBJECT_NAME DESC); Index created.
EXEC
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'TABLE_OWNER_HERE',TABNAME=>'T2',CASCADE=>TRUE);
PL/SQL procedure successfully completed.
SELECT
*
FROM
T2
ORDER BY
OWNER,
OBJECT_NAME DESC;
SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS'));
| Id | Operation | Name | E-Rows | -------------------------------------------------------- | 1 | TABLE ACCESS BY INDEX ROWID| T2 | 13945 | | 2 | INDEX FULL SCAN | T2_IND2 | 13945 |
SELECT
*
FROM
T2
ORDER BY
OWNER,
OBJECT_NAME;
SQL_ID 9jf1t5g0sgzz4, child number 0
| 1 | SORT ORDER BY | | 1 | 13945 | 13945 | 00:00:00.15 | 182 | 37 | 1824K| 650K| 1/0/0| | 2 | TABLE ACCESS FULL| T2 | 1 | 13945 | 13945 | 00:00:00.11 | 182 | 37 | | | | -------------------------------------------------------------------------------------------------------------------------So, we are consistent so far.
ALTER SYSTEM FLUSH SHARED_POOL;
DROP INDEX T2_IND2;
Let's try a test:
SELECT /*+ ALL_ROWS */
*
FROM
T2
ORDER BY
OWNER,
OBJECT_NAME;
SQL_ID b3myw3j5ufp1h, child number 0
| 1 | SORT ORDER BY | | 1 | 13945 | 13945 | 00:00:00.25 | 182 | 37 | 1824K| 650K| 1/0/0| | 2 | TABLE ACCESS FULL| T2 | 1 | 13945 | 13945 | 00:00:00.21 | 182 | 37 | | | | -------------------------------------------------------------------------------------------------------------------------
That plan and statistics look to be very similar to the one that we received when both indexes were available to be used by the cost based optimizer. Note how the execution time is 0.25 seconds (or 0.15 seconds when there were two indexes) compared to the 1.12 seconds required when the T2_IND1 index was used. Is it possible that the cost based optimizer recognized that the time required to continue analyzing the various access methods would exceed the time required to return the rows, so the optimizer decided to run with the best plan that it found so far?
Let's look at a 10053 trace for the case when there were two indexes
available:
BASE STATISTICAL INFORMATION
Cost_io: 38.00 Cost_cpu: 6791624
Resp_io: 38.00 Resp_cpu: 6791624
Best:: AccessPath: TableScan
Cost: 38.54 Degree: 1 Resp: 38.54 Card: 13945.00 Bytes: 0
***************************************
SORT resource Sort statistics
Sort width: 238 Area size: 208896 Max Area size:
41943040
Degree: 1
Blocks to Sort: 187 Row size: 109 Total
Rows: 13945
Initial runs: 2 Merge passes: 1 IO Cost /
pass: 102
Total IO sort cost: 289 Total CPU sort cost: 25948637
Total Temp space used: 3384000
***********************
SORT resource Sort statistics
Sort width: 238 Area size: 208896 Max Area size:
41943040
Degree: 1
Blocks to Sort: 187 Row size: 109 Total
Rows: 13945
Initial runs: 2 Merge passes: 1 IO Cost /
pass: 102
Total IO sort cost: 289 Total CPU sort cost: 25948637
Total Temp space used: 3384000
Cost: 329.5793 Degree: 1 Card: 13945.0000 Bytes: 1255050 Resc: 329.5793 Resc_io: 327.0000 Resc_cpu: 32740260 Resp: 329.5793 Resp_io: 327.0000 Resc_cpu: 32740260
The total expected cost was 329 - 289 of which was caused by the sort.
Now, let's look at the case where only the first index exists
(FIRST_ROWS):
BASE STATISTICAL INFORMATION
Cost_io: 38.00 Cost_cpu: 6791624
Resp_io: 38.00 Resp_cpu: 6791624
Best:: AccessPath: TableScan
Cost: 38.54 Degree: 1 Resp: 38.54 Card: 13945.00 Bytes: 0
***************************************
SORT resource Sort statistics
Sort width: 238 Area size: 208896 Max Area size:
41943040
Degree: 1
Blocks to Sort: 187 Row size: 109 Total
Rows: 13945
Initial runs: 2 Merge passes: 1 IO Cost /
pass: 102
Total IO sort cost: 289 Total CPU sort cost: 25948637
Total Temp space used: 3384000
***********************
Cost_io: 38.00 Cost_cpu: 6791624
Resp_io: 38.00 Resp_cpu: 6791624
Access Path: index (FullScan)
Cost: 5930.97 Degree: 1 Resp: 5930.97 Card: 13945.00 Bytes: 90
SORT resource Sort statistics
Sort width: 238 Area size: 208896 Max Area size:
41943040
Degree: 1
Blocks to Sort: 187 Row size: 109 Total
Rows: 13945
Initial runs: 2 Merge passes: 1 IO Cost /
pass: 102
Total IO sort cost: 289 Total CPU sort cost: 25948637
Total Temp space used: 3384000
Cost: 5930.9733 Degree: 1 Card: 13945.0000 Bytes: 1255050 Resc: 5930.9733 Resc_io: 5927.0000 Resc_cpu: 50436325 Resp: 5930.9733 Resp_io: 5927.0000 Resc_cpu: 50436325
The cost in this case is 5930, which is a bit higher than the cost of 329 that was predicted for the table access with sort operation. It appeared that when there were two indexes on the table, Oracle did not even bother checking the index access paths, as it expected the time to calculate the best path would be more than the time to return the data.
Paraphrased from "Cost-Based Oracle Fundamentals" (very likely the best book to read and re-read when trying to determine what is happening): "The optimizer may decide after testing a few join orders that the cost of executing the query is so small that it should discontinue checking join orders - as soon as the time spent checking join orders exceeds the predicted run time, Oracle runs with the best plan obtained so far. This means that if Oracle happens to pick an unsuitable starting table because it calculated the cardinality wrong, the standard permutation cycle of joining tables can cause problems."
It does not look like a bug to me, but instead an optimization.
Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.
Received on Thu Mar 15 2007 - 17:15:17 CDT
 |
 |