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/，如需轉載，請註明出處，否則將追究法律責任。