Oracle有多种方式获得一条SQL语句的查询计划,比如使用explain plan命令,在PLSQL Developer里面按F5快捷键等,但是作为SQL调优,比较简便的方式,还是在sqlplus中使用set autotrace on和set timing on的方式来得到SQL的实际运行时间和查询计划;
第一种:SQLPLUS中的autotrace是分析SQL的执行计划,执行效率的一个非常简单方便的工具,使用autotrace不会产生跟踪文件。利用autotrace工具提供的SQL执行计划和执行状态可以为优化SQL提供依据,以及效果的对比。
AutoTrace用法:
SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} | [EXP[LAIN]] [STAT[ISTICS]]
例如:
SET AUTOTRACE OFF 停止AutoTrace
SET AUTOTRACE ON 开启AutoTrace,显示AutoTrace信息和SQL执行结果
SET AUTOTRACE TRACEONLY 开启AutoTrace,仅显示AutoTrace信息
SET AUTOTRACE ON EXPLAIN 开启AutoTrace,仅显示Autotrace的EXPLAIN信息
SET AUTOTRACE STATISTICS 开启AutoTrace,仅显示Autotrace的STATISTICS信息
AutoTrace启用:
1、使用dba角色用户sys设置权限,执行脚本plustrce.sql。
Oracle10g存放目录为${ORACLE安装目录}\product\10.2.0\db_1\sqlplus\admin\plustrce.sql
Oracle11g存放目录为${ORACLE安装目录}\product\11.2.0\dbhome_1\sqlplus\admin\plustrce.sql
plustrce.sql脚本用于给SQL*Plus Set AutoTrace命令创建角色plustrace访问动态性能视图。该脚本必须在DBA角色权限下执行,
执行完毕后,给需要使用AutoTrace功能的用户赋予权限。
plustrce.sql脚本内容如下:
set echo on
drop role plustrace;
create role plustrace;
grant select on v_$sesstat to plustrace;
grant select on v_$statname to plustrace;
grant select on v_$mystat to plustrace;
grant plustrace to dba with admin option;
set echo off
执行脚本:
C:\Users\Administrator>sqlplus sys/bosft@LOCAL as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on 星期三 6月 4 10:36:33 2014
Copyright (c) 1982, 2010, Oracle. All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @J:\oracle11\product\11.2.0\dbhome_1\sqlplus\admin\plustrce.sql
SQL> drop role plustrace;
角色已删除。
SQL> create role plustrace;
角色已创建。
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> set echo off
SQL> grant plustrace to bsoft_ypjg;--授权给
授权成功。
2、使用AutoTrace进行SQL执行计划分析
SQL> show pagesize 查看SQL*Plus页面显示的总行数
pagesize 14
SQL> set pagesize 100; 设置SQL*Plus页面显示的总行数
SQL> show linesize 查看SQL*Plus行显示的总字符数
linesize 80
SQL> set linesize 500; 设置SQL*Plus行显示的总字符数
SQL> set timing on 设置打印sql语句的运行时间
SQL> set autotrace on 开启AutoTrace,显示AutoTrace信息和执行结果
SQL> select count(1) from dual;
COUNT(1)
----------
1
已用时间: 00: 00: 00.01
执行计划
----------------------------------------------------------
Plan hash value: 3910148636
-----------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
423 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed