Re: Query tuning

rajesh_at_solutionsoftware.com wrote in message news:<aa8abed4.0304101937.289ba7db_at_posting.google.com>...

OK, now we are talking.

1. Check if you have indexes on join columns of tables CUSTOMER, CONTACTNO and ADDRESS. Big tables are scanned in full, doesn't seem to be right.
2. Is it possible to find out if tables and indexes were analyzed lately? Doesn't look like that.
3. If it's impossible to analyze tables and indexes properly, and indexes on above tables exist, try to use hints to force index use.
4. Sometimes quick and dirty fixes work. Try hints RULE and FIRST_ROWS (separately) and see what happens. Wouldn't recommend such approach for production queries though. If you get, by chance, good plan with one of quick fix hints, try to force the same plan using other hints, like USE_HASH, USE_INDEX etc.

> Here is the explain plan. Actually, I didnot know what statistics we
> use to analyze a query. So I couldnot furnish any further information.
> I am using ORACLE. 8.1.7.2.1. Please instruct if i need to furnish
> anything more. I donot know anything about tkprof either.
> SELECT STATEMENT
> SORT UNIQUE
> CONCATENATION
> MERGE JOIN OUTER
> MERGE JOIN OUTER
> MERGE JOIN
> SORT JOIN
> HASH JOIN
> INDEX FAST FULL SCAN
> IND_POLICYCUSTOMER1
> HASH JOIN
> TABLE ACCESS FULL
> MTOPTION
> HASH JOIN
> TABLE ACCESS FULL
> MTOPTION
> NESTED LOOPS
> HASH JOIN
> TABLE ACCESS FULL
> AGENT
> INDEX FAST FULL SCAN
> IND_AGENTPOLICY1
> TABLE ACCESS BY INDEX ROWID
> POLICY
> INDEX UNIQUE SCAN
> IND_POLICYID
> SORT JOIN
> TABLE ACCESS FULL
> CUSTOMER
> SORT JOIN
> TABLE ACCESS FULL
> CONTACTNO
> SORT JOIN
> TABLE ACCESS FULL
> ADDRESS
> MERGE JOIN OUTER
> MERGE JOIN OUTER
> MERGE JOIN
> SORT JOIN
> HASH JOIN
> INDEX FAST FULL SCAN
> IND_POLICYCUSTOMER1
> HASH JOIN
> TABLE ACCESS FULL
> MTOPTION
> HASH JOIN
> TABLE ACCESS FULL
> MTOPTION
> HASH JOIN
> TABLE ACCESS FULL
> AGENT
> NESTED LOOPS
> INDEX FAST FULL SCAN
> IND_AGENTPOLICY1
> TABLE ACCESS BY INDEX ROWID
> POLICY
> INDEX UNIQUE SCAN
> IND_POLICYID
> SORT JOIN
> TABLE ACCESS FULL
> CUSTOMER
> SORT JOIN
> TABLE ACCESS FULL
> CONTACTNO
> SORT JOIN
> TABLE ACCESS FULL
> ADDRESS
>
> "Mikito Harakiri" <mikharakiri_at_ywho.com> wrote in message news:<sjDka.9$Hg1.132_at_news.oracle.com>...
> > Tuning strategy might be different depending what oracle version you are in.
> > If you are on 9.2, then the easiest way to tune is to capture execution
> > statistics from V$SQL_PLAN_STATISTICS_ALL. Pay attention to the values in
> > the following columns:
> >
> > LAST_OUTPUT_ROWS
> > LAST_STARTS
> > LAST_CR_BUFFER_GETS
> >
> > Sometimes it is quite easy to spot performance hot spots. For example you
> > may see huge number of LAST_STARTS originating from some join node in the
> > rowsource tree, then it becomes obvious that the join method must be changed
> > from NL to HJ or SMJ. As a first step why don't you capture statistics and
> > let the group know it in order to suggest some directions for query tuning?
> >
> > <rajesh_at_solutionsoftware.com> wrote in message
> > news:aa8abed4.0304072132.65cddb37_at_posting.google.com...
> > > Here is a query that I am executing in my application and it takes
> > > around 10 minutes to execute. Absolutely no idea how to proceed with
> > > fine tuning.
Received on Fri Apr 11 2003 - 11:15:08 CDT