I am new in Query Fine Tuning.... I know query tuning is such vast thing in DB side. We need to fine tune DB level , Business Logic , Physical Level , SQL tuning....
Can any one tel me , how to fine tune & optimize the query ? Suppose lets say if the query is taking such a long time , what are all the approach we need to take care of in our mind???
Let's say I have Query , In that i am using 7 to 8 tables , the Expected time for this query is 7mins according to the Business. But there is a case , this query is taking such a long time to give the op? I need to fine tune this???
How can i fine tune the qry ( SQL Tuning )
what are all the initial apporach i need to follow up?
how can i find out the Root Causes???
Is our Explain plan is Useful for whole tuning part ????
1.If the query has joins between 7 or 8 tables then split the query into smaller parts i.e create 2 or 3 volatile tables having half of the joins in one volatile table and the remaining in the other volatile table.And at last you can use these volatile tables to get the final output. 2.Check whether collect stats have been done on all tables used in this query.If not do collect stats. 3.See that the join condition is done on the indexed columns from both the tables.