ITPub博客

首页 > 数据库 > Oracle > 启用sqlplus的explain功能,保存结果至文件,查看sql的执行计划

启用sqlplus的explain功能,保存结果至文件,查看sql的执行计划

原创 Oracle 作者:xinghets 时间:2009-08-04 09:19:04 0 删除 编辑
>24)&0xff;return this.FONTMAP.substring(2*(id-1),2*id);}">启用sqlplus的explain功能,保存结果至文件,查看sql的执行计划
启用sqlplus的explain功能,保存结果至文件,查看sql的执行计划 收藏
--ORACLE服务端的$ORACLE_HOME/sqlplus/admin/glogin.sql下,
--这样每次用telnet窗口在服务端使用sqlplus
--设置显示格式,sql执行的跟踪
set wrap off
set linesize 1000
set timing on
SET AUTOTRACE TRACEONLY

保存输出结果:

步骤1:进行环境变量的设置。执行语句:spool on。例如,执行下列SET命令: set echo off set feedback off set verify off set heading off spool off set colsep"

步骤2:创建用于保存输出的平面文件。 执行语句:spool 例如:spool d:Spool_flatquery.txt 这样,SQL*Plus将把所有的输出以及在屏幕上的命令等都指定给该文件。

步骤3:执行查询输出。此时,系统并没有把结果保存到文件中,而是保存到缓冲区中。

步骤4:查询结束后,关闭文件即可。执行语句:spool off。此时,系统才真正把所有输出结果保存到文件中。以上方法适用于MS-DOS环境下的SQL*Plus。

sqlplus格式化参数:

set echo on/off 是否显示脚本中的需要执行的命令

set feedback on/off 是否显示 select 结果之后返回多少行的提示

set linesize n 设置一行最多显示多少字符,之前就是因为 n 设得过大,导致行与行之间有空白行

set termout on/off 在执行脚本时是否在屏幕上输出结果,如果 spool 到文件可以将其关闭

set heading on/off 是否显示查询结果的列名,如果设置为 off,将用空白行代替,如果要去除该空白行,可以用 set pagesize 0

set pagesize n 设置每页的行数,将 n 设为 0 可以不显示所有 headings, page breaks, titles, the initial blank line, and other formatting information

set trimspool on/off 在 spool 到文件时是否去除输出结果中行末尾的空白字符,之前的隔行可以用该参数去掉,和该参数对应的是 trimout,后者用于屏幕输出

set trimout on/off 是否去掉屏幕上输出结果行末尾的空白字符

相关资料:

---------------------------------------------------------------

Tracing Statements
You can automatically get a report on the execution path used by the SQL optimizer and the statement execution statistics. The report is generated after successful SQL DML (that is, SELECT, DELETE, UPDATE and INSERT) statements. It is useful for monitoring and tuning the performance of these statements.

Controlling the Autotrace Report
You can control the report by setting the AUTOTRACE system variable.

Autotrace Setting Result
SET AUTOTRACE OFF No AUTOTRACE report is generated. This is the default.
SET AUTOTRACE ON EXPLAIN The AUTOTRACE report shows only the optimizer execution path.
SET AUTOTRACE ON STATISTICS The AUTOTRACE report shows only the SQL statement execution statistics.
SET AUTOTRACE ON The AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics.
SET AUTOTRACE TRACEONLY Like SET AUTOTRACE ON, but suppresses the printing of the user's query output, if any. If STATISTICS is enabled, query data is still fetched, but not printed.


To use this feature, you must create a PLAN_TABLE table in your schema and then have the PLUSTRACE role granted to you. DBA privileges are required to grant the PLUSTRACE role. For information on how to grant a role and how to create the PLAN_TABLE table, see the Oracle Database SQL Reference.

For more information about the roles and the PLAN_TABLE, see the Oracle Database SQL Reference and the AUTOTRACE variable of the SET command.

Example 8-1 Creating a PLAN_TABLE

Run the following commands from your SQL*Plus session to create the PLAN_TABLE in the HR schema:

CONNECT HR
@$ORACLE_HOME/rdbms/admin/utlxplan.sql
Table created.


Example 8-2 Creating the PLUSTRACE Role

Run the following commands from your SQL*Plus session to create the PLUSTRACE role and grant it to the DBA:

CONNECT / AS SYSDBA
@$ORACLE_HOME/sqlplus/admin/plustrce.sql

drop role plustrace;
Role dropped.


create role plustrace;
Role created.


grant plustrace to dba with admin option;
Grant succeeded.


Example 8-3 Granting the PLUSTRACE Role

Run the following commands from your SQL*Plus session to grant the PLUSTRACE role to the HR user:

CONNECT / AS SYSDBA
GRANT PLUSTRACE TO HR;
Grant succeeded.


Execution Plan
The Execution Plan shows the SQL optimizer's query execution path. Execution Plan output is generated using EXPLAIN PLAN and DBMS_XPLAN.

For information about interpreting the output of DBMS_XPLAN, see the Oracle Database Performance Tuning Guide.

Statistics
The statistics are recorded by the server when your statement executes and indicate the system resources required to execute your statement. The results include the following statistics.

Database Statistic Name Description
recursive calls Number of recursive calls generated at both the user and system level. Oracle Database maintains tables used for internal processing. When Oracle Database needs to make a change to these tables, it internally generates an internal SQL statement, which in turn generates a recursive call.
db block gets Number of times a CURRENT block was requested.
consistent gets Number of times a consistent read was requested for a block
physical reads Total number of data blocks read from disk. This number equals the value of "physical reads direct" plus all reads into buffer cache.
redo size Total amount of redo generated in bytes
bytes sent through SQL*Net to client Total number of bytes sent to the client from the foreground processes.
bytes received through SQL*Net from client Total number of bytes received from the client over Oracle Net.
SQL*Net round-trips to/from client Total number of Oracle Net messages sent to and received from the client
sorts (memory) Number of sort operations that were performed completely in memory and did not require any disk writes
sorts (disk) Number of sort operations that required at least one disk write
rows processed Number of rows processed during the operation


The client referred to in the statistics is SQL*Plus. Oracle Net refers to the generic process communication between SQL*Plus and the server, regardless of whether Oracle Net is installed. You cannot change the default format of the statistics report.

For a more complete list of database statistics, see the Oracle Database Reference. For more information about the statistics and how to interpret them, see Chapter 3, "Gathering Optimizer Statistics" in the Oracle Database Performance Tuning Guide.

Example 8-4 Tracing Statements for Performance Statistics and Query Execution Path

If the SQL buffer contains the following statement:

SELECT E.LAST_NAME, E.SALARY, J.JOB_TITLE
FROM EMPLOYEES E, JOBS J
WHERE E.JOB_ID=J.JOB_ID AND E.SALARY>12000;
The statement can be automatically traced when it is run:

SET AUTOTRACE ON
/
LAST_NAME SALARY JOB_TITLE
------------------------- ---------- -----------------------------------
King 24000 President
De Haan 17000 Administration Vice President
Kochhar 17000 Administration Vice President
Partners 13500 Sales Manager
Russell 14000 Sales Manager
Hartstein 13000 Marketing Manager
6 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2988506077
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 360 | 6 (17)| 00:00:01 |
|* 1 | HASH JOIN | | 6 | 360 | 6 (17)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| EMPLOYEES| 6 | 204 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| JOBS | 19 | 494 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."JOB_ID"="J"."JOB_ID")
2 - filter("E"."SALARY">12000)

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
706 bytes sent via SQL*Net to client
496 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed


Example 8-5 Tracing Statements Without Displaying Query Data

To trace the same statement without displaying the query data, enter:

SET AUTOTRACE TRACEONLY
/
6 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2988506077

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 360 | 6 (17)| 00:00:01 |
|* 1 | HASH JOIN | | 6 | 360 | 6 (17)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| EMPLOYEES| 6 | 204 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| JOBS | 19 | 494 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."JOB_ID"="J"."JOB_ID")
2 - filter("E"."SALARY">12000)

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
706 bytes sent via SQL*Net to client
496 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed


This option is useful when you are tuning a large query, but do not want to see the query report.

----------------------------------------------------------------------------------------------------------------

如何启用sqlplus的AutoTrace功能 转自:

http://www.eygle.com/faq/AutoTrace.htm

通过以下方法可以把Autotrace的权限授予Everyone,

如果你需要限制Autotrace权限,可以把对public的授权改为对特定user的授权。

D:oracleora92>sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on 星期二 6月 3 15:16:03 2003

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> connect sys as sysdba
请输入口令:
已连接。
SQL> @?rdbmsadminutlxplan

表已创建。

SQL> create public synonym plan_table for plan_table;

同义词已创建。

SQL> grant all on plan_table to public ;

授权成功。

SQL> @?sqlplusadminplustrce
SQL>
SQL> drop role plustrace;
drop role plustrace
*
ERROR 位于第 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_$session to plustrace;

授权成功。

SQL> grant plustrace to dba with admin option;

授权成功。

SQL>
SQL> set echo off



DBA用户首先被授予了plustrace角色,然后我们可以把plustrace授予public

这样所有用户都将拥有plustrace角色的权限.

SQL> grant plustrace to public ;

授权成功。



然后我们就可以使用AutoTrace的功能了.

SQL> connect eqsp/eqsp
已连接。
SQL> set autotrace on
SQL> set timing on
SQL>



关于Autotrace几个常用选项的说明:

SET AUTOTRACE OFF ---------------- 不生成AUTOTRACE 报告,这是缺省模式
SET AUTOTRACE ON EXPLAIN ------ AUTOTRACE只显示优化器执行路径报告
SET AUTOTRACE ON STATISTICS -- 只显示执行统计信息
SET AUTOTRACE ON ----------------- 包含执行计划和统计信息
SET AUTOTRACE TRACEONLY ------ 同set autotrace on,但是不显示查询输出

SQL> set autotrace traceonly
SQL> select table_name from user_tables;

已选择98行。

已用时间: 00: 00: 00.04

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 NESTED LOOPS
2 1 NESTED LOOPS (OUTER)
3 2 NESTED LOOPS (OUTER)
4 3 NESTED LOOPS (OUTER)
5 4 NESTED LOOPS (OUTER)
6 5 NESTED LOOPS
7 6 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'
8 7 INDEX (RANGE SCAN) OF 'I_OBJ2' (UNIQUE)
9 6 TABLE ACCESS (CLUSTER) OF 'TAB$'
10 9 INDEX (UNIQUE SCAN) OF 'I_OBJ#' (NON-UNIQUE)
11 5 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'
12 11 INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE)
13 4 INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE)
14 3 TABLE ACCESS (CLUSTER) OF 'USER$'
15 14 INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE)
16 2 TABLE ACCESS (CLUSTER) OF 'SEG$'
17 16 INDEX (UNIQUE SCAN) OF 'I_FILE#_BLOCK#' (NON-UNIQUE)
18 1 TABLE ACCESS (CLUSTER) OF 'TS$'
19 18 INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1389 consistent gets
0 physical reads
0 redo size
2528 bytes sent via SQL*Net to client
569 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
98 rows processed

SQL>


-The End-


本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/rudyMatrix/archive/2009/07/23/4373201.aspx

[@more@]

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22032845/viewspace-1026147/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2009-08-25

  • 博文量
    2
  • 访问量
    12768