40 积分	0 好友	14 主题

发消息

oracle数据库突然变慢，如何查变慢原因

1^#

发表于 2012-1-17 14:39:10 | 查看: 10496| 回复: 6

opas 观察发现数据库一直比较繁忙
Disk Busy%    KBPS    TPS KB-Read KB-Writ                MEMORY
Topas Monitor for host: xtydb1             EVENTS/QUEUES FILE/TTY
Tue Jan 17 12:37:40 2012 Interval:  2       Cswitch 11136  Readch  2868.6K
                                             Syscall  120.8K  Writech 75916
CPU  User%  Kern%  Wait%  Idle%                Reads    7571  Rawin       0
ALL 45.6 7.6 3.7 43.0                Writes    263  Ttyout    616
                                             Forks       5  Igets       0
Network  KBPS I-Pack  O-Pack KB-In  KB-Out  Execs       5  Namei    7096
Total 14.1K  8834.4  8204.6  7877.0  6607.2  Runqueue 2.5  Dirblk       0
                                             Waitqueue 0.0
Disk Busy%    KBPS    TPS KB-Read KB-Writ                MEMORY
Total 100.0 1820.9 215.0  1739.1 81.8  PAGING          Real,MB 19584
                                             Faults    5774  % Comp    92
FileSystem       KBPS    TPS KB-Read KB-Writ  Steals       0  % Noncomp 6
Total          956.1    6.7K 956.1 0.0 PgspIn       0  % Client 6
                                             PgspOut    0
Name          PID  CPU%  PgSp Owner          PageIn       0  PAGING SPACE
oracle    402018 8.3  13.0 oracle       PageOut    0  Size,MB 16384
oracle    393604 6.5  11.2 oracle       Sios       0  % Used    12
oracle    684284 6.2  22.6 oracle                         % Free    88
oracle    622726 5.7  10.6 oracle       NFS (calls/sec)
oracle    660004 5.3  22.2 oracle       SerV2       0  WPAR Activ 0
oracle    381288 4.4  11.0 oracle       CliV2       0  WPAR Total 0
oracle    426580 2.7  12.4 oracle       SerV3       0  Press: "h"-help
oracle    213048 2.6  22.0 oracle       CliV3       0       "q"-quit
oracle    602462 1.8  21.8 oracle
oracle    139602 1.7  22.0 oracle
oracle    225732 1.3  13.0 oracle
oracle    1118234 1.2  12.8 oracle
oracle    234132 1.1  22.0 oracle
oracle    713194 0.7  12.9 oracle
oracle    422624 0.4  11.6 oracle
oracle    397700 0.3  49.3 oracle
crsd.bin    115150 0.2  47.6 root
oracle    483568 0.1  12.8 oracle
oracle    737938 0.1  13.0 oracle

alert日志没有明显错误
下面的是awr报告里面的一些参数。

[ 本帖最后由 taoy_2008 于 2012-1-17 23:01 编辑 ]

分享0

收藏0 回复只看该作者道具举报

Maclean Liu(刘相兵

2^#

发表于 2012-1-17 15:41:22

请上传性能缓慢时段的AWR报告为附件不要直接贴网页内容

回复只看该作者道具举报

Maclean Liu(刘相兵

3^#

发表于 2012-1-17 16:48:06

Elapsed:         660.04 (mins)
DB Time:         1,109.87 (mins)

采集的快照跨度过长，可能无法准确体现性能问题

主要等待事件

Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
db file sequential read    3,488,886    23,158    7    34.8 User I/O
CPU time         19,116         28.7
db file scattered read    3,404,971    9,265    3    13.9 User I/O
gc buffer busy    874,339    3,491    4    5.2 Cluster
db file parallel read    480,237    2,559    5    3.8 User I/O

db file sequential read 3,488,886次  =》　　88次/s

buffer_cache       9,216 MB

主要物理读的数据段

hysical Reads Executions Reads per Exec %Total CPU Time (s) Elapsed Time (s)    SQL Id SQL Module SQL Text
15,982,618 52 307,358.04 30.31 1048.45 6375.58 6hbmsf3kw0p4x    sys_main.exe SELECT fin_ar_balance_v.billno...
8,393,026 15 559,535.07 15.92 780.23 3963.24 1wz1pz742yqbq    sys_main.exe SELECT t.compid, t.ownerid, t...

Segments by Physical Reads
Total Physical Reads: 52,735,603
Captured Segments account for 93.0% of Total
Owner Tablespace Name Object Name Subobject Name Obj. Type Physical Reads %Total
CMSXTY USERS FIN_AR_BILL      TABLE 23,347,028 44.27
CMSXTY USERS FIN_AR_LIST      TABLE 12,155,378 23.05
CMSXTY USERS SCM_SALBILL_HDR      TABLE 2,762,880 5.24

回复只看该作者道具举报

Maclean Liu(刘相兵

4^#

发表于 2012-1-17 16:50:37

Statistic Total per Second per Trans

table fetch continued row 4,982,070 125.80 83.17

table fetch continued row=> 125次/s

说明系统中数据存在严重的链式行、迁移行(chained rows、migrated rows) 可能是造成大量db file sequential read 的主要原因

建议：

对FIN_AR_BILL FIN_AR_LIST 这几个表做链式行分析 chained rows analyze 确认存在问题数据行的比例

回复只看该作者道具举报