第九章 sqlplus的调优

一、跟踪语句

1、自动跟踪报告

Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]

前提：自己的用户下用命令@$ORACLE_HOME/RDBMS/ADMIN/UTLXPLAN.SQL创建plan_table，并由dba权限用户授予plustrace角色

2、执行计划
执行计划包括四个字段：
ID_PLUS_EXP：   Shows the line number of each execution step.
PARENT_ID_PLUS_EXP： Shows the relationship between each step and its parent.This column is useful for large  reports.
PLAN_PLUS_EXP：  Shows each step of the report.
OBJECT_NODE_PLUS_EXP： Shows database links or parallel query servers used.

通过命令column parent_id_plus_exp noprint 可以隐藏该列。

3、统计信息
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

按照需要可以合理选择set autot的选项。通常来说，查询大量数据时，考虑set autot on traceonly

二、收集时间信息

set timing on 显示一条语句或一个语句块的执行时间
set timing off
clear timing

三、并行和分步式查询的跟踪

对于并行或分布式的查询，执行计划显示的成本是基于优化器对行数评估而得到的。通常统计结果是累计值。
含有星号的行标识。

执行计划中有三列：
ID_PLUS_EXP ：Shows the line number of each execution step.
OTHER_TAG_PLUS_EXP：Describes the function of the SQL statement in the OTHER_PLUS_EXP column.
OTHER_PLUS_EXP：Shows the text of the query for the parallel server or remote database

就统计信息来说，如果consistent gets或physical reads的值相对于其所返回的值来说比较高，表明查询代价很高，可能需要优化。可以使用tkprof或v$sql视图进行监控。

四、sqlplus脚本的调优

1.column noprint
用法 COLUMN column_name NOPRINT
表示不显示某一列信息。可以用在排除少数几个不需要的列显示的情况。该命令是将指定的列查出来了但不显示，而如果在select

中没有这一列，则是根本就不对该列进行查询。

2.set appinfo off
设置脚本通过dbms_application_info的注册，该包用于监控脚本的性能和资源使用情况，off表示关闭了sqlplus对该包的内部调

用。

3.set arraysize
设置sqlplus每次从数据库抽取数据的行数，范围从1到5000
当前的几个版本的数据库，该值的设置影响不大。如果这个值过大，会花费更多的sqlplus内存，降低性能。

4.set define off
禁止用命令设置 用值代替变量

5.set flush off
允许操作系统的缓存输出。
on禁止缓存输出到屏幕。
如果运行的脚本不需要用户交互，可以在执行结束后再看到，就可以设置为off。

6.set linesize
设置每行的字符数
尽量将此值设的小一点，以避免额外的内存分配；但linesize太小，列值不在一行显示，也会降低性能

7.set longchunksize
设置sqlplus在输出CLOB、LONG、NCLOB、XML类型数据时的大小。

8.set pagesize
设置每页的行数。增加该值可以避免频繁显示页眉信息，设置为0会阻止页眉显示。

9.set serveroutput
off将禁止dbms_output.put_line的输出显示，在内部不会调用dbms_output包

10.set sqlprompt
设置sqlplus的提示符，默认是“SQL>”。iSQLplus不支持

11.set tab
设置输出的结果中空格的格式。set tab on后多个空格被压缩。除了减少数据写以外，该命令的使用弊大于利。

12.set termout
set termout off 不会将结果显示在屏幕上，如果既不需要显示结果，也不需要将结果spooling到文件，可以用该命令。

13.set trimout on/set trimspool on
去掉结果结尾的空格或空行

14.undefine
删除不必要的替代变量，减少了对‘&’、new_value、old_value调用时的时间花费。