Oracle Tuning性能调整的一些总结-技术开发专区

Oracle Tuning性能调整的一些总结

作者：a36040343 编辑：阿雪 2007-07-19 15:03

2. 应用优化
    下面我们从技术的角度入手，来探讨数据库优化方面的问题。通常作为优化Oracle系统的人，或者是DBA，其实很多时候对应用并不很了解甚至可以说是完全不了解，更不要说对应用程序代码的了解。事实上呢，一个系统运行的快或者慢相信大家都明白，第一重要的是数据库的设计，然后是应用的设计， SQL语句的编写，最后才是数据库参数的调整和硬件、网络的问题，等等。所以在我们不了解一个系统的时候来优化数据库应用不是一个轻松的容易的事情。那么我们第一步应该怎么做呢？
通常有两类方法：
    其中一个方法就是我们常用的，使用statspack来进行诊断系统的瓶颈所在。在statspack中oracle给出了几乎涵盖oracle大部分重要内容的信息。
    另外一种方式，就是trace session。假如某个session运行很慢或者某个用户的某个查询很慢，那么这个时候我们可以通过trace session的方式来诊断到底是慢在哪里，看究竟执行计划是怎样的，然后在user_dump_dest下根据该session的进程号或者线程号可以找到一个产生的trace文件。通过使用tkprof格式化文件之后我们就可以看见很多的统计信息，这里包括了执行计划、parse/fetch等步骤消耗cpu的时间。通常我们是观察query模式下的consistent gets来首先看sql是否使用了索引，然后看执行计划是不是正常，是不是有调整的余地。当然如果您没有实际做过的话，这些内容说起来很抽象。这是在不了解应用和程序下针对特定session的诊断和调整过程。
trace session的方式是一种自下而上的方法，从sql入手；而statspack是自顶向下的方法，也就是从宏观上先诊断数据库的瓶颈在哪里，然后从瓶颈入手来做调整，这个习惯上又可以称为通过等待事件（wait event）入手的方法。

2.1 使用statspack
    statspack是一个性能诊断工具，首先发布于Oracle8.1.6版本，在8.1.7版本中功能得到加强。Statspack除了查找实例中的性能问题外，还可以查找应用程序中高负荷的SQL语句，很容易确定Oracle 数据库的瓶颈所在，并且记录数据库性能状态。
在数据库中Statspack 的脚本位于$ORACLE_HOME/RDBMS/ADMIN 目录下，对于ORACLE8.1.6,是一组以stat 开头的文件；对于ORACLE8.1.7,是一组以sp 开头的文件。
在Statspack 发布之前，我们通常能够使用诊断数据库的工具是两个脚本UTLBSTAT.SQL 和UTLESTAT.SQL，BSTAT/ESTAT 是一个非常简单的性能诊断工具。UTLBSTAT 获得开始时很多V$视图的快照，UTLESTAT 通过先前的快照和当前视图生成一个报表。
    该报表实际上相当于statspack 中的两个采样点。
Statspack 通过连续的采样，能够给我们提供至关重要的趋势分析数据。这是一个巨大的进步。能够使用Statspack 的环境我们就尽量不要使用BSTAT/ESTAT 的方式来诊断数据库问题。

2.1.1 安装statapack
§ 步骤一：
    为了能够顺利安装和运行Statspack ，首先需要设置以下两个系统参数：
1. job_queue_processes
    为了能够建立自动任务，执行数据收集，该参数需要大于0。你可以在初试化参数文件中修改该参数(使该参数在重起后以然有效)。
    该参数可以在系统级动态修改(重起后失效)。

SQL> alter system set job_queue_processes = 6;
System altered

在Oracle9i 当中，可以指定范围，如 both,这样该修改在当前及之后保持有效(仅当你使用spfile 时，如果在9i 中仍然使用pfile，那么更改方法同8i 相同):

SQL> alter system set job_queue_processes = 6 scope=both;
System altered

2. timed_statistics
收集操作系统的计时信息，这些信息可被用来显示时间等统计信息、优化数据库和 SQL 语句。要防止因从操作系统请求时间而引起的开销，请将该值设置为False。
使用statspack 收集统计信息时建议将该值设置为 TRUE，否则收集的统计信息大约只能起到10%的作用，将timed_statistics 设置为True 所带来的性能影响与好处相比是微不足道的。
该参数使收集的时间信息存储在在V$SESSTATS 和V$SYSSTATS 等动态性能视图中。
timed_statistics 参数也可以在实例级进行更改

SQL> alter system set timed_statistics = true;
System altered

如果你担心一直启用timed_statistics 对于性能的影响，你可以在使用statspack 之前在system 更改，采样过后把该参数动态修改成false。

§ 步骤二：
需要单独为statspack创建一个存储数据的表空间，如果采样间隔较短，周期较长，打算长期使用，那么可能需要一个大一点的表空间，如果每个半个小时采样一次，连续采样一周，数据量是很大的。下面的例子中创建了一个500M 的测试表空间。
注意: 这里创建的表空间不能太小，如果太小的话创建对象会失败，建议至少建立100M 表空间。

SQL> create tablespace perfstat
2 datafile '/oracle/oradata/oradata/res/perfstat.dbf'
3 size 500M;
Tablespace created。

§ 步骤三：
在 sqlplus 中用internal 身份登陆，或者拥有SYSDBA(connect / as sysdba)权限的用户登陆。
注: 在Oracle9i 中，不存在internal 用户，可以使用sys 用户以sysdba 身份连接。
先转到$ORACLE_HOME/RDBMS/ADMIN 目录，检查安装脚本是否存在，同时我们执行脚本也可以方便些。

$ cd $ORACLE_HOME/rdbms/admin
$ ls -l sp*.sql
-rw-r--r--   1 oracle   other       1774 Feb 18  2000 spauto.sql
-rw-r--r--   1 oracle   other      62545 Jun 15  2000 spcpkg.sql
-rw-r--r--   1 oracle   other        877 Feb 18  2000 spcreate.sql
-rw-r--r--   1 oracle   other      31193 Jun 15  2000 spctab.sql
-rw-r--r--   1 oracle   other       6414 Jun 15  2000 spcusr.sql
-rw-r--r--   1 oracle   other        758 Jun 15  2000 spdrop.sql
-rw-r--r--   1 oracle   other       3615 Jun 15  2000 spdtab.sql
-rw-r--r--   1 oracle   other       1274 Jun 15  2000 spdusr.sql
-rw-r--r--   1 oracle   other       6760 Jun 15  2000 sppurge.sql
-rw-r--r--   1 oracle   other      71034 Jul 12  2000 spreport.sql
-rw-r--r--   1 oracle   other       2191 Jun 15  2000 sptrunc.sql
-rw-r--r--   1 oracle   other      30133 Jun 15  2000 spup816.sql
$

接下来我们就可以开始安装Statspack 了。在Oracle8.1.6 版本中运行statscre.sql; 在Oracle8.1.7 版本中运行spcreate.sql。
这期间会提示你输入缺省表空间和临时表空间的位置,输入我们为 perfstat 用户创建的表空间和你的临时表空间。安装脚本会自动创建perfstat 用户。

$ sqlplus
SQL*Plus: Release 8.1.7.0.0 - Production on Sat Jul 26 16:27:31 2003
(c) Copyright 2000 Oracle Corporation.  All rights reserved.
Enter user-name: internal
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production
SQL>
SQL> @spcreate
... Installing Required Packages
Package created.
Grant succeeded.
View created.
Package body created.
Package created.
Synonym dropped.
Synonym created.
……
Specify PERFSTAT user's default   tablespace
Enter value for default_tablespace: perfstat
Using perfstat for the default tablespace
User altered.
User altered.
Specify PERFSTAT user's temporary tablespace
Enter value for temporary_tablespace: temp
Using temp for the temporary tablespace
User altered.
NOTE:
SPCUSR complete. Please check spcusr.lis for any errors.
……

如果安装成功，你可以接着看到如下的输出信息：

….
Creating Package STATSPACK...
Package created.
No errors.
Creating Package Body STATSPACK...
Package body created.
No errors.
NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.

可以查看.lis 文件查看安装时的错误信息。

§ 步骤四：
如果安装过程中出现错误，那么可以运行spdrop.sql 脚本来删除这些安装脚本建立的对象。然后重新运行spcreate.sql来创建这些对象。

SQL> @spdrop
Dropping old versions (if any)
Synonym dropped.
Sequence dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
View dropped.
……
NOTE:
SPDUSR complete. Please check spdusr.lis for any errors.

（以上的安装过程描述是在 HP 11.11 + Oracle 8.1.7 平台上得到的）

2.1.2 测试statspack
运行statspack.snap 可以产生系统快照，运行两次，然后执行spreport.sql 就可以生成一个基于两个时间点的报告。
如果一切正常，说明安装成功。

SQL>execute statspack.snap
PL/SQL procedure successfully completed.
SQL>execute statspack.snap
PL/SQL procedure successfully completed.
SQL>@spreport.sql

可是有可能你会得到以下错误：

SQL> exec statspack.snap;
BEGIN statspack.snap; END;
*
ERROR at line 1:
ORA-01401: inserted value too large for column
ORA-06512: at "PERFSTAT.STATSPACK", line 978
ORA-06512: at "PERFSTAT.STATSPACK", line 1612
ORA-06512: at "PERFSTAT.STATSPACK", line 71
ORA-06512: at line 1

这是Oracle 的一个Bug，Bug 号1940915。
该Bug 自8.1.7.3 后修正。
这个问题只会出现在多位的字符集, 需要修改spcpkg.sql 脚本，$ORACLE_HOME/rdbms/admin/spcpkg.sql，将"substr" 修改为 "substrb"，然后重新运行该脚本。
该脚本错误部分：

select l_snap_id
, p_dbid
, p_instance_number
, substr(sql_text,1,31)
．．．．．．．．．．．

    substr 会将多位的字符, 当作一个byte.substrb 则会当作多个byte。在收集数据时， statpack 会将 top10 的 sql 前 31 个字节存入数据表中,若在SQL 的前31 个字有中文，就会出现此错误。
注意：运行 spcpkg.sql 也需要以 internal 用户登录 sqlplus

2.1.3 生成statspack报告
    调用spreport.sql 可以生成分析报告：
    当调用spreprot.sql 时，系统首先会查询快照列表，然后要求你选择生成报告的开始快照ID(begin_snap)和结束快照ID(end_snap),生成一个报告.
为了生成一个report,我们至少需要两次采样:

SQL> @spreport

   DB Id    DB Name      Inst Num Instance
-----------   ------------       -------- ------------
  2749170756 RES              1      res

Completed Snapshots

                           Snap                    Snap
Instance     DB Name         Id   Snap Started    Level Comment
------------ ------------ ----- ----------------- ----- ----------------------
res          RES              1 26 Jul 2003 16:36     5
                              2 26 Jul 2003 16:37     5
                              3 26 Jul 2003 17:03     5


Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap:2
Begin Snapshot Id specified: 2

Enter value for end_snap: 3
End   Snapshot Id specified: 3


Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_2_3.  To use this name,
press to continue, otherwise enter an alternative.
Enter value for report_name: rep0726.txt

……

End of Report

    在运行 spreport.sql 生成 statspack 报告的过程中，会有三个地方提示用户输入：
1、开始快照ID；
2、结束快照ID；
3、输出报告文件的文件名，缺省的文件名是sp__
    上面输入的开始快照ID是2，开始快照ID是3，输出报告文件的文件名是rep0726.txt
成功运行一次 statspack.snap 就会产生一个 snapshot ，在生成 statspack 报告的时候就可以看到这个 snap id 和 snap 运行的时间。运行 statspack.snap ，就是上面所说的采样，statspack 报告是分析两个采样点之间各种情况。

2.1.4 删除历史快照数据
    前面讲过，成功运行一次 statspack.snap 就会产生一个 snapshot ，这个 snapshot 的基本信息是存放在 PERFSTAT.stats$snapshot 表中的，生成 statspack报告时会查询该表的数据，供用户选择准备分析的 snapshot 。如果运行 statspack.snap 次数多了以后，该表的数据也会增加，历史数据会影响正常运行的效果，因此需要定时清理一下历史快照数据。
删除stats$snapshot 数据表中的相应数据，其他表中的数据会相应的级连删除：

SQL> select max(snap_id) from stats$snapshot;
MAX(SNAP_ID)
------------
166
SQL> delete from stats$snapshot where snap_id < = 166;
143 rows deleted

你可以更改snap_id 的范围以保留你需要的数据。
在以上删除过程中，你可以看到所有相关的表都被锁定。

SQL> select a.object_id,a.oracle_username ,b.object_name
from v$locked_object a,dba_objects b
where a.object_id = b.object_id
/
OBJECT_ID ORACLE_USERNAME OBJECT_NAME
------------------------------------- ---------------------------------------------------
156 PERFSTAT SNAP$
39700 PERFSTAT STATS$LIBRARYCACHE
39706 PERFSTAT STATS$ROLLSTAT
39712 PERFSTAT STATS$SGA
39754 PERFSTAT STATS$PARAMETER
39745 PERFSTAT STATS$SQL_STATISTICS
39739 PERFSTAT STATS$SQL_SUMMARY
39736 PERFSTAT STATS$ENQUEUESTAT
39733 PERFSTAT STATS$WAITSTAT
39730 PERFSTAT STATS$BG_EVENT_SUMMARY
39724 PERFSTAT STATS$SYSTEM_EVENT
39718 PERFSTAT STATS$SYSSTAT
39715 PERFSTAT STATS$SGASTAT
39709 PERFSTAT STATS$ROWCACHE_SUMMARY
39703 PERFSTAT STATS$BUFFER_POOL_STATISTICS
39697 PERFSTAT STATS$LATCH_MISSES_SUMMARY
39679 PERFSTAT STATS$SNAPSHOT
39682 PERFSTAT STATS$FILESTATXS
39688 PERFSTAT STATS$LATCH
174 PERFSTAT JOB$
20 rows selected

Oracle 还提供了系统脚本用于Truncate 这些统计信息表，这个脚本名字是: sptrunc.sql (8i、9i 都相同)
该脚本主要内容如下，里面看到的就是statspack 相关的所有系统表：

truncate table STATS$FILESTATXS;
truncate table STATS$LATCH;
truncate table STATS$LATCH_CHILDREN;
truncate table STATS$LATCH_MISSES_SUMMARY;
truncate table STATS$LATCH_PARENT;
truncate table STATS$LIBRARYCACHE;
truncate table STATS$BUFFER_POOL_STATISTICS;
truncate table STATS$ROLLSTAT;
truncate table STATS$ROWCACHE_SUMMARY;
truncate table STATS$SGA;
truncate table STATS$SGASTAT;
truncate table STATS$SYSSTAT;
truncate table STATS$SESSTAT;
truncate table STATS$SYSTEM_EVENT;
truncate table STATS$SESSION_EVENT;
truncate table STATS$BG_EVENT_SUMMARY;
truncate table STATS$WAITSTAT;
truncate table STATS$ENQUEUESTAT;
truncate table STATS$SQL_SUMMARY;
truncate table STATS$SQL_STATISTICS;
truncate table STATS$SQLTEXT;
truncate table STATS$PARAMETER;
delete from STATS$SNAPSHOT;
delete from STATS$DATABASE_INSTANCE;
commit;

2.1.5 一些重要脚本
1．通过导出保存及共享数据
在诊断系统问题时，可能需要向专业人士提供原始数据，这时我们可以导出Statspack 表数据，
其中我们可能用到：spuexp.par
其内容主要为：

file=spuexp.dmp log=spuexp.log compress=y grants=y indexes=y rows=y constraints=y 
owner=PERFSTAT consistent=y

我们可以导出如下：

exp userid=perfstat/my_perfstat_password parfile=spuexp.par

2．删除数据
spdrop.sql 在执行时主要调用两个脚本: spdtab.sql 、spdusr.sql
前者删除表及同义词等数据，后者删除用户

3．Oracle92 中新增加的脚本
1）用于升级statspack 对象的脚本,这些脚本需要以具有SYSDBA 权限的用户运行, 升级前请先
备份存在的Schema 数据:
spup90.sql: 用于升级9.0 版本的模式至9.2 版本。
spup817.sql: 如果从Statspack 8.1.7 升级,需要运行这个脚本
spup816.sql: 从Statspack 8.1.6 升级,需要运行这个脚本，然后运行spup817.sql
2） sprepsql.sql 用于根据给定的SQL Hash 值生成SQL 报告

2.1.6 调整statspack的收集门限
Statspack 有两种类型的收集选项：

1．级别（level）：控制收集数据的类型
Statspack 共有三种快照级别，默认值是5
a. level 0: 一般性能统计。包括等待事件、系统事件、系统统计、回滚段统计、行缓存、SGA、会话、锁、缓冲池统计等等。
b. level 5: 增加SQL 语句。除了包括level0 的所有内容，还包括SQL 语句的收集，收集结果记录在stats$sql_summary 中。
c. level 10: 增加子锁存统计。包括level5 的所有内容。并且还会将附加的子锁存存入stats$lathc_children 中。在使用这个级别时需要慎重，建议在Oracle support 的指导下进行。
可以通过statspack 包修改缺省的级别设置
SQL>execute statspack.snap(i_snap_level=>0,i_modify_parameter=>’true’);
通过这样的设置，以后的收集级别都将是0 级。
如果你只是想本次改变收集级别，可以忽略i_modify_parameter 参数。
SQL>execute statspack.snap(i_snap_level=>10);

2．快照门限：设置收集的数据的阈值。
快照门限只应用于stats$sql_summary 表中获取的SQL 语句。
因为每一个快照都会收集很多数据，每一行都代表获取快照时数据库中的一个SQL 语句，所以stats$sql_summary 很快就会成为Statspack 中最大的表。
门限存储在stats$statspack_parameter 表中。让我们了结一下各种门限：
a. executions_th 这是SQL 语句执行的数量(默认值是100)
b. disk_reads_tn 这是SQL 语句执行的磁盘读入数量（默认值是1000）
c. parse_calls_th 这是SQL 语句执行的解析调用的数量（默认值是1000）
d. buffer_gets_th 这是SQL 语句执行的缓冲区获取的数量（默认值是10000）
任何一个门限值超过以上参数就会产生一条记录。
通过调用statspack.modify_statspack_parameter 函数我们可以改变门限的默认值。
例如：

SQL>execute statspack.modify_statspack_parameter(i_buffer_gets_th=>100000
,i_disk_reads_th=>100000;

第1页：SGA的设置第2页：应用优化第3页：对statspack报告的分析第4页：基于成本的优化器技术内幕

关注我们