sql培训

PL/SQL系列培训之三：PLSQL块、SQL优化

PL/SQL块

PL/SQL

如果不使用PL/SQL，ORACLE一次只能处理一条SQL语句，这样在性能上会产生很

大的开销。1988年，ORACLE公司发布ORACLE6，PL/SQL诞生。

PL/SQL的全称是“ProceduralLanguageextensionstotheStructuredQueryLanguage”。

ORACLE通过PL/SQL对标准SQL进行了扩展并提供了一个完整的系统实现方案。

PL/SQL块及其结构

PL/SQL块（PL/SQL块）是PL/SQL代码分组的最小单位。

PL/SQL块由四个部分组成：头部分，定义部分，可执行部分和异常处理部分。

头部分：

定义非匿名块的调用方式。只有非匿名块需要这部分，匿名块没有此部分。

定义部分：

定义将在可执行部分中调用的所有变量、常量、游标和用户自定义的例外处理。这部分

可以没有。

可执行部分：

包括对数据库中进行操作的SQL语句，以及对块中进行组织、控制的PL/SQL语句。

这部分必须存在。

异常处理部分：

对可执行部分中的语句，在执行过程中出错或出现非正常现象时所做的相应处理。这部

分可以没有。

PL/SQL块的分类

PL/SQL块存在以下分类：

（1）匿名块：没有命名的PL/SQL块

（2）嵌套块：可以通过使用嵌套块来控制程序的边界和变量的使用域。

（3）函数

（4）存储过程

（5）触发器

（6）包

PLSQL块的执行

PL/SQL是一种“解释型”的语言。PL/SQL编译器会将PL/SQL语句编译为机器代码，然后

在一个称为PL/SQL运行时引擎的虚拟机上运行。这个过程与JAVA的编译执行过程比较类

似。

PL/SQLCOMPILER：

PL/SQL编译器，它的工作可以分为两个阶段。

前一阶段解析PLSQL语句，产生一个树型结构以及相关语法、语义信息（称为DIANA）；

后一阶段进行编译，产生一个可在虚拟机上执行的PLSQL字节码。如果编译成功，将

字节码存放到一块共享内存区域中；否则，返回错误信息。

PL/SQLRUNTIMEENGINE

PL/SQL运行时引擎，也称PL/SQL虚拟机，简称PVM。

主要工作是执行PL/SQL字节码，根据情况去调用SQL引擎（注意这是另外一个概念！）。

PL/SQL调用图例1：

PL/SQL调用图例2（含SQL语句）：

图例中PL/SQL编译器拿到整个PL/SQL块之后，进行解析，然后发现一句SQL语句，

接着就向SQLCOMPILER（SQL解析器）发送解析请求，SQL解析器首先检查SQL语句

语法是否正确，然后去识别DUAL是一个表，dummy是这个表的一个列，此外还要进行权

限方面的检查。

接着，就要将解析后的PL/SQL块编译为可执行的代码。在这个过程中，SQL解析器会

计算并选择一个效率最高的执行计划，然后SQL编译器将执行计划发送给SQL执行引擎并

由SQL执行引擎去进行取数并返回给PL/SQL运行时引擎。

PL/SQL调用图例3（存储过程调用）：

PL/SQL编译器开始编译，但是它可以在ORACLE数据字典里面找到SP对应的DIANA

和字节码，因此编译器只需要解析并编译PL/SQL块本身而不需要解析或编译存储过程。

此外，一旦存储过程被调用，它的这些信息从磁盘中读取之后，ORACLE会将这些信

息存放在一块称为LibraryCache的内存区域中，这样进一步提高了整个执行过程的效率。

存储过程

（1）关于RETURN

终止SP执行并将控制权返回调用者。RETURN后面不需要跟任何表达式。

对于存储过程的可执行部分而言，RETURN不是必须的。

（2）关于参数

·参数定义

·参数的输入输出类型

·参数的默认值

·形式参数与实际参数

参数的定义

在存储过程中，我们将参数定义在头部分，而将变量定义在声明部分，除此之外，它们

还存在两个方面的差异：

·参数定义存在输入输出的模式设置，而变量定义没有

·参数定义不像变量定义那么严格：

变量定义需要根据数据类型指定类型长度，而参数定义只需要定义数据类型。

变量定义：

DECLARE

v_item_codeNVARCHAR2(10);

v_item_nameNVARCHAR2(10);

参数定义：

CREATEORREPLACEPROCEDUREtest_sp(pi_idNVARCHAR2)

参数的输入输出类型

参数存在三种输入输出类型：

输入输出

类型

传值/传引用可读/可写作用

IN（默认）传引用只可读在存储过程等PLSQL模块中可以使用传进

来的参数的值，而不能修改。

可以设置默认值。

OUT

传值可写/写后可

读

存储过程等PLSQL模块需要赋值给该参

数。事实上，在程序执行过程中，每次对

OUT参数的赋值实际上是对一个OUT参数

的本地内部拷贝进行赋值；当SP成功执行

结束且控制权回到调用者，存放在本地内

部拷贝中的值才会赋给OUT的实际参数。

注意事项：

（1）不能设置默认值；

（2）对应的实际参数必须为变量。

（3）如果模块中没有对该参数赋值，最终

该OUT参数的值为NULL。

（4）如果程序执行过程中发生异常且异常

没有处理，那么OUT参数对应的实际参数

的值将会保持调用PLSQL模块之前的值。

INOUT

传值可读也可写存储过程等PLSQL模块中可以读取该参数

的值，也可以修改该参数的值。

注意事项：

（1）不能设置默认值

（2）对应的实际参数必须为变量

（3）如果未对OUT参数赋值，对应的实

际参数将保留原值（这与OUT参数不同）

（4）如果程序执行过程中发生异常且异常

没有处理，与OUT参数一样，对应的实际

参数将保留调用前的值

举例，测试四种情况：

（1）没有异常的情况

（2）无异常，但是未对OUT参数赋值的情况

（3）有异常的情况

（4）有异常，但异常处理的情况

CREATEORREPLACEPROCEDUREtest_sp(

pi_paraINNVARCHAR2,

po_paraOUTNVARCHAR2,

pio_paraINOUTNVARCHAR2

)

IS

tanjie_exceptionEXCEPTION;

BEGIN

NULL;

po_para:='HELLOWORLD';

pio_para:='HELLOWORLD';

RAISEtanjie_exception;

EXCEPTION

WHENOTHERSTHEN

--NULL;

RAISE;

END;

调用：

DECLARE

v_in_paraNVARCHAR2(20);

v_out_paraNVARCHAR2(20);

v_inout_paraNVARCHAR2(20);

BEGIN

v_in_para:='HI';

v_out_para:='HI';

v_inout_para:='HI';

BEGIN

test_sp(v_in_para,v_out_para,v_inout_para);

EXCEPTION

WHENOTHERSTHEN

NULL;

END;

DBMS__LINE('IN:'||v_in_para||CHR(10)||

'OUT:'||v_out_para||CHR(10)||

'OUT:'||v_inout_para||CHR(10)

);

END;

参数的默认值

如上，可以对IN类型的参数指定默认值。

可以通过DEFAULT关键字或赋值操作符:=进行默认参数的设置。

举例：

CREATEORREPLACEPROCEDUREtest_sp(

pi_aINNVARCHAR2:='HELLOA',

pi_bINNVARCHAR2DEFAULT'HELLOB'

)

IS

BEGIN

DBMS__LINE('pi_a:'||pi_a||CHR(10)||

'pi_b:'||pi_b||CHR(10)

);

END;

调用：

BEGIN

test_sp('HIA','HIB');

test_sp('HIA');

test_sp();

END;

形式参数和实际参数

定义在存储过程头中的参数定义列表为形式参数。实际调用存储过程时传入的相应的值

或表达式称为实际参数。

形式参数：

CREATEORREPLACEPROCEDUREtest_sp(

pi_idNVARCHAR2--formparameter

)

实际参数：

BEGIN

test_sp('001');--actualparameter

END;

形式参数与实际参数的对应存在两种方式：

·通过位置对应：

通过实际参数的顺序位置隐含地与形式参数进行对应；

·通过名称对应：

通过显式指定形式参数的名称来与实际参数进行对应；

对应语法：formal_parameter_name=>argument_value

需要注意两点：

一是既然通过名称来对应，参数位置与顺序无关；

二是可以与位置对应方式混用，但是位置对应的必须放在名称对应的前面。

对应方式举例：

CREATEORREPLACEPROCEDUREtest_sp(

pi_person_idNVARCHAR2,

pi_dept_idNVARCHAR2

)

IS

BEGIN

DBMS__LINE('ID:'||pi_person_id||CHR(10)||

'DEPT:'||pi_dept_id||CHR(10)

);

END;

调用：

BEGIN

--通过位置进行对应

test_sp('001','A');

--通过名称进行对应

test_sp(pi_person_id=>'001',pi_dept_id=>'A');

--通过名称进行对应（说明与位置无关）

test_sp(pi_dept_id=>'A',pi_person_id=>'001');

--位置和名称对应的混用（注意按位置对应的参数需要放到按名称查找的参数前面）

test_sp('001',pi_dept_id=>'A');

END;

使用名称进行参数对应的方式值得推荐，理由如下：

（1）根据参数名称显式地参数对应，使程序更加明了

（2）如果参数列表中存在设置默认值的情况，使用名称对应的方式能够使得程序更加灵活。

NOCOPY参数

对于OUT或INOUT类型的参数，使用NOCOPY将改变传值方式为传引用方式。

这里不予赘述。

函数

（1）函数可以作为表达式来使用

（2）关于参数

参见存储过程的参数使用。

（3）关于RETURN

函数的头部分必须指定RETURN的数据类型。

函数的可执行部分中各条逻辑分支（包括异常处理部分）都必须RETURN一个值，这

个值的数据类型需要与HEAD中所指定的相匹配）

举例：

CREATEORREPLACEFUNCTIONTEST_FUNCTION(pi_paraNVARCHAR2)

RETURNNVARCHAR2

AS

tanjie_exceptionEXCEPTION;

BEGIN

IF(pi_para='OK')THEN

RETURN'HELLOWORLD';

ELSE

RAISEtanjie_exception;

ENDIF;

EXCEPTION

WHENOTHERSTHEN

RETURN'ERROR';

END;

调用：

BEGIN

DBMS__LINE('OK:'||TEST_FUNCTION('OK')||CHR(10)||

'ERR:'||TEST_FUNCTION('X')||CHR(10)

);

END;

触发器

当数据库中发生某种事件时自动触发的PL/SQL块称为触发器。

触发器常用类型：

（1）DML触发器

当一条记录被插入，修改或删除时触发。可以用来验证数据，设置默认值，审核数据变

更或拒绝某种类型的DML

（2）DDL触发器

当DDL执行时触发，例如表创建时触发。可以用来执行验证或阻止特定DDL发生。

（3）INSTEADOF触发器

INSTEADOF触发器是一种视图DML操作时触发用来阻止对视图的更新操作的触发

器。当DML操作发生时，不会执行这些DML语句，而是执行触发器中的语句

关于DML触发器

（１）BEFORE/AFTER

（２）STATEMENT-LEVEL/ROW-LEVEL

（３）NEW/OLD

（４）WHEN子句

CREATE[ORREPLACE]TRIGGERtriggername

{BEFORE|AFTER}

{INSERT|DELETE|UPDATE|UPDATEOFcolumnlist}ONtablename

[FOREACHROW]

[WHEN(...)]

[DECLARE...]

BEGIN

...executablestatements...

[EXCEPTION...]

END[triggername];

关于DML触发器的事务

（１）通常，DML触发器是触发它的事务的一部分

（２）如果DML触发器中抛出异常，主事务将回滚

（３）不能在DML触发器中进行COMMIT或ROLLBACK操作

（４）可以指定DML触发器的事务为自治事务

举例：

CREATEORREPLACETRIGGERtanjie_test_trigger

AFTERUPDATE

ONtanjie21

FOREACHROW

DECLARE

PRAGMAAUTONOMOUS_TRANSACTION;

BEGIN

INSERTINTOtanjie_history

VALUES(:,

:);

COMMIT;

END;

包

包是PL/SQL中的一个重要组件。包的一个重要特性就是区分公有和私有，通过隐藏不必要

的数据或操作，可以降低系统的复杂度。

包由包说明（PACKAGESPECIFICATION）和包体（PACKAGEBODY）两部分组成。包说

明和包体分开编译，并作为两个分开的对象存放在数据字典中。包不能嵌入在其他PL/SQL

块中。

包说明中说明了包中所有PUBLIC的元素，这些元素可以被包以外的程序所调用，称这些

元素为包的公有元素。包说明中不包含任何PL/SQL块或可执行代码。外界程序完全通过包

说明来获知包能够提供哪些功能以及如何调用它们。

包体中实现了所有在包说明中定义的元素；此外，包体中也可以包含没有出现在包说明中的

元素，它们对外界程序不可见，不能被外界程序所调用，因此称这些元素为包的私有元素，。

包的初始化部分：

在包体的尾部可以定义包的初始化部分。有些类似JAVA的构造函数，可以在包初始化

部分执行复杂的代码来进行整个包的初始化工作。需要注意的是，在一个会话中，包只会被

初始化一次。

包的初始化部分不是必须定义的。

举例：

包说明部分

CREATEORREPLACEPACKAGETANJIE_TEST_PKGIS

--非安全状态

STATE_NOTSAFECONSTANTCHAR(1):='N';

--安全状态

STATE_SAFECONSTANTCHAR(1):='S';

/**

*取得库房状态

*/

FUNCTIONGET_STATE(pi_warehou_idNVARCHAR2)

RETURNCHAR;

/**

*改变库房物料数

*/

PROCEDUREDO_ADD_SP(pi_warehou_idNVARCHAR2,

pi_numberNUMBER);

ENDTANJIE_TEST_PKG;

包体部分

CREATEORREPLACEPACKAGEBODYTANJIE_TEST_PKG

IS

--物料下限

LOWER_LIMITCONSTANTNUMBER:=0;

--物料安全储备

SAFE_LIMITCONSTANTNUMBER:=10;

--物料上限

UPPER_LIMITCONSTANTNUMBER:=100;

TYPEtype_warehou_listISTABLEOFNUMBERINDEXBYVARCHAR2(100);

--库房列表

glo_warehou_listtype_warehou_list;

/***********私有方法部分**********/

/**

*记录并处理异常

*/

PROCEDURELOGGER_ERROR(pi_errorNVARCHAR2)

IS

BEGIN

RAISE_APPLICATION_ERROR('-20050',pi_error);

END;

/**

*验证库房是否存在

*/

PROCEDUREVALIDATE_EXISTS(pi_warehou_idNVARCHAR2)

IS

BEGIN

IF(glo_warehou_(pi_warehou_id))THEN

LOGGER_ERROR('WAREHOUSENOTEXISTS:'||pi_warehou_id);

ENDIF;

END;

/**

*验证改变库房数目是否合理

*/

PROCEDUREVALIDATE_ADD_NUMBER(pi_warehou_idNVARCHAR2,

pi_numberNUMBER)

IS

v_now_numberNUMBER;

BEGIN

v_now_number:=glo_warehou_list(pi_warehou_id);

IF(v_now_number+pi_number

LOGGER_ERROR('LACKOFITEM:'||v_now_number);

ELSIF(v_now_number+pi_number>UPPER_LIMIT)THEN

LOGGER_ERROR('OVERFLOW:'||v_now_number);

ENDIF;

END;

/***********公有方法部分**********/

/**

*获取库房状态

*/

FUNCTIONGET_STATE(pi_warehou_idNVARCHAR2)

RETURNCHAR

IS

v_now_numberNUMBER;

BEGIN

VALIDATE_EXISTS(pi_warehou_id);

v_now_number:=glo_warehou_list(pi_warehou_id);

IF(v_now_number

RETURNSTATE_NOTSAFE;

ELSE

RETURNSTATE_SAFE;

ENDIF;

END;

/**

*改变库房物料数

*/

PROCEDUREDO_ADD_SP(pi_warehou_idNVARCHAR2,

pi_numberNUMBER)

IS

BEGIN

VALIDATE_EXISTS(pi_warehou_id);

VALIDATE_ADD_NUMBER(pi_warehou_id,pi_number);

glo_warehou_list(pi_warehou_id):=

glo_warehou_list(pi_warehou_id)+pi_number;

END;

BEGIN

glo_warehou_list('A'):=30;

glo_warehou_list('B'):=30;

EXCEPTION

WHENOTHERSTHEN

DBMS__LINE('INITEXCEPTION');

ENDTANJIE_TEST_PKG;

外部程序调用：

BEGIN

TANJIE_TEST__ADD_SP('A',-25);

IF(TANJIE_TEST__STATE('A')=

TANJIE_TEST__SAFE)THEN

DBMS__LINE('SAFE');

ELSE

DBMS__LINE('NOTSAFE');

ENDIF;

END;

关于包说明和包体的进一步说明：

（1）可以在包说明或包体中声明几乎所有类型的元素，包括数值、异常、集合等。但是建

议避免在包说明中声明变量。

（2）在包中定义公有的过程和函数时，需要在包说明中声明它们的头部分，在包体中来实

现它们。

（3）在包说明或包体中可以声明各种数据结构的TYPE，包括集合TYPE，RECORDTYPE，

游标变量TYPE等。

（4）可以在包中定义公有或私有的显式游标，ORACLE不支持在包中定义游标变量。但是

建议避免在包说明中声明显式游标。

（5）包体不总是必须的，但是如果在包说明中包含了过程、函数、显式游标的声明，则必

须定义包体。

（6）包体如果被定义，内部不可以为空，要么定义初始化部分，要么定义声明部分，二者

也可以都存在。

（7）包体的异常处理部分只处理所有由初始化部分抛出的异常

（8）外部程序调用包中的元素，需要使用PACKAGE_T_NAME的方式；

而包内部调用包中的元素，则不需要加包名。

关于包数据和会话持有的说明：

包数据指在包说明或包体内定义的常量或变量（注意不是在包方法里面的常量或变量）。

它们是包级别的数据，作用域为整个包。

会话第一次调用包（无论以任何形式引用包中的元素），ORACLE就会对包进行初始化

操作：实例化所有包数据并赋值，执行初始化部分的语句。

包一旦被加载，整个包都将保持在会话中。这样以后对包中的子程序进行调用，将会减

少I/O操作。包被会话加载的同时，包数据（注意不是在包方法里面的变量或常量）也将被

会话所持有。也就是说，如果向包中定义的变量赋值，那么该变量的值将在整个会话中被

保持。

包数据只能被当前会话所引用。但是ORACLE也提供了DBMS_PIPE包来实现在不同

会话之间共享数据的目的。

注意：如果包已经被会话所加载，如果重新编译包，那么会话再次调用包的时候将会报

ORA-04068错误。

SQL优化

SQL执行体系

SQL执行体系中包含以下主要组件：

解析器：语法解析，语义解析（对象是否存在，属性是否正确等）

优化器：通常SQL语句可以以很多种方式来执行，查询优化器从中选择出一个最优方案并

输出出来。存在两种优化方式：RBO和CBO。后者是ORACLE推荐的方式，前者已经在

逐渐放弃。

执行计划生成器：执行计划生成器所做的工作就是将优化器产生的最优方案转换为执行计

划。执行计划由一组ROWSOURCE以树型结构所组成。

SQL执行引擎：执行执行计划并产生查询结果。

执行计划

对于一句DML语句，ORACLE可能需要进行很多个步骤的操作。这些用于执行SQL语句

的若干步骤的组合即被称为执行计划。在执行计划中，包含了每一个表的访问路径以及表与

表之间的连接顺序和方式。

以下面的SQL语句为例来进一步了解执行计划。

SELECTemployee_id,job_id,salary,department_name

FROMemployees,departments

ment_id=ment_id

ANDNOTEXISTS(SELECT*

FROMjobs

BETWEENmin_salaryANDmax_salary);

执行计划为：

ORACLE会根据这个执行计划进行以下步骤的执行过程：

步骤3和6都是从数据表EMPLOYEES和JOBS中获取数据。

步骤5根据department_id在索引PK_DEPARTMENT_ID中进行查找并返回匹配行的行标识；

步骤4根据行标识再到数据表DEPARTMENTS中访问数据行数据。

步骤2执行了一个NESTEDLOOPS操作，将步骤3中的返回的行与步骤4中返回的行进行

连接，并将连接结果返回到步骤1。

步骤1执行了FILTER操作。它接收到步骤2返回的连接结果，然后将其与步骤6中返回的

结果进行对应，排除掉不符合条件的行，将剩下的行返回给步骤0。

数据访问方式

以下是常见的数据访问方式：

（1）TABLEFULLSCAN

全表扫描

（2）INDEXUNIQUESCAN

在表中创建UNIQUE索引，如果ORACLE认为根据该索引查询结果只会为0或1条记

录，此时ORACLE会采用INDEXUNIQUESCAN。这种查找方式即按照两分法在索引中树

型查找记录。

（3）INDEXRANGESCAN

如果ORACLE认为查询结果将会大于1条，会进行INDEXRANGESCAN，例如

UNIQUEINDEX中字段进行不完全匹配查找、非UNIQUEINDEX查找。与INDEXUNIQUE

SCAN相类似，也会进行树型方式查找。

（4）TABLEACCESSBYINDEXROWID

当发生索引扫描时，如果需要返回的字段都在索引上，则直接返回索引上的数据；而如

果还需要返回非索引上的字段的值，则需要根据从索引上查找的ROWID到表段中对应的数

据块上取数据，这个过程称为TABLEACCESSBYINDEXROWID

（5）INDEXFASTFULLSCAN

称为索引快速全扫描。与上面所述的两种索引扫描方式不同，它不是从索引树的根节点

开始读取，而是直接扫描索引的所有叶子节点。

数据关联方式

以下是三种常见的数据关联方式：

嵌套循环连接NESTEDLOOPJOIN

优化器将会指定一个驱动表，称为OUTERTABLE，另外一个表即INNERTABLE。

对于OUTERTABLE中的每一行，ORACLE会去到INNERTABLE中进行查找。

嵌套循环连接的主要消耗在从驱动表中逐条读取行数据并到INNERTABLE中进行匹配。

在执行计划中前面的表是驱动表。

哈希连接HASHJOIN

哈希连接用来处理大数据集的连接。优化器会使用小些的那个表（或结果集）在内存中创建

一个以连接字段作为KEY的HASH表，接着去扫描大表，根据大表的数据到HASH表中去

探测可以连接的数据行。哈希连接只能在等式连接下使用。

哈希连接的主要资源消耗在CPU和内存上，但是大多数情况下，效率更高。

在执行计划中前面的表是将要创建HASHTABLE的表。

排序合并连接SORTMERGEJOIN

在合并连接中，不存在驱动表的概念，优化器会首先对两个结果集进行排序操作（如果已经

排序就不再排序），然后进行合并操作。

合并连接的主要消耗在于需要将结果集完全读入内存并排序。

下面情况ORACLE可能会使用合并连接：

（1）两表连接方式为不等式连接

（2）两个较大数据集事先已经按照连接字段排序，优化器可能会认为与哈希连接相比，合

并连接的代价更小。

物理读与逻辑读

物理读

从磁盘上直接读取，发生物理读的可能情况如下：

·当数据块第一次被访问

·没有足够大的BUFFERCACHE导致数据块重新读入

·全表扫描

逻辑读

从内存中的BUFFERCACHE中读取数据块。SQL优化中应当尽可能降低逻辑读。

按照访问数据块的模式不同，可以分为即时读（CurrentRead）和一致性读（ConsistentRead）。

·即时读：

读取数据块当前的最新数据。任何时候在BUFFERCACHE中只有一份当前数据块。即

时读通常发生在对数据的删除、修改操作时。此时，进程会给数据加上行级锁并标识数据为

脏数据。

·一致性读：

Oracle是一个多用户系统。当一个会话开始读取数据还未结束读取之前，可能会有其他

会话修改它将要读取的数据。如果会话读取到修改后的数据，就会造成数据的不一致。一致

性读就是为了保证数据的一致性。在BufferCache中的数据块上都会有最后一次修改数据块

时的SCN。如果一个事务需要修改数据块中数据，会先在回滚段中保存一份修改前数据和

SCN的数据块，然后再更新BufferCache中的数据块的数据及其SCN，并标识其为脏数据。

当其他进程读取数据块时，会先比较数据块上的SCN和自己的SCN。如果数据块上的SCN

小于等于进程本身的SCN，则直接读取数据块上的数据；如果数据块上的SCN大于进程本

身的SCN，则会从回滚段中找出修改前的数据块读取数据。通常，普通查询都是一致性读。

统计信息

统计信息主要是描述数据库中表、索引的大小、规模、数据分布状况等的一类信息。基于

CBO的优化器正是根据这些统计信息，估算出不同数据访问方式、不同数据关联方式下各

种计划的成本，最后从中选择最优的计划去执行。

统计信息的收集

（1）当表数据发生重大修改时，如新增进大量的数据或者发生了许多数据的更新操作，应

当重新收集统计信息

（2）定期收集整个方案的统计信息。

（3）收集统计信息

例如：

收集某个表的统计信息：

EXECDBMS__TABLE_STATS('RS10','TEST_TAB');

收集某个表的统计信息，同时收集对表上所有索引的统计信息：

EXECDBMS__TABLE_STATS('RS10','TEST_TAB',CASCADE=>true);

收集某个用户所有对象的统计信息：

EXECDBMS__SCHEMA_STATS('RS10');

强烈建议定期收集整个方案（某个用户）下的所有统计信息！

需要注意的是，在收集统计信息的时候对数据库的使用会有一定的影响，因此建议选择合

适地时机进行统计信息的收集！

SQL诊断工具

（1）SETAUTOTRACEON

在SQL*PLUS中执行该PL/SQL命令，可以简单地查看每一句SQL的执行情况（包括

执行计划和物理读、逻辑读等统计信息）。

（2）SQLTRACEandTKPROF

·SQL>ALTERSESSIONSETSQL_TRACE=true;

可以在PL/SQLDeveloper中执行该SQL命令，将会在oracleproduct{oracle版本号}

admin{oracle_SID}udump目录下自动为当前会话（执行窗口）产生一个TRACE文件（后

缀.trc），则此后该会话执行的所有SQL的相关信息将被自动记录在该TRACE文件中。

如果打开这个TRACE文件查看，可能会发现看不懂，这样就需要借助ORACLE提供

的tkprof工具。

·CMD>tkproftracefileoutputfile

例如：打开命令提示符，到trace文件所在目录下执行：

tkprofdrs10_ora_

那么再打开这个文件，我们就可以看到每一条SQL的具体执行情况和执行计

划等信息

如下是一些TRACE信息的说明：

TRACE信息说明

Count

执行调用的次数

CPU

CPU使用的秒数

Elapd

总的持续时间

Disk

物理读数，通常由于缓存会比较低

Query

一致性读(consistentread)的逻辑读数，主要

是SELECT语句使用

Current

当前模式(currentmode)的逻辑读数，主要是

DML语句使用

Rows

处理的行数。如果是查询语句，即FETCH的

行数；如果是DML语句，即处理的行数。

对于SQLTRACE和TKPROF，根据具体的情况还有一些别的参数可以设置，可以自行查阅。

（3）STATSPACK

·安装Statspack：执行SQL>@进行安装，只需要安装一次

·手动抓取快照：SQL>

·产生报告：执行SQL>@

注：上述的两个.sql文件存放在oracleproduct{oracle版本号}db_1RDBMSADMIN目录下