statspack 사용법
[STATSPACK] – 8.1.6에서 나온기능으로 이하버젼에서는 수행 불가 – STATSPACK에 대한 자세한 내용은 $ORACLE_HOME/rdbms/admin/statspack.doc 또는 Note:94224.1 <statspack 사용법>
1. STATSPACK 설치 cd $ORACLE_HOME/rdbms/admin sqlplus internal @statscre
==> perfstat user가 생성되고 필요한 package, table, view가 생성된다. ==> perfstat user의 default, temp tablespace를 입력해야하고 table이 저장될 tablespace를 입력해야 한다.
– 주의 사항 system tablespace를 사용하지 말것 최소 35Mbyte 이상의 tablespace를 사용할 것 설치시 error가 발생하면 지우고 다시 시도
2. STATSPACK의 실행 cd $ORACLE_HOME/rdbms/admin sqlplus perfstat/perfstat exec statspack.snap;
==> 주기적으로 원하는 시간을 주고 반복 수행한다. ==> OPS환경에서는 각각의 node에서 수행해야 한다. ==> 자동으로 수행하게 할때는 dbms_job(statsauto.sql 참고 : 1시간 간격으로 수행함)을 사용 하거나 OS cron, shell등을 사용한다.
==> level 정의 예) exec statspack.snap(i_snap_level=>0); Level >= 0 : default Level >= 5 : + SQL 정보 Level >= 10 : + High Chile Latch information
– 주의사항 utlbstat/utlestat와는 동시에 수행하면 않된다.
3. STATSPACK의 report 생성 cd $ORACLE_HOME/rdbms/admin sqlplus perfstat/perfstat @statsrep
==> DB의 정보가 display되고 statspack을 수행한 정보가 display되면 원하는 시간대의 Snap Id (beginning Snap Id, ending Snap Id)를 넣고 output file이름을 입력한다.
– 주의사항 쌓인 정보가 많을때 보다나은 performance를 위해서는 다음을 수행할 필요가 있다. execute dbms_utility.analyze_schema(‘PERFSTAT’,’COMPUTE’); 또는 execute dbms_stats.gather_schema_stats(‘PERFSTAT’);
4. STATSPACK의 삭제 cd $ORACLE_HOME/rdbms/admin sqlplus perfstat/perfstat @statsdrp
아래는 그래서 나온 결과임.
STATSPACK report for
DB Name DB Id Instance Inst Num Release OPS Host ———- ———– ———- ——– ———- —- ———- V81664 1480384946 V81664 1 8.1.6.2.0 NO tchp1
Snap Length Start Id End Id Start Time End Time (Minutes) ——– ——– ——————– ——————– ———– 1 2 27-Sep-00 01:56:11 27-Sep-00 01:57:21 1.17
Cache Sizes ~~~~~~~~~~~ db_block_buffers: 3562 db_block_size: 8192 log_buffer: 163840 shared_pool_size: 12000000
Load Profile ~~~~~~~~~~~~ Per Second Per Transaction ————— ————— Redo size: 4,809.37 336,656.00 Logical reads: 147.21 10,305.00 Block changes: 9.66 676.00 Physical reads: 6.06 424.00 Physical writes: 4.36 305.00 User calls: 2.44 171.00 Parses: 1.54 108.00 Hard parses: 0.13 9.00 Sorts: 0.86 60.00 Transactions: 0.01
Rows per Sort: 85.53 Pct Blocks changed / Read: 6.56 Recursive Call Pct: 88.13 Rollback / transaction Pct: 0.00
Instance Efficiency Percentages (Target 100%) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait Ratio: 100.00 Buffer Hit Ratio: 95.89 Library Hit Ratio: 87.94 Redo NoWait Ratio: 99.73 In-memory Sort Ratio: 100.00 Soft Parse Ratio: 91.67 Latch Hit Ratio: 100.00
Top 5 Wait Events ~~~~~~~~~~~~~~~~~ Wait % Total Event Waits Time (cs) Wt Time ——————————————– ———— ———— ——- db file sequential read 420 0 .00 control file sequential read 47 0 .00 control file parallel write 29 0 .00 log file parallel write 12 0 .00 db file parallel write 6 0 .00 ————————————————————- Wait Events for DB: V81664 Instance: V81664 Snaps: 1 – 2 ->cs – centisecond – 100th of a second ->ms – millisecond – 1000th of a second (unit often used for disk IO timings)
Avg Total Wait wait Waits Event Waits Timeouts Time (cs) (ms) /txn —————————- ———— ———- ———– —– —— db file sequential read 420 0 0 0 420.0 control file sequential read 47 0 0 0 47.0 control file parallel write 29 0 0 0 29.0 log file parallel write 12 0 0 0 12.0 db file parallel write 6 0 0 0 6.0 refresh controlfile command 5 0 0 0 5.0 file identify 2 0 0 0 2.0 log file single write 2 0 0 0 2.0 file open 2 0 0 0 2.0 LGWR wait for redo copy 1 0 0 0 1.0 log file sync 1 0 0 0 1.0 SQL*Net break/reset to clien 1 0 0 0 1.0 log file sequential read 1 0 0 0 1.0 db file scattered read 1 0 0 0 1.0 log file switch completion 1 0 0 0 1.0 SQL*Net message from client 166 0 0 0 166.0 SQL*Net message to client 166 0 0 0 166.0 PX Idle Wait 34 34 0 0 34.0 ————————————————————- Background Wait Events for DB: V81664 Instance: V81664 Snaps: 1 –
Avg Total Wait wait Waits Event Waits Timeouts Time (cs) (ms) /txn —————————- ———— ———- ———– —– —— control file parallel write 29 0 0 0 29.0 control file sequential read 27 0 0 0 27.0 log file parallel write 12 0 0 0 12.0 db file parallel write 6 0 0 0 6.0 file identify 2 0 0 0 2.0 log file single write 2 0 0 0 2.0 file open 2 0 0 0 2.0 LGWR wait for redo copy 1 0 0 0 1.0 log file sequential read 1 0 0 0 1.0 rdbms ipc message 84 68 0 0 84.0 pmon timer 23 23 0 0 23.0 ————————————————————- SQL ordered by Gets for DB: V81664 Instance: V81664 Snaps: 1 – 2
Gets % of Buffer Gets Executes per Exec Total Hash Value ————– ———— ———— —— ———— SQL statement —————————————————————————— 21 7 3.0 .2 395844583 select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,nvl(
15 3 5.0 .1 199702406 select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags, i.property,i.p
10 1 10.0 .1 1737259834 select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, d_owner#
9 3 3.0 .1 2216582187 select ts#,file#,block#,nvl(bobj#,0),nvl(tab#,0),intcols,nvl(clucols,0),audit$
9 3 3.0 .1 2918884618 select node,owner,name from syn$ where obj#=:1
9 2 4.5 .1 2893641427 insert into idl_sb4$(obj#,part,version,piece#,length,piece) values(:1,:2,:3,:4
8 4 2.0 .1 1966425544 select text from view$ where rowid=:1
8 2 4.0 .1 4195740643 select pos#,intcol#,col#,spare1 from icol$ where obj#=:1
7 3 2.3 .1 955191413 select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1 from ob
7 2 3.5 .1 1142460911 insert into idl_ub2$(obj#,part,version,piece#,length,piece) values(:1,:2,:3,:4
7 2 3.5 .1 1004464078 select grantee#,privilege#,nvl(col#,0),max(nvl(option$,0)) from objauth$ where
6 1 6.0 .1 4049165760 select order#,columns,types from access$ where d_obj#=:1
4 1 4.0 .0 181436173 select /*+ index(idl_sb4$ i_idl_sb41) +*/ max(version) from idl_sb4$ where o
4 2 2.0 .0 1536916657 select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,nvl(enabled,0),
4 2 2.0 .0 4261939565 select col#, grantee#, privilege#,max(nvl(option$,0)) from objauth$ where obj#
3 1 3.0 .0 114083071 update obj$ set obj#=:6,type#=:7,ctime=:8,mtime=:9,stime=:10,status=:11,dataob
3 1 3.0 .0 365454555 select cols,audit$,textlength,intcols,property,flags,rowid from view$ where ob
————————————————————- SQL ordered by Reads for DB: V81664 Instance: V81664 Snaps: 1 – 2
Physical Reads % of Reads Executes per Exec Total Hash Value ————– ———— ———— —— ———— SQL statement —————————————————————————— 2 7 0.3 .5 395844583 select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,nvl(
2 3 0.7 .5 955191413 select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1 from ob
2 2 1.0 .5 1004464078 select grantee#,privilege#,nvl(col#,0),max(nvl(option$,0)) from objauth$ where
2 1 2.0 .5 1737259834 select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, d_owner#
2 3 0.7 .5 2216582187 select ts#,file#,block#,nvl(bobj#,0),nvl(tab#,0),intcols,nvl(clucols,0),audit$
2 2 1.0 .5 4261939565 select col#, grantee#, privilege#,max(nvl(option$,0)) from objauth$ where obj#
2 1 2.0 .5 4049165760 select order#,columns,types from access$ where d_obj#=:1
1 3 0.3 .2 199702406 select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags, i.property,i.p
1 1 1.0 .2 365454555 select cols,audit$,textlength,intcols,property,flags,rowid from view$ where ob
1 2 0.5 .2 1142460911 insert into idl_ub2$(obj#,part,version,piece#,length,piece) values(:1,:2,:3,:4
1 3 0.3 .2 2918884618 select node,owner,name from syn$ where obj#=:1
1 2 0.5 .2 4195740643 select pos#,intcol#,col#,spare1 from icol$ where obj#=:1
1 4 0.3 .2 1966425544 select text from view$ where rowid=:1
0 1 0.0 .0 114083071 update obj$ set obj#=:6,type#=:7,ctime=:8,mtime=:9,stime=:10,status=:11,dataob
0 1 0.0 .0 181436173 select /*+ index(idl_sb4$ i_idl_sb41) +*/ max(version) from idl_sb4$ where o
0 1 0.0 .0 1428100621 select /*+ index(idl_ub2$ i_idl_ub21) +*/ piece#,length,piece from idl_ub2$ wh
0 1 0.0 .0 3218356218 select /*+ index(idl_sb4$ i_idl_sb41) +*/ piece#,length,piece from idl_sb4$ wh
————————————————————- SQL ordered by Rows for DB: V81664 Instance: V81664 Snaps: 1 – 2
Rows Rows Processed Executes per Exec Hash Value ————– ———— ———— ———— SQL statement —————————————————————————— 33 7 4.7 395844583 select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,nvl(
4 4 1.0 1966425544 select text from view$ where rowid=:1
3 2 1.5 1004464078 select grantee#,privilege#,nvl(col#,0),max(nvl(option$,0)) from objauth$ where
3 3 1.0 2216582187 select ts#,file#,block#,nvl(bobj#,0),nvl(tab#,0),intcols,nvl(clucols,0),audit$
3 3 1.0 2918884618 select node,owner,name from syn$ where obj#=:1
2 3 0.7 199702406 select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags, i.property,i.p
2 1 2.0 1737259834 select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, d_owner#
2 2 1.0 2893641427 insert into idl_sb4$(obj#,part,version,piece#,length,piece) values(:1,:2,:3,:4
2 2 1.0 1142460911 insert into idl_ub2$(obj#,part,version,piece#,length,piece) values(:1,:2,:3,:4
2 2 1.0 4195740643 select pos#,intcol#,col#,spare1 from icol$ where obj#=:1
2 1 2.0 4049165760 select order#,columns,types from access$ where d_obj#=:1
1 1 1.0 114083071 update obj$ set obj#=:6,type#=:7,ctime=:8,mtime=:9,stime=:10,status=:11,dataob
1 3 0.3 955191413 select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1 from ob
1 1 1.0 365454555 select cols,audit$,textlength,intcols,property,flags,rowid from view$ where ob
1 1 1.0 181436173 select /*+ index(idl_sb4$ i_idl_sb41) +*/ max(version) from idl_sb4$ where o
0 1 0.0 957616262 select /*+ index(idl_char$ i_idl_char1) +*/ piece#,length,piece from idl_char$
0 1 0.0 1428100621 select /*+ index(idl_ub2$ i_idl_ub21) +*/ piece#,length,piece from idl_ub2$ wh
————————————————————- Instance Activity Stats for DB: V81664 Instance: V81664 Snaps: 1 –
Statistic Total per Second per Trans ——————————— —————- ———— ———— CR blocks created 0 0.0 0.0 DBWR buffers scanned 198 2.8 198.0 DBWR checkpoint buffers written 184 2.6 184.0 DBWR checkpoints 1 0.0 1.0 DBWR free buffers found 143 2.0 143.0 DBWR lru scans 4 0.1 4.0 DBWR make free requests 8 0.1 8.0 DBWR summed scan depth 198 2.8 198.0 DBWR transaction table writes 4 0.1 4.0 DBWR undo block writes 38 0.5 38.0 SQL*Net roundtrips to/from client 163 2.3 163.0 background checkpoints completed 0 0.0 0.0 background checkpoints started 1 0.0 1.0 background timeouts 68 1.0 68.0 buffer is not pinned count 6,009 85.8 6,009.0 buffer is pinned count 4,478 64.0 4,478.0 bytes received via SQL*Net from c 11,542 164.9 11,542.0 bytes sent via SQL*Net to client 82,780 1,182.6 82,780.0 calls to get snapshot scn: kcmgss 127 1.8 127.0 calls to kcmgas 9 0.1 9.0 calls to kcmgcs 14 0.2 14.0 cleanouts and rollbacks – consist 0 0.0 0.0 cleanouts only – consistent read 28 0.4 28.0 cluster key scan block gets 2,292 32.7 2,292.0 cluster key scans 1,662 23.7 1,662.0 commit cleanout failures: block l 0 0.0 0.0 commit cleanout failures: buffer 0 0.0 0.0 commit cleanout failures: callbac 2 0.0 2.0 commit cleanout failures: cannot 0 0.0 0.0 commit cleanouts 66 0.9 66.0 commit cleanouts successfully com 64 0.9 64.0 consistent changes 0 0.0 0.0 consistent gets 8,547 122.1 8,547.0 current blocks converted for CR cursor authentications 22 0.3 22.0 data blocks consistent reads – un 0 0.0 0.0 db block changes 676 9.7 676.0 db block gets 1,758 25.1 1,758.0 deferred (CURRENT) block cleanout 29 0.4 29.0 dirty buffers inspected 68 1.0 68.0 enqueue conversions 0 0.0 0.0 enqueue releases 65 0.9 65.0 enqueue requests 66 0.9 66.0 enqueue timeouts 0 0.0 0.0 execute count 119 1.7 119.0 free buffer inspected 68 1.0 68.0 free buffer requested 468 6.7 468.0 hot buffers moved to head of LRU 86 1.2 86.0 immediate (CR) block cleanout app 28 0.4 28.0 immediate (CURRENT) block cleanou 17 0.2 17.0 leaf node splits 6 0.1 6.0 logons cumulative 3 0.0 3.0 logons current messages received 28 0.4 28.0 messages sent 28 0.4 28.0 no buffer to keep pinned count 1,602 22.9 1,602.0 Instance Activity Stats for DB: V81664 Instance: V81664 Snaps: 1 –
Statistic Total per Second per Trans ——————————— —————- ———— ———— no work – consistent read gets 5,219 74.6 5,219.0 opened cursors cumulative 101 1.4 101.0 parse count (hard) 9 0.1 9.0 parse count (total) 108 1.5 108.0 physical reads 424 6.1 424.0 physical reads direct 0 0.0 0.0 physical writes 305 4.4 305.0 physical writes direct 0 0.0 0.0 physical writes non checkpoint 266 3.8 266.0 pinned buffers inspected 0 0.0 0.0 prefetched blocks 2 0.0 2.0 prefetched blocks aged out before 0 0.0 0.0 recursive calls 1,269
Comments