***********************************************************
----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对数据库进行分析。
转载于:https://blog.51cto.com/ocpyang/1194470
140
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
