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)