Re: performance issue

On Jan 29, 4:02 am, "Steve Robin" <ocma..._at_gmail.com> wrote:
> SQL> SELECT COUNT(1) FROM mcc_quote.SC_QUOTE WHERE LATESTVERSION =
> 1;
>
> COUNT(1)
> ----------
> 154539
>
> Elapsed: 00:00:00.01
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=2)
> 1 0 SORT (AGGREGATE)
> 2 1 INDEX (FAST FULL SCAN) OF 'SC_QUOTE_LATESTVERSION' (NON-
> UNIQUE) (Cost=4 Card=89656 Bytes=179312)
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 0 db block gets
> 353 consistent gets
> 331 physical reads
> 0 redo size
> 408 bytes sent via SQL*Net to client
> 499 bytes received via SQL*Net from client
> 2 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 1 rows processed
>
> SQL> SELECT COUNT(1) FROM mcc_quote.SC_QUOTE WHERE LATESTVERSION = 1
> ORDER BY MODIFICATIONDATE;
>
> COUNT(1)
> ----------
> 154539
>
> Elapsed: 00:00:33.01
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=413 Card=1 Bytes=9)
> 1 0 SORT (AGGREGATE)
> 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'SC_QUOTE' (Cost=413 Ca
> rd=89656 Bytes=806904)
>
> 3 2 INDEX (RANGE SCAN) OF 'SC_QUOTE_LATESTVERSION' (NON-UN
> IQUE) (Cost=13 Card=89656)
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 0 db block gets
> 43086 consistent gets
> 43068 physical reads
> 0 redo size
> 408 bytes sent via SQL*Net to client
> 499 bytes received via SQL*Net from client
> 2 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 1 rows processed
>
> Second query is taking huge time. Please tell me why, What can I do
> this time. Because last time when physical reads were too much I
> created index.

Fixing the SQL statement is the best bet. The ORDER BY does not make any sense when used without a GROUP BY. The first SQL statement can be satisfied by only accessing the index, while the second SQL statement must also access the table data to determine the MODIFICATIONDATE for the sort (the results of which are discarded). Note that the consistent gets (reads from the buffer cache) are roughly the same as the physical reads (disk reads) - this may be OK, or it may be very bad.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Mon Jan 29 2007 - 04:32:40 CST