Statspack安裝配置使用說明
在數據庫治理中，Oracle提供的statspack是一個很強大的工具，通過Statspack，可以收集系統信息，診斷數據庫故障，也方便第三方技術支持進行遠程閱讀和建議。
看到很多人還不太了解這個工具的使用，論壇上同樣的問題很多，所以寫了一下一個說明文檔，主要涉及安裝配置和數據收集。
關于參數說明和性能部分還在繼續進行中。
先帖一部分寫完的出來，希望通過這些文字可以使不了解的朋友學會怎樣配置和使用Statspack!
下面是正文：
Oracle Statspack 從Oracle8.1.6開始被引入Oracle,并馬上成為DBA和Oracle專家用來診斷數據庫性能的強有力的工具。通過Statspack我們可以很輕易的確定Oracle數據庫的瓶頸所
在，記錄數據庫性能狀態。因此了解和使用Statspack對于DBA來說至關重要。
在數據庫中Statspack的腳本位于$ORACLE_HOME/RDBMS/ADMIN目錄下，對于ORACLE8.1.6,是一組以stat開頭的文件；對于ORACLE8.1.7,是一組以sp開頭的文件。
在Oracle8.1.6中，Statspack第一次發布，但是你也可以在以下鏈接找到可用于Oracle80~Oracle8.1.5的版本。
http://www.oracle.com/oramag/oracle...tml?o20tun.Html
http://www.oracle.com/oramag/oracle...pack-other.html
在816以前的版本使用Statspack，你需要使用statscbps.sql腳本建立一個v$buffer_pool_statistics視圖，該腳本包含在以上鏈接下載的tar文件中。
訪問該鏈接，你可能需要一個OTN帳號，申請該帳號是免費的。
在Statspack發布之前，我們通常能夠使用診斷數據庫的工具是兩個腳本UTLBSTAT.SQL和UTLESTAT.SQL，BSTAT/ESTAT是一個非常簡單的性能診斷工具。UTLBSTAT獲得開始時很多
V$視圖的快照，UTLESTAT通過先前的快照和當前視圖生成一個報表。實際上通過這個報表我們很難讀出或者解釋系統性能方面的問題。Statspack改變了這一切，通過連續的數據收集，
Statspack能給我們提供至關重要的趨勢分析。這是一個巨大的進步。
下面我們來講一講Statspack的安裝，配置，使用，解讀
一. 系統參數
為了能夠順利安裝和運行Statspack你可能需要設置以下系統參數：
1. job_queue_PRocesses
為了能夠建立自動任務，執行數據收集，該參數需要大于0。你可以在初試化參數文件中修改該參數。
2. timed_statistics
收集操作系統的計時信息，這些信息可被用來顯示時間等統計信息、優化數據庫和 SQL 語句。要防止因從操作系統請求時間而引起的開銷，請將該值設置為False。
使用statspack收集統計信息時建議將該值設置為 TRUE，否則收集的統計信息大約只能起到10%的作用，將timed_statistics設置為True所帶來的性能影響與好處相比是微不足道的。
該參數使收集的時間信息存儲在在V$SESSTATS 和V$SYSSTATS 動態性能視圖中。
Timed_statistics參數可以在實例級進行更改
SQL> alter system set timed_statistics = true;
System altered
SQL>
二. 安裝Statspack
安裝Statspack需要用internal身份登陸，或者擁有SYSDBA(connect / as sysdba)權限的用戶登陸。需要在本地安裝或者通過telnet登陸到服務器。
在Oracle8.1.6版本中運行statscre.sql;在Oracle8.1.7版本中運行spcreate.sql。
首先登陸到數據庫，最好轉到$ORACLE_HOME/RDBMS/ADMIN目錄，這樣我們執行腳本就可以方便些。
D:/>cd oracle/ora81/rdbms/admin
D:/oracle/ora81/RDBMS/ADMIN>sqlplus internal
SQL*Plus: Release 8.1.7.0.0 - ProdUCtion on 星期二 12月 3 16:54:53 2002
(c) Copyright 2000 Oracle Corporation. All rights reserved.
請輸入口令:
連接到:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production
SQL> select instance_name,host_name,version,startup_time from v$instance;
INSTANCE_NAME
----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_TI
----------------- ----------
eygle
AM-SERVER
8.1.7.0.0 22-11月-02
SQL>
檢查數據文件路徑及磁盤空間，以決定創建數據文件的位置：
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
D:/ORACLE/ORADATA/EYGLE/SYSTEM01.DBF
D:/ORACLE/ORADATA/EYGLE/RBS01.DBF
D:/ORACLE/ORADATA/EYGLE/USERS01.DBF
D:/ORACLE/ORADATA/EYGLE/TEMP01.DBF
D:/ORACLE/ORADATA/EYGLE/TOOLS01.DBF
D:/ORACLE/ORADATA/EYGLE/INDX01.DBF
D:/ORACLE/ORADATA/EYGLE/DR01.DBF
D:/ORACLE/ORADATA/EYGLE/GGQIANG01.DBF
D:/ORACLE/ORADATA/EYGLE/EQSP01.DBF
D:/ORACLE/ORA81/DATABASE/MP5_DATA.DBF
D:/ORACLE/ORA81/DATABASE/MP5_INDX.DBF
D:/ORACLE/ORA81/DATABASE/MP5_TEMP.DBF
D:/ORACLE/ORA81/DATABASE/MP5_IDNX.DBF
D:/ORACLE/ORADATA/EYGLE/HRM01.DBF
D:/ORACLE/ORADATA/EYGLE/TIB.DBF
D:/ORACLE/ORADATA/EYGLE/EQSP02.DBF
D:/ORACLE/ORADATA/EYGLE/RMAN_TS.DBF
D:/ORACLE/ORADATA/EYGLE/EQSP03.DBF
D:/ORACLE/ORADATA/EYGLE/EAPP01.DBF
D:/ORACLE/ORADATA/EYGLE/EQSP04.DBF
D:/ORACLE/ORADATA/EYGLE/AM01.DBF
D:/ORACLE/ORADATA/EYGLE/SYSTEM02.DBF
D:/ORACLE/ORADATA/EYGLE/FNC01.DBF
D:/ORACLE/ORADATA/EYGLE/HH_AM01.ORA
已選擇24行。
SQL>
創建存儲數據的表空間，假如采樣間隔較短，周期較長，打算長期使用，那么你可能需要一個大一點的表空間，假如每個半個小時采樣一次，連續采樣一周，數據量是很大的。本例創建一個
500M的測試表空間。
SQL> create tablespace perfstat
2 datafile 'd:/oracle/oradata/eygle/perfstat.dbf'
3 size 500M;
表空間已創建。
SQL>
檢查是否存在安裝所需要的腳本文件
SQL> host dir sp*
驅動器 D 中的卷沒有標簽。
卷的序列號是 5070-5982
D:/oracle/ora81/RDBMS/ADMIN 的目錄
2000-02-18 14:34 1,841 spauto.sql
2000-06-15 15:21 64,492 spcpkg.sql
2000-02-18 14:34 916 spcreate.sql
2000-06-15 15:21 31,985 spctab.sql
2000-06-15 15:21 6,623 spcusr.sql
2000-06-15 15:53 47,820 spdoc.txt
2000-06-15 15:22 794 spdrop.sql
2000-06-15 15:21 3,732 spdtab.sql
2000-06-15 15:21 1,334 spdusr.sql
2000-06-15 15:22 7,002 sppurge.sql
2000-07-12 15:07 72,992 spreport.sql
2000-06-15 15:22 2,278 sptrunc.sql
2000-02-18 14:34 612 spueXP.par
2000-06-15 15:22 31,122 spup816.sql
14 個文件 273,543 字節
0 個目錄 3,974,799,360 可用字節
接下來我們就可以開始安裝Statspack了。這期間會提示你輸入缺省表空間和臨時表空間的位置,輸入我們為perfstat用戶創建的表空間和你的臨時表空間。
SQL> @spcreate
.
.
.
.
Specify PERFSTAT user's default tablespace
輸入 default_tablespace 的值: perfstat
Using perfstat for the default tablespace
用戶已更改。
用戶已更改。
Specify PERFSTAT user's temporary tablespace
輸入 temporary_tablespace 的值: temp
假如安裝成功，你可以看到如下的輸出信息：
SQL> host type spcpkg.lis
Creating Package STATSPACK...
程序包已創建。
沒有錯誤。
Creating Package Body STATSPACK...
程序包主體已創建。
沒有錯誤。
NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.
你可以查看.lis文件查看安裝時的錯誤信息。
SQL> host dir *.lis
驅動器 D 中的卷沒有標簽。
卷的序列號是 5070-5982
D:/oracle/ora81/RDBMS/ADMIN 的目錄
2002-12-03 17:25 204 spcpkg.lis
2002-12-03 17:25 2,276 spctab.lis
2002-12-03 17:25 3,965 spcusr.lis
2002-12-03 17:23 1,187 spdtab.lis
2002-12-03 17:24 351 spdusr.lis
5 個文件 7,983 字節
0 個目錄 3,965,304,832 可用字節
SQL> host find “ORA-“ *.lis
SQL> host find "err" *.lis
---------- SPAUTO.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.
---------- SPDTAB.LIS
在UNIX上，你可以通過以下命令查看相應的錯誤信息
$ ls *.lis
spauto.lis spcpkg.lis spctab.lis spcusr.lis spdtab.lis spdusr.lis
$ grep ORA- *.lis
$ grep 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.
spdtab.lis:SPDTAB complete. Please check spdtab.lis for any errors.
spdusr.lis:SPDUSR complete. Please check spdusr.lis for any errors.
在這一步，假如出現錯誤，那么你可以運行spdrop.sql腳本來刪除這些對象。然后重新運行spcreate.sql來創建這些對象。運行 SQL*Plus, 以具有SYSDBA 權限的用戶登陸：
SQL> @spdrop.sql
.
.
.
同義詞已丟棄。off;
視圖已丟掉。
同義詞已丟棄。
視圖已丟掉。
同義詞已丟棄。
用戶已丟棄
NOTE:
SPDUSR complete. Please check spdusr.lis for any errors.
SQL>
三. 測試安裝好的Statspack
運行statspack.snap可以產生系統快照，運行兩次，然后執行spreport.sql就可以生成一個基于兩個時間點的報告。
假如一切正常，說明安裝成功。
SQL>execute statspack.snap
PL/SQL procedure successfully completed.
SQL>execute statspack.snap
PL/SQL procedure successfully completed.
SQL>@spreport.sql
…
可是有可能你會得到以下錯誤：
SQL> exec statspack.snap;
BEGIN statspack.snap; END;
*
ERROR at line 1:
ORA-01401: inserted value too large for column
ORA-06512: at "PERFSTAT.STATSPACK", line 978
ORA-06512: at "PERFSTAT.STATSPACK", line 1612
ORA-06512: at "PERFSTAT.STATSPACK", line 71
ORA-06512: at line 1
這是Oracle的一個Bug，Bug號1940915。
該Bug自8.1.7.3后修正。
這個問題只會出現在多位的字符集,需要修改spcpkg.sql腳本，$ORACLE_HOME/rdbms/admin/spcpkg.sql，將"substr" 修改為 "substrb"，然后重新運行該腳本。
該腳本錯誤部分：
select l_snap_id
, p_dbid
, p_instance_number
, substr(sql_text,1,31)
．．．．．．．．．．．
substr 會將多位的字符, 當作一個byte.substrb 則會當作多個byte。在收集數據時， statpack 會將 top 10 的 sql 前 31 個字節 存入數據表中,若在SQL的前31 個字有中文，就會出
現此錯誤。
四. 規劃自動任務
Statspack正確安裝以后，我們就可以設置定時任務，開始收集數據了?？梢允褂胹patuo.sql來定義自動任務。
先來看看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 每半小時一次
1/144 MI 每十分鐘一次
1/288 MI 每五分鐘一次
我們可以修改spauto.sql來更改執行間隔，如：
dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/144,'MI'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);
然后我們執行spauto，這樣我們就建立了一個每30分鐘執行一次的數據收集計劃。你可以查看spauto.lis來獲得輸出信息：
SQL>
SQL> --
SQL> -- Schedule a snapshot to be run on this instance every hour, on the hour
SQL>
SQL> variable jobno number;
SQL> variable instno number;
SQL> begin
2 select instance_number into :instno from v$instance;
3 dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/144,'MI'), 'trunc(SYSDATE+1/144,''MI'')', TRUE, :instno);
4 commit;
5 end;
6 /
PL/SQL 過程已成功完成。
SQL>
Job number for automated statistics collection for this instance
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Note that this job number is needed when modifying or removing
the job:
JOBNO
----------
22
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:
SQL> show parameter job_queue_processes
NAME TYPE value
------------------------------------ ------- ------------------------------
job_queue_processes integer 12
Next scheduled run
~~~~~~~~~~~~~~~~~~
The next scheduled run for this job is:
SQL> select job, next_date, next_sec
2 from user_jobs
3 where job = :jobno;
JOB NEXT_DATE NEXT_SEC
---------- ---------- ----------------
22 04-12月-02 15:00:00
SQL>
五. 生成分析報告
調用spreport.sql可以生成分析報告：
SQL> @spreport
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
1277924236 EYGLE 1 eygle
Completed Snapshots
Snap Snap
Instance DB Name Id Snap Started Level Comment
------------ ------------ ----- ----------------- ----- ----------------------
eygle EYGLE 1 04 12月 2002 14:4 5
8
2 04 12月 2002 15:0 5
0
3 04 12月 2002 15:1 5
0
4 04 12月 2002 15:2 5
0
………………..
87 05 12月 2002 02:2 5
3
88 05 12月 2002 02:3 5
3
89 05 12月 2002 02:4 5
3
90 05 12月 2002 02:5 5
3
91 05 12月 2002 03:0 5
3
92 05 12月 2002 03:1 5
3
93 05 12月 2002 03:2 5
3
94 05 12月 2002 03:3 5
3
95 05 12月 2002 03:4 5
3
96 05 12月 2002 03:5 5
3
97 05 12月 2002 04:0 5
3
98 05 12月 2002 04:1 5
3
eygle EYGLE 99 05 12月 2002 04:2 5
3
100 05 12月 2002 04:3 5
3
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
輸入 begin_snap 的值: 1
Begin Snapshot Id specified: 1
輸入 end_snap 的值: 100
End Snapshot Id specified: 100
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_1_100. To use this name,
press <return> to continue, otherwise enter an alternative.
輸入 report_name 的值: rep1205.txt
Using the report name rep1205.txt
這樣就生成了一個報告，可是假如中間停過機，那么你可能收到以下錯誤信息：
ERROR: Snapshots chosen span an instance shutdown: RESULTS ARE INVALID
STATSPACK report for
DB Name DB Id Instance Inst Num Release OPS Host
------------ ----------- ------------ -------- ----------- --- ------------
EYGLE 1277924236 eygle 1 8.1.7.0.0 NO AM-SERVER
:ela := ;
*
ERROR 位于第 4 行:
ORA-06550: 第 4 行, 第 17 列:
PLS-00103: 出現符號 ";"在需要下列之一時：
(-+modnotnull<an identifier>
<a double-quoted delimited-identifier><a bind variable>avg
countcurrentexistsmaxminpriorsqlstddevsumvarianceexecute
foralltimetimestampintervaldate
<a string literal with character set specification>
<a number><a single-quoted SQL string>
符號 "null" 被替換為 ";" 后繼續。
ORA-06550: 第 6 行, 第 16 列:
PLS-00103: 出現符號 ";"在需要下列之一時：
(-+modnotnull<an identifier>
<a double-quoted delimited-identifier><a bind variable>avg
countcurrentexistsmaxminpriorsqlstddevsumvarianceexecute
foralltimetimestampintervaldate
<a stri
六. 移除定時任務
移除一個定時任務，可以如下操作:
SQL> select job,log_user,priv_user,last_date,next_date,interval from user_jobs;
JOB LOG_USER LAST_DATE NEXT_DATE INTERVAL
---------- ------------------------------ ------------------------------ ------------------------------ ----------- 22 PERFSTAT 2002-12-5:14:33:26 2002-12-5 14:43:00
trunc(SYSDATE+1/144,'MI')
SQL> execute dbms_job.remove('22')
PL/SQL procedure successfully completed
七. 刪除歷史數據
刪除stats$snapshot數據表中的相應數據，其他表中的數據會相應的級連刪除：
SQL> select max(snap_id) from stats$snapshot;
MAX(SNAP_ID)
------------
166
SQL> delete from stats$snapshot where snap_id < = 166;
143 rows deleted
你可以更改snap_id的范圍以保留你需要的數據。
在以上刪除過程中，你可以看到所有相關的表都被鎖定。
SQL> select a.object_id,a.oracle_username ,b.object_name
from v$locked_object a,dba_objects b
where a.object_id = b.object_id
/
OBJECT_ID ORACLE_USERNAME OBJECT_NAME
------------------------------------- --------------------------------------------------------------------------------
156 PERFSTAT SNAP$
39700 PERFSTAT STATS$LIBRARYCACHE
39706 PERFSTAT STATS$ROLLSTAT
39712 PERFSTAT STATS$SGA
39754 PERFSTAT STATS$PARAMETER
39745 PERFSTAT STATS$SQL_STATISTICS
39739 PERFSTAT STATS$SQL_SUMMARY
39736 PERFSTAT STATS$ENQUEUESTAT
39733 PERFSTAT STATS$WAITSTAT
39730 PERFSTAT STATS$BG_EVENT_SUMMARY
39724 PERFSTAT STATS$SYSTEM_EVENT
39718 PERFSTAT STATS$SYSSTAT
39715 PERFSTAT STATS$SGASTAT
39709 PERFSTAT STATS$ROWCACHE_SUMMARY
39703 PERFSTAT STATS$BUFFER_POOL_STATISTICS
39697 PERFSTAT STATS$LATCH_MISSES_SUMMARY
39679 PERFSTAT STATS$SNAPSHOT
39682 PERFSTAT STATS$FILESTATXS
39688 PERFSTAT STATS$LATCH
174 PERFSTAT JOB$
20 rows selected
八. 調整STATSPACK的收集門限
Statspack有兩種類型的收集選項：
級別（level）：控制收集數據的類型
門限（threshold）：設置收集的數據的閾值。
1．級別（level）
Statspack共有三種快照級別，默認值是5
a.level 0: 一般性能統計。包括等待事件、系統事件、系統統計、回滾段統計、行緩存、SGA、會話、鎖、緩沖池統計等等。
b.level 5: 增加SQL語句。除了包括level0的所有內容，還包括SQL語句的收集，收集結果記錄在stats$sql_summary中。
c.level 10: 增加子鎖存統計。包括level5的所有內容。并且還會將附加的子鎖存存入stats$lathc_children中。在使用這個級別時需要慎重，建議在Oracle support的指導下進行。
可以通過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);
2．快照門限
快照門限只應用于stats$sql_summary表中獲取的SQL語句。
因為每一個快照都會收集很多數據，每一行都代表獲取快照時數據庫中的一個SQL語句，所以stats$sql_summary很快就會成為Statspack中最大的表。
門限存儲在stats$statspack_parameter表中。讓我們了結一下各種門限：
a. executions_th 這是SQL語句執行的數量(默認值是100)
b. disk_reads_tn 這是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=>100000,i_disk_reads_th=>100000;
一一. 在815上的安裝配置
a. 數據庫狀況
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.5.0.0, 64 bit - Producti
PL/SQL Release 8.1.5.0.0 - Production
CORE Version 8.1.3.0.0 - Production
TNS for HPUX: Version 8.1.5.0.0 - Production
NLSRTL Version 3.4.0.0.0 - Production
b. 運行statscbps.sql
SQL> @statscbps.sql
View created.
Synonym created.
Grant succeeded.
SQL>
其他步驟都是相同的。
關于結果分析，不是幾句話就說得清楚的。
我后面也會整理一些常見的說明出來。
這篇的目的是給出一個安裝、配置的方法，第一帖中已經說明了。
【發表回復】【查看CU論壇原帖】【添加到收藏夾】【關閉】
