***********************************************************
----1:获取“刚刚”的执行计划display_cursor
***********************************************************
Explain plan命令在Oracle中,可以对后面的SQL语句进行直接的解析,将执行计划保存在一个plan_table的中间表中。之后通过dbms_xplan包的方法进行获取。
select count(*) from t1;
--查询v$sql视图,找到该语句的sql_id(前提是你要查询的sql语句还在shared pool):
select sql_id from v$sql where sql_text='select count(*) from t1';
SQL_ID
-------------
5bc0v4my7dvr5
--调用dbms_xplan包,查看该语句执行时的实现执行计划:
select * from table(dbms_xplan.display_cursor('5bc0v4my7dvr5'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 5bc0v4my7dvr5, child number 0
-------------------------------------
select count(*) from t1
Plan hash value: 3724264953
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 74 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 2 | TABLE ACCESS FULL| T1 | 100K| 74 (2)| 00:00:01 |
-------------------------------------------------------------------
Note
-----
- SQL plan baseline SQL_PLAN_f4251dfwsquh4616acf47 used for this statement
已选择18行。
/*************
直接调用display_cursor,不指定sql_id,就可以将刚刚当前会话执行的SQL命令执行计划从library cache中查询出来。
注意:display_cursor也支持format参数,可以进行详细执行计划信息的抽取。
只能在sqlplus或者sqlplusw上使用。如果是TOAD、PL/SQL develop等其它三方工具,可能调用不能正常使用。
***********/
***********************************************************
2:explain plan for
***********************************************************
---案例1、显示简单的计划
explain plan for select count(*) from t1;
查看结果:
select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3724264953
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 74 (2)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 100K| 74 (2)| 00:00:01 |
-------------------------------------------------------------------
Note
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----
- SQL plan baseline "SQL_PLAN_f4251dfwsquh4616acf47" used for this statement
已选择13行。
--案例2、显示详细执行计划信息
explain plan for select count(*) from t1;
查看结果:
select * from table(dbms_xplan.display(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3724264953
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 74 (2)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 100K| 74 (2)| 00:00:01 |
-------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T1@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$1" "T1"@"SEL$1")
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('11.2.0.3')
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Column Projection Information (identified by operation id):
-----------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
Note
-----
- SQL plan baseline "SQL_PLAN_f4251dfwsquh4616acf47" used for this statement
已选择38行。
SQL>
/****
Explain plan for使用起来非常顺手,特别是可以支持第三方开发工具中使用,但是explain plan在使用的时候,有些
小问题,需要我们注意:
1、explain plan for是单纯对SQL语句进行优化器分析,获取并产生到的执行计划。
这个过程中,并没有真正执行。所以,生成的执行计划有时候会可能有问题,而且进行统计的信息情况没有
autotrace的准确度高;
2、explain plan for由于只是对执行计划进行估计。所以在有绑定变量的SQL时,生成的执行计划并不准确;
*****/
***********************************************************
3、autotrace工具使用
***********************************************************
*************3.1 配置AUTOTRACE
配置AUTOTRACE 的方法不止一种,以下是我采用的方法:
1、
(1)cd [ORACLE_HOME]/sqlplus/admin;
(2)作为SYS 或SYSDBA 登录SQL*Plus;
(3)运行@/plustrce.sql;
(4)运行GRANT PLUSTRACE TO PUBLIC。
如果愿意,可以把GRANT TO PUBLIC 中的PUBLIC 替换为某个用户。
通过将PLAN_TABLE置为public,任何人都可以使用SQL*Plus 进行跟踪。这么一来,就不需要每个用户都安装自己的计划表
--配置如下:想配置scott下使用autotrace
--1.查看scott是否具有权限
C:\>sqlplus scott/tiger@orcl
SQL> show user;
USER 为 "SCOTT"
SQL> set autotrace on;
SP2-0618: 无法找到会话标识符。启用检查 PLUSTRACE 角色
SP2-0611: 启用 STATISTICS 报告时出错
--2.作为SYS 或SYSDBA 登录SQL*Plus运行@/plustrce.sql
SQL> show user;
USER 为 "SYS"
SQL> @E:\app\Administrator\product\11.2.0\dbhome_1\sqlplus\admin\plustrce.sql
返回:----------------------------------
SQL> drop role plustrace;
drop role plustrace
*
第 1 行出现错误:
ORA-01919: 角色 'PLUSTRACE' 不存在
SQL> create role plustrace;
角色已创建。
SQL>
SQL> grant select on v_$sesstat to plustrace;
授权成功。
SQL> grant select on v_$statname to plustrace;
授权成功。
SQL> grant select on v_$mystat to plustrace;
授权成功。
SQL> grant plustrace to dba with admin option;
授权成功。
SQL>
SQL> set echo off
-------------------------------------
--3.授权给scott账户
SQL> grant plustrace to scott;
授权成功。
--4.scott账户验证是否具有权限
SQL> show user;
USER 为 "SCOTT"
SQL> set autotrace on;
SQL> set autotrace traceonly;
SQL> set timing on;
*************3.2 autotrace工具使用
SQL> set autotrace
用法: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
--用法1:查看执行计划、统计信息并且返回sql结果集
set autotrace on;
select count(*) from t;
COUNT(*)
----------
50295
已用时间: 00: 00: 00.01
执行计划
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 159 (2)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 50295 | 159 (2)| 00:00:02 |
-------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
509 bytes sent via SQL*Net to client
211 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
---方法2:查看执行计划、统计信息不返回sql结果集:
SQL> set autotrace traceonly;
SQL> select * from t2;
已选择402344行。
执行计划
----------------------------------------------------------
Plan hash value: 1513984157
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 402K| 33M| 1240 (3)| 00:00:15 |
| 1 | TABLE ACCESS FULL| T2 | 402K| 33M| 1240 (3)| 00:00:15 |
--------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
911 bytes sent via SQL*Net to client
190 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
402344 rows processed
---方法3:只看执行计划不返回sql结果集:
QL> set autotrace traceonly explain;
SQL> select * from t;
已用时间: 00: 00: 00.01
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50295 | 4273K| 161 (3)| 00:00:02 |
| 1 | TABLE ACCESS FULL| T | 50295 | 4273K| 161 (3)| 00:00:02 |
---方法4:只看统计信息不返回sql结果集:
SQL> set autotrace traceonly statistics;
SQL> select * from t;
已选择50295行。
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
911 bytes sent via SQL*Net to client
189 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
50295 rows processed
***********************************************************
4、SQL_TRACE会话跟踪
***********************************************************
当SQL语句出现性能问题时,我们可以用SQL_TRACE来跟踪SQL的执行情况,通过跟踪,我们可以了解一条SQL或者PL/SQL包的运行情况,SQL_TRACE命令会将SQL执行的整个过程输出到一个trace文件中,我们可以读这个trace 文件来了解在这个SQL执行过程中Oracle
都做了哪些操作。
可以通过sql命令启动SQL_TRACE,或者在初始化参数里面。
SQL>alter session set sql_trace=true;
或者
SQL> alter database set sql_trace=true;
这两条命令的区别:
在session级别设置,只对当前session进行跟踪,在实例级别,会对实例上所有的SQL做跟踪,这种方式跟踪的SQL太多,
代价是非常大的,所有很少用。
如果是在初始化文件里面设置,只需要在参数文件里添加一个sql_trace 参数即可。
示例:
1.确定当前的trace文件。
1.1 通过设置trace 文件标识
SQL> alter session set tracefile_identifier='ocpyang';
会话已更改。
设置标识的目的就是方便我们查找生成的trace文件。我们只需要在trace目录查找文件名里带有标识的文件即可。 在Oracle 10g中,SQL_TRACE生成的trace文件默认路劲是$ORACLE_BASE/admin/SID/udump.
到了11g,trace 默认路径在:$ORACLE_BASE/diag/rdbms/orcl/orcl/trace目录下.
1.2直接用如下SQL直接查出,当前的trace文件名。
SELECT d.VALUE
|| '/'
|| LOWER (RTRIM (i.INSTANCE, CHR (0)))
|| '_ora_'
|| p.spid
|| '.trc' AS "trace_file_name"
FROM
(SELECT p.spid
FROM v$mystat m,
v$session s,
v$process p
WHERE m.statistic# = 1
AND s.SID = m.SID
AND p.addr = s.paddr
) p,
(SELECT t.INSTANCE
FROM v$thread t,
v$parameter v
WHERE v.NAME = 'thread'
AND (v.VALUE = 0
OR t.thread# = TO_NUMBER (v.VALUE))
) i,
(SELECT VALUE FROM v$parameter WHERE NAME = 'user_dump_dest'
) d;
trace_file_name
--------------------------------------------------------------------------------
E:\app\Administrator\diag\rdbms\orcl\orcl\trace/orcl_ora_68612.trc
2. 启动SQL_TRACE
SQL> alter session set sql_trace=true;
会话已更改。
3. 进行相关事务操作
SQL> select * from t1;
4.关闭SQL_TRACE
SQL> alter session set sql_trace=false;
会话已更改。
注意,这里是显示的关闭SQL_TRACE,在session级别,也可以直接退出SQLPLUS来终止SQL_TRACE。
***********************************************************
5、10046跟踪
***********************************************************
10046 事件主要用来跟踪SQL语句,它并不是ORACLE 官方提供给用户的命令,在官方文档上也找不到事件的说明信息。
但是用的却比较多,因为10046事件获取SQL的信息比SQL_TRACE 更多。 更有利于我们对SQL的判断。
10046事件说明10046事件是Oracle提供的内部事件,是对SQL_TRACE的增强.
10046事件可以设置以下四个级别:
1 - 启用标准的SQL_TRACE功能,等价于sql_trace
4 - Level 1 加上绑定值(bind values)
8 - Level 1 + 等待事件跟踪
12 - Level 1 + Level 4 + Level 8
类似sql_trace,10046事件可以在全局设置,也可以在session级设置。
开启10046事件
1.对当前session 使用10046事件
SQL>alter session set events ‘10046 trace name context forever, level 12’; --启动10046事件
执行相关事务
SQL>alter session set events ‘10046 trace name context off’; -- 关闭10046事件
2.对其他的会话进行跟踪
2.1用SQL_TRACE跟踪
SQL> select sid,serial# from v$session where SID=267;
SID SERIAL#
---------- ----------
267 996
SQL> execute dbms_system.set_sql_trace_in_session(267,996,true); -- 启动SQL_TRACE
PL/SQL 过程已成功完成。
SQL> execute dbms_system.set_sql_trace_in_session(267,996,false); -- 关闭SQL_TRACE
PL/SQL 过程已成功完成。
2.2 使用10046 事件跟踪
SQL> exec dbms_monitor.session_trace_enable(267,996,waits=>true,binds=>true); -- 启动trace
PL/SQL 过程已成功完成。
SQL> exec dbms_monitor.session_trace_disable(267,996); -- 关闭trace
PL/SQL 过程已成功完成。
注意:
如果一条SQL语句中包含了通过DBLINK进行的数据操作,我们想对这条SQL进行trace跟踪,在本地只能够
trace到本地执行的SQL信息,而对于远程的SQL语句,由于它运行在远端的数据库上,我们要获得它的信息,
需要到远端的数据库上,找到运行这条SQL语句的session,然后对它做Trace。 另外,这条SQL语句的执行
计划也只能从远端数据库上捕获到。
总之,当SQL语句操作出现性能问题时,我们可以用SQL_TRACE 或者10046事件进行跟踪是最合适的。
如果是数据库整体性能下降,就需要使用statspack或者AWR对数据库进行分析。