statspack的安装配置

我曾经疑惑在Oracle10g中能否使用statspack，后来得知可以。Oracle10g的AWR是不必安装的，但是statspack不是的。

另外，据说statspack所有功能是免费的，但AWR不是的，AWR只在Oracle10g后的版本中带。如下是在Oracle10g中statspack的安装配置过程。

安装前需要如下前期准备工作：

1、初始化参数：

job_queue_processes需要大于0，这是为了能够建立自动任务，执行数据收集

timed_statistics需要至少配置为true，因为statspack就是要针对动态性能视图差值进行对比分析，只要timed_statistics=true时才能收集操作系统的计时统计。

2、新用户（perfstat）密码、统计信息所存储的独立表空间及临时表空间

perfstat模式使用的密码，如果输入口令不符合规范(如123或以数字开头的口令)，创建会失败。

perfstat使用的默认表空间

perfstat使用的临时表空间，这里使用系统临时表空间temp

[@more@]

一、安装statspack

做好以上两个步骤后，就可以开始安装过程，具体如下：

1、SQL> conn /as sysdba --定要sysdba连接才能有权安装

SQL> show parameter job_queue_processes

NAME TYPE VALUE

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

job_queue_processes integer 10

SQL> show parameter timed_statistics

NAME TYPE VALUE

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

timed_statistics boolean TRUE

2、创建perfstat表空间

如果想知道具体已有数据文件具体路径，则可以通过

SQL> select file_name from dba_data_files;

FILE_NAME

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

/opt/oracle/oradata/test/users01.dbf

/opt/oracle/oradata/test/sysaux01.dbf

/opt/oracle/oradata/test/undotbs01.dbf

/opt/oracle/oradata/test/system01.dbf

查询得知。

SQL> create tablespace perfstat datafile '/opt/oracle/oradata/test/perfstat.dbf' size 500m;

Tablespace created.

3、执行spcreate.sql脚本并按提示输入新增用户密码及其默认表空间和临时表空间名。脚本路径为$ORACLE_HOME/RDBMS/ADMIN

SQL> @?/rdbms/admin/spcreate.sql

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

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

PERFSTAT PERMANENT

SYSAUX PERMANENT *

USERS PERMANENT

Pressing 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 will result in the database's default Temporary

tablespace (identified by *) being used.

Enter value for temporary_tablespace: temp

Using tablespace temp as PERFSTAT temporary tablespace.

... Creating PERFSTAT user

... Installing required packages

... Creating views

... Granting privileges

NOTE:

SPCUSR complete. Please check spcusr.lis for any errors.

SQL>

SQL> --

SQL> -- Build the tables and synonyms

SQL> connect perfstat/&&perfstat_password

Connected.

SQL> @@spctab

SQL> Rem

…

Synonym created.

NOTE:

SPCTAB complete. Please check spctab.lis for any errors.

SQL> -- Create the statistics Package

SQL> @@spcpkg

SQL> Rem

SQL> Rem $Header: spcpkg.sql 31-may-2005.14:07:53 cdgreen Exp $

SQL> Rem

SQL> Rem spcpkg.sql

SQL> Rem

…

Package created.

No errors.

Creating Package Body STATSPACK...

Package body created.

No errors.

NOTE:

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

SQL>

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

--执行成功后在SQL执行的当前路径下会生成三个文件

[oracle@dbserv ~]$ ls

spcpkg.lis spctab.lis spcusr.lis

spcreate脚本内容如下：

[oracle@dbserv ~]$ cd /opt/oracle/product/10.2.0/database/rdbms/admin/

[oracle@dbserv admin]$ more spcreate.sql

Rem

…

--

-- Create PERFSTAT user and required privileges

@@spcusr

--

-- Build the tables and synonyms

connect perfstat/&&perfstat_password

@@spctab

-- Create the statistics Package

@@spcpkg

[oracle@dbserv admin]$

包含了三个脚本spcusr.sql、spctab.sql、spcpkg.sql

二、卸载statspack

4、如何卸载statspack，利用spdrop.sql删除用户（spdusr.sql）和视图(spdtab.sql)

内容如下：

-- Drop PERFSTAT's tables andindexes

@@spdtab

--

-- Drop PERFSTATuser

@@spdusr

这些脚本都会生成安装日志，放置路径是该sql*plus的运行环境路径

三、生成statspack报告

1、手工执行并形成报告

以perfstat用户登录进去，连续执行两次或以上execute statspack.snap ，再执行@?/rdbms/admin/spreport即可：

[oracle@dbserv ~]$ sqlplus / as sysdba

SQL> execute statspack.snap

PL/SQL procedure successfully completed.

SQL> execute statspack.snap

PL/SQL procedure successfully completed.

SQL> @?/rdbms/admin/spreport.sql

Current Instance

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

DB Id DB Name Inst Num Instance

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

3181246008 test 1 test

Instances in this Statspack schema

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

DB Id Inst Num DB Name Instance Host

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

3181246008 1 test test dbserv

Using 3181246008 for database Id

Using 1 for instance number

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 Id Snap Started Level Comment

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

test test 1 16 Jul 2012 16:31 5

2 16 Jul 2012 16:32 5

3 16 Jul 2012 16:34 5

4 16 Jul 2012 16:35 5

11 16 Jul 2012 16:38 5

12 16 Jul 2012 16:38 5

Specify the Begin and End Snapshot Ids

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

Enter value for begin_snap: 11

Begin Snapshot Id specified: 11

Enter value for end_snap: 12

End Snapshot Id specified: 12

Specify the Report Name

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

The default report file name is sp_11_12. To use this name,

press to continue, otherwise enter an alternative.

Enter value for report_name:

Using the report name sp_11_12

...

End of Report ( sp_11_12.lst )

SQL> exit

[oracle@dbserv ~]$ ls

sp_11_12.lst spcpkg.lis spctab.lis spcusr.lis

[oracle@dbserv ~]$

2、设置定时任务并形成报告

确定正确以后，设置定时任务，开始收集数据打开该目录下$ORACLE_HOME/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小时为时间间隔，过短对系统的性能会产生较大的影响。

然后执行$ORACLE_HOME/rdbms/admin/spauto

任务创建后，就可生成分析报告@$ORACLE_HOME/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)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/18841027/viewspace-1058823/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/18841027/viewspace-1058823/