Re: More explain plan cost can improve performance ?

From: aman.oracle.dba <aman.oracle.dba_at_gmail.com>
Date: Mon, 19 Apr 2010 04:15:07 -0700 (PDT)
Message-ID: <93168123-aef9-49c8-845d-7d550ae8a7f8_at_j17g2000yqa.googlegroups.com>

On Apr 19, 3:12 pm, "aman.oracle.dba" <aman.oracle...._at_gmail.com> wrote:
> explain plan for SELECT MSG_ID, LIC_GUID, MSG_RO
>   FROM (SELECT MSG_ID, LIC_GUID, MSG_RO,
>    row_number() over (order by MSG_ID ASC) msg_rn
>   FROM MSG_TRAANS   WHERE ACK ='RE2aASqND'   and RES < 10
>    ORDER BY MSG_ID ASC) where msg_rn < 301;
>
> ---------------------------------------------------------------------------------------------------------------------------------
> | Id  | Operation                            |
> Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
> Pstart| Pstop |
> ---------------------------------------------------------------------------------------------------------------------------------
> |   0 | SELECT STATEMENT
> |                          |   191K|    96M|  6822   (1)| 00:01:22
> |       |       |
> |*  1 |  VIEW
> |                          |   191K|    96M|  6822   (1)| 00:01:22
> |       |       |
> |*  2 |   WINDOW SORT PUSHED RANK
> |                          |   191K|    23M|  6822   (1)| 00:01:22
> |       |       |
> |*  3 |    TABLE ACCESS BY GLOBAL INDEX ROWID|
> MSG_TRAANS               |   191K|    23M|  6822   (1)| 00:01:22 |
> ROWID | ROWID |
> |*  4 |     INDEX RANGE SCAN                 |
> MSG_PK                   |   219K|       |   199   (2)| 00:00:03
> |       |       |
> ---------------------------------------------------------------------------------------------------------------------------------
>
> explain plan for SELECT MSG_ID, LIC_GUID, MSG_RO
>   FROM (SELECT MSG_ID, LIC_GUID, MSG_RO  FROM MSG_TRAANS WHERE ACK
> ='RE2aASqND' and RES < 10   ORDER BY MSG_ID ASC) where rownum < 301;
>
> --------------------------------------------------------------------------------------------------------------------------------
> | Id  | Operation                            | Name
> | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
> --------------------------------------------------------------------------------------------------------------------------------
> |   0 | SELECT STATEMENT                     |
> |   300 |   151K|   255   (0)| 00:00:04 |       |       |
> |*  1 |  COUNT STOPKEY                       |
> |       |       |            |          |       |       |
> |   2 |   VIEW                               |
> |   300 |   151K|   255   (0)| 00:00:04 |       |       |
> |*  3 |    TABLE ACCESS BY GLOBAL INDEX ROWID| MSG_TRAANS              |
> 191K|  9530K|   255   (0)| 00:00:04 | ROWID | ROWID |
> |   4 |     INDEX FULL SCAN                  | MSG_PK
> |  2549 |       |     3   (0)| 00:00:01 |       |       |
> --------------------------------------------------------------------------------------------------------------------------------
>
> Same tables, same data and same output; cost of first query is shown
> as 6822 but only 255 in second; But 2nd query is taking 19 minutes and
> 1st query is taking only 3 seconds. Why oracle is behaving this this
> way. Please suggest.
>
> Thanks,
> Steve

Some more information

1st query:

     103789  consistent gets
       10356  physical reads

2nd Query

    1682000 consistent gets
      71830 physical reads Received on Mon Apr 19 2010 - 06:15:07 CDT