oracle explain plan autotrace - ghdrako/doc_snipets GitHub Wiki
Explain plan
explain plan for
select drv.forename
, drv.surname
from drivers drv
where drv.surname = 'Verstappen'
/
--- After the explain plan is created, it can be inspected by executing the following.
select *
From dbms_xplan.display( 'plan_table' )
/
Utworzenie tabeli dla planow
<Oracle_Home>/rdbms/admin/utlxplan.sql
SQL> @?/rdbms/admin/utlxplan.sql
SQL> explain plan for
2 select *
3 from hr_employees
4 full join hr_departments
5 on emp_dep_id = dep_id;
SQL> @?/rdbms/admin/utlxpls.sql
PLAN_TABLE_OUTPUT
---------------------------
Plan hash value: 2757235693
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 124 | 44392 |
6 (0)| 00:01 |
| 1 | VIEW | VW_FOJ_0 | 124 | 44392 |
6 (0)| 00:01 |
|* 2 | HASH JOIN FULL OU| | 124 | 11408 |
6 (0)| 00:01 |
| 3 | TABLE ACCESS FUL| HR_DEPARTMEN| 28 | 588 |
3 (0)| 00:01 |
| 4 | TABLE ACCESS FUL| HR_EMPLOYEES| 107 | 7597 |
3 (0)| 00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMP_DEP_ID"="DEP_ID")
CREATE GLOBAL TEMPORARY TABLE PLAN_TABLE sharing=none (
statement_id varchar2(30),
plan_id number,
timestamp date,
remarks varchar2(4000),
operation varchar2(30),
options varchar2(255),
object_node varchar2(128),
object_owner varchar2(128),
object_name varchar2(128),
object_alias varchar2(261),
object_instance numeric,
object_type varchar2(30),
optimizer varchar2(255),
search_columns number,
id numeric,
parent_id numeric,
depth numeric,
position numeric,
cost numeric,
cardinality numeric,
bytes numeric,
other_tag varchar2(255),
partition_start varchar2(255),
partition_stop varchar2(255),
partition_id numeric,
other long,
distribution varchar2(30),
cpu_cost numeric,
io_cost numeric,
temp_space numeric,
access_predicates varchar2(4000),
filter_predicates varchar2(4000),
projection varchar2(4000),
time numeric,
qblock_name varchar2(128),
other_xml clob
) ON COMMIT DELETE ROWS;
Run time execution plan
ALTER session set timed_statistics=TRUE;
ALTER session set statistics_level=ALL;
SELECT count(*) from xtbl;
SELECT * from table(DBMS_XPLAN.DISPLAY_CURSOR(null,null,'ADVANCED ALLSTATS LAST'));
Autotrace
SQL> connect sys as sysdba
SQL> @?/sqlplus/admin/plustrce.sql
SQL> grant plustrace to public;
SQL> connect demo_user/demo_pwd;
SQL> set autotrace traceonly
SQL> select *
2 from hr_employees
3 full join hr_departments
4 on emp_dep_id = dep_id;
SQL> set autotrace on
SQL> SELECT count(*) from xtbl;
SQL>set autotrace off
To get the actual rowsorurce execution statistics (that's the data in the A-Row,A-Time -- and Buffers columns), you must either use the gather_pal_statistics hint in your query or you must set the statistics_level parameter to ALL (default TYPICAL). Notice serveroutput off - to ensure thet the execution pala is the last query execution plan
- STARTS - the number of times particular step is executed
- E-Rows - the number of rows that optimizer estimated by each step rowsource operation
- A-Rows - the actual number of rows
- A-Time - the actual elapsed duration for each plan operation. Note that parent steps include the durations of their child steps
- Buffers - the actual count of access operations upon buffers in the buffer cache( logical I/O (LIO)). Note that parent steps include the buffer access count of their child steps
set serveroutput off
select /*+ gather_plan_statistics */ * from ord where order_no = 256769 ;
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));