Re: Oracle Optmizer does not use the right index (Oracle 9.2.0.6)

Thanks for the reply.
The fact remains that in the situation I have described, the Oracle Optimizer loses track of the first of the indexes (depending on the order of creation). My earlier example has a table of about 14,000 rows, where your argument on the possible lower cost of a table scan (as opposed to an index scan) might hold water. But we have a production machine where the same problem occurs on a table of about 300,000 rows. There is no way the Optimizer can argue that a table scan and a subsequent sort would be better.

In fact, I also tried to force the use of the index on the table, by using the Index hint. With both indexes in place, for a query requiring the use of the "forgotten index", the optimizer goes for the wrong index, retrieves the rows through the index and then does a sort to get the rows in the required order! See below:

SQL> select /*+ index(tab1) */ * from tab1 order by owner, object_name desc;

Execution Plan

   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=2442 Card=4137 B
          ytes=310275)

   1    0   SORT (ORDER BY) (Cost=2442 Card=4137 Bytes=310275)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'TAB1' (TABLE) (Cost=23
          16 Card=4137 Bytes=310275)

   3    2       INDEX (FULL SCAN) OF 'TAB1IND1' (INDEX) (Cost=43 Card=
          4137)

On Mar 15, 6:15 pm, "Charles Hooper" <hooperc2..._at_yahoo.com> wrote:
> 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',CAS­CADE=>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
> -------------------------------------
> SELECT /*+ FIRST_ROWS */ * FROM T2 ORDER BY OWNER, OBJECT_NAME
>
> Plan hash value: 1199450076
>
> ---------------------------------------------------------------------------­-------------------------------
> | 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',CAS­CADE=>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
> -------------------------------------
> SELECT /*+ FIRST_ROWS */ * FROM T2 ORDER BY OWNER, OBJECT_NAME
>
> Plan hash value: 2552596561
>
> ---------------------------------------------------------------------------­----------------------------------------------
> | Id | Operation | Name | Starts | E-Rows | A-Rows | A-
> Time | Buffers | Reads | OMem | 1Mem | O/1/M |
> ---------------------------------------------------------------------------­----------------------------------------------
> | 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
> -------------------------------------
> SELECT /*+ ALL_ROWS */ * FROM T2 ORDER BY OWNER, OBJECT_NAME
>
> Plan hash value: 2552596561
>
> ---------------------------------------------------------------------------­----------------------------------------------
> | Id | Operation | Name | Starts | E-Rows | A-Rows | A-
> Time | Buffers | Reads | OMem | 1Mem | O/1/M |
> ---------------------------------------------------------------------------­----------------------------------------------
> | 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
> ***********************
> Table Stats::
> Table: T2 Alias: T2
> #Rows: 13945 #Blks: 190 AvgRowLen: 105.00
> Index Stats::
> Index: T2_IND1 Col#: 1 2
> LVLS: 1 #LB: 67 #DK: 12870 LB/K: 1.00 DB/K: 1.00 CLUF:
> 5859.00
> Index: T2_IND2 Col#: 1 14
> LVLS: 1 #LB: 69 #DK: 12870 LB/K: 1.00 DB/K: 1.00 CLUF:
> 5870.00
> ***************************************
> SINGLE TABLE ACCESS PATH
> Table: T2 Alias: T2
> Card: Original: 13945 Rounded: 13945 Computed: 13945.00 Non
> Adjusted: 13945.00
> Access Path: TableScan
> Cost: 38.54 Resp: 38.54 Degree: 0
> 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
> ***************************************
> OPTIMIZER STATISTICS AND COMPUTATIONS
> ***************************************
> GENERAL PLANS
> ***************************************
> Considering cardinality-based initial join order.
> Permutations for Starting Table :0
> ***********************
> Join order[1]: T2[T2]#0
> ORDER BY sort
> 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
> ...
>
> read more »- Hide quoted text -
>
> - Show quoted text -
Received on Fri Mar 16 2007 - 13:28:59 CDT