statspack基本应用

一，创建statspack

1.调整参数

job_queue_processes

参数需要大于0

SYS@orcl> alter system set job_queue_processes=6;

System altered.

timed_statistics ：设置为true。9i以后默认为true

SYS@orcl>show parameter timed_sta

NAME                                 TYPE        VALUE

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

timed_statistics                     boolean       TRUE

2.以sys用户登录

[oracle@hxy admin]$  sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Apr 28 14:27:32 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SYS@orcl>

3.创建专用表空间

SYS@orcl>create tablespace perfstat

  2  datafile '/opt/oracle/oradata/orcl/perfstat01.dbf'

  3  size 100M;

Tablespace created.

4.安装statspack

SYS@orcl>@?/rdbms/admin/spcreate.sql

下面需要输入密码，注意密码不能太简单，如123 否则会报错！

Choose the PERFSTAT user's password

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

Not specifying a password will result in the installation FAILING

Enter value for perfstat_password: perfstat

perfstat

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

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

HXY                                   PERMANENT

HXY1                                  PERMANENT

PERFSTAT                       PERMANENT

RMAN                           PERMANENT

SYSAUX                         PERMANENT *

USERS                          PERMANENT

Pressing <return> will result in STATSPACK's recommended default

tablespace (identified by *) being used.

Enter value for 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 <return> will result in the database's default Temporary

tablespace (identified by *) being used.

Enter value for temporary_tablespace: 

Using tablespace TEMP as PERFSTAT temporary tablespace.

... Creating PERFSTAT user

...............

...............

出现

No errors.

Creating Package Body STATSPACK...

Package body created.

No errors.

NOTE:

SPCPKG complete. Please check spcpkg.lis for any errors.

表示安装成功！！！

如果创建失败，使用下面的语句先删除之前创建的对象，然后再执行创建脚本

SYS@orcl>@?/rdbms/admin/spdrop.sql

测试：

执行两次

execute statspack.snap

然后执行

@？/rdbms/admin/spreport.sql

即可以生成两个时间点的报告

规划自动任务

修改spauto.sql文件

1/24 HH :每小时

1/48 M：每半小时

1/144 MI：每10分钟

1/288 MI：每5分钟

修改完后执行@spauto

SYS@orcl> @?/rdbms/admin/spauto.sql

PL/SQL procedure successfully completed.

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     6

Next scheduled run

~~~~~~~~~~~~~~~~~~

The next scheduled run for this job is:

       JOB NEXT_DATE NEXT_SEC

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

        21 28-APR-13 16:00:00

移除定时任务

查询

SYS@orcl>select job,log_user,priv_user,last_date,next_date,interval from user_jobs;

SYS@orcl>execute dbms_job.remove('22')   ---job号

建立定时任务时，一定要及时移除！

生成分析报告

@?/dbms/admin/spreport.sql

删除历史数据

删除stats$snapshot中的数据，其他表中的数据会相应地级联删除

SYS@orcl>select max(snap_id) from stats$snapshot;

MAX(SNAP_ID)

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

           2

SYS@orcl>delete from stats$snapshot where snap_id <= 2;

2 rows deleted.

删除一定范围的采样数据

SYS@orcl>@?/rdbms/admin/sppurge.sql

truncate统计信息

SYS@orcl>@?/rdbms/admin/sptrunc.sql

如果要删除大量的数据，直接delete删除非常慢，可以考虑使用上面的那个脚本删除