Statspack總結
工作中總結的一點點Statspack的用法，記錄一下
1.How to create the statspack?
create tablespace perfstat
datafile 'c:\oracle\ora9i\oradata\irmdb\perfstat01.dbf' size 100M
autoextend on
next 10m
maxsize unlimited
conn / as sysdba;
@?\rdbms\admin\spcreate.sql
exec statspack.snap
2,How to modify the script. of statspack to generate the customize report?
vi $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 (how many stats for segment will be shown)
define num_rows_per_hash = 5; //the number of line for each SQL
3.How to identify the Hot table/index by using statspack report
in Oracle9i we can query the table and list the result as below
select * from STATS$level_description;
"0" ----This level captures general statistics, including rollback segment, row cache, SGA, system events, background events, session events, system statistics, wait st
atistics, lock statistics, and Latch information
"5" ----This level includes capturing high resource usage SQL Statements, along with all data captured by lower levels
"6" ----This level includes capturing SQL plan and SQL plan usage information for high r
esource usage SQL Statements, along with all data captured by lower levels
"7" ----This level captures segment level statistics, including logical and physical reads, row lock, itl and buffer busy waits, along with all data captured by lower l
evels
"10" ----This level includes capturing Child Latch statistics, along with all data captur
ed by lower levels
we can use the script
exec statspack.snap(i_snap_level=>7);
to collect the level of statspack
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
-------------------------------------------------------------
we modified the top_n_segstat to list more stats for segment,and put them into keep pool
db_keep_cache_size indicates the size of keep pool
by using the command-----alter table &table_name storage (buffer_pool keep);
db_recycle_cache_size indicates the size of recycle pool
by using the command-----alter table &table_name storage (buffer_pool recycle);
db_cache_size indicates the size of default pool
4.How to identify the allocation for both keep pool and default pool?
by using the command-----alter table &table_name storage(buffer_pool &buffer_pool)
we can move some tables into corresponding buffer_pool such as recycle,keep each about default
and then recollect the statspacke and generate the report as below:
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
-------------------------------------------------------------
the script. for collect snapshot
************************************************************************
#!/bin/sh
. ~oracle/.bash_profile
/home/oracle/product/9.2.0/bin/sqlplus -s icmadmin/passw0rd@icmnlsdb<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 icmadmin/passw0rd@icmnlsdb<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
************************************************************************
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12361284/viewspace-6532/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 效能優化 - Oracle Tuning 總結 2-1 Statspack優化Oracle
- Statspack之一-Statspack簡介
- Statspack之三-安裝statspack
- 【STATSPACK】Statspack安裝、測試與使用
- statspack分析
- statspack解析
- 【實驗】【STATSPACK】Statspack 安裝、測試與使用
- Statspack之四-測試安裝好的Statspack
- Oracle之StatspackOracle
- 【statspack安裝】
- statspack 初學
- oracle statspack詳解Oracle
- Oracle Statspack的使用Oracle
- Oracle statspack綜合分析Oracle
- oracle statspack 詳解Oracle
- statspack report分析
- 簡易操作statspack
- statspack安裝記
- Statspack之十三-EnqueueENQ
- statspack 報告分析
- Oracle Statspack ReportOracle
- javaSE總結（轉+總結）Java
- statspack安裝與使用
- Oracle statspack工具使用解析Oracle
- 【statspack級別設定】
- STATSPACK資料清除（二）
- STATSPACK資料清除（一）
- 詳解statspack 報告
- statspack的安裝配置
- STATSPACK資料清除（三）
- statspack report分析 (zt)
- 總結？
- this總結
- 總結
- statspack報告分析摘錄
- Use the statspack to generate the accurate explain planAI
- 【筆記】statspack 學習（一）筆記
- Statspack分析報告說明