What is Explain Plan???
Oracle10g+
auto generates this table now. No need to run the script after
Oracle10g. IF any of the tools complain about this table not being
present…have your DBA create a public synonym for it under the SYS
account. Be sure to drop the PLAN_TABLE in your schema.
NOTE:-
It is important to rerun this script when
migrating to a newer instance of Oracle. There are always newer columns
and you will not be taking advantage of the latest features of Oracle
without using the latest PLAN_TABLE. In Oracle11g+, this table is
automatically generated during the migration process.
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
The explain plan process stores data in thePLAN_TABLE. This table can be located in the current schema or a shared schema
and is created using in SQL*Plus as follows.In Oracle 11g a shared-- Creating a shared PLAN_TABLE prior to 11g 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;
PLAN_TABLE is created by default, but you can still create a local version of the table using the "utlxplan.sql" script.AUTOTRACE - The Easy Option?
Switching on theAUTOTRACE 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
TheEXPLAIN 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.Then the execution plan displayed.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>
For parallel queries use the "utlxplp.sql" script instead of "utlxpls.sql".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>
From Oracle 9i onward, you should display execution plans using the DBMS_XPLAN package.
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 theSTATEMENT_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.Reading Execution Plans
Execution plans can look very confusing, but reading them is reasonably simple provided you follow three simple rules:- The first operation, or starting point, is the first leaf node, when reading from the top to the bottom. That is, the first element without an indented entry below it. You read from that point backwards.
- Join operations always require two sets. The order you read the sets is top down, so the first set is the driving set and the second is the probed set. In the case of a nested loop, the first set is the outer loop. In the case of a hash join, the first set is used to build the hash table.
- One join is performed at a time, so you only need to consider two sets and their join operation at any one time.
No comments:
Post a Comment