OS:LINUX(DEBIAN)
DB VERSION:9204
CPU:2
MEM:2G
最近数据库突然很忙,没办法,只有进行调优,调优之前的数据库状态如下:
SQL> select * from v$system_event where event like '%wait%';
EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT TIME_WAITED_MICRO
-------------------- ----------- -------------- ----------- ------------ -----------------
free buffer waits 830018 810608 81392200 98 8.1392E+11
local write wait 5524 5383 537033 97 5370332470
write complete waits 11183 11062 1108677 99 1.1087E+10
buffer busy waits 7248388 20665 5306393 1 5.3064E+10
LGWR wait for redo c 13522 4516 4594 0 45944228
opy
wait for stopper eve 69380 62490 622963 9 6229631070
nt to be increased
wait for a undo reco 150484 132209 1310736 9 1.3107E+10
查询v$session_wait得到的数据如下:
SQL> select event,p1,p2,p3,wait_time from v$session_wait where event not like '%SQL%' AND EVENT NOT LIKE '%smon%' and event not like '%ipc%' and event not like '%pmon%'
2 ;
EVENT P1 P2 P3 WAIT_TIME
-------------------- ---------- ---------- ---------- ----------
db file scattered re 7 1024656 16 0
ad
db file scattered re 7 1099975 2 -1
ad
查询dba_extents 视图发现主要是
SQL> select * from dba_extents where file_id='7' and 1024656 between block_id and block_id+blocks-1;
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME
EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------------------------------ --------------------------------------------------------------------------------- ------------------------------ ------------------ -----------------
------------- ---------- ---------- ---------- ---------- ---------- ------------
SMS T_S200408 TABLE SMSSYSTEM
132 7 1024009 8388608 1024 7
SQL> select * from dba_extents where file_id='7' and 1099975 between block_id and block_id+blocks-1;
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME
EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------------------------------ --------------------------------------------------------------------------------- ------------------------------ ------------------ -----------------
------------- ---------- ---------- ---------- ---------- ---------- ------------
SMS T_MOBILETABLE TABLE SMSSYSTEM
10 7 1099785 8388608 1024 7
在T_S200408这个表中目前有数据700,0000多万数据
在T_MOBILETABE表中有数据大概为500,0000多万数据
现在buffer hit ratio 只有%80,但是buffer_cache_size 有800M
(硬件限制,目前最大只能给这么多了)
请问各位大虾,什么导致buffer hit ratio 只有这么底呢,是不是大表的全表扫描过多??
还有FREE_BUFFER_BUSY跟buffer busy waits 在不断的增加。
比较匆忙,有时间用STATSPACE做个REPORT上来!
有什么好的建议????先说说吧,谢谢
DB VERSION:9204
CPU:2
MEM:2G
最近数据库突然很忙,没办法,只有进行调优,调优之前的数据库状态如下:
SQL> select * from v$system_event where event like '%wait%';
EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT TIME_WAITED_MICRO
-------------------- ----------- -------------- ----------- ------------ -----------------
free buffer waits 830018 810608 81392200 98 8.1392E+11
local write wait 5524 5383 537033 97 5370332470
write complete waits 11183 11062 1108677 99 1.1087E+10
buffer busy waits 7248388 20665 5306393 1 5.3064E+10
LGWR wait for redo c 13522 4516 4594 0 45944228
opy
wait for stopper eve 69380 62490 622963 9 6229631070
nt to be increased
wait for a undo reco 150484 132209 1310736 9 1.3107E+10
查询v$session_wait得到的数据如下:
SQL> select event,p1,p2,p3,wait_time from v$session_wait where event not like '%SQL%' AND EVENT NOT LIKE '%smon%' and event not like '%ipc%' and event not like '%pmon%'
2 ;
EVENT P1 P2 P3 WAIT_TIME
-------------------- ---------- ---------- ---------- ----------
db file scattered re 7 1024656 16 0
ad
db file scattered re 7 1099975 2 -1
ad
查询dba_extents 视图发现主要是
SQL> select * from dba_extents where file_id='7' and 1024656 between block_id and block_id+blocks-1;
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME
EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------------------------------ --------------------------------------------------------------------------------- ------------------------------ ------------------ -----------------
------------- ---------- ---------- ---------- ---------- ---------- ------------
SMS T_S200408 TABLE SMSSYSTEM
132 7 1024009 8388608 1024 7
SQL> select * from dba_extents where file_id='7' and 1099975 between block_id and block_id+blocks-1;
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME
EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------------------------------ --------------------------------------------------------------------------------- ------------------------------ ------------------ -----------------
------------- ---------- ---------- ---------- ---------- ---------- ------------
SMS T_MOBILETABLE TABLE SMSSYSTEM
10 7 1099785 8388608 1024 7
在T_S200408这个表中目前有数据700,0000多万数据
在T_MOBILETABE表中有数据大概为500,0000多万数据
现在buffer hit ratio 只有%80,但是buffer_cache_size 有800M
(硬件限制,目前最大只能给这么多了)
请问各位大虾,什么导致buffer hit ratio 只有这么底呢,是不是大表的全表扫描过多??
还有FREE_BUFFER_BUSY跟buffer busy waits 在不断的增加。
比较匆忙,有时间用STATSPACE做个REPORT上来!
有什么好的建议????先说说吧,谢谢
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28419/viewspace-613739/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28419/viewspace-613739/