Oracle – Explain Plan Basics & Brief History

Whenever a SQL statement is submitted to the database server (be it Oracle or SQL Server), the server generates an execution plan which it uses to get the data in a well-defined order. The engine that does this work is called the “optimizer”.

History of optimizers:

RBO – Rule based optimizer

Back in the day, optimizers were “Rule Based”. In other words, the plan that the optimizer generated largely depended on how the SQL was structured, the indexes available on the objects acted upon and the relationships between them. These would work if the distribution of data is even. Unfortunately, in the real world, the distribution is not even. e.g.,: A company that does business out of NYC may have 1000 customers from the city and a handful from outside the city. If there was an index on the “city” column, it would work great when selecting the handful of non-NYC customers but not so otherwise.

CBO – Cost based optimizer

The current generation of optimizers use a “Cost Based” approach. At the most basic level, they use statistics that they gather on the data by sampling (well ahead of query time) to guide the plan creation. CBO comes up with a “cost” for each statement. The number represents the amount of time the optimizer will take to bring back the results (not it terms of actual seconds but in terms of single-block reads). The higher the number, the longer it takes. The premise is that every operation has an associated cost based on the available statistics referred to before. In the interest of response time,  the optimizer picks the best cost plan in a reasonable amount of time without exhausting all the combinations.  8i used only the amount of I/O involved to calculate cost. 9i used I/O and CPU costing. 10g and above uses an offline optimizer to do keep track of critical statistical information related to cache, etc.

Key takeaway

The key takeaway from the above is that the performance depends on the availability of statistics. Conversely said, the lack of statistics or having outdated statistics results in a bad execution plan to be chosen.

There are four broad types of statistics

  • Table statistics
  • Index statistics
  • Column statistics
  • System statistics

Let us take a quick look at looking at the explain plan for a SQL statement

Connect to my_database using SQL*Plus:

C:\Users\my_user_id>sqlplus my_schema/my_schema_password@my_database

SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 13 13:12:46 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

Create an explain plan for our SQL using the “EXPLAIN PLAN FOR” statement:

SQL> EXPLAIN PLAN FOR
  2   SELECT COUNT(*)
  3   INTO v_Valid_CoCode_Count
  4   FROM ods.pods_current_xref
  5   WHERE eclient_code = v_Eclient_Code
  6   AND company_code != 0
  7   AND tare_coverage_status = 'ACTIVE';
WHERE eclient_code = v_Eclient_Code
                      *
ERROR at line 5:
ORA-00904: "V_ECLIENT_CODE": invalid identifier

It resulted in an error complaining about syntax issues. Basically, when creating explain plans, variables cannot exist. Just prefix them with a colon (:).

SQL> EXPLAIN PLAN FOR
  2   SELECT COUNT(*)
  3   INTO :v_Valid_CoCode_Count
  4   FROM ods.pods_current_xref
  5   WHERE eclient_code = :v_Eclient_Code
  6   AND company_code != 0
  7   AND tare_coverage_status = 'ACTIVE';

Explained.

Displaying the plan:

At this point, the explain plan is generated and stored in a table called the “Plan table” and not displayed by default. Let us set the approximate display width for a line so that it is long enough and use the DBMS_XPLAN.DISPLAY method to display the plan

SQL> SET LINESIZ 300
SQL>
SQL> SELECT *
  2  FROM   TABLE(DBMS_XPLAN.DISPLAY);

The output is as follows

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                |  Name              | Rows  | Bytes | Cost  | Pstart| Pstop |  TQ    |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                    |     1 |    17 |   846 |       |       |        |      |            |
|   1 |  SORT AGGREGATE          |                    |     1 |    17 |       |       |       |        |      |            |
|   2 |   SORT AGGREGATE         |                    |     1 |    17 |       |       |       | 92,00  | P->S | QC (RAND)  |
|   3 |    PARTITION RANGE SINGLE|                    |       |       |       |   KEY |   KEY | 92,00  | PCWP |            |
|*  4 |     TABLE ACCESS FULL    | ods.pods_current_xref  |   109K|  1815K|   846 |   KEY |   KEY | 92,00  | PCWP |            |
----------------------------------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("ods.pods_current_xref"."ECLIENT_CODE"=TO_NUMBER(:Z) AND "ods.pods_current_xref"."COMPANY_CODE"<>0 AND
              "ods.pods_current_xref"."TARE_COVERAGE_STATUS"='ACTIVE')

Note: cpu costing is off

18 rows selected.

SQL>

At this point, the explain plan is generated and stored in a table called the “Plan table” and not displayed by default. Let us set the approximate display width for a line so that it is long enough and use the DBMS_XPLAN.DISPLAY method to display the plan

SQL> SET LINESIZ 300
SQL>
SQL> SELECT *
  2  FROM   TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                |  Name              | Rows  | Bytes | Cost  | Pstart| Pstop |  TQ    |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                    |     1 |    17 |   846 |       |       |        |      |            |
|   1 |  SORT AGGREGATE          |                    |     1 |    17 |       |       |       |        |      |            |
|   2 |   SORT AGGREGATE         |                    |     1 |    17 |       |       |       | 92,00  | P->S | QC (RAND)  |
|   3 |    PARTITION RANGE SINGLE|                    |       |       |       |   KEY |   KEY | 92,00  | PCWP |            |
|*  4 |     TABLE ACCESS FULL    | PODS_CURRENT_XREF  |   109K|  1815K|   846 |   KEY |   KEY | 92,00  | PCWP |            |
----------------------------------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("PODS_CURRENT_XREF"."ECLIENT_CODE"=TO_NUMBER(:Z) AND "PODS_CURRENT_XREF"."COMPANY_CODE"<>0 AND
              "PODS_CURRENT_XREF"."TARE_COVERAGE_STATUS"='ACTIVE')

Note: cpu costing is off

18 rows selected.

SQL>
SQL>

Reading the plan:

The order in which the operations are performed is NOT the order in which the plan displays it.  If you think of the above as a tree as shown below with numbers, the leaf level nodes are the first operations to happen. The root level node is the last operation performed. At the branches with forks, the top branch will be done first and the second branch next. No node with have more than two branches. Nodes with two branches are always “Joins”.

The numbering here illustrates the order of execution

Plan
SELECT STATEMENT CHOOSE Cost: 846 Bytes: 17 Cardinality: 1
4 SORT AGGREGATE Bytes: 17 Cardinality: 1
3 SORT AGGREGATE PARALLEL_TO_SERIAL :Q420613000 Bytes: 17 Cardinality: 1
2 PARTITION RANGE SINGLE PARALLEL_COMBINED_WITH_PARENT :Q420613000 Partition #: 3 Partitions determined by Key Values
1 TABLE ACCESS FULL PARALLEL_COMBINED_WITH_PARENT ODS.PODS_CURRENT_XREF :Q420613000 Cost: 846 Bytes: 1,859,256 Cardinality: 109,368 Partition #: 3 Partitions determined by Key Values

Here is another view of the same plan that shows the exact order of operations graphically

explain_plan

Easy, on the go explain plans with AUTOTRACE

Earlier, we saw how to get explain plans when we lack the GUI tools to help us out.

Unless you use the command line on a regular basis, if I asked someone to recall how it was done, most would have a hard time without looking up again.

For the lazy people among us, there is another easy and more powerful way in SQL*Plus:

AUTOTRACE

Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]

AUTOTRACE produces more than just the explain plan.

The simplest usage is

SET AUTOTRACE ON

When you run SQL following the above

  • The SQL statement is run
  • Explain plan is displayed
  • Statistics (related to execution) are displayed

Here is an example:

SQL> SET AUTOT ON
SQL> select count(1) from client_master;

  COUNT(1)
----------
       337

Execution Plan
----------------------------------------------------------

--------------------------------------------------------------
| Id  | Operation        | Name              | Rows  | Cost  |
--------------------------------------------------------------
|   0 | SELECT STATEMENT |                   |     1 |     1 |
|   1 |  SORT AGGREGATE  |                   |     1 |       |
|   2 |   INDEX FULL SCAN|  CLIENT_MASTER_I1 |   337 |     1 |
--------------------------------------------------------------

Note
-----
   - 'PLAN_TABLE' is old version

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        347  bytes sent via SQL*Net to client
        364  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

That makes it much simpler. All you have to remember is “SET AUTOT ON” and you are set. You did not even have to spell out AUTOTRACE.

Most GUI tools do not provide the vital “statistics” information which is key in helping you determine the efficiency of one version of SQL from another. When you challenge another developer to say that your query is better than the other persons (to get the same data), if you offered proof in the form of statistics, it will be a more convincing argument than if you did not.

Sometimes, you may not want to run the SQL itself but just want to get the explain plan (e.g., if you wanted it for a DELETE). Here is how it is done
SET AUTOT TRACEONLY

The output would be the exact same as above except that the COUNT will not be displayed since the SQL was not run at all.

If I wanted to run the SQL and get the statistics but don’t care about the explain plan:
SET AUTOT ON STAT

Here is a matrix:

Option Runs the SQL? Explain Plan? Statistics?
SET AUTOT ON Yes Yes Yes
SET AUTOT TRACEONLY No Yes Yes
SET AUTOT ON STAT Yes No Yes
SET AUTOT ON EXP Yes Yes No

If you forget everything in this post, all you have remember to do in SQL*Plus is

SET AUTOTRACE

..and you will receive the usage information

Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]

SET AUTOTRACE OFF 😉

In a later post, we will see what to look for in explain plans.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s