-
Apr21
ORACLE STATSPACK REPORT输出结果说明
Posted in Database, 757 views
-
1、报表头信息数据库实例相关信息,包括数据库名称、ID、版本号及主机等信息STATSPACK report forDB Name DB Id Instance Inst Num Release Cluster Host------------ ----------- ------------ -------- ----------- ------- ------------COLM 357371480 colm 1 9.2.0.1.0 NO STEVENHUANG3Snap Id Snap Time Sessions Curs/Sess Comment------- ------------------ -------- --------- -------------------Begin Snap: 3 11-10月-06 22:27:04 13 4.6End Snap: 4 11-10月-06 22:32:02 13 4.9Elapsed: 4.97 (mins)Cache Sizes (end)~~~~~~~~~~~~~~~~~Buffer Cache: 88M Std Block Size: 8KShared Pool Size: 48M Log Buffer: 512K2、负载间档该部分提供每秒和每个事物的统计信息,是监控系统吞吐量和负载变化的重要部分Load Profile~~~~~~~~~~~~ Per Second Per Transaction--------------- ---------------Redo size: 2,184.89 217,032.00Logical reads: 31.99 3,178.00Block changes: 10.09 1,002.33Physical reads: 0.00 0.00Physical writes: 0.07 6.67User calls: 0.06 6.00Parses: 0.80 79.00Hard parses: 0.02 1.67Sorts: 0.42 42.00Logons: 0.01 1.33Executes: 1.56 154.67Transactions: 0.01% Blocks changed per Read: 31.54 Recursive Call %: 98.11Rollback per transaction %: 0.00 Rows per Sort: 73.82说明:Redo size:每秒产生的日志大小(单位字节),可标志数据变更频率, 数据库任务的繁重与否Logical reads:平决每秒产生的逻辑读,单位是blockblock changes:每秒block变化数量,数据库事物带来改变的块数量Physical reads:平均每秒数据库从磁盘读取的block数Physical writes:平均每秒数据库写磁盘的block数User calls:每秒用户call次数Parses: 每秒解析次数,近似反应每秒语句的执行次数, 软解析每秒超过300次意味着你的"应用程序"效率不高,没有使用soft soft parse,调整session_cursor_cacheHard parses:每秒产生的硬解析次数, 每秒超过100次,就可能说明你绑定使用的不好Sorts:每秒产生的排序次数Executes:每秒执行次数Transactions:每秒产生的事务数,反映数据库任务繁重与否Recursive Call %: 如果有很多PLSQL,那么他就会比较高Rollback per transaction %:看回滚率是不是很高,因为回滚很耗资源如果回滚率过高,可能说明你的数据库经历了太多的无效操作过多的回滚可能还会带来Undo Block的竞争该参数计算公式如下:Round(User rollbacks / (user commits + user rollbacks) ,4)* 100%3、实例命中率该部分可以提前找出ORACLE潜在将要发生的性能问题,很重要Instance Efficiency Percentages (Target 100%)~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Buffer Nowait %: 100.00Redo NoWait %: 100.00Buffer Hit %: 100.00In-memory Sort %: 100.00Library Hit %: 99.15Soft Parse %: 97.89Execute to Parse %: 48.92Latch Hit %: 100.00Parse CPU to Parse Elapsd %: %Non-Parse CPU: 100.00说明:Buffer Nowait %:在缓冲区中获取Buffer的未等待比率, Buffer Nowait<99%说明,有可能是有热, 块(查找x$bh的 tch和v$latch_children的cache buffers chains)Redo NoWait %:在Redo缓冲区获取Buffer的未等待比率Buffer Hit %:数据块在数据缓冲区中得命中率,通常应在90%以上,否则,需要调整, 小于 95%,重要的参数,小于90%可能是要加db_cache_size,但是大量的非选择的索引也会造成该值很高(大量的db file sequential read)In-memory Sort %:在内存中的排序率Library Hit %:主要代表sql在共享区的命中率,通常在95%以上,否,需要要考虑加大共享池,绑定变量,修改cursor_sharing等参数。Soft Parse %:近似看作sql在共享区的命中率,小于<95%,需要考虑到绑定,如果低于80%,那么就可能sql基本没有被重用Execute to Parse %:sql语句解析后被重复执行的次数,如果过低,可以考虑设置 session_cached_cursors参数, 公式为100 * (1 - Parses/Executions) = Execute to Parse所以如果系统Parses > Executions,就可能出现该比率小于0的情况, 该值<0通常说明shared pool设置或效率存在问题造成反复解析,reparse可能较严重,或者可是同snapshot有关如果该值为负值或者极低,通常说明数据库性能存在问题Latch Hit %: Latch Hit<99%,要确保>99%,否则存在严重的性能问题,比如绑定等会影响该参数Parse CPU to Parse Elapsd %:解析实际运行事件/(解析实际运行时间+解析中等待资源时间)
越高越好% Non-Parse CPU:查询实际运行时间/(查询实际运行时间+sql解析时间),太低表示解析消耗时间过多。100*(parse time cpu / parse time elapsed)= Parse CPU to Parse Elapsd %Shared Pool Statistics Begin End------ ------Memory Usage %: 63.65 63.75% SQL with executions>1: 63.73 64.12% Memory for SQL w/exec>1: 59.93 60.33Shared Pool相关统计数据Memory Usage %:共享池内存使用率,应该稳定在70%-90%间,太小浪费内存,太大则内存不足。% SQL with executions>1:执行次数大于1的sql比率,若太小可能是没有使用bind variables。% Memory for SQL w/exec>1:也即是memory for sql with execution > 1:执行次数大于1的sql消耗内存/所有sql消耗的内存4.首要的5个等待事件(Top 5 wait events),是整个报告中最能反映问题的一部分运行statspack期间必须session上设置TIMED_STATISTICS = TRUE.(推荐)常见的等待事件以及可能的解决方法1. DB File Scattered Read –-通常与全表扫描有关,需要确认是否真的需要全表扫描,能否改用索引或者把把较小的表整个的放入内存缓冲区中,避免反复磁盘读取2. DB File Sequential Read –表明有很多索引读,需要你调整代码,特别是表连接部分,适当的调整DB_CACHE_SIZE的值3. Free Buffer –没有可用的内存缓冲区而等待,增大DB_CACHE_SIZE,加速检查点和调整代码4. Buffer Busy Wait -–段头出现问题,增加freelists或者freelist maxtrans--数据块问题,分离‘热点’数据,采用反向关键字索引,采用小的数据块,增大initrans和maxtrans--undo header问题,增加回滚段--undo block问题,增加提交频率,增大回滚段5. Latch Free--library Cache问题,使用绑定变量,调整shared_pool_size--shared pool问题,使用绑定变量,调整shared_pool_size--redo Allocation,,最小化redo生成并避免不必要的提交--redo Copy, 增大_log_simultaneous_copies--Row Cache Object,增大共享池--Cache Buffers Chain,_Db_block_Hash_Buckers应被增大或变为质数--Cache Buffers LRU Chain,设置DB_BLOCK_LRU_LACHES或者使用多个缓冲区池6. Enqueue –ST --使用本地表空间或者预先分配大扩展7. Enqueue –HW 预先分配扩展与高水位线之上8. Enqueue –TX4 增大表或者索引的initrans和maxtrans9. Enqueue –TM 为外键建立索引,查看应用程序的表锁10. Log Buffer Space 增大日志缓冲区,重做日志放在快速磁盘上11. Log File Switch 归档设备太慢或者太满,增加或者扩大重做日志12. Log File Sync 每次提交更多记录,更快的存放重做日志的磁盘,裸设备13. idle event 其他的一些等待事件可以忽略Top 5 Timed Events~~~~~~~~~~~~~~~~~~ % TotalEvent Waits Time (s) Ela Time-------------------------------------------- ------------ ----------- --------CPU time 0 52.79control file parallel write 97 0 26.72control file sequential read 52 0 11.55log file parallel write 113 0 6.99log file sync 1 0 .96-------------------------------------------------------------5.等待事件(Wait events)的具体数据Wait Events for DB: COLM Instance: colm Snaps: 3 -4-> s - second-> cs - centisecond - 100th of a second-> ms - millisecond - 1000th of a second-> us - microsecond - 1000000th of a second-> ordered by wait time desc, waits desc (idle events last)AvgTotal Wait wait WaitsEvent Waits Timeouts Time (s) (ms) /txn---------------------------- ------------ ---------- ---------- ------ --------control file parallel write 97 0 0 2 32.3control file sequential read 52 0 0 2 17.3log file parallel write 113 102 0 0 37.7log file sync 1 0 0 7 0.3db file parallel write 12 6 0 1 4.0SQL*Net break/reset to clien 2 0 0 0 0.7virtual circuit status 10 10 300 30005 3.3SQL*Net message from client 4 0 278 69450 1.3wakeup time manager 9 9 277 30732 3.0jobq slave wait 66 63 202 3060 22.0SQL*Net message to client 4 0 0 0 1.3-------------------------------------------------------------Background Wait Events for DB: COLM Instance: colm Snaps: 3 -4-> ordered by wait time desc, waits desc (idle events last)AvgTotal Wait wait WaitsEvent Waits Timeouts Time (s) (ms) /txn---------------------------- ------------ ---------- ---------- ------ --------control file parallel write 97 0 0 2 32.3log file parallel write 113 102 0 0 37.7db file parallel write 12 6 0 1 4.0rdbms ipc message 560 461 3,036 5422 186.7smon timer 1 1 307 ###### 0.3-------------------------------------------------------------6.SQL语句SQL ordered by Gets for DB: COLM Instance: colm Snaps: 3 -4-> End Buffer Gets Threshold: 10000-> Note that resources reported for PL/SQL includes the resources used byall SQL statements called within the PL/SQL code. As individual SQLstatements are also reported, it is possible and valid for the summedtotal % to exceed 100CPU ElapsdBuffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value--------------- ------------ -------------- ------ -------- --------- ----------3,753 9 417.0 39.4 0.11 0.20 238087931select t.schema, t.name, t.flags, q.name from system.aq$_queue_tables t, sys.aq$_queue_table_affinities aft, system.aq$_queues q where aft.table_objno = t.objno and aft.owner_instance = :1 and q.table_objno = t.objno and q.usage = 0 and bitand(t.flags, 4+16+32+64+128+256) = 0 for update of t.name, aft。。。。。。。。。。。。7.实例活动(Instance activity)Instance Activity Stats for DB: COLM Instance: colm Snaps: 3 -4Statistic Total per Second per Trans--------------------------------- ------------------ -------------- ------------CPU used by this session 40 0.1 13.3CPU used when call started 40 0.1 13.3CR blocks created 28 0.1 9.3DBWR checkpoint buffers written 20 0.1 6.7DBWR transaction table writes 10 0.0 3.3DBWR undo block writes 8 0.0 2.7SQL*Net roundtrips to/from client 4 0.0 1.3active txn count during cleanout 34 0.1 11.3background timeouts 350 1.2 116.7buffer is not pinned count 2,775 9.3 925.0buffer is pinned count 4,064 13.6 1,354.7bytes received via SQL*Net from c 883 3.0 294.3bytes sent via SQL*Net to client 752 2.5 250.7calls to get snapshot scn: kcmgss 2,021 6.8 673.7calls to kcmgas 645 2.2 215.0calls to kcmgcs 8 0.0 2.7change write time 6 0.0 2.0cleanout - number of ktugct calls 39 0.1 13.0cleanouts and rollbacks - consist 18 0.1 6.0cleanouts only - consistent read 0 0.0 0.0cluster key scan block gets 11 0.0 3.7cluster key scans 4 0.0 1.3commit cleanout failures: callbac 0 0.0 0.0commit cleanouts 682 2.3 227.3commit cleanouts successfully com 682 2.3 227.3commit txn count during cleanout 14 0.1 4.7consistent changes 444 1.5 148.0consistent gets 7,145 24.0 2,381.7consistent gets - examination 1,271 4.3 423.7cursor authentications 23 0.1 7.7data blocks consistent reads - un 444 1.5 148.0db block changes 3,007 10.1 1,002.3db block gets 2,389 8.0 796.3deferred (CURRENT) block cleanout 415 1.4 138.3enqueue conversions 26 0.1 8.7enqueue releases 1,517 5.1 505.7enqueue requests 1,524 5.1 508.0execute count 464 1.6 154.7free buffer requested 291 1.0 97.0immediate (CR) block cleanout app 18 0.1 6.0immediate (CURRENT) block cleanou 24 0.1 8.0index fetch by key 610 2.1 203.3index scans kdiixs1 3,670 12.3 1,223.3leaf node 90-10 splits 0 0.0 0.0leaf node splits 10 0.0 3.3logons cumulative 4 0.0 1.3messages received 113 0.4 37.7messages sent 113 0.4 37.7no buffer to keep pinned count 0 0.0 0.0no work - consistent read gets 1,565 5.3 521.7opened cursors cumulative 237 0.8 79.0parse count (hard) 5 0.0 1.7parse count (total) 237 0.8 79.0parse time cpu 0 0.0 0.0parse time elapsed 0 0.0 0.0physical reads 0 0.0 0.0Instance Activity Stats for DB: COLM Instance: colm Snaps: 3 -4Statistic Total per Second per Trans--------------------------------- ------------------ -------------- ------------physical reads direct 0 0.0 0.0physical writes 20 0.1 6.7physical writes direct 0 0.0 0.0physical writes non checkpoint 8 0.0 2.7prefetched blocks 0 0.0 0.0process last non-idle time 1,160,577,119 3,894,554.1 ############recursive calls 935 3.1 311.7recursive cpu usage 37 0.1 12.3redo blocks written 1,355 4.6 451.7redo entries 1,547 5.2 515.7redo size 651,096 2,184.9 217,032.0redo synch time 0 0.0 0.0redo synch writes 1 0.0 0.3redo wastage 19,312 64.8 6,437.3redo write time 27 0.1 9.0redo writer latching time 0 0.0 0.0redo writes 113 0.4 37.7rollback changes - undo records a 0 0.0 0.0rollbacks only - consistent read 46 0.2 15.3rows fetched via callback 602 2.0 200.7session connect time 1,160,577,119 3,894,554.1 ############session logical reads 9,534 32.0 3,178.0session uga memory 46,048 154.5 15,349.3session uga memory max 438,768 1,472.4 146,256.0shared hash latch upgrades - no w 3,718 12.5 1,239.3sorts (memory) 126 0.4 42.0sorts (rows) 9,301 31.2 3,100.3switch current to new buffer 198 0.7 66.0table fetch by rowid 868 2.9 289.3table fetch continued row 0 0.0 0.0table scan blocks gotten 1,404 4.7 468.0table scan rows gotten 6,145 20.6 2,048.3table scans (long tables) 2 0.0 0.7table scans (short tables) 249 0.8 83.0user calls 18 0.1 6.0user commits 3 0.0 1.0workarea executions - optimal 89 0.3 29.7-------------------------------------------------------------7.表空间IOTablespace IO Stats for DB: COLM Instance: colm Snaps: 3 -4->ordered by IOs (Reads + Writes) descTablespace------------------------------Av Av Av Av Buffer Av BufReads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)-------------- ------- ------ ------- ------------ -------- ---------- ------UNDOTBS10 0 0.0 18 0 0 0.0SYSTEM0 0 0.0 2 0 0 0.0-------------------------------------------------------------8.文件I/O(File I/O)File IO Stats for DB: COLM Instance: colm Snaps: 3 -4->ordered by Tablespace, FileTablespace Filename------------------------ ----------------------------------------------------Av Av Av Av Buffer Av BufReads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)-------------- ------- ------ ------- ------------ -------- ---------- ------SYSTEM C:\ORACLE\ORADATA\COLM\SYSTEM01.DBF0 0 2 0 0UNDOTBS1 C:\ORACLE\ORADATA\COLM\UNDOTBS01.DBF0 0 18 0 0---------------------------------------------------------------其余的一些收集信息9. Buffer Pool Statistics for DB10. Instance Recovery Stats for DB11. Buffer Pool Advisory for DB12. PGA Aggr Target Stats for DB13. Rollback Segment Stats for DB14. Rollback Segment Storage for DB15. Latch Activity for DB --比较重要的信息16.Dictionary Cache Stats for DB17.Shared Pool Advisory for DBShared Pool Advisory for DB: COLM Instance: colm End Snap: 4-> Note there is often a 1:Many correlation between a single logical objectin the Library Cache, and the physical number of memory objects associatedwith it. Therefore comparing the number of Lib Cache objects (e.g. inv$librarycache), with the number of Lib Cache Memory Objects is invalidEstdShared Pool SP Estd Estd Estd Lib LC TimeSize for Size Lib Cache Lib Cache Cache Time Saved Estd Lib CacheEstim (M) Factr Size (M) Mem Obj Saved (s) Factr Mem Obj Hits----------- ----- ---------- ------------ ------------ ------- ---------------24 .5 20 4,917 766 1.0 41,10132 .7 20 4,917 766 1.0 41,10140 .8 20 4,917 766 1.0 41,10148 1.0 20 4,917 766 1.0 41,10156 1.2 20 4,917 766 1.0 41,10164 1.3 20 4,917 766 1.0 41,10172 1.5 20 4,917 766 1.0 41,10180 1.7 20 4,917 766 1.0 41,10188 1.8 20 4,917 766 1.0 41,10196 2.0 20 4,917 766 1.0 41,101-------------------------------------------------------------18. SGA Memory Summary for DBSGA Memory Summary for DB: COLM Instance: colm Snaps: 3 -4SGA regions Size in Bytes------------------------------ ----------------Database Buffers 92,274,688Fixed Size 453,352Redo Buffers 667,648Variable Size 117,440,512----------------sum 210,836,200-------------------------------------------------------------19. init.ora Parameters for DBinit.ora Parameters for DB: COLM Instance: colm Snaps: 3 -4End valueParameter Name Begin value (if different)----------------------------- --------------------------------- --------------aq_tm_processes 1background_dump_dest C:\oracle\admin\COLM\bdumpcompatible 9.2.0.0.0control_files C:\oracle\oradata\COLM\CONTROL01.core_dump_dest C:\oracle\admin\COLM\cdumpdb_block_size 8192db_cache_advice ONdb_cache_size 83886080db_domaindb_file_multiblock_read_count 32db_keep_cache_size 8388608db_name COLMdispatchers (PROTOCOL=TCP) (SERVICE=COLMXDB)fast_start_mttr_target 0hash_area_size 1048576hash_join_enabled TRUEinstance_name COLMjava_pool_size 33554432job_queue_processes 6large_pool_size 8388608open_cursors 300pga_aggregate_target 33554432processes 150query_rewrite_enabled TRUEremote_login_passwordfile EXCLUSIVEsga_max_size 210836200shared_pool_size 50331648sort_area_size 1048576star_transformation_enabled TRUEtimed_statistics TRUEundo_management AUTOundo_retention 10800undo_tablespace UNDOTBS1user_dump_dest C:\oracle\admin\COLM\udump-------------------------------------------------------------Related posts:

Leave a comment | Trackback 这篇文章有1个评论.
1F lingling
2008-04-21 2:03 pm
酥酥,咱这网站还能再专业点么。。。
好歹写点我能看懂的啊,就说我以后也许没准也会搞技术吧~