oracle 10g statspack学习

oracle 10g statspack学习

原文：http://hi.baidu.com/ybgba/blog/item/b6d0d1232c85b24693580704.html
-- statspack
1.使用SYS用户登陆
2.创建STATSPACK的表空间,必须大于200M
SQL> CREATE TABLESPACE PERFSTAT DATAFILE '/opt/ora_data/tablespaces/test_perfstat.dbf' size 500M;
3.执行$ORACLE_HOME/RDBMS/spcreate.sql创建STATSPACK,这里会要求输入默认表空间和默认临时表空间
SQL> @SPCREATE.SQL
4.执行snap
SQL> EXECUTE STATSPACK.SNAP;
SQL> EXECUTE STATSPACK.SNAP;
SQL> SELECT * FROM V$SNAPSHOT; -- 可以查看到执行的SNAP_ID
5.执行$ORACLE_HOME/RDBMS/spreport.sql生成报表，这里会要求输入开始SNAP_ID和结束SNAP_ID
SQL> @spreport.sql
6.在当前文件夹下生成*.lst的性能跟踪文件
7.SQL> select * from stats$snapshot; -- 所有SNAP信息都会存放在这里，必须经常删除过期的数据，否则影响性能
8.spdrop.sql -- 用于删除安装好的statspack，调用spdtab.sql 、spdusr.sql
9.spuexp.par -- 导出保存及共享数据
$ exp userid=ybgba/yangbiao parfile=spuexp.par -- 运行成功后在在当钱文件夹下生成spuexp.dmp和spuexp.log两个文件
10.spup*.sql -- 用于升级statspack
11.调整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;
12.对statspack报告的分析
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)内存信息分析
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；