执行计划小结,真是琳琅满目啊!
1,sqlplus中的autotrace
SET AUTOT[RACE] {ON| OFF | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]
参考另一文档:http://blog.csdn.net/jc_benben/article/details/17270105
实际上,此处的执行计划是由DBMS_XPLAN工具创建的。执行的信息包含一系列从V$SESSTAT表中收集的性能指标,包括逻辑度物理读,排序,网络统计等
SQL>set autotrace on exp
SQL> select * from dual;
DU
--
X
执行计划
----------------------------------------------------------
Plan hash value: 272002086
--------------------------------------------------------------------------
| 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 |
--------------------------------------------------------------------------
SQL> set auto off exp;
2,生成执行计划
explain for…dbms_xplan.display()/dbms_xplan.display_cursor()
另一篇参考:http://blog.csdn.net/jc_benben/article/details/51683149
其中display是展示plan_table中的执行计划,display_cursor则展示了在V$SQL_PLAN中缓存的执行计划信息
SQL>explain plan for select * from dual;
已解释。
SQL> select * from table(dbms_xplan.display_cursor(format=>'ALLSTAT,LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Error: format 'ALLSTAT,LAST' not valid forDBMS_XPAN.DISPLAY_CURSOR()
SQL> select * fromtable(dbms_xplan.display_cursor(format=>'ALLSTATs,LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 52ta4vf0q9xtd, child number 0
-------------------------------------
select * fromtable(dbms_xplan.display_cursor(format=>'ALLSTAT,LAST'))
Plan hash value: 3713220770
---------------------------------------------------------------------
| Id | Operation | Name | E-Rows |
---------------------------------------------------------------------
| 0| SELECT STATEMENT | | |
| 1| COLLECTION ITERATOR PICKLER FETCH|DISPLAY_CURSOR | 8168 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------------------------
Note
-----
-Warning: basic plan statistics not available. These are only collected when
:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or systemleve
l
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
已选择 19 行。
3,使用sql_trace跟踪
虽然以上工具是主要的调优工具,但是有时候我们还要看具体的事务或者应用SQL执行详情时,跟踪就很有用,它提供了上边提供的所有功能,并且更加详细
SQL> alter session set sql_trace=true;
会话已更改。
SQL> select 1111,id,name from t2;
1111 ID NAME
---------- ---------- --------------------
1111 1 china
1111 2 china
SQL> alter session set sql_trace=false;
会话已更改。
路径:
SQL> select value from v$diag_info wherename='Default Trace File';
VALUE
--------------------------------------------------------------------
G:\APP\ORACLE\diag\rdbms\orcl\orcl\trace\orcl_ora_8392.trc
这里看到的跟踪文件不容易理解,还需要使用工具Tkprof格式化为更易于阅读的形式,可以参考http://blog.csdn.net/jc_benben/article/details/10530799
4,使用 10046
Event 10046 - Enable SQL Statement Trace
This event can be used to dump SQL statements executed by a session withexecution plans and statistics.
Bind variable and wait statistics can optionally be included. Level 12 is themost detailed.
For example
ALTER SESSION SET EVENTS
'10046 trace name context forever, level 12';
Levels are
Level Action
1 Print SQL statements, execution plans and execution statistics –其实就相当于sql_trace
4 As level 1 plus bind variables
8 As level 1 plus wait statistics
12 As level 1 plus bind variables and wait statistics
还有其他很多事件,可以在linux安装目录中一个文件中查看到$ORACLE_HOME/rdbms/mesg/oraus.msg
SQL> alter session set events '10046trace name context forever,level 12';
会话已更改。
SQL> select id,name from t2;
ID NAME
---------- --------------------
1 china
2 china
SQL> alter session set events '10046trace name context off';
会话已更改。
5,使用dbms_system.set_sql_trace_in_session
SQL> select sid,serial#,username fromv$session where username='LOGE';
SID SERIAL#
---------- ----------
USERNAME
------------------------------------------------------------
129 56923
LOGE
n 使用其他用户启动监控
SQL> exec dbms_system.set_sql_trace_in_session(129,56923,TRUE);
PL/SQL 过程已成功完成。
n 关闭监控
SQL> execdbms_system.set_sql_trace_in_session(129,56923,FALSE);
PL/SQL 过程已成功完成。
6,监控视图
V$SYSSTAT -- 包含数据库全局性能统计信息的表
V$session -- 每个连接的会话创建一条信息
v$sesstat -- 每个绘画都包含一份来自v$sysstat的统计信息
v$process -- 每个服务器进程都包含一条记录
v$sql,v$sql_plan --包含缓存的sql的详情
v$system_event --记录数据库启动以来的等待汇总
v$sesion_event --记录各个会话等待汇总
v$session,v$session_wait --展示一个会话当前的等待
v$sess_time_model -- 展示会话时间模型数据
比如总耗时最多的前10条sql:
1 select sql_id,child_number,sql_text,elapsed_time
2 from (select sql_id,child_number,sql_text,elapsed_time,cpu_time,disk_reads,
3 rank() over(order by elapsed_time desc) as elapsed_rank
4 from v$sql)
5* where elapsed_rank<=10
7,图形化工具
比如TOAD,PL/SQL等
二,跟踪文件部分解析
1,Trace文件也是苦涩,比如:
=====================
PARSING IN CURSOR #138996696 len=22 dep=0uid=110 oct=3 lid=110 tim=150225868821 hv=1481359936 ad='7ff7a29af28'sqlid='4yj0q9xc4rhk0'
select id,name from t2
END OF STMT
PARSE#138996696:c=0,e=5730,p=0,cr=8,cu=0,mis=1,r=0,dep=0,og=1,plh=1513984157,tim=150225868819
EXEC#138996696:c=0,e=41,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1513984157,tim=150225868995
WAIT #138996696: nam='SQL*Net message toclient' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=150225869106
FETCH#138996696:c=0,e=65,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=1,plh=1513984157,tim=150225869229
WAIT #138996696: nam='SQL*Net message fromclient' ela= 492 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=150225869806
WAIT #138996696: nam='SQL*Net message toclient' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=150225869924
FETCH #138996696:c=0,e=110,p=0,cr=5,cu=0,mis=0,r=1,dep=0,og=1,plh=1513984157,tim=150225870010
STAT #138996696 id=1 cnt=2 pid=0 pos=1obj=92795 op='TABLE ACCESS FULL T2 (cr=8 pr=0 pw=0 time=54 us cost=3 size=40card=2)'
*** 2016-06-16 09:08:06.026
WAIT #138996696: nam='SQL*Net message fromclient' ela= 49975525 driver id=1413697536 #bytes=1 p3=0 obj#=-1tim=150275845667
CLOSE#138996696:c=0,e=18,dep=0,type=0,tim=150275846057
简单解读,PARSING IN CURSOR部分
len sql 语句长度
dep sql 语句递归深度
uid user id
oct oracle command type
lid privilege user id
tim timestamp,时间戳,v$timer视图
hv hash id
ad sql address 地址, 用在 v$sqltext
sqlid sql id
parse部分:
c CPU消耗的时间
e Elapsed time
p number of physical reads 物理读的次数
cr number of buffers retrieved for CRreads 逻辑读的数据块
cu numberof buffers retrieved in current mode (current 模式读取的数据块)
mis cursor missed in the cache 库缓存中丢失的游标, 硬解析次数
r number of rows processed 处理的行数
dep 递归深度
og optimizer mode 【1:all_rows, 2:first_rows,3:rule, 4:choose】
plh plan hash value
tim timestamp
WAIT 部分:
nam an event that we waited for 等待事件
ela 消耗的时间
p3 块号
trm 时间戳
STAT 执行计划:
cnt 当前行源返回的行数
pid parent id of this row source 当前行源的父结点 id
pos position in explain plan 执行计划的位置
obj object id of row source (if this is a baseobject)
op the row source access operation
2,使用tkprof格式化
跟tracefile区别,它不包含绑定变量西西你,不包含真正的sql执行顺序,他的优势是可以看到根据CPU时长,磁盘读取的SQL很直观的内容,是经过格式化后的内容
使用:
tkprof orcl_ora_8392.trc g:/trace.trc -- 生成trace.trc文件
可以看具体:http://blog.csdn.net/jc_benben/article/details/10530799