Re: More explain plan cost can improve performance ?

From: Carlos <miotromailcarlos_at_netscape.net>
Date: Tue, 20 Apr 2010 00:31:51 -0700 (PDT)
Message-ID: <4acb6845-3277-427a-86eb-9f317de04cd7_at_v8g2000vbh.googlegroups.com>

On Apr 19, 7:43 pm, joel garry <joel-ga..._at_home.com> wrote:
> 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-t...

" The costs aren't comparable between different queries."

Exactly. That's it. Spread the word.

Cheers.

Carlos. Received on Tue Apr 20 2010 - 02:31:51 CDT