There are two different methods you can use to look at the execution plan of a SQL statement:
- EXPLAIN PLAN command - This displays an execution plan for a SQL statement without actually executing the statement.
- V$SQL_PLAN - A dictionary view that shows the execution plan for a SQL statement that has been compiled into a cursor in the cursor cache.
Under certain conditions the plan shown when using EXPLAIN PLAN can be different from the plan shown using V$SQL_PLAN. For example, when the SQL statement contains bind variables the plan shown from using EXPLAIN PLAN ignores the bind variable values while the plan shown in V$SQL_PLAN takes the bind variable values into account in the plan generation process.
Displaying an execution plan is made easy if you use the DBMS_XPLAN package. This packages provides several PL/SQL procedures to display the plan from different sources:
- EXPLAIN PLAN command
- V$SQL_PLAN
- Automatic Workload Repository (AWR)
- SQL Tuning Set (STS)
- SQL Plan Baseline (SPM)
Example 1: Uses the EXPLAIN PLAN command and the DBMS_XPLAN.DISPLAY function.
SQL> EXPLAIN PLAN FOR
2 select prod_category, avg(amount_sold)
3 from sales s, products p
4 where p.prod_id = s.prod_id
5 group by prod_category;
Explained.
SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'basic'));
------------------------------------------
Id Operation Name
------------------------------------------
0 SELECT STATEMENT
1 HASH GROUP BY
2 HASH JOIN
3 TABLE ACCESS FULL PRODUCTS
4 PARTITION RANGE ALL
5 TABLE ACCESS FULL SALES
------------------------------------------
The arguments used by DBMS_XPLAN.DISPLAY_CURSOR are:
SQL ID (default NULL, which means the last SQL statement executed in this session)
Child number (default 0)
Format (default 'TYPICAL')
Example 2: Generating and displaying the execution plan for the last SQL statement executed in a session:
SQL> select prod_category, avg(amount_sold)
2 from sales s, products p
3 where p.prod_id = s.prod_id
4 group by prod_category;
no rows selected
SQL> select plan_table_output
2 from table(dbms_xplan.display_cursor(null,null,'basic'));
------------------------------------------
Id Operation Name
------------------------------------------
0 SELECT STATEMENT
1 HASH GROUP BY
2 HASH JOIN
3 TABLE ACCESS FULL PRODUCTS
4 PARTITION RANGE ALL
5 TABLE ACCESS FULL SALES
------------------------------------------
The arguments used by DBMS_XPLAN.DISPLAY_CURSOR are:
SQL ID (default NULL, which means the last SQL statement executed in this session)
Child number (default 0)
Format (default 'TYPICAL')
The details are in $ORACLE_HOME/rdbms/admin/dbmsxpln.sql.
Example 3: Autotrace
SIRIMA1@tclmdev SQL> set autotrace on
SIRIMA1@tclmdev SQL> select * from dual;
D
-
X
Execution Plan
----------------------------------------------------------
Plan hash value: 3543395131
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
538 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SIRIMA1@tclmdev SQL>
if your query returns hundreds or thousands of rows you have to wait for Oracle to display them all. This can be a pain.
Luckily you can suppress the results with the trace[only] option. You can also choose to show just the plan or the stats. For example, to hide the query output and show just the stats, use:
set autotrace trace stat
To include the output and the plan, enter
set autotrace on exp
Once you're finished you can switch it off with:
set autotrace off
Example 4: Using XPLAN
SIRIMA1@tclmdev SQL>
SIRIMA1@tclmdev SQL> alter session set statistics_level=all;
Session altered.
SIRIMA1@tclmdev SQL> select count(*) from dba_objects;
COUNT(*)
----------
32960
SIRIMA1@tclmdev SQL> select plan_table_output from table(dbms_xplan.display_cursor);
----------------------------------------------------------------------------------------------------
SQL_ID 9990hymf36wum, child number 1
-------------------------------------
select plan_table_output from table(dbms_xplan.display_cursor)
Plan hash value: 3602215112
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 29 (100)| |
| 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY_CURSOR | 112 | 224 | 29 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Note
-----
- statistics feedback used for this statement
17 rows selected.
Example 4: Using XPLAN when SQL id is known : Displaying the execution plan for any other statement requires the SQL ID to be provided, either directly or indirectly:
select plan_table_output from table(dbms_xplan.display_cursor('gngtvs38t0060',null,'basic'));
SQL> select plan_table_output
from v$sql s,
table(dbms_xplan.display_cursor(s.sql_id,
s.child_number, 'basic')) t
where s.sql_text like 'select PROD_CATEGORY%';
Ref: https://blogs.oracle.com/optimizer/how-do-i-display-and-read-the-execution-plans-for-a-sql-statement
Ref: https://blogs.oracle.com/sql/how-to-create-an-execution-plan
Ref: https://blogs.oracle.com/sql/how-to-create-an-execution-plan
No comments:
Post a Comment