Re: More explain plan cost can improve performance ?

From: joel garry <joel-garry_at_home.com>
Date: Mon, 19 Apr 2010 10:43:33 -0700 (PDT)
Message-ID: <6134472b-e495-4b96-8fd6-80156e18e4c1_at_g23g2000yqn.googlegroups.com>

On Apr 19, 4:15 am, "aman.oracle.dba" <aman.oracle...._at_gmail.com> wrote:
> 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

You're getting a big win with analytics by not having to scan the whole index. Try putting in a hint for MSG_PK to do a range scan, if it makes a comparable increase in speed, then we can proceed further with figuring out how to tell the optimizer to do what it should, considering the rows 2549 versus 219K. The costs aren't comparable between different queries. A CBO trace would show more details about the decisions the optimizer is making.

Exact version and detailed information about how you collect statistics is necessary, you may be running into basic histogram issues or something.

jg

--
_at_home.com is bogus.
http://threatpost.com/en_us/blogs/palm-pwned-researchers-hack-webos-text-messages-041910

Received on Mon Apr 19 2010 - 12:43:33 CDT