"statspack"的一些使用技巧：
一 怎樣修改statspack的腳本產(chǎn)生自定義報(bào)表？
通常statspack報(bào)表可以滿足大部分的需要,有時(shí)我們需要對(duì)產(chǎn)生報(bào)表的腳本進(jìn)行一些微小的修改,這樣產(chǎn)生的報(bào)表將會(huì)更有用途。
比如說(shuō)某些SQL很多,但在statspack產(chǎn)生的報(bào)表中,每個(gè)SQL只顯示5行,結(jié)果有些比較長(zhǎng)的SQL就只能看到一部分;又如在top events部分,標(biāo)準(zhǔn)的報(bào)表只顯示top 5,其實(shí)我們可以顯示更多的events,那如何修改呢？用編輯工具(在linux下用vi)打開(kāi)（$ORACLE_HOME/rdbms/admin/sprepins.sql）
define top_n_events = 5; // top 5 events
define top_n_sql = 65; // top sql
define top_n_segstat = 5; // top 5 segstat
define num_rows_per_hash=5; // 每個(gè)SQL顯示5行
就看到在該腳本中已經(jīng)定義了一些常數(shù),我們只需要把它改為我們需要的值。
define top_n_events = 10; // top 10 events
define top_n_sql = 65; // top sql
define top_n_segstat = 10; // top 10 segstat
define num_rows_per_hash=10; // 每個(gè)SQL顯示10行
修改后,我們就可以看到效果了.
二 如何用statspack的報(bào)表確定熱表及索引？
如果想用statspack表確定熱表及索引,必須修改statspack快照的收集級(jí)別,8i中statspack共有三種快照級(jí)別,默認(rèn)值是5。
select * from STATS$level_DESCRIPTION;
SNAP_LEVEL DESCRIPTION
---------- -------------------------
0 一性性能統(tǒng)計(jì):包含回退段狀態(tài)、字典緩存、SGA、系統(tǒng)事件、后臺(tái)事件、會(huì)話事件、系統(tǒng)統(tǒng)計(jì)、等待統(tǒng)計(jì)、鎖統(tǒng)計(jì)、閂鎖統(tǒng)計(jì)。
5 增加了收集SQL的信息、并包括0級(jí)收集的信息。
10 增加了收集子閂鎖的信息,并包括所有低級(jí)別的信息。
在9i中statspack共有五種快照級(jí)別,默認(rèn)值是5。
select * from STATS$level_DESCRIPTION;
SNAP_LEVEL DESCRIPTION
---------- ----------------------------
0 一性性能統(tǒng)計(jì):包含回退段狀態(tài)、字典緩存、SGA、系統(tǒng)事件、后臺(tái)事件、會(huì)話事件、系統(tǒng)統(tǒng)計(jì)、等待統(tǒng)計(jì)、鎖統(tǒng)計(jì)、閂鎖統(tǒng)計(jì)
5 增加了收集SQL的信息、并包括0級(jí)收集的信息。
6 增強(qiáng)了在SQL收集信息方面的功能(列出占用資源較高的SQL),并包所有低級(jí)別的信息。
7 增加了收集段級(jí)別的統(tǒng)計(jì)信息(如段的邏輯讀與物理讀、行鎖、ITL及buffer busy waits),
并包括所有低級(jí)別的信息。 10 增加了收集子閂鎖的信息,并包括所有低級(jí)別的信息。
如果你收用statspack確定熱表及熱索引,那就需要使用7/10的級(jí)別來(lái)收集快照。
//通過(guò)這樣的設(shè)置，以后的收集級(jí)別都將是7級(jí)。
//如果你只是想本次改變收集級(jí)別，可以忽略i_modify_parameter參數(shù)
SQL>execute statspack.snap(i_snap_level=>7,i_modify_parameter=>true);
SQL>execute statspack.snap(i_snap_level=>7);
修改完收集級(jí)別后,那大家就可以根據(jù)自己的需要設(shè)定收集的頻率,現(xiàn)在我們只需要注意statspack報(bào)表中的“段級(jí)別的統(tǒng)計(jì)信息”:
Top 5 Logical Reads per Segment for DB: ESAL Instance: esal Snaps: 2368 -2380
-> End Segment Logical Reads Threshold: 10000
Subobject Obj. Logical
Owner Tablespace Object Name Name Type Reads %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
CYBERCAFE TS_CYBERCA AGENT_CARD_TYPE TABLE 115,220,864 18.07
CYBERCAFE TS_CYBERCA GAME_CARD_TYPE TABLE 79,103,600 12.40
CYBERCAFE TS_CYBERCA AGENT_TASK TABLE 57,030,304 8.94
CYBERCAFE TS_CYBERCA AGENT_PRICE_LEVEL_OW TABLE 46,393,968 7.28
CYBERCAFE TS_CYBERCA IDX_ASL_RESLOG_ID INDEX 23,261,600 3.65
---------------------------------------------------------
Top 5 Physical Reads per Segment for DB: ESAL Instance: esal Snaps: 2368 -2380
-> End Segment Physical Reads Threshold: 1000
Subobject Obj. Physical
Owner Tablespace Object Name Name Type Reads %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
CYBERCAFE TS_CYBERCA AGENT_GAME_CARD_GM13 TABLE 76,476 7.36
CYBERCAFE TS_CYBERCA AGENT_SALE_LOG ASL_200500 TABLE 61,270 5.89
CYBERCAFE TS_CYBERCA RESELLER_LOG RL_200412 TABLE 48,950 4.71
CYBERCAFE TS_CYBERCA AGENT_GAME_CARD_GM14 TABLE 46,259 4.45
CYBERCAFE TS_CYBERCA AGENT_CAPITAL_LOG ACL_200500 TABLE 45,476 4.37
-------------------------------------------------------------
Top 5 Buf. Busy Waits per Segment for DB: ESAL Instance: esal Snaps: 2368 -2380
-> End Segment Buffer Busy Waits Threshold: 100
Subobject Obj. Buffer Busy
Owner Tablespace Object Name Name Type Waits %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
CYBERCAFE TS_CYBERCA AGENT_TASK TABLE 22 36.07
CYBERCAFE TS_CYBERCA AGENT_CARD_TYPE TABLE 9 14.75
CYBERCAFE TS_CYBERCA IDX_RESACC_UPDTIME INDEX 5 8.20
CYBERCAFE TS_CYBERCA AGENT_SALE_LOG ASL_200501 TABLE 4 6.56
CYBERCAFE TS_CYBERCA IDX_ACL_ACPITAL_LOGI INDEX 4 6.56
-------------------------------------------------------------
Top 5 Row Lock Waits per Segment for DB: ESAL Instance: esal Snaps: 2368 -2380
-> End Segment Row Lock Waits Threshold: 100
Subobject Obj. Row Lock
Owner Tablespace Object Name Name Type Waits %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
CYBERCAFE TS_CYBERCA IDX_APL_GCTID2 APL_200501 INDEX 54 24.77
CYBERCAFE TS_CYBERCA IDX_RB_RESELLER_ID INDEX 41 18.81
CYBERCAFE TS_CYBERCA IDX_RL_RESLOG_ID INDEX 38 17.43
CYBERCAFE TS_CYBERCA IDX_ACT_ACT_ID INDEX 17 7.80
CYBERCAFE TS_CYBERCA IDX_SERVICE_ID INDEX 14 6.42
-------------------------------------------------------------
在這里可以看到邏輯讀/物理讀/緩存忙/行鎖符合條件的一些對(duì)象,通過(guò)這些對(duì)象,可以確定熱的表及索引,然后分析如何對(duì)業(yè)務(wù)進(jìn)行優(yōu)化,降低對(duì)這些表的訪問(wèn)量等。如果你覺(jué)得顯示top 5 segment不夠的話,可以按一所述修改top_n_segstat,然后就可以顯示更多的符合條件的對(duì)象,然后將這些熱表放到keep池中。
三 如何用statspack的報(bào)表確定keep池與default池的分配？
如果你想使用default池與keep池,在9i中需要分配db_cache_size及db_keep_cache_size參數(shù),但如何確定它們的大小呢？我們可以根據(jù)2所示的一些熱表,計(jì)算熱表放入keep池需要的內(nèi)存,然后用將表放入相應(yīng)的pool中。
alter table &table_name storage(buffer_pool &buffer_pool);
將確定的熱表放入keep中之后,然后收集一段時(shí)間后再產(chǎn)生一個(gè)新的報(bào)表:
Buffer Pool Statistics for DB: ESAL Instance: esal Snaps: 2277 -2289
-> Standard block size Pools D: default, K: keep, R: recycle
-> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k
Free Write Buffer
Number of Cache Buffer Physical Physical Buffer Complete Busy
P Buffers Hit % Gets Reads Writes Waits Waits Waits
--- ---------- ----- ----------- ----------- ---------- ------- -------- ------
D 128,128 99.7 482,298,597 1,557,980 265,662 0 0 88
K 32,032 100.0 372,560,023 13,951 42,405 0 0 17
-------------------------------------------------------------
確定keep池與default的需要內(nèi)存時(shí),可以根據(jù)這一部分對(duì)keep池與default池的大小進(jìn)行評(píng)估,如果K所標(biāo)識(shí)的cache hit%比較小,說(shuō)明keep池不足,如果D顯示的cache hit%比較小,說(shuō)明default池分配懷足,如果K是的default顯示是100%,那們可以將更多的熱表放入到keep池中，然后經(jīng)過(guò)一段時(shí)間的調(diào)整,相信可以將default池與keep池調(diào)到一個(gè)相對(duì)比較合適的集團(tuán)。
四 如何用crontab定期產(chǎn)生statspack的報(bào)表？
看了一段時(shí)間的statspack報(bào)表后,就懶于每天手工去產(chǎn)生一個(gè)報(bào)表,那如何產(chǎn)系統(tǒng)自動(dòng)產(chǎn)生一個(gè)報(bào)表呢?
經(jīng)過(guò)測(cè)試,用crontab可以方便地產(chǎn)生報(bào)表,然后通過(guò)sendmail直接發(fā)到相關(guān)人員的郵箱中。
[oracle@www1 sql]$ more backup/auto_send_perf.sh
#!/bin/sh
. ~oracle/.bash_profile
/home/oracle/product/9.2.0/bin/sqlplus -s aaa/bbb@ccc< set head off
set timing off
spool /home/oracle/sql/backup/snap_begin.lst
select min(snap_id) snap_id
from stats$snapshot
where snap_time between trunc(sysdate) and trunc(sysdate)+1;
spool off
spool /home/oracle/sql/backup/snap_end.lst
select max(snap_id) snap_id
from stats$snapshot
where snap_time between trunc(sysdate) and trunc(sysdate)+1;
spool off
exit
!
BEGIN_SNAP=`cat /home/oracle/sql/backup/snap_begin.lst | tail -n 2`
END_SNAP=`cat /home/oracle/sql/backup/snap_end.lst | tail -n 2`
#END_SNAP=`expr $BEGIN_SNAP + 13`
REPORT_NAME=/home/oracle/sql/report/sp`date +%m%d`_ac
/home/oracle/product/9.2.0/bin/sqlplus -s aaa/bbb@ccc< define begin_snap=$BEGIN_SNAP
define end_snap=$END_SNAP
define report_name=$REPORT_NAME
@?/rdbms/admin/spreport
exit
!
mail -s "perfstat report" ddd@eee.fff
< /home/oracle/sql/report/sp`date +%m%d`_ac.lst
[oracle@www1 sql]$crontab -l
* 21 * * * /home/oracle/sql/backup/auto_send_perf.sh >>
/home/oracle/sql/backup/perf.lst 2>&1
注釋:早上8點(diǎn)至晚上8點(diǎn)之間進(jìn)行快照收集,9點(diǎn)執(zhí)行cron進(jìn)程啟動(dòng),產(chǎn)生報(bào)表的快照也限于當(dāng)天收集的快照,將當(dāng)天最小的snap_id與的snap_id放到兩個(gè)文件中,在sheel中讀出,并計(jì)算出一個(gè)報(bào)表名稱,
最后產(chǎn)生的報(bào)表通過(guò)"sendmail"發(fā)送到相關(guān)人員的郵箱,此后每晚只需要收郵件就即以看到當(dāng)天的報(bào)表。
一 怎樣修改statspack的腳本產(chǎn)生自定義報(bào)表？
通常statspack報(bào)表可以滿足大部分的需要,有時(shí)我們需要對(duì)產(chǎn)生報(bào)表的腳本進(jìn)行一些微小的修改,這樣產(chǎn)生的報(bào)表將會(huì)更有用途。
比如說(shuō)某些SQL很多,但在statspack產(chǎn)生的報(bào)表中,每個(gè)SQL只顯示5行,結(jié)果有些比較長(zhǎng)的SQL就只能看到一部分;又如在top events部分,標(biāo)準(zhǔn)的報(bào)表只顯示top 5,其實(shí)我們可以顯示更多的events,那如何修改呢？用編輯工具(在linux下用vi)打開(kāi)（$ORACLE_HOME/rdbms/admin/sprepins.sql）
define top_n_events = 5; // top 5 events
define top_n_sql = 65; // top sql
define top_n_segstat = 5; // top 5 segstat
define num_rows_per_hash=5; // 每個(gè)SQL顯示5行
就看到在該腳本中已經(jīng)定義了一些常數(shù),我們只需要把它改為我們需要的值。
define top_n_events = 10; // top 10 events
define top_n_sql = 65; // top sql
define top_n_segstat = 10; // top 10 segstat
define num_rows_per_hash=10; // 每個(gè)SQL顯示10行
修改后,我們就可以看到效果了.
二 如何用statspack的報(bào)表確定熱表及索引？
如果想用statspack表確定熱表及索引,必須修改statspack快照的收集級(jí)別,8i中statspack共有三種快照級(jí)別,默認(rèn)值是5。
select * from STATS$level_DESCRIPTION;
SNAP_LEVEL DESCRIPTION
---------- -------------------------
0 一性性能統(tǒng)計(jì):包含回退段狀態(tài)、字典緩存、SGA、系統(tǒng)事件、后臺(tái)事件、會(huì)話事件、系統(tǒng)統(tǒng)計(jì)、等待統(tǒng)計(jì)、鎖統(tǒng)計(jì)、閂鎖統(tǒng)計(jì)。
5 增加了收集SQL的信息、并包括0級(jí)收集的信息。
10 增加了收集子閂鎖的信息,并包括所有低級(jí)別的信息。
在9i中statspack共有五種快照級(jí)別,默認(rèn)值是5。
select * from STATS$level_DESCRIPTION;
SNAP_LEVEL DESCRIPTION
---------- ----------------------------
0 一性性能統(tǒng)計(jì):包含回退段狀態(tài)、字典緩存、SGA、系統(tǒng)事件、后臺(tái)事件、會(huì)話事件、系統(tǒng)統(tǒng)計(jì)、等待統(tǒng)計(jì)、鎖統(tǒng)計(jì)、閂鎖統(tǒng)計(jì)
5 增加了收集SQL的信息、并包括0級(jí)收集的信息。
6 增強(qiáng)了在SQL收集信息方面的功能(列出占用資源較高的SQL),并包所有低級(jí)別的信息。
7 增加了收集段級(jí)別的統(tǒng)計(jì)信息(如段的邏輯讀與物理讀、行鎖、ITL及buffer busy waits),
并包括所有低級(jí)別的信息。 10 增加了收集子閂鎖的信息,并包括所有低級(jí)別的信息。
如果你收用statspack確定熱表及熱索引,那就需要使用7/10的級(jí)別來(lái)收集快照。
//通過(guò)這樣的設(shè)置，以后的收集級(jí)別都將是7級(jí)。
//如果你只是想本次改變收集級(jí)別，可以忽略i_modify_parameter參數(shù)
SQL>execute statspack.snap(i_snap_level=>7,i_modify_parameter=>true);
SQL>execute statspack.snap(i_snap_level=>7);
修改完收集級(jí)別后,那大家就可以根據(jù)自己的需要設(shè)定收集的頻率,現(xiàn)在我們只需要注意statspack報(bào)表中的“段級(jí)別的統(tǒng)計(jì)信息”:
Top 5 Logical Reads per Segment for DB: ESAL Instance: esal Snaps: 2368 -2380
-> End Segment Logical Reads Threshold: 10000
Subobject Obj. Logical
Owner Tablespace Object Name Name Type Reads %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
CYBERCAFE TS_CYBERCA AGENT_CARD_TYPE TABLE 115,220,864 18.07
CYBERCAFE TS_CYBERCA GAME_CARD_TYPE TABLE 79,103,600 12.40
CYBERCAFE TS_CYBERCA AGENT_TASK TABLE 57,030,304 8.94
CYBERCAFE TS_CYBERCA AGENT_PRICE_LEVEL_OW TABLE 46,393,968 7.28
CYBERCAFE TS_CYBERCA IDX_ASL_RESLOG_ID INDEX 23,261,600 3.65
---------------------------------------------------------
Top 5 Physical Reads per Segment for DB: ESAL Instance: esal Snaps: 2368 -2380
-> End Segment Physical Reads Threshold: 1000
Subobject Obj. Physical
Owner Tablespace Object Name Name Type Reads %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
CYBERCAFE TS_CYBERCA AGENT_GAME_CARD_GM13 TABLE 76,476 7.36
CYBERCAFE TS_CYBERCA AGENT_SALE_LOG ASL_200500 TABLE 61,270 5.89
CYBERCAFE TS_CYBERCA RESELLER_LOG RL_200412 TABLE 48,950 4.71
CYBERCAFE TS_CYBERCA AGENT_GAME_CARD_GM14 TABLE 46,259 4.45
CYBERCAFE TS_CYBERCA AGENT_CAPITAL_LOG ACL_200500 TABLE 45,476 4.37
-------------------------------------------------------------
Top 5 Buf. Busy Waits per Segment for DB: ESAL Instance: esal Snaps: 2368 -2380
-> End Segment Buffer Busy Waits Threshold: 100
Subobject Obj. Buffer Busy
Owner Tablespace Object Name Name Type Waits %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
CYBERCAFE TS_CYBERCA AGENT_TASK TABLE 22 36.07
CYBERCAFE TS_CYBERCA AGENT_CARD_TYPE TABLE 9 14.75
CYBERCAFE TS_CYBERCA IDX_RESACC_UPDTIME INDEX 5 8.20
CYBERCAFE TS_CYBERCA AGENT_SALE_LOG ASL_200501 TABLE 4 6.56
CYBERCAFE TS_CYBERCA IDX_ACL_ACPITAL_LOGI INDEX 4 6.56
-------------------------------------------------------------
Top 5 Row Lock Waits per Segment for DB: ESAL Instance: esal Snaps: 2368 -2380
-> End Segment Row Lock Waits Threshold: 100
Subobject Obj. Row Lock
Owner Tablespace Object Name Name Type Waits %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
CYBERCAFE TS_CYBERCA IDX_APL_GCTID2 APL_200501 INDEX 54 24.77
CYBERCAFE TS_CYBERCA IDX_RB_RESELLER_ID INDEX 41 18.81
CYBERCAFE TS_CYBERCA IDX_RL_RESLOG_ID INDEX 38 17.43
CYBERCAFE TS_CYBERCA IDX_ACT_ACT_ID INDEX 17 7.80
CYBERCAFE TS_CYBERCA IDX_SERVICE_ID INDEX 14 6.42
-------------------------------------------------------------
在這里可以看到邏輯讀/物理讀/緩存忙/行鎖符合條件的一些對(duì)象,通過(guò)這些對(duì)象,可以確定熱的表及索引,然后分析如何對(duì)業(yè)務(wù)進(jìn)行優(yōu)化,降低對(duì)這些表的訪問(wèn)量等。如果你覺(jué)得顯示top 5 segment不夠的話,可以按一所述修改top_n_segstat,然后就可以顯示更多的符合條件的對(duì)象,然后將這些熱表放到keep池中。
三 如何用statspack的報(bào)表確定keep池與default池的分配？
如果你想使用default池與keep池,在9i中需要分配db_cache_size及db_keep_cache_size參數(shù),但如何確定它們的大小呢？我們可以根據(jù)2所示的一些熱表,計(jì)算熱表放入keep池需要的內(nèi)存,然后用將表放入相應(yīng)的pool中。
alter table &table_name storage(buffer_pool &buffer_pool);
將確定的熱表放入keep中之后,然后收集一段時(shí)間后再產(chǎn)生一個(gè)新的報(bào)表:
Buffer Pool Statistics for DB: ESAL Instance: esal Snaps: 2277 -2289
-> Standard block size Pools D: default, K: keep, R: recycle
-> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k
Free Write Buffer
Number of Cache Buffer Physical Physical Buffer Complete Busy
P Buffers Hit % Gets Reads Writes Waits Waits Waits
--- ---------- ----- ----------- ----------- ---------- ------- -------- ------
D 128,128 99.7 482,298,597 1,557,980 265,662 0 0 88
K 32,032 100.0 372,560,023 13,951 42,405 0 0 17
-------------------------------------------------------------
確定keep池與default的需要內(nèi)存時(shí),可以根據(jù)這一部分對(duì)keep池與default池的大小進(jìn)行評(píng)估,如果K所標(biāo)識(shí)的cache hit%比較小,說(shuō)明keep池不足,如果D顯示的cache hit%比較小,說(shuō)明default池分配懷足,如果K是的default顯示是100%,那們可以將更多的熱表放入到keep池中，然后經(jīng)過(guò)一段時(shí)間的調(diào)整,相信可以將default池與keep池調(diào)到一個(gè)相對(duì)比較合適的集團(tuán)。
四 如何用crontab定期產(chǎn)生statspack的報(bào)表？
看了一段時(shí)間的statspack報(bào)表后,就懶于每天手工去產(chǎn)生一個(gè)報(bào)表,那如何產(chǎn)系統(tǒng)自動(dòng)產(chǎn)生一個(gè)報(bào)表呢?
經(jīng)過(guò)測(cè)試,用crontab可以方便地產(chǎn)生報(bào)表,然后通過(guò)sendmail直接發(fā)到相關(guān)人員的郵箱中。
[oracle@www1 sql]$ more backup/auto_send_perf.sh
#!/bin/sh
. ~oracle/.bash_profile
/home/oracle/product/9.2.0/bin/sqlplus -s aaa/bbb@ccc< set head off
set timing off
spool /home/oracle/sql/backup/snap_begin.lst
select min(snap_id) snap_id
from stats$snapshot
where snap_time between trunc(sysdate) and trunc(sysdate)+1;
spool off
spool /home/oracle/sql/backup/snap_end.lst
select max(snap_id) snap_id
from stats$snapshot
where snap_time between trunc(sysdate) and trunc(sysdate)+1;
spool off
exit
!
BEGIN_SNAP=`cat /home/oracle/sql/backup/snap_begin.lst | tail -n 2`
END_SNAP=`cat /home/oracle/sql/backup/snap_end.lst | tail -n 2`
#END_SNAP=`expr $BEGIN_SNAP + 13`
REPORT_NAME=/home/oracle/sql/report/sp`date +%m%d`_ac
/home/oracle/product/9.2.0/bin/sqlplus -s aaa/bbb@ccc< define begin_snap=$BEGIN_SNAP
define end_snap=$END_SNAP
define report_name=$REPORT_NAME
@?/rdbms/admin/spreport
exit
!
mail -s "perfstat report" ddd@eee.fff
< /home/oracle/sql/report/sp`date +%m%d`_ac.lst
[oracle@www1 sql]$crontab -l
* 21 * * * /home/oracle/sql/backup/auto_send_perf.sh >>
/home/oracle/sql/backup/perf.lst 2>&1
注釋:早上8點(diǎn)至晚上8點(diǎn)之間進(jìn)行快照收集,9點(diǎn)執(zhí)行cron進(jìn)程啟動(dòng),產(chǎn)生報(bào)表的快照也限于當(dāng)天收集的快照,將當(dāng)天最小的snap_id與的snap_id放到兩個(gè)文件中,在sheel中讀出,并計(jì)算出一個(gè)報(bào)表名稱,
最后產(chǎn)生的報(bào)表通過(guò)"sendmail"發(fā)送到相關(guān)人員的郵箱,此后每晚只需要收郵件就即以看到當(dāng)天的報(bào)表。