Statspack的安装和使用

         登录具有dba权限的用户进入设置参数。       

alter  system  set  job_queue_processes  =   6 ;
-- job_queue_processes需要大于0，这是为了能够建立自动任务，执行数据收集。

alter  system  set  timed_statistics  =  true;
-- 收集操作系统的计时统计，这些信息可被用来显示时间等统计信息，优化数据库和sql语句。

select   file_name   from  dba_data_files; 
-- 查看数据文件的所在路径

-- 紧接着创建表空间
create  tablespace perfstat
datafile  ' /opt/ora10/oradata/orcl/perfstat.dbf '  size 500m extent management local;

-- 执行spcreate.sql，期间会要求输入密码，表空间(perfstat)，临时表空间(temp)
@ / opt / ora10 / product / 10.2 / rdbms / admin / spcreate

-- 提示：若执行期间出现错误，可以@/opt/ora10/product/10.2/rdbms/admin/spdrop.sql脚本来删除这些对象，然后重新创
-- 建。

-- 执行成功后会有三个文件
-- 在/opt/ora10/product/10.2/rdbms/admin目录下面
-- spcpkg.lis，spctab.lis，spcusr.lis

         第二步就以perfstat用户登录进去，连续执行两次execute statspack.snap ，再执行@/opt/ora10/product/10.2/rdbms/admin/spreport   用以测试statspack安装成功

        确定正确以后，设置定时任务，开始收集数据打开该目录下/opt/ora10/product/10.2/rdbms/admin的spatuto.sql ， 找到中间一段

begin
   select  instance_number  into  :instno  from  v$instance;
  dbms_job.submit(:jobno,  ' statspack.snap; ' , trunc(sysdate + 1 / 24 , ' HH ' ),  ' trunc(SYSDATE+1/24, '' HH '' ) ' , TRUE, :instno);
   commit ;
end ;

这里的时间间隔可做对应修改：
1/24 HH    --  每小时一次
1/48 MI      --  每半小时一次
1/144 MI    --  每十分钟一次
1/288 MI    --  每五分钟一次
一般以1小时为时间间隔，过短对系统的性能会产生较大的影响。

           然后执行@/opt/ora10/product/10.2/rdbms/admin/spauto 
          任务创建后，就可生成分析报告@/opt/ora10/product/10.2/rdbms/admin/spreport
期间会输入begin_snap和end_snap，可通过select * from stats$snapshot;来获得snap_id，最后会输入生成报告的name，这个报告文件最房子安装oracle的linux用户下的根目录，也可以用find -name xxx来查找。

         想要移除这个任务

select  job, log_user, priv_user, last_date, next_date, interval  from  user_jobs;
execute  dbms.job.remove(snap_id)

        分析结果

select  substr(to_char(snap_time,  ' yyyy-mm-dd HH24:MI:SS ' ),  12 ), (newreads.value  -  oldreads.value) reads
, (newwrites.value  -  oldwrites.value) writes   from  perfstat.stats$sysstat oldreads,  perfstat.stats$sysstat newreads, 
perfstat.stats$sysstat oldwrites,  perfstat.stats$sysstat newwrites, perfstat.stats$snapshot sn
where   1 = 1
and  newreads.snap_id  =  sn.snap_id
and  newwrites.snap_id  =  sn.snap_id
and  oldreads.snap_id  =  sn.snap_id  -   1
and  oldwrites.snap_id  =  sn.snap_id  -   1
and  oldreads.statistic#  =   40
and  newreads.statistic#  =   40
and  oldwrites.statistic#  =   41
and  newwrites.statistic#  =   41
and  (newreads.value  -  oldreads.value)  >   0
and  (newwrites.value  -  oldwrites.value)  >   0  ;

-- ---------------------------------------------------------------------------------------------------

SUBSTR(TO_CHAR(SNAP_TIME, ' YYYY      READS     WRITES
------------------------------ ---------- ----------
10:20:13                               22          4
11:50:05                             2448        260

--可以观察数据库物理IO的访问，日常访问的峰值及繁忙程度

 

select  substr(to_char(snap_time,  ' yyyy-mm-dd HH24:MI:SS ' ),  12 ), 
round ( 100   *  (((a.value  -  e.value)  +  (b.value  -  f.value))  -  (c.value  -  g.value))  /  ((a.value  -  e.value)  +  (b.value  -  f.value))) "buffer hit ratio"
from  perfstat.stats$sysstat a,  perfstat.stats$sysstat b, 
perfstat.stats$sysstat c,  perfstat.stats$sysstat d, 
perfstat.stats$sysstat e,  perfstat.stats$sysstat f, 
perfstat.stats$sysstat g, 
perfstat.stats$snapshot sn
where   1 = 1
and  a.snap_id  =  sn.snap_id
and  b.snap_id  =  sn.snap_id
and  c.snap_id  =  sn.snap_id
and  d.snap_id  =  sn.snap_id
and  e.snap_id  =  sn.snap_id  -   1
and  f.snap_id  =  sn.snap_id  -   1
and  g.snap_id  =  sn.snap_id  -   1
and  a.statistic#  =   39
and  e.statistic#  =   39
and  b.statistic#  =   38
and  f.statistic#  =   38
and  c.statistic#  =   40
and  g.statistic#  =   40
and  d.statistic#  =   41  ;

-- ---------------------------------------------------------

SUBSTR(TO_CHAR(SNAP_TIME, ' YYYY buffer hit ratio
------------------------------ ----------------
10:20:13                                    100
11:50:05                                    100

--命中率是Oracle数据库的重要指标，它代表在内存中找到需要数据的比率，一般来说，如果该值小于90%，则可能说明
--数据库存在大量代价昂贵的IO操作。