详细解读 STATSPACK 报告2

最新推荐文章于 2024-05-20 19:10:28 发布

cmkcnufwy33419835

最新推荐文章于 2024-05-20 19:10:28 发布

阅读量168

点赞数

文章标签：数据库

6、实例的活动信息

这部分数据主要是从V$SYSSTAT表中统计出来的，一些条目的详细内容会在后面逐条标注。

Instance Activity Stats for DB: ORA92 Instance: ora92 Snaps: 13 -14

Statistic Total per Second per Trans

--------------------------------- ------------------ -------------- ------------

CPU used by this session 36,055 42.8 0.5

CPU used when call started 9,526 11.3 0.1

CR blocks created 9,509 11.3 0.1

DBWR buffers scanned 12,962 15.4 0.2

DBWR checkpoint buffers written 87,437 103.7 1.2

DBWR checkpoints 1 0.0 0.0

DBWR free buffers found 12,700 15.1 0.2

DBWR lru scans 116 0.1 0.0

DBWR make free requests 124 0.2 0.0

DBWR summed scan depth 12,962 15.4 0.2

DBWR transaction table writes 23 0.0 0.0

DBWR undo block writes 18,974 22.5 0.3

PX local messages recv'd 0 0.0 0.0

PX local messages sent 0 0.0 0.0

SQL*Net roundtrips to/from client 436,777 518.1 5.8

SQL*Net roundtrips to/from dblink 482 0.6 0.0

active txn count during cleanout 8,651 10.3 0.1

background checkpoints completed 2 0.0 0.0

background checkpoints started 1 0.0 0.0

background timeouts 1,288 1.5 0.0

branch node splits 6 0.0 0.0

buffer is not pinned count 2,170,225 2,574.4 28.7

buffer is pinned count 2,694,289 3,196.1 35.6

bytes received via SQL*Net from c 35,743,183 42,400.0 472.8

bytes received via SQL*Net from d 123,793 146.9 1.6

bytes sent via SQL*Net to client 25,187,619 29,878.6 333.1

bytes sent via SQL*Net to dblink 76,754 91.1 1.0

calls to get snapshot scn: kcmgss 1,533,555 1,819.2 20.3

calls to kcmgas 149,646 177.5 2.0

calls to kcmgcs 10,190 12.1 0.1

change write time 762 0.9 0.0

cleanout - number of ktugct calls 13,095 15.5 0.2

cluster key scan block gets 424 0.5 0.0

cluster key scans 202 0.2 0.0

commit cleanout failures: block l 1 0.0 0.0

commit cleanout failures: buffer 18 0.0 0.0

commit cleanout failures: callbac 63 0.1 0.0

commit cleanout failures: cannot 2,087 2.5 0.0

commit cleanouts 643,505 763.4 8.5

commit cleanouts successfully com 641,336 760.8 8.5

commit txn count during cleanout 35,188 41.7 0.5

consistent changes 63,943 75.9 0.9

consistent gets 16,616,758 19,711.5 219.8

由consistent gets，db block gets和physical reads这三个值，我们也可以计算得到buffer hit ratio，计算的公式如下： buffer hit ratio = 100*(1-physical reads /(consistent gets+ db block gets))，例如在这里，我们可以计算得到：buffer hit ratio =100*(1-26524/(16616758+2941398))= 99.86

consistent gets - examination 1,168,584 1,386.2 15.5

current blocks converted for CR 0 0.0 0.0

cursor authentications 2 0.0 0.0

data blocks consistent reads - un 63,873 75.8 0.8

db block changes 2,596,938 3,080.6 34.4

db block gets 2,941,398 3,489.2 38.9

deferred (CURRENT) block cleanout 130,783 155.1 1.7

dirty buffers inspected 166 0.2 0.0

脏数据从LRU列表中老化，A value here indicates that the DBWR is not keeping up。如果这个值大于0，就需要考虑增加DBWRs。

dirty buffers inspected: This is the number of dirty (modified) data buffers that were aged out on the LRU list. You may benefit by adding more DBWRs.If it is greater than 0, consider increasing the database writes.

enqueue conversions 485 0.6 0.0

enqueue deadlocks 0 0.0 0.0

enqueue releases 318,825 378.2 4.2

enqueue requests 318,825 378.2 4.2

enqueue timeouts 0 0.0 0.0

Instance Activity Stats for DB: ORA92 Instance: ora92 Snaps: 13 -14

Statistic Total per Second per Trans

--------------------------------- ------------------ -------------- ------------

enqueue waits 728 0.9 0.0

exchange deadlocks 30 0.0 0.0

execute count 1,004,280 1,191.3 13.3

free buffer inspected 188 0.2 0.0

这个值包含dirty，pinned，busy的buffer区域，如果free buffer inspected - dirty buffers inspected - buffer is pinned count的值还是比较大，表明不能被重用的内存块比较多，这将导致latch争用，需要增大buffer cache。

free buffer requested 116,422 138.1 1.5

hot buffers moved to head of LRU 17,750 21.1 0.2

immediate (CR) block cleanout app 1,916 2.3 0.0

immediate (CURRENT) block cleanou 81,385 96.5 1.1

index fast full scans (full) 0 0.0 0.0

index fetch by key 335,907 398.5 4.4

index scans kdiixs1 692,053 820.9 9.2

leaf node 90-10 splits 418 0.5 0.0

leaf node splits 1,941 2.3 0.0

logons cumulative 716 0.9 0.0

messages received 67,830 80.5 0.9

messages sent 67,830 80.5 0.9

no buffer to keep pinned count 0 0.0 0.0

no work - consistent read gets 14,240,381 16,892.5 188.4

opened cursors cumulative 84,306 100.0 1.1

parse count (failures) 6,074 7.2 0.1

parse count (hard) 6,090 7.2 0.1

parse count (total) 191,531 227.2 2.5

通过parse count (hard)和parse count (total)，可以计算soft parse率为：

100-100*(parse count (hard)/parse count (total)) =100-100*(1-6090/191531)=96.82

parse time cpu 44 0.1 0.0

parse time elapsed 635 0.8 0.0

physical reads 26,524 31.5 0.4

physical reads direct 288 0.3 0.0

physical writes 87,993 104.4 1.2

physical writes direct 128 0.2 0.0

physical writes non checkpoint 29,010 34.4 0.4

pinned buffers inspected 0 0.0 0.0

prefetch clients - default 0 0.0 0.0

prefetched blocks 16,550 19.6 0.2

prefetched blocks aged out before 0 0.0 0.0

process last non-idle time 0 0.0 0.0

recursive calls 1,398,277 1,658.7 18.5

recursive cpu usage 27,349 32.4 0.4

redo blocks written 749,639 889.3 9.9

redo buffer allocation retries 13 0.0 0.0

redo entries 1,343,828 1,594.1 17.8

redo log space requests 19 0.0 0.0

redo log space wait time 38 0.1 0.0

redo ordering marks 0 0.0 0.0

redo size 355,818,888 422,086.5 4,706.2

redo synch time 10,483 12.4 0.1

redo synch writes 74,372 88.2 1.0

redo wastage 15,765,096 18,701.2 208.5

redo write time 6,171 7.3 0.1

redo writer latching time 3 0.0 0.0

redo writes 67,055 79.5 0.9

rollback changes - undo records a 250 0.3 0.0

rows fetched via callback 310,070 367.8 4.1

session connect time 0 0.0 0.0

session cursor cache count 1,818 2.2 0.0

session cursor cache hits 168,798 200.2 2.2

session logical reads 19,558,052 23,200.5 258.7

session pga memory 549,909,680 652,324.7 7,273.4

Instance Activity Stats for DB: ORA92 Instance: ora92 Snaps: 13 -14

Statistic Total per Second per Trans

--------------------------------- ------------------ -------------- ------------

session pga memory max 1,185,992,768 1,406,871.6 15,686.5

session uga memory 3,015,076,014,672 ############## ############

session uga memory max 175,484,416 208,166.6 2,321.0

shared hash latch upgrades - no w 675,962 801.9 8.9

shared hash latch upgrades - wait 3,460 4.1 0.1

sorts (disks) 0 0 0

磁盘排序一般不能超过5％。如果超过5％，需要设置参数PGA_AGGREGATE_TARGET或者 SORT_AREA_SIZE，注意，这里SORT_AREA_SIZE是分配给每个用户的，PGA_AGGREGATE_TARGET则是针对所有的session的一个总数设置。

sorts (memory) 180,293 213.9 2.4

内存中的排序数量

sorts (rows) 511,574 606.9 6.8

summed dirty queue length 430 0.5 0.0

switch current to new buffer 59,534 70.6 0.8

table fetch by rowid 2,094,274 2,484.3 27.7

这是通过索引或者where rowid=语句来取得的行数，当然这个值越大越好。

table fetch continued row 408 0.5 0.0

这是发生行迁移的行。当行迁移的情况比较严重时，需要对这部分进行优化。

检查行迁移的方法：

1）运行$ORACLE_HOME/rdbms/admin/utlchain.sql

2） analyze table table_name list chained rows into CHAINED_ROWS

3） select * from CHAINED_ROWS where table_name='table_name';

清除的方法：

方法1：create table table_name_tmp as select * from table_name where rowed in (select head_rowid from chained_rows);

Delete from table_name where rowed in (select head_rowid from chained_rows);

Insert into table_name select * from table_name_tmp;

方法2：create table table_name_tmp select * from table_name ;

truncate table table_name

insert into table_name select * from table_name_tmp

方法3：用exp工具导出表，然后删除这个表，最后用imp工具导入这表

方法4：alter table table_name move tablespace tablespace_name，然后再重新表的索引

上面的4种方法可以用以消除已经存在的行迁移现象，但是行迁移的产生很多情况下时由于PCT_FREE参数设置的太小所导致，所以需要调整PCT_FREE参数的值。

table scan blocks gotten 299,249 355.0 4.0

table scan rows gotten 1,912,851 2,269.1 25.3

table scans (long tables) 0 0.0 0.0

longtables就是表的大小超过buffer buffer* _SMALL_TABLE_THRESHOLD的表。如果一个数据库的大表扫描过多，那么db file scattered read等待事件可能同样非常显著。如果table scans (long tables)的per Trans值大于0，你可能需要增加适当的索引来优化你的SQL语句。

table scans (short tables) 143,830 170.6 1.9

short tables是指表的长度低于buffer chache 2％（2％是有隐含参数_SMALL_TABLE_THRESHOLD定义的，这个参数在oracle不同的版本中，有不同的含义。在9i和10g中，该参数值定义为2%，在8i中，该参数值为20个blocks，在v7中，该参数为5个blocks）的表。这些表将优先使用全表扫描。一般不使用索引。_SMALL_TABLE_THRESHOLD值的计算方法如下（9i,8K）： (db_cache_size/8192)*2％。

注意：_SMALL_TABLE_THRESHOLD参数修改是相当危险的操作。

transaction rollbacks 70 0.1 0.0

transaction tables consistent rea 0 0.0 0.0

user calls 345,054 409.3 4.6

user commits 75,587 89.7 1.0

user rollbacks 19 0.0 0.0

workarea executions - optimal 247,121 293.1 3.3

write clones created in backgroun 0 0.0 0.0

write clones created in foregroun 25 0.0 0.0

7、I/O统计信息

下面两个报表是面向I/O的。通常，在这里期望在各设备上的读取和写入操作是均匀分布的。要找出什么文件可能非常“热”。一旦DBA了解了如何读取和写入这些数据，他们也许能够通过磁盘间更均匀的分配I/O而得到某些性能提升。

在这里主要关注Av Rd(ms)列 (reads per millisecond)的值，一般来说，大部分的磁盘系统的这个值都能调整到14ms以下，oracle认为该值超过20ms都是不必要的。如果该值超过1000ms，基本可以肯定存在I/O的性能瓶颈。如果在这一列上出现######，可能是你的系统存在严重的I/O问题，也可能是格式的显示问题。

当出现上面的问题，我们可以考虑以下的方法：

1）优化操作该表空间或者文件的相关的语句。

2）如果该表空间包含了索引，可以考虑压缩索引，是索引的分布空间减小，从而减小I/O。

3）将该表空间分散在多个逻辑卷中，平衡I/O的负载。

4）我们可以通过设置参数DB_FILE_MULTIBLOCK_READ_COUNT来调整读取的并行度，这将提高全表扫描的效率。但是也会带来一个问题，就是oracle会因此更多的使用全表扫描而放弃某些索引的使用。为解决这个问题，我们需要设置另外一个参数OPTIMIZER_INDEX_COST_ADJ=30（一般建议设置10－50）。

关于OPTIMIZER_INDEX_COST_ADJ＝n：该参数是一个百分比值，缺省值为100，可以理解为FULL SCAN COST/INDEX SCAN COST。当n%* INDEX SCAN COST

5）检查并调整I/O设备的性能。

Tablespace IO Stats for DB: ORA92 Instance: ora92 Snaps: 13 -14

->ordered by IOs (Reads + Writes) desc

Tablespace

------------------------------

Av Av Av Av Buffer Av Buf

Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)

-------------- ------- ------ ------- ------------ -------- ---------- ------

ICD_DXH_IDX

3,869 5 8.6 1.0 36,701 44 1,180 0.1

ICD_DXH_DAT

2,572 3 9.7 1.0 16,545 20 1,076 0.2

UNDOTBS1

16 0 86.9 1.0 19,084 23 283 0.0

ICD_DXH_HISIDX

689 1 30.8 1.0 14,953 18 108 0.0

ICD_DXH_HISDAT

2,756 3 7.9 7.3 1,082 1 3 0.0

PERFSTAT

215 0 6.0 1.0 193 0 0 0.0

SYSTEM

55 0 11.5 5.0 17 0 717 0.1

INDX

1 0 40.0 1.0 1 0 0 0.0

TOOLS

1 0 40.0 1.0 1 0 0 0.0

USERS

1 0 40.0 1.0 1 0 0 0.0

-------------------------------------------------------------

File IO Stats for DB: ORA92 Instance: ora92 Snaps: 13 -14

->ordered by Tablespace, File

Tablespace Filename

------------------------ ----------------------------------------------------

Av Av Av Av Buffer Av Buf

Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)

-------------- ------- ------ ------- ------------ -------- ---------- ------

ICD_DXH_DAT /dev/rlv_data001

377 0 9.4 1.0 1,640 2 321 0.2

/dev/rlv_data002

327 0 9.0 1.0 1,630 2 169 0.0

/dev/rlv_data003

313 0 10.0 1.0 1,718 2 87 0.0

/dev/rlv_data004

357 0 9.9 1.0 1,661 2 86 0.0

/dev/rlv_data005

400 0 11.5 1.0 1,627 2 128 0.2

/dev/rlv_data006

389 0 8.5 1.0 6,700 8 126 0.0

/dev/rlv_data007

409 0 9.6 1.0 1,569 2 159 0.7

8、Buffer Pool统计信息

这里将buffer poll细分，列举default、keep、recycle三种类型的buffer的详细情况。在这份报告中，我们的系统中只使用Default size的buffer pool。这里的3个waits统计，其实在前面的等待时间中已经包含，所以可以参考前面的描述。关于命中率也已经在前面讨论。

所以，其实这段信息不需要怎么关注。

Buffer Pool Statistics for DB: ORA92 Instance: ora92 Snaps: 13 -14

-> 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 635,200 99.9 19,556,815 26,990 88,450 0 0 3,368

9、实例的恢复情况统计信息

这部分主要是关于实例的恢复的一些统计信息，也不需要怎么关注。

Instance Recovery Stats for DB: ORA92 Instance: ora92 Snaps: 13 -14

-> B: Begin snapshot, E: End snapshot

Targt Estd Log File Log Ckpt Log Ckpt

MTTR MTTR Recovery Actual Target Size Timeout Interval

(s) (s) Estd IOs Redo Blks Redo Blks Redo Blks Redo Blks Redo Blks

- ----- ----- ---------- ---------- ---------- ---------- ---------- ----------

B 300 70 54311 452198 450720 450720 1224858

E 300 69 53127 452947 450720 450720 1472619

10、Buffer Pool调整的Advisory

这是oracle的对buffer pool的大小的调整建议。从advisory的数据看，当然buffer是越大，物理读更小，随着buffer的增大，对物理读的性能改进越来越小。当前buffer 设置为5,120M，物理读因子=1。我们可以看到，buffer pool在3G之前的扩大，对物理读的改善非常明显，之后，这种改善的程度越来越低。

Buffer Pool Advisory for DB: ORA92 Instance: ora92 End Snap: 14

-> Only rows with estimated physical reads >0 are displayed

-> ordered by Block Size, Buffers For Estimate (default block size first)

Size for Size Buffers for Est Physical Estimated

P Estimate (M) Factr Estimate Read Factor Physical Reads

--- ------------ ----- ---------------- ------------- ------------------

D 512 .1 63,520 9.85 245,880,558

D 1,024 .2 127,040 5.41 134,932,093

D 1,536 .3 190,560 3.38 84,471,707

D 2,048 .4 254,080 2.41 60,240,471

D 2,560 .5 317,600 1.86 46,399,611

D 3,072 .6 381,120 1.54 38,365,243

D 3,584 .7 444,640 1.32 33,017,978

D 4,096 .8 508,160 1.18 29,353,901

D 4,608 .9 571,680 1.07 26,763,133

D 5,120 1.0 635,200 1.00 24,962,078

D 5,632 1.1 698,720 0.95 23,661,399

D 6,144 1.2 762,240 0.91 22,672,122

D 6,656 1.3 825,760 0.88 21,902,502

D 7,168 1.4 889,280 0.85 21,277,585

D 7,680 1.5 952,800 0.83 20,755,944

D 8,192 1.6 1,016,320 0.81 20,331,009

D 8,704 1.7 1,079,840 0.80 19,949,127

D 9,216 1.8 1,143,360 0.78 19,563,065

D 9,728 1.9 1,206,880 0.77 19,226,351

D 10,240 2.0 1,270,400 0.76 18,948,622

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22782597/viewspace-619433/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22782597/viewspace-619433/

cmkcnufwy33419835

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
详细解读 STATSPACK 报告2

6、实例的活动信息这部分数据主要是从V$SYSSTAT表中统计出来的，一些条目的详细内容会在后面逐条标注。Instance Activity Stats for DB: ORA92 Instance: ora92 S...
复制链接

扫一扫