Re: query optimization

From: <fitzjarrell_at_cox.net>
Date: Thu, 26 Jun 2008 10:47:57 -0700 (PDT)
Message-ID: <ad7e61e5-b092-495b-8791-dd55fa70a645@y38g2000hsy.googlegroups.com>


Comments embedded.

On Jun 26, 12:11 pm, Garg <sendtog..._at_gmail.com> wrote:
> Hi,
> One is been asked to me on query optimization and the question was, if
> you have to optimize a sql query which is dealing with 10 tables then
> tell me how will you go about.

One starts by asking a proper question and providing sufficient detail to derive an answer. This offering, obviously, does not meet that criteria.

> I can’t use stored procedures.

How would that tune a poorly performing query? The stored procedure would likely use the exact same query about which the users complain.

> As I am not a DBA, I am a simple Java developer so I am not having any
> idea about this

Yet, as a developer, you should have SOME idea of where to begin. Query tuning is akin to debugging code, and you should be competent in that regard.

> so I given a stupid answer of creating a View.

Stupid, no; uninformed, yes. Creating a view uses the same poorly performant query which then does no real good. If my orange car runs poorly how will painting it purple improve that situation?

> Could you please tell me what steps should I take to optimize that
> query.

What query? I see no SELECT statement posted, all I see is a vague question about one supposed query involving 10 tables. That notwithstanding, reason should have given you at least a small clue as to how to proceed. Absent that a search of this newsgroups archives and a trip to google.com would have unearthed a plethora of documents and websites providing any number of suggestions. Furthermore, a visit to asktom.oracle.com, or www.jlcomp.demon.co.uk, would reveal any number of techniques for initiating a tuning strategy. Most attempts at query tuning involve one of four Oracle-supplied tools:

1 explain plan
2 event 10046 trace
3 using autotrace
4 event 10053 trace

The first three provide an execution plan for the submitted query, the third providing per-query statistics. Option 2 also provides some statistics, but these are for all queries run during the traced session. Option 1 provides a query plan, although it may differ from the plan generated when actually executing the query (this is why I prefer optons 2 and 3 in the list). Option 4 is best used after one of Options 1-3 have been utilized, as it produces a plain text description of the cost-based optimizer activities and decisions used to determine the final query plan.

There is much more to tuning a query than what I've stated here, yet this is a good starting point for those unfamiliar with the process.

>
> Thanks in advance,
> Tarun

I strongly suggest you either borrow or purchase a copy of "Cost-Based Oracle Fundamentals", Jonathan Lewis, ISBN i-59059-636-6 and read it, cover to cover. When you've finished, repeat the process.

My personal opinion is you cannot write efficient queries if you can't see that they are efficient, and knowing how to tune them is a necessary skill every developer should learn.

David Fitzjarrell Received on Thu Jun 26 2008 - 12:47:57 CDT

Original text of this message