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 Thu Apr 10 2003 - 22:37:38 CDT
