performance issue

From: Steve Robin <ocmaman_at_gmail.com>
Date: 29 Jan 2007 01:02:24 -0800
Message-ID: <1170061344.275995.234460@p10g2000cwp.googlegroups.com>

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. Received on Mon Jan 29 2007 - 03:02:24 CST