Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Query tuning

Re: Query tuning

From: <rajesh_at_solutionsoftware.com>
Date: 10 Apr 2003 20:37:38 -0700
Message-ID: <aa8abed4.0304101937.289ba7db@posting.google.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US