Oracle Tuning (Oracle 性能调整)的一些总结 3

最新推荐文章于 2024-11-10 22:49:00 发布

lanmh

最新推荐文章于 2024-11-10 22:49:00 发布

阅读量122

点赞数

分类专栏： Oracle 文章标签： Oracle SQL 脚本数据结构设计模式

Oracle 专栏收录该内容

145 篇文章 0 订阅

订阅专栏

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;

2.2 对statspack报告的分析
从上面的描述可以看出，产生一个statspack报告是比较简单的，但是如何读懂statspack报告却不是那么容易，需要对Oracle的体系架构、内存结构、等待事件以及应用系统有充分的了解，加上不断的实践，才能基本读懂statspack报告并且从报告中找到调整优化Oracle的途径。
下面接合一个实际的statspack报告，大致分析一下。

2.2.1 基本信息分析
DB Name         DB Id    Instance     Inst Num Release     OPS Host
------------ ----------- ------------ --------          ----------- ---      --------- ---
RES           2749170756 res                 1 8.1.7.0.0   NO res

                Snap Id     Snap Time      Sessions
                ------- ------------------ --------
Begin Snap:          2 26-Jul-03 16:37:08       38
   End Snap:          3 26-Jul-03 17:03:23       38
    Elapsed:                  26.25 (mins)

Statspack报告首先描述了数据库的基本情况，比如数据库名、实例名、实例个数、oracle版本号等等；然后是该报告的开始快照和结束快照的信息，包括 snap id , snap time 等等；最后是该报告经过的时间跨度，单位是分钟(mins)。

Cache Sizes
~~~~~~~~~~~
db_block_buffers: 61440 log_buffer: 163840
db_block_size: 8192 shared_pool_size: 52428800

然后描述了Oracle内存结构中几个重要的参数。

2.2.2 内存信息分析
Load Profile
~~~~~~~~~~~~                       Per Second       Per Transaction
                                   ---------------       ---------------
              Redo size:              4,834.87             11,116.67
          Logical reads:                405.53                932.43
          Block changes:                 60.03                138.02
          Physical reads:                138.63                318.75
          Physical writes:                 54.27                124.79
          User calls:                     62.69                144.13
          Parses:                        19.14                 44.00
          Hard parses:                    2.26                  5.20
                  Sorts:                  1.83                  4.20
                 Logons:                  0.21                  0.47
               Executes:                 21.10                 48.50
            Transactions:                  0.43

% Blocks changed per Read: 14.80 Recursive Call %: 34.45
Rollback per transaction %: 0.00 Rows per Sort: 20.57

Redo size: 是日志的生成量，分为每秒和每事务所产生的，通常在很繁忙的系统中日志生成量可能达到上百k，甚至几百k；

Logical reads: 逻辑读实际上就是logical IO=buffer gets表示的含义，我们可以这样认为，block在内存中，我们每一次读一块内存，就相当于一次逻辑读；

Parses 和 Hard parses: Parse 和 hard parse通常是很容易出问题的部分，80%的系统的慢都是由于这个原因所导致的。
所谓parse分soft parse 和hard parse，soft parse是当一条sql传进来后，需要在shared pool中找是否有相同的sql，如果找到了，那就是soft parse，如果没有找着，那就开始hard parse，实际上hard parse主要是检查该sql所涉及到的所有的对象是否有效以及权限等关系，hard parse之后才根据rule/cost模式生成执行计划，再执行sql。
而hard parse的根源，基本都是由于不使用bind var所导致的，不使用bind var违背了oracle的shared pool的设计的原则，违背了这个设计用来共享的思想，这样导致shared_pool_size里面命中率下降。因此不使用bind var，将导致cpu使用率的问题，极有使得性能急剧下降。
还有就是为了维护internal structure，需要使用latch，latch是一种Oracle低级结构,用于保护内存资源，是一种内部生命周期很短的lock，大量使用latch将消耗大量的cpu资源。

Sorts: 表示排序的数量；

Executes: 表示执行次数；

Transactions: 表示事务数量；

Rollback per transaction %: 表示数据库中事务的回退率。如果不是因为业务本身的原因，通常应该小于10%为好，回退是一个很消耗资源的操作。

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
           Buffer Nowait %: 100.00       Redo NoWait %:   99.98
           Buffer Hit   %:   65.82    In-memory Sort %:   99.65
           Library Hit   %:   91.32        Soft Parse %:   88.18
         Execute to Parse %:    9.28         Latch Hit %:   99.99
Parse CPU to Parse Elapsd %:   94.61     % Non-Parse CPU:   99.90

Buffer Hit %: 数据缓冲区命中率，通常应该大于90%；

Library Hit %: libaray cache的命中率，通常应该大于98%；

In-memory Sort %: 排序在内存的比例，如果这个比例过小，可以考虑增大sort_area_size，使得排序在内存中进行而不是在temp表空间中进行；

Soft Parse %: 软解析的百分比，这个百分比也应该很大才好，因为我们要尽量减少hard parse。 soft parse 百分比=soft/(soft+hard)；

Execute to Parse %: 这个数字也应该是越大越好，接近100%最好。有些报告中这个值是负的，看上去很奇怪。事实上这表示一个问题，sql如果被age out的话就可能出现这种情况，也就是sql老化，或执行alter system flush shared_pool等。

Shared Pool Statistics          Begin   End
                             ------   ------
         Memory Usage %:    90.63   87.19
   % SQL with executions>1:   71.53   75.39
% Memory for SQL w/exec>1: 59.45   65.17

% SQL with executions>1: 这个表示SQL被执行次数多于一次的比率，也应该大为好，小则表示很多sql只被执行了一次，说明没有使用bind var；