Oracle9i中的性能优化方法和工具学习（三）

Oracle9i数据库中STATSPACK的使用

一．摘要
    本文讨论STATSPACK实用工具的优点和性能，同时描述了如何安装、使用、配置和管理这个实用工具。

 

二．概述
    STATSPACK实用工具在指定的时段中收集与单个或多个Oracle服务器性能相关的统计信息。STATSPACK实用工具的用途与BSTAT和ESTAT实用工具相类似，但是STATSPACK实用工具提供了更完全的功能。STATSPACK的功能显著表现在如下方面：
    1.自动计算比率，如每秒或者每事务的比率
    2.在持久表中存储统计信息，以进行长期比较
    3.从报告生成中分离数据收集
    4.在统计中包含回滚事务
    5.收集与耗费资源的SQL语句性能相关的数据

 

三．安装STATSPACK
    要安装STATSPACK实用工具，请使用SYSDBA 权限登录SQL*Plus并运行安装脚本ORACLE_HOME/rdbms/admin/spcreate。
    该安装脚本创建一个名为PERFSTAT的用户，其密码为“perfstat”，该用户拥有STATSPACK实用工具创建的所有对象，以及STATSPACK程序包本身。安装脚本自动分配特权给PERFSTAT用户，但是它会提示你为该用户指定默认和临时表空间。你应该使用一个非关键的表空间，如使用TOOLS作为默认表空间。如果你并不想让脚本提示你输入表空间名称，你可以在运行安装脚本前使用DEFINE语句指定它们。
    创建PERFSTAT用户后，安装脚本将创建STATSPACK实用工具所需的所有的表，然后创建STATSPACK软件包。你应该检查输出文件spcusr.lis、spctab.lis以及spcpkg.lis（它们是安装过程的日志文件）以确保安装过程没有发生错误。
    如果在字典管理表空间中安装STATSPACK实用工具，也许需要调整那些由STATSPACK对象占用的程序段的存储簇。

 

四.使用STATSPACK
    1.获取快照
        快照是在特定时间内得到的性能数据的集合。为了生成统计报告，STATSPACK实用工具需要比较两个快照。
        要得到一个快照，请以PERFSTAT身份登录SQL*Plus并执行STATSPACK.SNAP过程。这个操作将当前性能统计存储在STATSPACK 表中。在快照中包含实时信息是非常有用的。你可以通过设置TIMED_STATISTICS的初始化参数值为TRUE来实现。由于你需要得到多个快照来生成报告，因此在有规律的时间间隔中制定快照计划是十分有意义的。例如，你也许想每三小时收集一次性能统计。
        你可以使用操作系统实用工具或Oracle的DMBS_JOB过程来制定自动统计收集计划。在使用DMBS_JOB过程制定快照计划前，你需要确保JOB_QUEUE_PROCESSES初始化参数的值大于零。STATSPACK实用工具包含了一个样本脚本spauto.sql，该脚本制定了每小时一次快照的计划。你可以使用DMBS_JOB.INTERVAL过程来改变两个快照之间的间隔时间。例如，要每6小时获取一次快照，你可以输入以下代码来制定快照计划：
        EXECUTE DMBS_JOB.INTERVAL (1, 'SYSDATE+(1/4)');
        此例中，1/4是指要求的时间间隔为1/4天，即6小时。
    2.生成报告
    STATSPACK实用工具可以生成对两个快照的性能数据进行比较的报告。由于STATSPACK实用工具生成报告是独立于统计收集操作的，因此你可以使用任意两个快照来生成报告。这就允许你比较在得到快照的任何时段的性能统计。然而，如果数据库在两次快照之间被关闭，STATSPACK实用工具就会返回一个错误。
    STATSPACK实用工具生成两类报告：
    a. 常规实例状态报告
    b. SQL报告
    常规实例状态报告涵盖了实例性能的所有方面，而SQL报告则是检查指定的耗费资源较多的SQL语句的性能。对于评估整体的系统性能，常规实例状态报告已经足够了，但是它们有时显示出某些特定的SQL语句已经严重地影响了性能。在这种情况下，你需要生成这些语句的SQL报告。
    若要运行常规实例状态报告，要以PERFSTAT身份登录，并在ORACLE_HOME/rdbms/admin目录下运行spreport.sql 脚本。spreport.sql 脚本会提示输入要比较的两个快照的快照ID和报告的名称。如果不想让脚本提示你输入这些值，可以在运行安装脚本前使用DEFINE语句指定它们。若要运行一个SQL报告，请以PERFSTAT身份登录系统，并在ORACLE_HOME/rdbms/admin目录下运行sprepsql.sql 脚本。sprepsql.sql 脚本会提示输入要比较的两个快照的快照ID、要检查的SQL语句的散列值以及报告的名称。就像创建常规实例状态报告那样，可以选择在运行安装脚本前使用DEFINE语句指定这些值。

 

五．配置STATSPACK实用工具
    通过配置获取快照的级别和用来识别耗费资源的SQL语句的阈值，可以调整STATSPACK实用工具收集的数据数量，以及因此STATSPACK所需要的处理进程的数量。
    1.快照级别
    快照级别决定了快照包含的细节的数量。0至4级别之间的快照用来收集等待统计、加锁统计、行高速缓冲数据、回滚段数据、缓冲池统计、系统全局区域数据、后台事件、会话事件和父锁存统计。
    级别5的快照收集所有使用了比指定阈值更多资源的SQL语句的额外数据。级别5是默认的快照级别，它提供了细节与影响系统性能之间的平衡。
    在级别6至9之间的快照除了收集级别5快照包含的数据，还为耗费资源的SQL语句收集关于执行计划的信息。如果你正考虑为特定的语句改变执行计划，也许需要获取这些级别的快照。
    级别为10 或者高于10的快照收集与父锁存、子锁存相关的数据，以及级别6至9的快照收集的数据。高级别快照的获取过程是比较耗费资源的，因此你应该尽量避免使用不必要的高级别获取快照。
    当获取一个快照时，可以指定一个会话ID。这样生成的快照既包含特定会话统计，也包含基于实例的统计。
    若要仅为当前快照获取任何其它级别而非默认级别的快照，请在执行STATSPACK.SNAP过程时，指定所要求的级别。例如，你可以使用下面的代码来指定快照的级别为6：
    EXECUTE STATSPACK.SNAP(i_snap_level=>6);
    若要为所有的快照改变默认的快照级别，请使用I_MODIFY_PARAMETER来保存你已经指定的级别，如下所示：
    EXECUTE STATSPACK.SNAP(i_snap_level=>6, I_MODIFY_PARAMETER=>TRUE);
    你也能通过STATSPACK.MODIFY_STATSPACK_PARAMETER过程来改变默认的级别而无需获取快照。
    2.阈值
    默认情况下，耗费资源的SQL语句被定义为这样的语句：
    a.已经被执行100次以上
    b.已经做过1000次以上磁盘读取
    c.已经做过1000次以上解析调用
    d.已经做过1000次以上缓冲区获取
    e.已经有超过20的版本计数，或
    f.使用超过1Mb的可共享内存
    可以使用STATSPACK.MODIFY_STATSPACK_PARAMETER过程来改变这些默认的阈值。例如，下面这个语句将默认的快照级别改为6、磁盘读取阈值改为2000：
    EXECUTESTATSPACK.MODIFY_STATSPACK_PARAMETER(i_snap_level=>6,i_disk_reads_th=>2000);
    当前的快照级别和阈值存储在表STATS$STATSPACK_PARAMETER中。

 

六．管理STATSPACK实用工具
    因为STATSPACK实用工具监视系统性能需要使用一定的资源，所以要确保该工具没有给系统性能带来负面的影响。不管什么时候，只要在STATSPACK的表中的数据量有显著变化，你就应该在PERFSTAT用户所拥有的表与索引中收集更优化的统计。你可以使用DBMS_STATS和DBMS_UTILITY程序来实现。
    如果发现STATSPACK实用工具的资源使用量高出合理的范围——例如STATSPACK表中包含有许多旧的快照——这时应该通过清除旧的快照和截去旧的表来减少STATSPACK表中的数据。
    若要清除旧的快照，请使用sppurge.sql 脚本，同时指定清除的开始点与结束点的快照ID。若要截去旧的表，请使用sptrunc.sql 脚本。这些脚本都位于ORACLE_HOME/rdbms/admin目录下。若要运行这两个脚本，请以PERFSTAT身份登录系统。根据想要删除的数据的数量，你也许需要在数据库的低活跃期来执行这些脚本以防止危及系统的性能。

 

七．小结
    STATSPACK实用工具在指定的时段中收集与单个或多个Oracle服务器性能相关的统计信息。
    可以通过使用SYSDBA权限登录SQL*Plus并运行spcreate.sql 脚本来安装STATSPACK实用工具。spcreate.sql 脚本创建STATSPACK实用工具必需的所有的表和包，同时创建一个拥有这些表的用户——PERFSTAT用户。你需要为PERFSTAT用户提供用户信息。
    要使用STATSPACK实用工具，请以PERFSTAT用户的身份连接并运行STATSPACK.SNAP过程。这将创建当前数据库性能数据的一个快照。一旦创建了两个或两个以上的快照，就可以创建一个性能报告，这个报告对任意两个快照进行比较。spreport.sql 脚本生成一个常规实例完整报告，而sprepsql.sql 脚本则生成一个SQL报告，该SQL报告记录了那些导致较高资源使用率的特定的SQL语句。
    可以通过以下设置来配置STATSPACK实用工具收集的数据数量：设置默认的快照级别以及调整阈值。阈值决定了哪些SQL语句被记录为消耗较多资源的语句。
    需要监视和管理STATSPACK实用工具以确保它的监测活动（可能会比较浪费资源）不会影响数据库的性能。