query optimization
in [Oracle]
|
From: Garg on 26 Jun 2008 13:11 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. I canÂ’t use stored procedures. As I am not a DBA, I am a simple Java developer so I am not having any idea about this so I given a stupid answer of creating a View. Could you please tell me what steps should I take to optimize that query. Thanks in advance, Tarun
From: fitzjarrell on 26 Jun 2008 13:47 Comments embedded. On Jun 26, 12:11Â pm, Garg <sendtog...(a)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
From: joel garry on 26 Jun 2008 16:24 On Jun 26, 10:11Â am, Garg <sendtog...(a)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. I canÂ’t use stored procedures. > As I am not a DBA, I am a simple Java developer so I am not having any > idea about this so I given a stupid answer of creating a View. > Could you please tell me what steps should I take to optimize that > query. > > Thanks in advance, > Tarun David's answer is better for the real world, but just for the kool-aid (like if the question was asked for a job interview or someone wants to know if you read the docs) see http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/sql_1016.htm#i35699 It is a good idea to read the docs and become familiar with how to research Oracle questions. There's a wiki at Oracle, and a mini-faq for this group: http://www.dbaoracle.net/readme-cdos ) jg -- @home.com is bogus. Pay-option was good for me. But I was able to get out before it wasn't. http://www.signonsandiego.com/uniontrib/20080626/news_1n26country.html
From: joel garry on 26 Jun 2008 16:35 On Jun 26, 1:24Â pm, joel garry <joel-ga...(a)home.com> wrote: > for this group: Â http://www.dbaoracle.net/readme-cdos) Oops, http://www.dbaoracle.net/readme-cdos.htm jg -- @home.com is bogus. Oopsie!
|
Pages: 1 Prev: DBA_PROCEDURES / DBA_TRIGGERS Next: %rowcount on select into statements |