How to Get
Execution Plan and Statistics of SQL Query
How do you check
EXECUTION PLAN of a QUERY? I got this question on my facebook chat many times.
"EXPLAIN PLANE" The answer was quite simple, Right? Actually it
depends on what I am looking for. "AUTOT TRACE" is my personal
favourite but I use one of the following depending on various situations.
1) EXPLAIN PLAN
2) AUTO TRACE
3) DBMS_XPLAN.DISPLAY_CURSOR
4) SQL TRACE (10046)and TKPROF
Lets execute them to have an idea on how these methods are different and what information one provides and other doesn't.
1) EXPLAIN PLAN (basic and simple)
SQL> explain plan for select * from dual;
Explained.
SQL> SELECT PLAN_TABLE_OUTPUT FROM
TABLE(DBMS_XPLAN.DISPLAY());
PLAN_TABLE_OUTPUT
----------------------------------------------------------
| Id |
Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------
| 0 |
SELECT STATEMENT | |
1 | 2 | 2 |
| 1
| TABLE ACCESS FULL| DUAL | 1 |
2 | 2 |
----------------------------------------------------------
Note
-----
-
'PLAN_TABLE' is old version
11 rows selected.
2) AUTO TRACE (PLAN + STATS)
SQL> set autotrace on
SQL> select * from dual;
Execution Plan
----------------------------------------------------------
----------------------------------------------------------
| Id |
Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------
| 0 |
SELECT STATEMENT | |
1 | 2 | 2 |
| 1
| TABLE ACCESS FULL| DUAL | 1 |
2 | 2 |
----------------------------------------------------------
Note
-----
-
'PLAN_TABLE' is old version
Statistics
----------------------------------------------------------
25 recursive calls
13 db block gets
34 consistent gets
1 physical reads
3060 redo size
208 bytes sent via SQL*Net to client
362 bytes received via SQL*Net
from client
2 SQL*Net roundtrips to/from
client
0 sorts (memory)
0 sorts (disk)
1 rows processed
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
Autotrace
in SQL Developer
So for each operation you can see
metrics such as:
- How many buffer gets it used
- How long it took to run
- How many disk reads and writes it did
This makes it much easier to which
points are consuming the most resources.
Often when you're tuning queries you
want to save the plan for later reference or to share with others. To do this,
right click the plan and you'll get an option to "Export HTML":
But there is one big drawback to
autotrace: you have to wait for the query to finish!
3) DBMS_XPLAN.DISPLAY_CURSOR (PLAN + OTHER DETAILS)
SQL> SELECT * FROM DUAL;
D
-
X
SQL> SELECT
SQL_ID FROM V$SQL WHERE SQL_TEXT LIKE 'SELECT * FROM DUAL%';
SQL_ID
-------------
9g6pyx7qz035v
SQL> select * from table(dbms_xplan.display_cursor('9g6pyx7qz035v',NULL,'ADVANCED'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
SQL_ID
9g6pyx7qz035v, child number 0
-------------------------------------
SELECT * FROM DUAL
Plan hash value: 3543395131
--------------------------------------------------------------------------
| Id |
Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 |
SELECT STATEMENT | |
| | 2 (100)| |
| 1 |
TABLE ACCESS FULL| DUAL | 1
| 2 | 2
(0)| 00:00:01 |
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
Query Block Name / Object Alias (identified by
operation id):
-------------------------------------------------------------
1 -
SEL$1 / DUAL@SEL$1
Outline Data
-------------
/*+
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "DUAL"@"SEL$1")
END_OUTLINE_DATA
*/
Column Projection Information (identified by
operation id):
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
1 -
"DUAL"."DUMMY"[VARCHAR2,1]
Note
-----
- SQL
plan baseline SQL_PLAN_c7fbanxudy9yvef6f73b0 used for this statement
41 rows selected.
4) SQL TRACE (10046) and TKPROF
1. Set the following
parameters
TIMED_STATISTICS = TRUE
MAX_DUMP_FILE_SIZE = unlimited (also see metalink article 108723.1)
USER_DUMP_DEST = /oracle/admin/ora9i/udump
2. Enable SQL TRACE for a session
alter session set SQL_TRACE true;
3. Run the query
4.obtain the number included in the trace filename
Select spid, s.sid,s.serial#, p.username, p.program
from v$process p, v$session s
where p.addr = s.paddr
and s.sid = (select sid from v$mystat where rownum=1)
or
Select spid, s.sid,s.serial#, p.username, p.program
from v$process p, v$session s
where p.addr = s.paddr
and s.sid = (select userenv('sid') from dual)
5.Run TKPROF at the command line to put the TRACE file into readable format
tkprof ora_19554.trc rich2.prf explain=system/manager sort=FCHCPU,EXECPU,PRSCPU print=5
This method is easy but limited. For
example, it only traces your session.
- Session_id
- Serial_num
- Waits
- Binds
- Plan_stat
Pass in the relevant session_id and
serial_num to trace another session. If you leave these null, Oracle will trace
your current session. Setting waits and binds to true includes information
about these in the file.
To stop tracing, call DBMS_monitor.session_trace_disable. As with the enable procedure, pass the relevant session_id
and serial_num. Or leave blank if tracing your current session.
So to generate a trace file for your
current session, including waits and bind details, do the following:
exec
DBMS_monitor.session_trace_enable ( null, null, true, true );
***your
code here***
exec
DBMS_monitor.session_trace_disable;
DBMS_monitor also includes
procedures to trace all statements:
- Across the database
- For given clients
- Specific combinations of service name, module and action.
Note that tracing adds overhead. So
avoid enabling it for the whole database. And remember to disable it when
you've finished!
Once you've traced your code you need to get the
file so you can analyze it.
With the trace file in hand you can
parse it with TKPROF. The basic syntax for this is:
tkprof
<trace_file_name> <output_file_name>
For example:
tkprof
ORCL_ora_27883.trc trace.log
This parses the contents of
ORCL_ora_27883.trc into trace.log. You're now ready to start analyzing the
queries!
use the sort option:
tkprof
<trace_file_name> <output_file_name> sort=prsela,exeela,fchela
SQL Monitor
The SQL Monitor levels up autotrace. It provides similar operation-level
stats. But with a bonus. You can view the progress of execution plans in real
time!
Got a troublesome full tablescan?
You can watch while Oracle churns away at it. Instead of having to wait for the
query to finish, you can see the plan immediately. This makes identifying
bottlenecks easy.
Even better, unlike autotrace which
you need to run manually, Oracle will capture the plan for you automatically.
So how do you view the plans?
They're available in SQL Developer
by going to Tools -> Monitor SQL…
Or you can view them in the Performance tab of
Enterprise Manager:
For those of you who prefer text,
you can output the plans using SQL. To do so, use the following query:
select dbms_sqltune.report_sql_monitor(
sql_id => '4htx5uyx0gxxx',
type => 'TEXT',
report_level => 'ALL'
) as report
from
dual;
You'll need to replace SQL ID
parameter with the ID of your query. You can find this with the following
statement:
select sql_id, sql_text from v$sql
where
sql_text like '%some text from your query%'
and
sql_text not like '%not this%';
No comments:
Post a Comment