Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Query tuning
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