Using EXPLAIN PLAN - MySQL

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.