oracle statspack詳解
---
這是我的學(xué)習(xí)筆記，根據(jù)eygle老師的<statspack使用指南>及君三思的<Statspack(1，2,3)>學(xué)習(xí)并整理的。
eygle老師的<statspack使用指南>(原鏈接找不到了，放到我的共享里了)：
君三思的<Statspack(1，2,3):
全面介紹oracle statspack的原理，安裝，使用，生成報告等過程和方法……。
+++
介紹:
---
oracle Statspack從Oracle8.1.6被引入，馬上成為DBA和Oracle專家用來診斷數(shù)據(jù)庫性能的強有力工具。通過Statspack我們可以很容易的確定Oracle數(shù)據(jù)庫的瓶頸所有，記錄數(shù)據(jù)庫性能狀態(tài)，也可以使遠程技術(shù)人員迅速了解的的數(shù)據(jù)庫運行狀況。所以，了解和使用Statspack對于DBA來說至關(guān)重要。
在Statspack發(fā)布之前，我們經(jīng)常用來診斷數(shù)據(jù)庫的工具是兩個腳本utlbstat.sql和utlestat.sql。BSTAT/ESTAT是一個非常簡單的性能診斷工具。UTLBSTAT獲得開始時很多V$視圖的快照，UTLESTST通過先前的快照和當(dāng)前視圖生成一個報表。實際上此報表相當(dāng)于statspack的兩個采樣點。
Statspack通過連續(xù)的采樣，能夠給我們提供很重要的趨勢分析數(shù)據(jù)，這是一個巨大的進步。
所以，能夠使用Statspack的環(huán)境，就盡量不使用BSTAT/ESTAT方式來診斷數(shù)據(jù)庫問題。
(Oracle816以前的版本使用Statspack，需要使用statscbps.sql腳本建立一個V$buffer_pool_statistics視圖。可以到oracle網(wǎng)站上下載。)
+++
原理：
---
1，運行oracle自帶腳本，生成一系列的統(tǒng)計表。
2，生成快照，采樣(運行statspack.snap可生成快照，一般通過自動任務(wù)生成快照)
3，根據(jù)快照生成報告
+++
一，準備
---
1，檢查部分參數(shù)值
--job_queue_process：為了能夠建立自動任務(wù)，執(zhí)行數(shù)據(jù)收集，此參數(shù)必須大于0
alter system set job_queue_processess=6;
---timed_statistics，設(shè)置為true，使收集的時間信息存儲在V$sessstats和V$sysstats等動態(tài)性能視圖中，但會消耗資源，可以在使用Satspack之前設(shè)為true，采樣過后，把該參數(shù)動態(tài)修改為false.
alter system set timed_statistics=true;
2，創(chuàng)建表空間，用于保存采樣數(shù)據(jù)
create tablespace perfstat datafile 'e:\hs01\dat\perstat.ora'
size 100m
extent management local;
Statspack的報表數(shù)據(jù)還是相當(dāng)占空間的，特別是在多次連續(xù)采樣的情況下，所以不能太小，最小100M，否則創(chuàng)建對象會失敗。
3，檢查腳本
使用此功能，需要運行oracle自帶腳本，在數(shù)據(jù)庫中生成一系列的表和視圖，用于收集各種信息。
腳本位于%oracle_home%\rdbms\admin目錄下，oracle816下是一組以stat開頭的文件，以后的版本是一組以sp開頭的文件。
oracle816中相關(guān)文件
C:\Documents and Settings\pln_27>dir \\10.105.10.12\e$\Oracle\Ora81\rdbms\admin\
stat*.sql
驅(qū)動器 \\10.105.10.12\e$ 中的卷沒有標(biāo)簽。
卷的序列號是 78C8-0DFB
\\10.105.10.12\e$\Oracle\Ora81\rdbms\admin 的目錄
1999-12-06 20:38 1,805 statsauto.sql
1999-12-06 20:38 882 statscre.sql
1999-11-10 18:10 27,879 statsctab.sql
1999-11-10 18:10 4,844 statscusr.sql
1999-08-27 12:01 829 statsdrp.sql
1999-11-10 18:10 3,344 statsdtab.sql
1999-11-10 18:10 1,136 statsdusr.sql
1999-11-10 18:10 51,400 statspack.sql
1999-11-10 18:10 48,205 statsrep.sql
9 個文件 140,324 字節(jié)
0 個目錄 84,135,944,192 可用字節(jié)
10G中相關(guān)文件
C:\Documents and Settings\pln_27>dir \\10.102.20.60\e$\oracle\product\10.2.0\db_
1\RDBMS\ADMIN\sp*.sql
驅(qū)動器 \\10.102.20.60\e$ 中的卷沒有標(biāo)簽。
卷的序列號是 A89B-BF1F
\\10.102.20.60\e$\oracle\product\10.2.0\db_1\RDBMS\ADMIN 的目錄
2010-03-23 16:53 1,978 spauto.sql
2005-05-31 17:43 204,684 spcpkg.sql
2002-05-17 19:45 895 spcreate.sql
2005-05-31 17:43 84,296 spctab.sql
2005-05-31 17:43 15,682 spcusr.sql
2000-06-19 13:23 794 spdrop.sql
2005-05-31 17:43 7,686 spdtab.sql
2005-05-31 17:43 1,739 spdusr.sql
2003-03-07 19:18 5,075 sppurge.sql
2005-03-31 13:05 5,379 sprepcon.sql
2005-05-31 17:43 258,168 sprepins.sql
2001-04-23 22:48 1,330 spreport.sql
2002-10-11 14:16 1,314 sprepsql.sql
2005-05-31 17:43 34,694 sprsqins.sql
2005-05-31 17:43 4,758 sptrunc.sql
2005-05-31 17:43 23,725 spup10.sql
2004-03-23 11:47 31,961 spup816.sql
2004-03-23 11:47 24,375 spup817.sql
2004-03-23 11:47 20,012 spup90.sql
2004-03-23 11:47 42,925 spup92.sql
20 個文件 771,470 字節(jié)
0 個目錄 24,598,134,784 可用字節(jié)
+++
二，安裝
---
1，以sysdba身份登錄。
8i可以用internal用戶登錄：sqlplus internal
9i及以后版本，可以用sys用戶以sysdba身份登錄：sqlplus / as sysdba
(最好轉(zhuǎn)到腳本所有目錄%oracle_home%\rdbms\admin，便于執(zhí)行腳本)
2，運行腳本，安裝statspack
Oracle816：%oracle_home%\rdbms\admin\statscre.sql
816以后：%oracle_home%\rdbms\admin\spcreate.sql
--腳本會創(chuàng)建用戶perfstat，需要指定此用戶密碼。
輸入 perfstat_password 的值: perfstat
--需要輸入用戶perfstat使用的表空間：指定新建的表空間即可。
輸入 default_tablespace 的值: perfstat
--需要指定用戶perfstat使用的臨時表空間。
輸入 temporary_tablespace 的值: tmp10
--安裝成功，可以看到如下信息：
Creating Package STATSPACK...
程序包已創(chuàng)建。
沒有錯誤。
Creating Package Body STATSPACK...
程序包體已創(chuàng)建。
沒有錯誤。
NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.
--查看錯誤信息
NT下：
host find "ORA-" *.LIS
host find "err-" *.LIS
Unix下：
grep ORA- *.lis
grep err *.lis
如果出現(xiàn)錯誤，可以運行腳本刪除相關(guān)內(nèi)容：@%oracle_home%\rdbms\admin\spdrop.sql
(注意：也要在sysdba下運行腳本刪除相關(guān)對象)
然后再重新運行腳本安裝。
這個安裝過程創(chuàng)建了一系列的表，用于存入采樣數(shù)據(jù)。
查看新建表：
select dt.table_name from dba_tables dt where dt.owner='PERFSTAT'
conn perfstat/perfstat
select table_name from user_tables;
+++
三，測試statspack
---
用perfstat用戶連接
(如果你剛執(zhí)行完@spcreate，則oracle默認將當(dāng)前用戶切換為perfstat 。)
運行statspack.snap可以產(chǎn)生系統(tǒng)快照，運行兩次，產(chǎn)生兩次快照。
SQL> execute statspack.snap;
PL/SQL 過程已成功完成。
SQL> execute statspack.snap;
PL/SQL 過程已成功完成。
然后執(zhí)行腳本就可以生成基于兩個快照的報告。腳本：
Ora816:%oracle_home%\rdbms\admin\statsrep.sql
816以后：%oracle_home%\rdbms\admin\spreport.sql
--需要輸入起始快照ID和結(jié)束快照ID，以及報告文件名。
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
輸入 begin_snap 的值: 1
Begin Snapshot Id specified: 1
輸入 end_snap 的值: 13
End Snapshot Id specified: 13
輸入 report_name 的值: e:\s1report.txt
如果一切正常，說明安裝成功。
--查看產(chǎn)生的快照
select t.snap_id,to_char(t.snap_time,'yyyy-mm-dd hh:mi:ss') as S_Time,t.snapshot_exec_time_s from STATS$SNAPSHOT t;
+++
四，規(guī)劃自動任務(wù)
---
安裝之后，我們就可以設(shè)置定時任務(wù)，定時采樣，收集數(shù)據(jù)。
1，使用系統(tǒng)腳本
使用腳本來定義自動任務(wù)。腳本：
816：statsauto.sql
816以后：spauto.sql
spauto.sql在關(guān)鍵內(nèi)容：
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;
執(zhí)行spquto，就建立了一個每小時執(zhí)行一次的數(shù)據(jù)收集計劃。
這個Job任務(wù)定義了收集數(shù)據(jù)(執(zhí)行statspack.snap)的時間間隔為1小時：
一天24小時，1440分鐘-->
1/24,'HH' 每小時一次
1/48,'MI' 每半小時一次
1/144,'MI' 每10分鐘一次
1/288,'MI' 每5分鐘一次
關(guān)于采樣間隔，通常建議1小時，如有特殊需要，可以設(shè)置更短，如半小時。但不推薦更短。因為statpack的執(zhí)行本身需要消息資源，太短的采樣對系統(tǒng)的性能會產(chǎn)生較大的影響(甚至?xí)箂tatspack的執(zhí)行出現(xiàn)在采樣數(shù)據(jù)中)
2，使用自定義語句
1)可以用以下語句創(chuàng)建一個job，我們設(shè)定其每個小時自動收集一次采樣。
Variable job number ;
begin
dbms_job.submit(:job, 'statspack.snap;' ,trunc( sysdate + 1 / 24 , 'hh24' ), 'trunc(sysdate+1/24,''hh24'')' );
commit ;
end ;
/
查看當(dāng)前job
set linesize 120
col schema_user for a12
col what for a20
col interval for a15
col next_d for a18
select job,schema_user,to_char(next_date,'yyyymmdd hhmiss') as next_D,interval,what from user_jobs;
JOB SCHEMA_USER NEXT_D INTERVAL WHAT
---------- ------------ ------------------ --------------- --------------------
41 PERFSTAT 20100324 030000 trunc(sysdate+1 statspack.snap;
/24,'hh24')
2)再創(chuàng)建一個job，讓它在第二天凌晨自動停止采樣job的執(zhí)行。
查出job編號，執(zhí)行以下語句
Variable job number ;
begin
dbms_job.submit(:job, 'dbms_job.broken(41,true);' ,trunc( sysdate + 1 ), 'null' );
commit ;
end ;
/
再查看任務(wù)，可以看到，已經(jīng)添加了刪除任務(wù)41的新的任務(wù)。
+++
五，生成報告
同測試一樣
用perfstat用戶連接
執(zhí)行腳本，輸入起始快照ID和結(jié)束快照ID，以及報告文件名，生成statspack報告。
816:%oracle_home%\rdbms\admin\statsrep.sql
816以后：%oracle_home%\rdbms\admin\spreport.sql
一次statspack報告不能跨越一次停機，但是之前或之后的連續(xù)區(qū)間，收集的信息依然有效，你可以選擇之前或之后的采樣生成report.
如果中間停過機，會收到以下錯誤信息：
第 1 行出現(xiàn)錯誤:
ORA-20200: The instance was shutdown between snapshots 1 and 31
ORA-06512: 在 line 48
+++
六，后續(xù)處理
1，移除定時任務(wù)
當(dāng)你完成一個采樣報告，你應(yīng)該及時移除這個job任務(wù)。遺漏一個無人照顧的job是非常危險的……
--查看任務(wù)并移除任務(wù)
select job, next_date, next_sec, what from user_jobs;
execute dbms_job.remove('ID')
2，刪除歷史數(shù)據(jù)
1)，刪除采樣數(shù)據(jù)
只要刪除stat$snapshot數(shù)據(jù)表中的相應(yīng)數(shù)據(jù)，其他表中的數(shù)據(jù)會相應(yīng)的級連刪除。
select max(snap_id) from stats$snapshot;
delete from stats$snapshot where snap_id<=166;
刪除過程中，你可以看到所有相關(guān)的表都被鎖定了。
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
2)，truncate統(tǒng)計信息表：使用腳本sptrunc.sql刪除所有的采樣數(shù)據(jù)但保留statspack的庫結(jié)構(gòu)。
如果有大量數(shù)據(jù)，直接delete采樣數(shù)據(jù)是非常緩慢的。使用腳本sptrunc.sql可以快速的刪除所有統(tǒng)計信息。
查看腳本，可以看出是用truncate table截掉所有相關(guān)的表中的內(nèi)容。
truncate table STATS$FILESTATXS;
truncate table STATS$TEMPSTATXS;
...
delete from STATS$DATABASE_INSTANCE;
3)，刪除statspack所有數(shù)據(jù)和各種對象：使用腳本spdrop.sql。全部刪除，不留痕跡!但有表空間在~~
+++
七，其它重要腳本
---
1，spuexp.par，數(shù)據(jù)導(dǎo)出用參數(shù)文件。主要內(nèi)容如下：
file=spuexp.dmp log=spuexp.log compress=y grants=y indexes=y rows=y constraints=y owner=PERFSTAT consistent=y
我們可以使用以下命令導(dǎo)出：
exp perfstat/perfstat parfile=spuexp.par
例：
E:\oracle\product\10.2.0\db_1\RDBMS\ADMIN>exp perfstat/perfstat parfile=spuexp.par
2，刪除數(shù)據(jù)腳本：spdtab.sql--刪除表及同義詞。spdusr.sql刪除用戶
3，oracle92新增腳本
1)用于升級statspack對象的腳本，需要sysdba權(quán)限，升級前請先備份存在的schema數(shù)據(jù)。
spup90.sql：用于升級9.0版本的模式至9.2版本
spup817.sql：如果從statspack8.1.7升級，需要運行這個腳本
spup816.sql：從statspack8.1.6升級，需要運行這個腳本，然后運行spup817.sql。
2)sqrepsql.sql 用于根據(jù)給定的SQL Hash值生成SQL報告
+++
九，調(diào)整statspack的收集門限
---
statspack有兩種類型的收集選項
level--級別：控制收集數(shù)據(jù)的類型
threshold--門限：設(shè)置收集的數(shù)據(jù)閥值
都保存在表stats$statspack_parameter中
1，級別(level)--有三種快照級別，默認值為5
--查看當(dāng)前l(fā)evel級別：
select snap_level from stats$statspack_parameter;
1)level=0：一般性能統(tǒng)計。包括等待事件，系統(tǒng)事件，系統(tǒng)統(tǒng)計，回滾段統(tǒng)計，行緩存，SGA，會話，鎖，緩沖池統(tǒng)計等等。
2)level=5：在level0的基礎(chǔ)上，增加SQL語句的收集。SQL語句收集結(jié)果記錄在ststs$sql_summary表中。
3)level=10：增加子鎖統(tǒng)計，包括level5的所有內(nèi)容，還會將附加子鎖的入stats$latc_children表中。使用這個級別需要慎重，建議在oracle support的指導(dǎo)下進行。
可以通過statspack包修改缺省的級別設(shè)置，如：
SQL>execute statspack.snap(i_snap_level=>0,i_modify_parameter=>'true');
通過這條語句，
以后的收集級別都將是0級。
如果你只是想修改本次收集級別，可以忽略i_modify_parameter參數(shù)，如下：
SQL>execute statspack.snap(i_snap_level=>10); (？？？是10么？)
2，快照門限--只應(yīng)用于stats$sql_summary表中獲取的Sql語句。
因為每一個快照都會收集很數(shù)據(jù)，及sql語句，所以stats$sql_summary很快就會成為statspack中最大的表。
--查看當(dāng)前各種門限
select executions_th,disk_reads_th,parse_calls_th,buffer_gets_th from stats$statspack_parameter;
各種門限：
1)executions_th--sql語句執(zhí)行的數(shù)量(默認值=100)
2)disk_reads_th--sql語句執(zhí)行的磁盤讀入數(shù)量(默認值=1000)
3)parse_calls_th--sql語句執(zhí)行的解析調(diào)用數(shù)量(默認值=1000)
4)buffer_gets_th--sql語句執(zhí)行的緩沖區(qū)獲取數(shù)量(默認值=10000)
任何一個門限值超過以上參數(shù)就會產(chǎn)生一條記錄。
通過調(diào)用statspack.modify_statspack_parameter函數(shù)我們可以改變門限的默認值：
SQL>execute statspack.modify_statspack_parameter(i_buffer_get_th=>100000,i_disk_reads_th=>100000;
+++
十，Statspack 報告分析
---
Statspack 報告分為如下部分
1. 數(shù)據(jù)庫總體信息
含實例、版本、是否RAC、CPU、物理內(nèi)存、oracle內(nèi)存設(shè)置等等
2. 每秒每事務(wù)的資源消耗情況
3. 實例的各組件的命中率
4. 共享池總體情況(Shared Pool Statistics)
5. 等待時間最長的前5個等待事件(Top 5 Timed Events)
含前5等待事件，兩次采樣間cpu占用，內(nèi)存分配等信息。Oracle各版本等待事件并不完全相同，數(shù)量依版本升高而增加，關(guān)于各項等待事情的說明，三思之前的"學(xué)習(xí)動態(tài)性能表"系列文章中有過介紹，有心的朋友可以去搜搜看。
6. DB 所有等待事件(Wait Events)--Total wait time>=0.001 的事件。
7. 后臺等待事件(Background Wait Events)--Total wait time>=0.001 的事件。
8. 柱狀顯示的等待事件(Wait Event Histogram)--顯示各等待事件不同響應(yīng)時間的比例
9. 根據(jù)CPU開銷進行排序的SQL(SQL ordered by CPU)
10. 根據(jù)執(zhí)行時間進行排序的SQL(SQL ordered by Elapsed)
11. 根據(jù)BufferGets進行排序的SQL(SQL ordered by Gets)
12. 根據(jù)物理讀進行排序的SQL(SQL ordered by Reads)
13. 根據(jù)執(zhí)行次數(shù)排序的SQL(SQL ordered by Executions)
14. 根據(jù)解析調(diào)用次數(shù)排序的SQL(SQL ordered by Parse Calls)
15. 實例記錄的各項活動的統(tǒng)計數(shù)據(jù)(Instance Activity Stats)
16. 表空間的IO統(tǒng)計(Tablespace IO Stats)
17. 數(shù)據(jù)文件的IO統(tǒng)計(File IO Stats)
18. 數(shù)據(jù)文件讀柱狀圖形式統(tǒng)計(File Read Histogram Stats)
19. Buffer 池統(tǒng)計數(shù)據(jù)(Buffer Pool Statistics)--含實例恢復(fù)的統(tǒng)計數(shù)據(jù)，buffer池大小設(shè)置建議等等。
20. PGA 統(tǒng)計數(shù)據(jù)(PGA Aggr Target Stats)--含PGA緩存命中率，柱狀圖形式的統(tǒng)計以及PGA設(shè)置建議等等。
21. 進程的內(nèi)存占用情況(Process Memory Summary Stats)--含占用內(nèi)存較多的進程等。
22. undo 段摘要
23. undo 段統(tǒng)計
24. 鎖存器的當(dāng)前情況
25. 鎖存器睡眠等待統(tǒng)計
26. 鎖存器失敗情況
27. 數(shù)據(jù)字典cache性能統(tǒng)計(Dictionary Cache Stats)
28. 庫緩存的活動情況(Library Cache Activity)
29. Rule 集(Rule Sets)
30. 共享池設(shè)置建議(Shared Pool Advisory)
31. SGA 摘要(SGA Memory Summary)
32. SGA 統(tǒng)計信息(SQL Memory Statistics)
33. 系統(tǒng)參數(shù)(init.ora Parameters)
報告很長，確實非常全面。如何分析，還需要再學(xué)習(xí)……
轉(zhuǎn)帖請注明：
本文源自TTT BLOG，原文地址：http://blog.chinaunix.net/u3/107265/showart_2201600.html
免費注冊
發(fā)表于 2011-12-19 13:56