5 Replies Latest reply: Dec 25, 2012 8:51 PM by DavidPaul RSS

    Ask for DL to learn performance tuning

    DavidPaul
      Hi All,

      I want to learn something related with sql performance tuning, especially the explain plan, I cannot understand it well, and I don't have enough resources in hand.

      If someone have nice place to learn, please share me the link.

      The more good link you share with, the better.

      Thanks,
      David
        • 1. Re: Ask for DL to learn performance tuning
          sb92075
          David Paul wrote:
          Hi All,

          I want to learn something related with sql performance tuning, especially the explain plan, I cannot understand it well, and I don't have enough resources in hand.

          If someone have nice place to learn, please share me the link.

          The more good link you share with, the better.

          Thanks,
          David
          when all else fails, Read The Fine Manual

          http://docs.oracle.com/cd/E11882_01/server.112/e10822/toc.htm
          • 2. Re: Ask for DL to learn performance tuning
            ranit B
            To start with :
            [1] 
            EXPLAIN PLAN FOR
            <your_entire_query>;
            
            [2]
            SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
            
            [3]
            You'll get something like this :
            Plan hash value: 1388734953
             
            -----------------------------------------------------------------
            | Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
            -----------------------------------------------------------------
            |   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
            |   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
            -----------------------------------------------------------------
            
            This is the Explain Plan, which indicates the steps how the query is executed by the Oracle Optimizer.
            Also, highlights factors like - Cost, Cardinality, Estimated Time taken, etc.
            This is a beautiful Oracle White-Paper on Explain Plan - <b>Oracle Optimizer : Explain the EXPLAIN PLAN</b>
            Read this thoroughly and you'll get to know all the internal workings and internal concepts.
            I've even kept a print-out of this with me. ;-)

            This is another mega link on Tuning SQL - <b>http://docs.oracle.com/cd/B19306_01/server.102/b14211/sql_1016.htm</b>

            HTH
            Ranit B.
            • 3. Re: Ask for DL to learn performance tuning
              DavidPaul
              Hi Ranit,

              Thanks for your help.

              I know this query, but I don't know how to figure out what's meaning of exact the parameters in the explain plan.

              I'd appreciate of you could give me introduction.

              Thanks,
              David
              • 4. Re: Ask for DL to learn performance tuning
                ranit B
                David Paul wrote:
                Hi Ranit,

                Thanks for your help.

                I know this query, but I don't know how to figure out what's meaning of exact the parameters in the explain plan.

                I'd appreciate of you could give me introduction.

                Thanks,
                David
                Hi David,
                Did you go through the link i sent??

                Excerpts from the White Paper link :
                >
                What is Cost?
                The Oracle Optimizer is a cost-based optimizer. The execution plan selected for a SQL statement is
                just one of the many alternative execution plans considered by the Optimizer. The Optimizer selects
                the execution plan with the lowest cost, where cost represents the estimated resource usage for that
                plan. The lower the cost the more efficient the plan is expected to be. The optimizer’s cost model
                accounts for the IO, CPU, and network resources that will be used by the query.
                >

                >
                In order to determine if you are looking at a good execution plan or not, you need to understand how
                the Optimizer determined the plan in the first place. You should also be able to look at the execution
                plan and assess if the Optimizer has made any mistake in its estimations or calculations, leading to a
                suboptimal plan. The components to assess are:
                • Cardinality– Estimate of the number of rows coming out of each of the operations.
                • Access method – The way in which the data is being accessed, via either a table scan or index
                access.
                • Join method – The method (e.g., hash, sort-merge, etc.) used to join tables with each other.
                • Join type – The type of join (e.g., outer, anti, semi, etc.).
                • Join order – The order in which the tables are joined to each other.
                • Partition pruning – Are only the necessary partitions being accessed to answer the query?
                • Parallel Execution – In case of parallel execution, is each operation in the plan being
                conducted in parallel? Is the right data redistribution method being used?
                >

                >
                Cardinality
                The cardinality is the estimated number of rows that will be returned by each operation. The Optimizer
                determines the cardinality for each operation based on a complex set of formulas that use both table
                and column level statistics as input (or the statistics derived by dynamic sampling). One of the simplest
                formulas is used when there is a single equality predicate in a single table query (with no histogram). In
                this case the Optimizer assumes a uniform distribution and calculates the cardinality for the query by
                dividing the total number of rows in the table by the number of distinct values in the column used in
                the where clause predicate.
                >


                Go through each section - Cardinality (i.e. no of rows), Access Method (i.e. Full Table Scan, Index Scan), various Join Methods, and all factors which determine the query optimization.

                I can't mention here all the details. It would be too much for this space. Hope you understand.
                Please go through the White Paper, you'll get answers to all your questions. Even if you don't understand something, we 're always here.


                *NOTE : Please make it a habit to close the posts by marking 'Answered'*


                Edited by: ranit B on Dec 25, 2012 11:16 AM
                -- text added
                • 5. Re: Ask for DL to learn performance tuning
                  DavidPaul
                  Thanks,
                  David