statspack的一些使用技巧

1 如何修改statspack的脚本产生自定义报表？
2 如何用statspack的报表确定热表及索引？
3 如何用statspack的报表确定keep池与default池的分配？
4 如何用crontab定期产生statspack的报表？

使用statspack有相当长的时间啦,从最初的推崇,到后来的否定,
再到现在的肯定,发现它已经是我工作中不可缺少的一部分,
每晚上读当天产生的statspack报表就成了一种习惯,
越是读得认真,越是觉得statspack妙用无穷,
现在本人就将一些心得告诉大家,希望对你有所帮助。

一 如何修改statspack的脚本产生自定义报表？
通常statspack报表可以满足大部分的需要,有时我们需要对产生报表的脚本
进行一些微小的修改,这样产生的报表就更有用途啦.
比如说某些SQL很多,但在statspack产生的报表中,每个SQL只显示5行,
结果有些比较长的SQL就只能看到一部分;
又如在top events部分,标准的报表只显示top 5,
其实我们可以显示更多的events,那如何修改呢？
用编辑工具(在linux下用vi)打开$ORACLE_HOME/rdbms/admin/sprepins.sql
define top_n_events = 5;      // top 5 events
define top_n_sql = 65;        // top sql
define top_n_segstat = 5;     // top 5 segstat
define num_rows_per_hash=5;   // 每个SQL显示5行
就看到在该脚本中已经定义了一些常数,我们只需要把它改为我们需要的值就可以啦.
define top_n_events = 10;     // top 10 events    
define top_n_sql = 65;    // top sql         
define top_n_segstat = 10;  // top 10 segstat   
define num_rows_per_hash=10; // 每个SQL显示10行  
做过修改后,然后大家就可以看到自己要的效果啦.

二 如何用statspack的报表确定热表及索引？
如果想用statspack表确定热表及索引,必须修改statspack快照的收集级别,
8i中statspack共有三种快照级别,默认值是5
select * from STATS$level_DESCRIPTION;
SNAP_LEVEL DESCRIPTION
---------- ----------------------------------------------------------------
         0 一性性能统计:包含回退段状态、字典缓存、SGA、系统事件、后台事件、会话事件、
      系统统计、等待统计、锁统计、闩锁统计
         5 增加了收集SQL的信息、并包括0级收集的信息.
        10 增加了收集子闩锁的信息,并包括所有低级别的信息
在9i中statspack共有五种快照级别,默认值是5
select * from STATS$level_DESCRIPTION;
SNAP_LEVEL DESCRIPTION
---------- ----------------------------------------------------------------
         0 一性性能统计:包含回退段状态、字典缓存、SGA、系统事件、后台事件、会话事件、
      系统统计、等待统计、锁统计、闩锁统计
         5 增加了收集SQL的信息、并包括0级收集的信息.
         6 增强了在SQL收集信息方面的功能(列出占用资源较高的SQL),并包所有低级别的信息
         7 增加了收集段级别的统计信息(如段的逻辑读与物理读、行锁、ITL及buffer busy waits),
      并包括所有低级别的信息
        10 增加了收集子闩锁的信息,并包括所有低级别的信息
如果你收用statspack确定热表及热索引,那就需要使用7/10的级别来收集快照。
//通过这样的设置，以后的收集级别都将是7级。
//如果你只是想本次改变收集级别，可以忽略i_modify_parameter参数。
SQL>execute statspack.snap(i_snap_level=>7,i_modify_parameter=>true);
SQL>execute statspack.snap(i_snap_level=>7);
修改完收集级别后,那大家就可以根据自己的需要设定收集的频率,
现在我们只需要注意statspack报表中的“段级别的统计信息”:
Top 5 Logical Reads per Segment for DB: ESAL  Instance: esal  Snaps: 2368 -2380
-> End Segment Logical Reads Threshold:     10000
                                           Subobject  Obj.       Logical
Owner      Tablespace Object Name          Name       Type         Reads  %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
CYBERCAFE  TS_CYBERCA AGENT_CARD_TYPE                 TABLE  115,220,864   18.07
CYBERCAFE  TS_CYBERCA GAME_CARD_TYPE                  TABLE   79,103,600   12.40
CYBERCAFE  TS_CYBERCA AGENT_TASK                      TABLE   57,030,304    8.94
CYBERCAFE  TS_CYBERCA AGENT_PRICE_LEVEL_OW            TABLE   46,393,968    7.28
CYBERCAFE  TS_CYBERCA IDX_ASL_RESLOG_ID               INDEX   23,261,600    3.65
-------------------------------------------------------------
Top 5 Physical Reads per Segment for DB: ESAL  Instance: esal  Snaps: 2368 -2380
-> End Segment Physical Reads Threshold:    1000
                                           Subobject  Obj.      Physical
Owner      Tablespace Object Name          Name       Type         Reads  %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
CYBERCAFE  TS_CYBERCA AGENT_GAME_CARD_GM13            TABLE       76,476    7.36
CYBERCAFE  TS_CYBERCA AGENT_SALE_LOG       ASL_200500 TABLE       61,270    5.89
CYBERCAFE  TS_CYBERCA RESELLER_LOG         RL_200412  TABLE       48,950    4.71
CYBERCAFE  TS_CYBERCA AGENT_GAME_CARD_GM14            TABLE       46,259    4.45
CYBERCAFE  TS_CYBERCA AGENT_CAPITAL_LOG    ACL_200500 TABLE       45,476    4.37
-------------------------------------------------------------
Top 5 Buf. Busy Waits per Segment for DB: ESAL  Instance: esal  Snaps: 2368 -2380
-> End Segment Buffer Busy Waits Threshold:     100
                                           Subobject  Obj.   Buffer Busy
Owner      Tablespace Object Name          Name       Type         Waits  %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
CYBERCAFE  TS_CYBERCA AGENT_TASK                      TABLE           22   36.07
CYBERCAFE  TS_CYBERCA AGENT_CARD_TYPE                 TABLE            9   14.75
CYBERCAFE  TS_CYBERCA IDX_RESACC_UPDTIME              INDEX            5    8.20
CYBERCAFE  TS_CYBERCA AGENT_SALE_LOG       ASL_200501 TABLE            4    6.56
CYBERCAFE  TS_CYBERCA IDX_ACL_ACPITAL_LOGI            INDEX            4    6.56
-------------------------------------------------------------
Top 5 Row Lock Waits per Segment for DB: ESAL  Instance: esal  Snaps: 2368 -2380
-> End Segment Row Lock Waits Threshold:     100
                                           Subobject  Obj.      Row Lock
Owner      Tablespace Object Name          Name       Type         Waits  %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
CYBERCAFE  TS_CYBERCA IDX_APL_GCTID2       APL_200501 INDEX           54   24.77
CYBERCAFE  TS_CYBERCA IDX_RB_RESELLER_ID              INDEX           41   18.81
CYBERCAFE  TS_CYBERCA IDX_RL_RESLOG_ID                INDEX           38   17.43
CYBERCAFE  TS_CYBERCA IDX_ACT_ACT_ID                  INDEX           17    7.80
CYBERCAFE  TS_CYBERCA IDX_SERVICE_ID                  INDEX           14    6.42
-------------------------------------------------------------
在这里可以看到逻辑读/物理读/缓存忙/行锁符合条件的一些对象,通过这些对象,
可以确定热的表及索引,然后分析如何对业务进行优化,降低对这些表的访问量等。
如果你觉得显示top 5 segment不够的话,可以按一所述修改top_n_segstat,
然后就可以显示更多的符合条件的对象,然后将这些热表放到keep池中。

三 如何用statspack的报表确定keep池与default池的分配？
如果你想使用default池与keep池,在9i中需要分配db_cache_size及
db_keep_cache_size参数,但如何确定它们的大小呢？我们可以根据2所示的一些
热表,计算热表放入keep池需要的内存,然后用将表放入相应的pool中.
alter table &table_name storage(buffer_pool &buffer_pool);
将确定的热表放入keep中之后,然后收集一段时间后再产生一个新的报表:
Buffer Pool Statistics for DB: ESAL  Instance: esal  Snaps: 2277 -2289
-> Standard block size Pools  D: default,  K: keep,  R: recycle
-> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k
                                                           Free    Write  Buffer
     Number of Cache      Buffer    Physical   Physical  Buffer Complete    Busy
P      Buffers Hit %        Gets       Reads     Writes   Waits    Waits   Waits
--- ---------- ----- ----------- ----------- ---------- ------- --------  ------
D      128,128  99.7 482,298,597   1,557,980    265,662       0        0      88
K       32,032 100.0 372,560,023      13,951     42,405       0        0      17
-------------------------------------------------------------
确定keep池与default的需要内存时,可以根据这一部分对keep池与default池的大小进行评估,
如果K所标识的cache hit%比较小,说明keep池不足,如果D显示的cache hit%比较小,说明default池
分配怀足,如果K是的default显示是100%,那们可以将更多的热表放入到keep池中，
然后经过一段时间的调整,相信可以将default池与keep池调到一个相对比较合适的集团。

四 如何用crontab定期产生statspack的报表？
看了一段时间的statspack报表后,就懒于每天手工去产生一个报表,那如何产系统自动产生一个报表呢?
经过测试,用crontab可以方便地产生报表,然后通过sendmail直接发到相关人员的邮箱中.
[oracle@www1 sql]$ more backup/auto_send_perf.sh
#!/bin/sh
. ~oracle/.bash_profile

/home/oracle/product/9.2.0/bin/sqlplus -s <set head off
set timing off
spool /home/oracle/sql/backup/snap_begin.lst
select min(snap_id) snap_id
 from stats$snapshot
where snap_time between trunc(sysdate) and trunc(sysdate)+1;
spool off
spool /home/oracle/sql/backup/snap_end.lst
select max(snap_id) snap_id
 from stats$snapshot
where snap_time between trunc(sysdate) and trunc(sysdate)+1;
spool off
exit
!

BEGIN_SNAP=`cat /home/oracle/sql/backup/snap_begin.lst | tail -n 2`
END_SNAP=`cat /home/oracle/sql/backup/snap_end.lst | tail -n 2`
#END_SNAP=`expr $BEGIN_SNAP + 13`
REPORT_NAME=/home/oracle/sql/report/sp`date +%m%d`_ac

/home/oracle/product/9.2.0/bin/sqlplus -s <define begin_snap=$BEGIN_SNAP
define end_snap=$END_SNAP
define report_name=$REPORT_NAME
@?/rdbms/admin/spreport
exit
!

mail -s "perfstat report" < /home/oracle/sql/report/sp`date +%m%d`_ac.lst

[oracle@www1 sql]$crontab -l
* 21 * * * /home/oracle/sql/backup/auto_send_perf.sh >> /home/oracle/sql/backup/perf.lst 2>&1

说明:从早上8点到晚上8点之间进行快照收集,9点执行cron进程启动,产生报表的快照也限于当天收集的快照,
将当天最小的snap_id与最大的snap_id放到两个文件中,在sheel中读出,并计算出一个报表名称,
最后产生的报表通过sendmail发送到相关人员的邮箱,然后每天晚只需要收邮件就可以看到当天的报表啦。