More explain plan cost can improve performance ?

From: aman.oracle.dba <aman.oracle.dba_at_gmail.com>
Date: Mon, 19 Apr 2010 03:12:46 -0700 (PDT)
Message-ID: <d97d41ac-ace1-4b4d-89c0-e483cf582af2_at_q15g2000yqj.googlegroups.com>

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 Received on Mon Apr 19 2010 - 05:12:46 CDT