Performance Tuning: Explain Plan

Source : Link
EXPLAIN PLAN Usage

When an SQL statement is passed to the server the Cost Based Optimizer (CBO) uses database statistics to create an execution plan which it uses to navigate through the data.
Once you've highlighted a problem query the first thing you should do is EXPLAIN the statement to check the execution plan that the CBO has created.
This will often reveal that the query is not using the relevant indexes, or indexes to support the query are missing.
Interpretation of the execution plan is beyond the scope of this article.

• Plan Table
• AUTOTRACE - The Easy Option?
• EXPLAIN PLAN
• Statement ID

Plan Table

The explain plan process stores data in the PLAN_TABLE.
This table can be located in the current schema or a shared schema and is created using in SQL*Plus as follows:
SQL> CONN sys/password AS SYSDBA
Connected
SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql
SQL> GRANT ALL ON sys.plan_table TO public;
SQL> CREATE PUBLIC SYNONYM plan_table FOR sys.plan_table;

AUTOTRACE - The Easy Option?

Switching on the AUTOTRACE parameter in SQL*Plus causes an explain to be performed on every query.

SQL> SET AUTOTRACE ON
SQL> SELECT *
  2  FROM   emp e, dept d
  3  WHERE  e.deptno = d.deptno
  4  AND    e.ename  = 'SMITH';

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO     DEPTNO DNAME          LOC
---------- ---------- -------------- -------------
      7369 SMITH      CLERK           7902 17-DEC-80        800
        20         20 RESEARCH       DALLAS



Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   NESTED LOOPS
   2    1     TABLE ACCESS (FULL) OF 'EMP'
   3    1     TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
   4    3       INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)




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

SQL>

This is a relatively easy way to get the execution plan but there is an issue.

In order to get the execution plan the statement must be run to completion.

If the query is particularly inefficient and/or returns many rows, this may take a considerable time.

At first glance, using the TRACEONLY option of AUTOTRACE seems to remove this issue, but this option merely suppresses the output of the query data, it doesn't prevent the statement being run.

As such, long running queries will still take a long time to complete, but they will not present their data.

The following example show this in practice.

CREATE OR REPLACE FUNCTION pause_for_secs(p_seconds IN NUMBER) RETURN NUMBER A
BEGIN
DBMS_LOCK.sleep(p_seconds);
RETURN p_seconds;
END;
/

Function created.

SQL> SET TIMING ON
SQL> SET AUTOTRACE ON
SQL> SELECT pause_for_secs(10) FROM DUAL;

PAUSE_FOR_SECS(10)
------------------
10

1 row selected.

Elapsed: 00:00:10.28

Execution Plan
----------------------------------------------------------
Plan hash value: 1550022268

-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------

Statistics
----------------------------------------------------------
189 recursive calls
0 db block gets
102 consistent gets
0 physical reads
0 redo size
331 bytes sent via SQL*Net to client
332 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
13 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> SET AUTOTRACE TRACEONLY
SQL> SELECT pause_for_secs(10) FROM DUAL;

1 row selected.

Elapsed: 00:00:10.26

Execution Plan
----------------------------------------------------------
Plan hash value: 1550022268

-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------

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

SQL>
The query takes the same time to return (about 10 seconds) whether the TRACEONLY option is used or not.

If the TRACEONLY option prevented the query running, you would expect it to return instantly, like an EXPLAIN PLAN.

The solution to this is to use the TRACEONLY EXPLAIN option, which only performs the EXPLAIN PLAN, rather than running the statement.

=======================================
EXPLAIN PLAN

The EXPLAIN PLAN method doesn't require the query to be run, greatly reducing the time it takes to get an execution plan for long-running queries compared to AUTOTRACE.

First the query must be explained:

SQL> EXPLAIN PLAN FOR
2 SELECT *
3 FROM emp e, dept d
4 WHERE e.deptno = d.deptno
5 AND e.ename = 'SMITH';

Explained.

SQL>
Then the execution plan displayed:

SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql

Plan Table
--------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes| Cost | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT | | | | | | |
| NESTED LOOPS | | | | | | |
| TABLE ACCESS FULL |EMP | | | | | |
| TABLE ACCESS BY INDEX RO|DEPT | | | | | |
| INDEX UNIQUE SCAN |PK_DEPT | | | | | |
--------------------------------------------------------------------------------

8 rows selected.

SQL>
For parallel queries use the utlxplp.sql script instead of utlxpls.sql.

Statement ID

If multiple people are accessing the same plan table, or you would like to keep a history of the execution plans you should use the STATEMENT_ID.
This associates a user specified ID with each plan which can be used when retrieving the data.

SQL> EXPLAIN PLAN SET STATEMENT_ID = 'TIM' FOR
2 SELECT *
3 FROM emp e, dept d
4 WHERE e.deptno = d.deptno
5 AND e.ename = 'SMITH';

Explained.

SQL> @explain.sql TIM

PLAN OBJECT_NAME OBJECT_TYPE BYTES COST PARTITION_START PARTITION_STOP
-------------------------------------- --------------- --------------- ----- ----- --------------- ---------------
Select Statement 57 4
1.1 Nested Loops 57 4
2.1 Table Access (Full) EMP TABLE 37 3
2.2 Table Access (By Index Rowid) DEPT TABLE 20 1
3.1 Index (Unique Scan) PK_DEPT INDEX (UNIQUE) 0

5 rows selected.

SQL>
By default the Oracle scripts do not accept a statement_id parameter. You can easily modify the scripts or you can use the script listed under DBA Scripts on this site.
====================================================
Source :Link

Understanding EXPLAIN PLAN

The EXPLAIN PLAN statement displays execution plans chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements.

A statement's execution plan is the sequence of operations Oracle performs to run the statement.
The row source tree is the core of the execution plan.

It shows the following information:

· An ordering of the tables referenced by the statement

· An access method for each table mentioned in the statement

· A join method for tables affected by join operations in the statement

· Data operations like filter, sort, or aggregation

The EXPLAIN PLAN results let you determine whether the optimizer selects a particular execution plan, such as, nested loops join.

It also helps you to understand the optimizer decisions, such as why the optimizer chose a nested loops join instead of a hash join, and lets you understand the performance of a query.

How Execution Plans Can Change

With the query optimizer, execution plans can and do change as the underlying optimizer inputs change.

EXPLAIN PLAN output shows how Oracle runs the SQL statement when the statement was explained.

This can differ from the plan during actual execution for a SQL statement, because of differences in the execution environment and explain plan environment.

Execution plans can differ due to the following:

· Different Schemas

· Different Costs

Different Schemas

· The execution and explain plan happen on different databases.

· The user explaining the statement is different from the user running the statement. Two users might be pointing to different objects in the same database, resulting in different execution plans.

· Schema changes (usually changes in indexes) between the two operations.

Different Costs

Even if the schemas are the same, the optimizer can choose different execution plans if the costs are different. Some factors that affect the costs include the following:

· Data volume and statistics

· Bind variable types and values

· Initialization parameters - set globally or at session level

Minimizing Throw-Away

Examining an explain plan lets you look for throw-away in cases such as the following:

· Full scans

· Unselective range scans

· Late predicate filters

· Wrong join order

· Late filter operations

For example, a very unselective range scan that is executed 76563 times, accesses 11432983 rows, throws away 99% of them, and retains 76563 rows. Why access 11432983 rows to realize that only 76563 rows are needed?

Looking Beyond Execution Plans

The execution plan operation alone cannot differentiate between well-tuned statements and those that perform poorly.

For example, an EXPLAIN PLAN output that shows that a statement uses an index does not necessarily mean that the statement runs efficiently.

Sometimes indexes can be extremely inefficient.

In this case, you should examine the following:

· The columns of the index being used

· Their selectivity (fraction of table being accessed)

It is best to use EXPLAIN PLAN to determine an access plan, and then later prove that it is the optimal plan through testing. When evaluating a plan, examine the statement's actual resource consumption.

Using V$SQL_PLAN Views

V$SQL_PLAN contains the execution plan for every statement stored in the cursor cache. Its definition is similar to the PLAN_TABLE.

The advantage of V$SQL_PLAN over EXPLAIN PLAN is that you do not need to know the compilation environment that was used to execute a particular statement.

For EXPLAIN PLAN, you would need to set up an identical environment to get the same plan when executing the statement.

The statistics in V$SQL_PLAN_STATISTICS are available for cursors that have been compiled with the STATISTICS_LEVEL initialization parameter set to ALL.

The V$SQL_PLAN_STATISTICS_ALL view enables side by side comparisons of the estimates that the optimizer provides for the number of rows and elapsed time.

This view combines both V$SQL_PLAN and V$SQL_PLAN_STATISTICS information for every cursor.

EXPLAIN PLAN Restrictions

Oracle does not support EXPLAIN PLAN for statements performing implicit type conversion of date bind variables. With bind variables in general, the EXPLAIN PLAN output might not represent the real execution plan.

From the text of a SQL statement, TKPROF cannot determine the types of the bind variables.

The PLAN_TABLE Output Table

PLAN_TABLE is the default sample output table into which the EXPLAIN PLAN statement inserts rows describing execution plans

While a PLAN_TABLE table is automatically set up for each user, you can use the SQL script utlxplan.sql to manually create a local PLAN_TABLE in your schema.

On Unix, it is located in the $ORACLE_HOME/rdbms/admin directory.

For example, run the commands in below Example from a SQL*Plus session to create the PLAN_TABLE in the HR schema.

Example: Creating a PLAN_TABLE

CONNECT HR/your_password

@$ORACLE_HOME/rdbms/admin/utlxplan.sql

Table created.

Oracle Corporation recommends that you drop and rebuild your local PLAN_TABLE table after upgrading the version of the database because the columns might change.

This can cause scripts to fail or cause TKPROF to fail, if you are specifying the table.

If you want an output table with a different name, first create PLAN_TABLE manually with the utlxplan.sql script and then rename the table with the RENAME SQL statement.

For example:

RENAME PLAN_TABLE TO my_plan_table;

PLAN_TABLE Columns (only important columns)

STATEMENT_ID

Value of the optional STATEMENT_ID parameter specified in the EXPLAIN PLAN statement.

PLAN_ID

Unique identifier of a plan in the database.

TIMESTAMP

Date and time when the EXPLAIN PLAN statement was generated.

OPERATION

Name of the internal operation performed in this step. In the first row generated for a statement, the column contains one of the following values:

• DELETE STATEMENT

• INSERT STATEMENT

• SELECT STATEMENT

• UPDATE STATEMENT

OBJECT_OWNER

Name of the user who owns the schema containing the table or index.

OBJECT_NAME

Name of the table or index.

OPTIMIZER

Current mode of the optimizer.

COST

Cost of the operation as estimated by the optimizer's query approach. Cost is not determined for table access operations. The value of this column does not have any particular unit of measurement; it is merely a weighted value used to compare costs of execution plans. The value of this column is a function of the CPU_COST and IO_COST columns.

Displaying PLAN_TABLE Output

After you have explained the plan, use the following SQL scripts or PL/SQL package provided by Oracle to display the most recent plan table output:

· UTLXPLS.SQL

This script displays the plan table output for serial processing.

· UTLXPLP.SQL

This script displays the plan table output including parallel execution columns.

· DBMS_XPLAN.DISPLAY procedure

This procedure accepts options for displaying the plan table output.
You can specify:

o A plan table name if you are using a table different than PLAN_TABLE

o A statement Id if you have set a statement Id with the EXPLAIN PLAN

o A format option that determines the level of detail: BASIC, SERIAL, and TYPICAL, ALL,

Some examples of the use of DBMS_XPLAN to display PLAN_TABLE output are:

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

SELECT PLAN_TABLE_OUTPUT

  FROM TABLE(DBMS_XPLAN.DISPLAY('MY_PLAN_TABLE', 'st1','TYPICAL'));

Performance Tuning

Monday, 29 November 2010

Explain Plan