Home arrow MySQL arrow Page 2 - SQL Performance and Tuning Considerations, concluded

Using EXPLAIN PLAN - MySQL

This article, the second of two parts, will show you how to make the SQL statements you write run faster and more efficiently. It is excerpted from chapter 11 of the book SQL DeMYSTiFied, written by Andy Oppel (McGraw-Hill/Osborne, 2005; ISBN: 0072262249).

TABLE OF CONTENTS:
  1. SQL Performance and Tuning Considerations, concluded
  2. Using EXPLAIN PLAN
  3. Microsoft SQL Server Considerations
  4. Displaying Execution Plans Using SQL Query Analyzer
  5. Quiz
By: McGraw-Hill/Osborne
Rating: starstarstarstarstar / 10
March 23, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

In Oracle, the SQL EXPLAIN PLAN statement analyzes an SQL statement and posts analysis results to a special plan table. The plan table must be created exactly as specified by Oracle, so it is best to use the script they provide for this purpose, which can be found under the Oracle Home directory as  /RDBMS/ADMIN/ catplan.sql. After running the EXPLAIN PLAN statement, you must then retrieve the results from the plan table using a SELECT statement. Fortunately, Oracle’s Enterprise Manager has a GUI version available that makes query tuning a lot easier.

Here is an example of an EXPLAIN PLAN statement:

EXPLAIN PLAN
 
SET STATEMENT_ID = 'STMT_1'
 
FOR
 
SELECT MOVIE_ID, 
        MOVIE_GENRE.MOVIE_GENRE_DESCRIPTION AS GENRE, 
        
MOVIE.MPAA_RATING_CODE AS RATING,
        MPAA_RATING.MPAA_RATING_DESCRIPTION AS RATING_DESC
  FROM MOVIE
      
JOIN MOVIE_GENRE ON MOVIE.MOVIE_GENRE_CODE =
            MOVIE_GENRE.MOVIE_GENRE_CODE

       JOIN MPAA_RATING ON MOVIE.MPAA_RATING_CODE =

            MPAA_RATING.MPAA_RATING_CODE
  WHERE MOVIE_ID < 6
  ORDER BY MOVIE_ID;
Explained.

NOTE:

  • STATEMENT_ID is any character string that the statement author wishes to use to identify the explain results. This feature allows multiple explains to be run with the STATEMENT_ID as the identifier that keeps the information about each execution plan separate in the plan table.
  • The statement to be explained follows the FOR keyword and can be any valid SQL statement.
  • When the EXPLAIN PLAN statement is run, the SQL statement is not actually executed. Instead of a result set containing rows of data, only the message “Explained.” is returned to the user. This indicates that the explain plan information has been successfully written to the plan table.

Following is the statement commonly used to retrieve and display the execution plan. It is a complex SQL statement, but the only thing that has to be changed when it is run is the STATEMENT_ID. The CONNECT BY clause is an Oracle proprietary SQL extension that joins a recursive relationship in a table through all iterations (from child to parent to grandparent, and so forth). This SQL was included here to illustrate one method of viewing explain plan results. As mentioned before, there is also a GUI tool in Oracle’s Enterprise Manager.

SELECT rtrim(substr(LPAD(' ',2*(LEVEL-1))||operation,1,30))||' '
                 
||rtrim(options)||' '||rtrim(object_name)|| ' '
                
||'(cost= '||cost||', cardinality='||cardinality||')'
                
"Query Plan"
 
FROM plan_table
 START WITH id = 0
       
AND upper(statement_id) = upper('STMT_1')
CONNECT BY PRIOR id = parent_id
         AND upper(statement_id) = upper('STMT_1');
Query Plan
-------------------------------------------
SELECT STATEMENT  (cost= 10, cardinality=5)
  SORT ORDER BY (cost= 10, cardinality=5)

    HASH JOIN (cost= 9, cardinality=5)
     
MERGE JOIN (cost= 5, cardinality=5)
        TABLE ACCESS BY INDEX ROWID MPAA_RATING
                      
(cost= 2, cardinality=6)
         
INDEX FULL SCAN SYS_C005440
                       (cost= 1, cardinality=6)
       
SORT JOIN (cost= 3, cardinality=5)
          TABLE ACCESS BY INDEX ROWID MOVIE
                       (cost= 2, cardinality=5)
           
INDEX RANGE SCAN SYS_C005449
                       (cost= 1, cardinality=5)
     
TABLE ACCESS FULL MOVIE_GENRE
                      
(cost= 3, cardinality=16)
10 rows selected.

Here are some key points regarding the query plan that appears in the result set:

  • The indentation shows the order of execution, with the innermost steps being performed first.
  • The cost values show a relative cost. The numbers have no meaning beyond their relative differences. For example, a cost of 10 represents a step that uses twice the resources of a step that has a cost of 5.
  • The cardinality values show the estimated number of rows that are processed by the step.
  • “TABLE ACCESS FULL” indicates a full table scan where all rows in the table are read sequentially.
  • “INDEX RANGE SCAN” indicates the scan of a portion of the rows in an index.
  • “TABLE ACCESS BY INDEX” indicates access to a table using the index shown.
  • The sorts and joins should be self evident by the step names.



 
 
>>> More MySQL Articles          >>> More By McGraw-Hill/Osborne
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: