Oracle Statspack的使用

一、Statspack概述

Oracle Statspack工具從Oracle 8.1.6開始引入，通過Statspack可以很容易地收集資料庫效能資料，並通過這些資料進而分析確定Oracle資料庫的瓶頸所在。從Oracle 10g開始，Oracle推出了新的工具——AWR自動負載資訊庫。比較起來，Statspack需要由使用者自行進行安裝排程，並且其收集的資訊較為有限，AWR收集的資訊大大增加，從10g開始，AWR被設計用來取代Statspack工具，並且在Oracle 11g中被不斷增強。但是不管Statspack還是AWR，其本質都是相同的，就是通過持續不斷地收集資料庫或者系統的效能資訊來提煉有意義的資料庫報告，以作為效能分析的基礎。

二、安裝Statspack

在資料庫中Statspack初始並未安裝，但是其安裝配置指令碼隨軟體提供，要使用這個工具首先需要通過一系列的指令碼來安裝和初始化這個工具。

Statspack的指令碼位於$ORACLE_HOME/rdbms/admin目錄下，是一組以sp開頭的檔案。

1、安裝前資料庫引數的修改

為了能夠順利安裝和執行Statspack，可能需要設定以下兩個系統引數。

（1）job_queue_processes

為了能夠建立自動任務，執行資料收集，該引數需要大於0.

對於Oracle 10g，該引數預設值是0；

對於Oracle 11g，該引數預設值是1000.

可以使用下面命令修改：

SQL> alter system set job_queue_processes=6 scope=both;

（2）timed_statistics

該引數為True時，Oracle將實時收集作業系統的計時資訊，這些資訊可被用來顯示時間等統計資訊、優化資料庫和SQL語句。

使用Statspack收集統計資訊時建議將該值設定為True，在Oracle 9i之前這個引數的預設值為False，從Oracle 9i開始這個引數預設值為True。將timed_statistics設定為True會對資料庫帶來一定的效能影響，但是這個效能影響與收集資訊帶來的好處相比是微不足道的。

該引數使收集的時間資訊儲存在V$SESSTATS和V$SYSSTATS動態效能檢視中。

SQL> alter system set timed_statistics=true;

2、安裝Statspack

安裝需要使用sys使用者以sysdba身份登入到資料庫安裝。

安裝過程通常很簡單，執行spcreate.sql指令碼即可。但是為了更好的規劃，還有一些準備工作需要提前做好。

（1）、建立獨立表空間

由於Statspack需要一定的儲存空間，最好建立獨立的表空間。這裡建立的表空間不能太小，如果太小建立物件會失敗，至少需要建立100M表空間。如果打算長期使用，可以建立稍大的表空間。如：假設每隔半小時取樣一次，連續取樣一週，那麼Statspack產生的資料量是很大的。這裡我建立一個500MB的測試表空間。

C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期一 12月 9 13:56:21 2013

連線到:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select file_name from dba_data_files;

FILE_NAME

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

E:\APP\ADMINISTRATOR\ORADATA\TEST\SYSTEM01.DBF

E:\APP\ADMINISTRATOR\ORADATA\TEST\SYSAUX01.DBF

E:\APP\ADMINISTRATOR\ORADATA\TEST\UNDOTBS01.DBF

E:\APP\ADMINISTRATOR\ORADATA\TEST\USERS01.DBF

SQL> create tablespace perfstat datafile 'E:\APP\ADMINISTRATOR\ORADATA\TEST\perfstat.dbf' size 500M;

表空間已建立。

（2）、執行安裝指令碼

E:\app\Administrator\product\11.2.0\dbhome_1\RDBMS\ADMIN>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期一 12月 9 14:11:10 2013

連線到:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @spcreate

Choose the PERFSTAT user's password

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

Not specifying a password will result in the installation FAILING

輸入 perfstat_password 的值: system

system

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 的值:

Using tablespace TEMP as PERFSTAT temporary tablespace.

按照提示輸入預設表空間和臨時表空間的名稱即可。

在這一步，如果出現錯誤，可以執行spdrop.sql指令碼來刪除這些物件，然後重新執行spcreate.sql來建立物件。

3、測試Statspack

執行statspack.snap可以產生系統快照，執行兩次，然後執行spreport.sql就可以生成一個基於兩個時間點的報告，如果一切正常，說明安裝成功。

SQL> execute statspack.snap

PL/SQL 過程已成功完成。

SQL> execute statspack.snap

PL/SQL 過程已成功完成。

SQL> @spreport.sql

......

三、規劃自動任務

Statspack正確安裝以後，就可以設定定時任務收集資料了。可以使用spauto.sql來定義自動任務。先來看看spauto.sql的關鍵內容：

dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);

這個job任務定義了收集資料的時間間隔，一天有24個小時，即1440分鐘，那麼：

l 1/24HH，即每小時一次；

l 1/48MI，即每半小時一次；

l 1/144MI，即每10分鐘一次；

l 1/288MI，即每5分鐘一次。

可以修改spauto.sql來更改執行間隔，然後執行spauto，這樣就建立了資料收集計劃。可以檢視spauto.lis來獲得輸出資訊：

SQL> @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

----------

2

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_COL_PLUS_SHOW_PARAM

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

TYPE

-----------

VALUE_COL_PLUS_SHOW_PARAM

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

job_queue_processes

integer

1000

Next scheduled run

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

The next scheduled run for this job is:

JOB NEXT_DATE NEXT_SEC

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

2 09-12月-13 16:00:00

關於取樣間隔，通常建議以1小時為時間間隔，對於有特殊需要的環境，可以設定更短的，如半小時作為取樣間隔，但不推薦更短。因為Statspack的執行本身需要消耗資源，對於繁忙的生產系統，太短的取樣對系統的效能會產生較大的影響（甚至會使Statspack的執行出現在取樣資料中）。如果是實時的問題診斷，需要取樣分析，可以根據具體需要手工執行取樣。

四、生成分析報告

呼叫spreport.sql可以生成分析報告：

SQL> @spreport

Current Instance

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

DB Id DB Name Inst Num Instance

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

2130218216 TEST 1 tsid

Instances in this Statspack schema

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

DB Id Inst Num DB Name Instance Host

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

2130218216 1 TEST tsid ZZLK-3

Using 2130218216 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

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

tsid TEST 1 09 12月 2013 14:32 5

2 09 12月 2013 14:32 5

3 09 12月 2013 15:00 5

Specify the Begin and End Snapshot Ids

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

輸入 begin_snap 的值: 1

Begin Snapshot Id specified: 1

輸入 end_snap 的值: 3

End Snapshot Id specified: 3

Specify the Report Name

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

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

press to continue, otherwise enter an alternative.

輸入 report_name 的值: e:\rep1209.txt

Using the report name e:\rep1209.txt

在生成報告之前，Oracle會查詢列出所有采樣的Snap ID，可以按照先後順序任選兩個Snap ID生成一個報告，begin_snap就是指其實Snap號，end_snap指終止Snap號；生成報表的名稱可以自己定義，也可以採用預設的；如不指定路徑，則會在當前目錄下生成一個報告。

如果系統在取樣過程中間停過機，那麼就可能收到錯誤資訊：

declare

*

第 1 行出現錯誤:

ORA-20200: The instance was shutdown between snapshots 1 and 11

ORA-06512: 在 line 48

一個Statspack的報告不能跨越一次停機，但是之前或之後的連續區間，收集的資訊依然有效，可以選擇之前或之後的取樣生成report。

五、移除定時任務

移除一個定時任務，可以如下操作：

SQL> select job,log_user,last_date,next_date,interval from user_jobs;

JOB LOG_USER LAST_DATE NEXT_DATE INTERVAL

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

3 PERFSTAT 2013/12/09 15:00:00 2013/12/09 16:00:00 trunc(SYSDATE+1/24,'HH')

SQL> execute dbms_job.remove('3')

PL/SQL 過程已成功完成。

當完成了一個取樣報告，就應該及時移除這個job任務，在生產環境中，遺漏一個無人照顧的job是非常危險的，如果Statspack連續執行一個星期或更長時間，取樣的資料量可能會是非常驚人的。

六、刪除歷史資料

刪除stats$snapshot資料表中的相應資料，其他表中的資料會相應地級聯刪除：

SQL> select max(snap_id) from stats$snapshot;

MAX(SNAP_ID)

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

11

SQL> delete from stats$snapshot where snap_id<=11;

已刪除4行。

可以更改snap_id的範圍以保留需要的資料。

sppurge.sql指令碼可以用於刪除一定範圍的取樣資料，執行該指令碼，輸入一個取樣範圍，會自動刪除該區間之間的取樣資料，但是需要注意的是，刪除會產生大量的歸檔日誌，在生產環境上要特別關注：

SQL> @sppurge

Database Instance currently connected to

========================================

Instance

DB Id DB Name Inst Num Name

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

2130218216 TEST 1 tsid

Snapshots for this database instance

====================================

Base- Snap

Snap Id Snapshot Started line? Level Host Comment

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

1 09 12月 2013 14:32:34 5 ZZLK-3

2 09 12月 2013 14:32:57 5 ZZLK-3

3 09 12月 2013 15:00:00 5 ZZLK-3

11 09 12月 2013 15:18:20 5 ZZLK-3

12 09 12月 2013 15:48:31 5 ZZLK-3

13 09 12月 2013 15:48:34 5 ZZLK-3

14 09 12月 2013 15:48:36 5 ZZLK-3

15 09 12月 2013 15:48:37 5 ZZLK-3

16 09 12月 2013 15:48:38 5 ZZLK-3

Warning

~~~~~~~

sppurge.sql deletes all snapshots ranging between the lower and

upper bound Snapshot Id's specified, for the database instance

you are connected to. Snapshots identified as Baseline snapshots

which lie within the snapshot range will not be purged.

It is NOT possible to rollback changes once the purge begins.

You may wish to export this data before continuing.

Specify the Lo Snap Id and Hi Snap Id range to purge

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

輸入 losnapid 的值:1

Using 1 for lower bound.

輸入 hisnapid 的值: 16

Using 16 for upper bound.

Deleting snapshots 1 - 16.

Number of Snapshots purged: 9

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

Purge of specified Snapshot range complete.

Oracle還提供了系統指令碼用於Truncate這些統計資訊表，這個指令碼名字是sptrunc.sql，指令碼里面的表就是和Statspack相關的所有系統表，如果取樣了大量的資料，直接Delete是非常緩慢的，可以考慮使用該指令碼截斷所有表。

七、調整Statspack的收集門限

Statspack有兩種型別的收集選項：級別（level）和門限（threshold），其中級別用於控制收集資料的型別，門限用於設定收集的資料的閾值。

Statspack共有以下5類快照級別（Level），預設級別是Level 5.

l Level 0：包含一般效能的統計資料，如等待事件、系統事件、系統統計、回滾段統計、行快取、SGA、會話、鎖、緩衝池統計等。

l Level 5：較之前級別增加了SQL語句資訊，除了包括Level 0的所有內容，還包括SQL語句的收集，收集結果記錄在stats$sql_summary中。

l Level 6：從Oracle 9i開始引入，除包含Level 5的全部資訊外，增加了SQL執行計劃部分資訊。

l Level 7：從Oracle 9i開始引入，除包含低階別的所有資訊之外，還增加段統計資訊（Segment Level Statistics）收集。

l Level 10：增加Parent/Children Latch資訊統計。這一級別包括低階別收集得所有資訊，並且還會將附加的Parent/Children Latch資訊記入stats$latch_children字典表中。使用這個級別收集資訊時可能會耗費大量的CPU時間，所以使用時需要慎重。

可以通過Statspack包修改預設的級別設定：

SQL> execute statspack.snap(i_snap_level=>0,i_modify_parameter=>’true’);

通過這樣的設定，以後的收集級別都將是0級。如果只是想本次改變收集級別，可以忽略i_modify_parameter引數。

SQL> execute statspack.snap(i_snap_level=>10);

而快照門限只應用於stats$sql_summary表中獲取的SQL語句。因為每一個快照都會收集很多資料，每一行都代表獲取快照時資料庫中的一個SQL語句，所以stats$sql_summary很快就會成為Statspack中最大的表。門限資訊儲存在 stats$statspack_parameter表中，各種門限說明如下：

l executions_th：這是SQL語句執行的數量（預設100）。

l disk_reads_th：這是SQL語句執行的磁碟讀入數量（預設1000）。

l parse_calls_th：這是SQL語句執行的解析呼叫的數量（預設1000）。

l buffer_gets_th：這是SQL語句執行的緩衝區獲取的數量（預設10000）。

任何一個門限值超過以上引數就會產生一條記錄。

通過呼叫statspack.modify_statspack_parameter函式就可以改變門限的預設值。例如：

SQL>execute statspack.modify_statspack_parameter(i_buffer_gets_th=>100000,i_disk_reads_th=>100000);

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25744374/viewspace-1062798/，如需轉載，請註明出處，否則將追究法律責任。

Oracle Statspack的使用

相關文章