如何使用SQLPLUS分析SQL语句（查询执行计划跟踪）

方法一：autotrace
;M2Qb S8H6V t'y%[0
brle l(oq3f{0f*QP01，  connect sys/密码 as sysdba，在sys用户下运行$ORACLE_HOME/sqlplus/admin/plustrce.sql
X Kq1km0这段sql的实际内容如下：
m e!bBHbZ__0set echo on
*FYE
QL9Q5hyi*a#x0drop role plustrace;
a7FA&Gq,V0create role plustrace;51Testing软件测试网7\(g7}
x"i
P
grant select  on v_$sesstat to plustrace;
)f}w)S O#\x0grant select on v_$statname to plustrace;
p|v k'{C@&P0grant select on v_$mystat  to plustrace;
t\4_5cCW&}0grant plustrace to dba with admin option;51Testing软件测试网-V$G&{8w/~,bv
set echo off
0B0Ta#HI4F }']]7W0
?;GB7E%tO0以上产生plustrace角色,然后在sys用户下把此角色赋予一般用户
T,`I"bytOE\0SQL> grant PLUSTRACE to 用户名;51Testing软件测试网 A jG0dIpe X#~
51Testing软件测试网p0m.n(b6L];J
2，然后在当前用户SQL>下运行$ORACLE_HOME/rdbms/admin/utlxplan.sql，它会创建一个plan_table，用来存储分析SQL语句的结果。51Testing软件测试网7q~C*@K[.\I}r+M

2umKP^0实际执行的是如下语句：51Testing软件测试网7BKS)Z0?0X L

V.S#H3w^X-J ^0create table PLAN_TABLE (51Testing软件测试网6zs4U;s%Br0T$y9g
                          statement_id    varchar2(30),
,RA
_;p3uUu0                          timestamp       date,
9c!Z2IYk0t'DX0                          remarks         varchar2(80),
~*}f[&I`s@0                          operation       varchar2(30),
gC!Vm"WB:G0                          options         varchar2(30),51Testing软件测试网k
lp}S"D;c.B[
                          object_node     varchar2(128),
!A#sM {+H(PM1rt.e;W0                          object_owner    varchar2(30),
8c'AA|P}0                          object_name     varchar2(30),
fz?xf
j0                          object_instance numeric,
"p+e9}]ISQb#n0                          object_type     varchar2(30),
;E6WS5quv Z0Jx0                          optimizer       varchar2(255),
k;jb)@/|;X!tF8Iq0                          search_columns  number,51Testing软件测试网%Mf?~)@.?0s
                          id              numeric,
?f2Nj%z&Wd0P0                          parent_id       numeric,
KDWpq2iO{f0                          position        numeric,

v#@Rer6?
P0                          cost            numeric,51Testing软件测试网 lKEBg5k(\Hv!S"@!~*t
                          cardinality     numeric,51Testing软件测试网7[2v
Ps2K0m gx"S
                          bytes           numeric,
,K'M'xi'MTDy0                          other_tag       varchar2(255),51Testing软件测试网@|i.pMs5m?H{"J
                          partition_start varchar2(255),
5H*c$b~{ a5r+l)Xn0                          partition_stop  varchar2(255),
n)q%A|k^%No0                          partition_id    numeric,51Testing软件测试网x&b5z RQ1QY
                          other           long,
Si,U C%p0                          distribution    varchar2(30));
$~H.jT?0
9vJ5w8I)U"L8g&Nru03，SQL/PLUS的窗口运行以下命令
ao o/|.b e1N&E1`051Testing软件测试网 oK:q%Pv
set time on;                 (说明：打开时间显示）（可选）
ISS6x_0set autotrace on;            (说明：打开自动分析统计，并显示SQL语句的运行结果）
v9Es]Z~0set autotrace traceonly;     (说明：打开自动分析统计，不显示SQL语句的运行结果）
/a4ak*?z ~2`o051Testing软件测试网.TA
_ j,qCv g
4，接下来你就运行需要查看执行计划的SQL语句，看到其分析统计结果了。一般来讲，我们的SQL语句应该避免对大表的全表扫描。
:W
Av5ji
g-g-I#Fjm0
5h4E-nns@05，关闭以上功能，在SQL/PLUS的窗口运行以下命令51Testing软件测试网T0`[K"o$P

Napn.^:F0set time off;                      (说明：关闭时间显示）51Testing软件测试网$Y%`(?evkxV'V4r
set autotrace off;       
,JbH|{g051Testing软件测试网,~DH C:J
附：相关的set autotrace命令：51Testing软件测试网rSm f hPl%S0xq)I

+gu|t
C0v9h"M(yE0SET AUTOTRACE OFF51Testing软件测试网~D2b
sLP[
No AUTOTRACE report is generated.
3aKK)o2_M cKJ0
H8W)bL7LtD&n0SET AUTOTRACE ON EXPLAIN
K{w4g s*j0shows only the optimizer execution path.
1B&hn+Y\aFe051Testing软件测试网2~8wsL/f
SET AUTOTRACE ON51Testing软件测试网 ]tW6G$}j'O@:G1]

@
d#aU7C;Z0STATISTICS shows only the SQL statement execution statistics.
N&@QVZn;N-F0
*mg j0L/P,Z uc,m0SET AUTOTRACE ON
uQF:bm*yB}g0includes both the optimizer execution path and the SQL statement execution statistics.51Testing软件测试网O^]5t)}
`

x9l8e,t?0SET AUTOTRACE TRACEONLY51Testing软件测试网,@&A?%k `
Like SET AUTOTRACE ON, but suppresses the printing of the user’s query output, if any.
d/b|0lMk?&j051Testing软件测试网p)XEU4UD*P(L9D
说明：如果执行了set autotrace on 语句，接下来的查询、插入、更新、删除语句就会显示执行计划和有用的统计信息，直到执行set autotrace off语句。
B9e'Tk2b'j}Z\V\0
3oFV'Yf$T B(HX6H0方法二： EXPLAIN PLAN FOR sql51Testing软件测试网3o5VVF8uaL
H
51Testing软件测试网k+?&g)j4Wi0\
1，在sqlplus下执行：EXPLAIN PLAN FOR sql语句，执行后会提示已经解释。51Testing软件测试网d N7JY6e

pm3`Y)Ut/D
m K02，然后执行如下查询，查询出执行计划
8J6Wl$IdDy0
.F gm!K wv0SQL> select * from table(dbms_xplan.display);51Testing软件测试网(MYxWRlBqoI

0Ku4d8ocRE.eM0例子：
0A7ha5Bq^l051Testing软件测试网(Q/U3gtA ?C!u
SQL> explain plan for select * from emp where deptno='20';51Testing软件测试网,B@Bm R*R
51Testing软件测试网-H+T!UlUy;v
Explained.51Testing软件测试网nny3W#KB,aF.@!~0c

)FFp6|)hA0SQL> select * from table(dbms_xplan.display);

 

注： 除了上面的，还有一个很有用的函数dbms_xplan.display_awr(sql_id ,null,null,'ALL');这个可以结合AWR报告中的SQL语句的SQL_ID来获得历史语句的执行计划，然后通过视图 dba_hist_sqlbind 找到语句对应的绑定变量值。