Tunning question

From: Adon Keber <adon_at_cool.iskon.local>
Date: 15 Sep 2003 16:00:03 GMT
Message-ID: <slrnbmbog3.1pc.adon@cool.iskon.local>

I don't understand something. I've been testing different execution plans for the same query (enforced by hints).

In all tests query with faster execution time shows so much larger values of consistent gets and physical reads in stats.

Server condition are more or less the same for all query runs. Only real difference in stats (other than consisteng gets and pysical reads) is number of memory sorts but it can't take 2 minutes to sort 2611 rows in memory.

Faster query uses index on column in order by, and that index is much better by my opinion because his clustering factor is much lower than index which is used in slower query.

But the optimizer (FIRST_ROWS, ALL_ROWS all the same) stubornly uses explain plan for slower query (cost factor of explain plan is almost 100 times lower for slower query), and these stats proves that. All statistics are gathered with COMPUTE.

And 'suboptimal' query still executes 2 minutes faster.

Any thougths ?

Take 1.

Operation                               Object Name     Rows    Bytes   Cost
SELECT STATEMENT Optimizer Mode=ALL_ROWS                22              446

2611 rows selected.

Elapsed: 00:03:183.84

Statistics

          0  recursive calls
          0  db block gets
      46984  consistent gets
      29428  physical reads
          0  redo size
     201216  bytes sent via SQL*Net to client
      11974  bytes received via SQL*Net from client
        176  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       2611  rows processed

Take 2.

Operation                               Object Name     Rows    Bytes   Cost
SELECT STATEMENT Optimizer Mode=ALL_ROWS                22              31845

2611 rows selected.

Elapsed: 00:01:116.47

Statistics

          0  recursive calls
          0  db block gets
     138324  consistent gets
      58538  physical reads
          0  redo size
     201214  bytes sent via SQL*Net to client
      11974  bytes received via SQL*Net from client
        176  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       2611  rows processed

Received on Mon Sep 15 2003 - 11:00:03 CDT