Oracle Statspack安装使用

jxdiscuz

　　C:\Users\Administrator>cd \oracle\product\10.2.0\db_1\RDBMS\ADMIN
　　C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN>sqlplus /nolog

　　SQL*Plus:>　　Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.a
　　SQL> conn /as sysdba
　　已连接。
　　一.系统参数
　　1.job_queue_processes
　　为了建立自动任务，执行数据收集，该参数要大于0。可以在初始化参数文件中修改,可以使用both参数。
　　SQL> show parameter job
　　NAME                                 TYPE        VALUE
　　------------------------------------ ----------- ------------------------------
　　job_queue_processes                  integer     10
　　2.timed_statistics
　　收集操作系统的计时信息，这些信息可被用来显示时间等统计信息、优化数据库和SQL语句。
　　SQL> show parameter timed_statistics;
　　NAME                                 TYPE        VALUE
　　------------------------------------ ----------- ------------------------------
　　timed_statistics                     boolean     TRUE
　　SQL>
　　二.安装Statspack
　　安装Statspack需要internal身份登录，或者拥有sysdba用户权限登录。需要在本地安装或者登录到服务器。
　　首先登录到数据库，转到$ORACLE_HOME\RDBMS\ADMIN
　　检查数据文件路径及磁盘空间，以决定创建数据文件的位置。
　　SQL> select file_name from dba_data_files;
　　FILE_NAME
　　---------------------------------------------
　　C:\ORADATA\ORADB\USERS01.DBF
　　C:\ORADATA\ORADB\SYSAUX01.DBF
　　C:\ORADATA\ORADB\UNDOTBS01.DBF
　　C:\ORADATA\ORADB\SYSTEM01.DBF
　　SQL>
　　创建存储数据的表空间，如果采集间隔较短，周期较长，打算长期采集，需要大一点的表空间，
　　如果每半小时一次，采集一周，数据量是很大的。本例创建一个500MB测试空间。
　　SQL>create tablespace perfstat
　　datafile 'C:\ORADATA\ORADB\perfstat.DBF'
　　size 500M
　　extent management local;
　　SQL> create tablespace perfstat
　　2     datafile 'C:\ORADATA\ORADB\perfstat.DBF'

　　3    >　　4     extent management local;
　　表空间已创建。
　　SQL> host dir /w sp*
　　驱动器 C 中的卷没有标签。
　　卷的序列号是 12D1-1A7F
　　C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN 的目录
　　spauto.sql     spcpkg.sql     spcreate.sql   spctab.sql     spcusr.sql
　　spdoc.txt      spdrop.sql     spdtab.sql     spdusr.sql     sppurge.sql
　　sprepcon.sql   sprepins.sql   spreport.sql   sprepsql.sql   sprsqins.sql
　　sptrunc.sql    spuexp.par     spup10.sql     spup816.sql    spup817.sql
　　spup90.sql     spup92.sql
　　22 个文件        925,371 字节
　　0 个目录 23,800,688,640 可用字节
　　SQL> @spcreate
　　Choose the PERFSTAT user's password
　　-----------------------------------
　　Not specifying a password will result in the installation FAILING
　　输入 perfstat_password 的值:  oracle
　　oracle
　　Choose the Default tablespace for the PERFSTAT user
　　---------------------------------------------------
　　Below is the list of online tablespaces in this database which can
　　store user data.  Specifying the SYSTEM tablespace for the user's
　　default tablespace will result in the installation FAILING, as
　　using SYSTEM for performance data is not supported.
　　Choose the PERFSTAT users's default tablespace.  This is the tablespace
　　in which the STATSPACK tables and indexes will be created.
　　TABLESPACE_NAME                CONTENTS  STATSPACK DEFAULT TABLESPACE
　　------------------------------ --------- ----------------------------
　　PERFSTAT                       PERMANENT
　　SYSAUX                         PERMANENT *
　　USERS                          PERMANENT
　　Pressing  will result in STATSPACK's recommended default
　　tablespace (identified by *) being used.
　　输入 default_tablespace 的值:  perfstat
　　Using tablespace PERFSTAT as PERFSTAT default tablespace.
　　Choose the Temporary tablespace for the PERFSTAT user
　　-----------------------------------------------------
　　Below is the list of online tablespaces in this database which can
　　store temporary data (e.g. for sort workareas).  Specifying the SYSTEM
　　tablespace for the user's temporary tablespace will result in the
　　installation FAILING, as using SYSTEM for workareas is not supported.
　　Choose the PERFSTAT user's Temporary tablespace.
　　TABLESPACE_NAME                CONTENTS  DB DEFAULT TEMP TABLESPACE
　　------------------------------ --------- --------------------------
　　TEMP                           TEMPORARY *
　　Pressing  will result in the database's default Temporary
　　tablespace (identified by *) being used.
　　输入 temporary_tablespace 的值:  temp
　　.........
　　.........
　　.........
　　SQL>
　　SQL> set echo off;
　　Creating Package STATSPACK...
　　程序包已创建。
　　没有错误。
　　Creating Package Body STATSPACK...
　　程序包体已创建。
　　没有错误。
　　NOTE:
　　SPCPKG complete. Please check spcpkg.lis for any errors.
　　SQL> host dir *.lis
　　驱动器 C 中的卷没有标签。
　　卷的序列号是 12D1-1A7F
　　C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN 的目录
　　2012/09/06  22:39               202 spcpkg.lis
　　2012/09/06  22:38             4,317 spctab.lis
　　2012/09/06  22:38             2,268 spcusr.lis
　　3 个文件          6,787 字节
　　0 个目录 23,800,406,016 可用字节
　　SQL> host find "ORA-" *.lis
　　---------- SPCPKG.LIS
　　---------- SPCTAB.LIS
　　---------- SPCUSR.LIS
　　SQL> host find "err" *.lis
　　---------- SPCPKG.LIS
　　SPCPKG complete. Please check spcpkg.lis for any errors.
　　---------- SPCTAB.LIS
　　SPCTAB complete. Please check spctab.lis for any errors.
　　---------- SPCUSR.LIS
　　SPCUSR complete. Please check spcusr.lis for any errors.
　　SQL>
　　如果建立出错可以删除以上对象
　　SQL>@spdrop.sql
　　三、生成报告
　　SQL> execute statspack.snap
　　PL/SQL 过程已成功完成。
　　SQL> execute statspack.snap
　　PL/SQL 过程已成功完成。
　　SQL> @spreport.sql
　　............
　　............
　　............
　　Specify the number of days of snapshots to choose from
　　~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
　　Entering the number of days (n) will result in the most recent
　　(n) days of snapshots being listed.  Pressing  without
　　specifying a number lists all completed snapshots.
　　Listing all Completed Snapshots
　　Snap

　　Instance     DB Name        Snap>　　------------ ------------ --------- ----------------- ----- ------------------
　　oradb        ORADB                1 06 9月  2012 22:4     5
　　4
　　2 06 9月  2012 22:4     5
　　4

　　Specify the Begin and End Snapshot>　　~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
　　输入 begin_snap 的值:  1

　　Begin Snapshot>　　输入 end_snap 的值:  2

　　End   Snapshot>　　Specify the Report Name
　　~~~~~~~~~~~~~~~~~~~~~~~
　　The default report file name is sp_1_2.  To use this name,

　　press  to continue, otherwise enter an>　　输入 report_name 的值:  statspack_rept
　　..................
　　..................
　　..................
　　nls_territory                 CHINA
　　open_cursors                  300
　　pga_aggregate_target          409993216
　　processes                     150
　　remote_login_passwordfile     EXCLUSIVE
　　sga_target                    1241513984
　　undo_management               AUTO
　　undo_tablespace               UNDOTBS1
　　user_dump_dest                C:\ORACLE\PRODUCT\10.2.0\ADMIN\OR
　　-------------------------------------------------------------
　　End of Report ( statspack_rept.lst )
　　SQL>
　　四、运行定时
　　1.定时配置
　　@ORACLE_HOME\RDBMS\ADMIN目录下spauto.sql文件
　　默认定时
　　dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);
　　定时配置
　　这个Job定义了收集数据的时间间隔：
　　一天有24个小时，1440分钟，那么：
　　1/24 HH 每小时一次
　　1/48 MI 每半小时一次（专家建议故障时间内，每15-30分钟执行一次）
　　1/144 MI 每十分钟一次
　　1/288 MI 每五分钟一次
　　我们可以修改spauto.sql来更改执行间隔，如
　　dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/48,'MI'), 'trunc(SYSDATE+1/48,''MI'')', TRUE, :instno);
　　2.执行定时
　　然后执行spauto,这样我们就建立了一个每30分钟执行一次的数据收集计划。你可以查看spauto.lis来获得输出信息；
　　SQL> @C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\spauto.sql
　　PL/SQL 过程已成功完成。
　　Job number for automated statistics collection for this instance
　　~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
　　Note that this job number is needed when modifying or removing
　　the job:
　　JOBNO
　　----------
　　21
　　Job queue process
　　~~~~~~~~~~~~~~~~~
　　Below is the current setting of the job_queue_processes init.ora
　　parameter - the value for this parameter must be greater
　　than 0 to use automatic statistics gathering:
　　NAME                                 TYPE        VALUE
　　------------------------------------ ----------- ------------------------------
　　job_queue_processes                  integer     10
　　Next scheduled run
　　~~~~~~~~~~~~~~~~~~
　　The next scheduled run for this job is:
　　JOB NEXT_DATE      NEXT_SEC
　　---------- -------------- ----------------
　　21 11-9月 -12     20:03:00
　　SQL>
　　五、移动定时任务
　　1.显示定时任务
　　SQL> select job,log_user,priv_user,last_date,next_date,interval from user_jobs;
　　JOB LOG_USER        PRIV_USER         LAST_DATE      NEXT_DATE            INTERVAL
　　---------- ----------------------------------------------------------------------------------------
　　21      SYS                SYS                                      11-9月 -12          trunc(SYSDATE+1/48,'MI')
　　2.移除定时任务
　　SQL> execute dbms_job.remove('21');
　　PL/SQL 过程已成功完成。
　　SQL> select job,log_user,priv_user,last_date,next_date,interval from user_jobs;
　　未选定行
　　SQL>
　　六、删除历史数据
　　1.删除采集数据
　　删除stats$snapshot数据表中得相应数据，其他表中的数据会级联删除
　　SQL> select max(snap_id) from stats$snapshot;
　　MAX(SNAP_ID)
　　------------
　　11
　　SQL> delete from stats$snapshot where snap_id  select * from stats$snapshot;
　　未选定行
　　2.查看锁定表
　　在删除数据过程中查看锁定的表
　　SQL> select a.object_id,a.oracle_username,b.object_name
　　2  from v$locked_object a,dba_objects b
　　3  where a.object_id=b.object_id;
　　oracle提供了直接trunk相关统计表的语句
　　$ORACLE_HOME\RDBMS\ADMIN目录下sptrunc.sql
　　七、调整STATSPACK的收集门限
　　Statspack有两种类型的收集选项：
　　级别（level）：控制收集数据的类型
　　门限（threshold）：设置收集数据的阀值
　　1.级别（level）
　　Statspack共有3种快照级别，默认值是5
　　a.level 0：一般性能统计，包括等待事件、系统事件、系统统计、回滚段统计、行缓存
　　、SGA、会话、锁、缓冲池统计等等。
　　b.level 5：增加SQL语句。除了包含level 0 所有内容，还包括SQL语句的收集，收集结果
　　记录在stats$sql_summary中。
　　c.level 10：增加子锁存统计。包含level 5 所有内容。并且还附加的子锁存存入stats$latc_children中。
　　在使用这个级别时需要慎重，建议在Oracle support的指导下进行。
　　可以通过statspack包修改缺省的级别设置
　　SQL>ececute statspack.snap(i_snap_level=>0,i_modify_parameter=>'true');
　　如果只想本次改变级别，可以忽略i_modify_parameter参数。
　　SQL>ececute 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_th 这是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=>10000,i_disk_reads_th=>100000);