Re: Query help please

From: Randolf Geist <mahrah_at_web.de>
Date: Wed, 18 Mar 2009 02:20:25 -0700 (PDT)
Message-ID: <61863560-daa8-46d9-a735-07ceac11fb57_at_j8g2000yql.googlegroups.com>

On Mar 17, 9:37 pm, "bob123" <bob..._at_gmail.com> wrote:
> How can I optimize this query (30 min) (9.2.0.6)

Your query has two potential issues:

1. The cardinality estimates are way off. You're getting back 71k rows but the optimizer estimates a single row at most. The issue starts with the access to the table PFMQ_MESSAGESTATUS. The predicate "ms.status = 64" is estimated to return only a single row from that table. Is that estimate correct? If not, you need to find out, why it is incorrect? In what state are your table/index stats? Have gathered statistics on both, if yes, how?
2. The SELECT MAX()... nested subquery is potentially executed for each row of the result set. Since the Oracle runtime engine has a powerful optimization for such filter operations it might not be the problem, but you could try to use analytic functions (e.g. SELECT FROM ...( , RANK() OVER (PARTITION BY MS.ID ORDER BY MS.STATUSREVISIONNUMBER DESC) as rnk) WHERE RNK = 1) or join the SELECT MAX() to the query instead of using a subquery.

You should trace your statement to find out where the bottleneck is, either 1. or 2. or both.

You can follow my blog post how to do that, it shows you step-by-step instructions: http://oracle-randolf.blogspot.com/2009/02/basic-sql-statement-performance.html

Regards,
Randolf

Oracle related stuff blog:
http://oracle-randolf.blogspot.com/

SQLTools++ for Oracle (Open source Oracle GUI for Windows): http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/ Received on Wed Mar 18 2009 - 04:20:25 CDT