昨天
数据库 io 忽然变的非常高，造成系统性能下降严重；经过排查发发现一个update操作的物理读竟然非常高；解决过程记录一下；下面的数据是进行的测试；
构造表一个测试表
scott@192.168.0.123:ORCL>select count(1) from emptest;
COUNT(1)
----------
14028
查看表的block数
scott@192.168.0.123:ORCL>select t.segment_name,t.bytes,t.blocks from dba_segments t where t.segment_name='EMPTEST';
SEGMENT_NA BYTES BLOCKS
---------- ---------- ----------
EMPTEST 786432 96
确保在buffer cache中没有相关的缓存：
scott@192.168.0.123:ORCL>alter system flush buffer_cache;
系统已更改。
查看全部扫描的执行计划：
scott@192.168.0.123:ORCL>alter system flush buffer_cache;
系统已更改。
scott@192.168.0.123:ORCL>set autot trace exp
scott@192.168.0.123:ORCL>select * from scott.emptest;
执行计划
----------------------------------------------------------
Plan hash value: 1471160681
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14028 | 506K| 27 (4)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMPTEST | 14028 | 506K| 27 (4)| 00:00:01 |
-----------------------------------------------------------------------------
刷出buffer cache；
Alter system flush buffer_cache;
此时做update并查看执行计划
scott@192.168.0.123:ORCL>alter system flush buffer_cache;
系统已更改。
scott@192.168.0.123:ORCL>set autot off
scott@192.168.0.123:ORCL>set autot on
scott@192.168.0.123:ORCL>alter system flush buffer_cache;
系统已更改。
scott@192.168.0.123:ORCL>update scott.emptest set empno=9999 where rownum=1;
已更新1行。
执行计划
----------------------------------------------------------
Plan hash value: 2944935809
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 13 | 26 (0)| 00:00:01 |
| 1 | UPDATE | EMPTEST | | | | |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | TABLE ACCESS FULL| EMPTEST | 14028 | 178K| 26 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM=1)
统计信息
----------------------------------------------------------
17 recursive calls
3 db block gets
8 consistent gets
9 physical reads
484 redo size
673 bytes sent via SQL*Net to client
586 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
scott@192.168.0.123:ORCL>
第二次再更新：
scott@192.168.0.123:ORCL>update scott.emptest set empno=9999 where rownum=1;
已更新1行。
执行计划
----------------------------------------------------------
Plan hash value: 2944935809
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 13 | 26 (0)| 00:00:01 |
| 1 | UPDATE | EMPTEST | | | | |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | TABLE ACCESS FULL| EMPTEST | 14028 | 178K| 26 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM=1)
统计信息
----------------------------------------------------------
0 recursive calls
4 db block gets
4 consistent gets
0 physical reads
0 redo size
675 bytes sent via SQL*Net to client
586 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
scott@192.168.0.123:ORCL>
Select * from scott.emptest;
将所有block缓存：
已选择14028行。
执行计划
----------------------------------------------------------
Plan hash value: 1471160681
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14028 | 506K| 27 (4)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMPTEST | 14028 | 506K| 27 (4)| 00:00:01 |
-----------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1021 consistent gets
83 physical reads
0 redo size
718562 bytes sent via SQL*Net to client
10670 bytes received via SQL*Net from client
937 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14028 rows processed
再次：
scott@192.168.0.123:ORCL>update scott.emptest set empno=9999 where rownum=1;
已更新1行。
执行计划
----------------------------------------------------------
Plan hash value: 2944935809
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 13 | 26 (0)| 00:00:01 |
| 1 | UPDATE | EMPTEST | | | | |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | TABLE ACCESS FULL| EMPTEST | 14028 | 178K| 26 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM=1)
统计信息
----------------------------------------------------------
0 recursive calls
4 db block gets
4 consistent gets
0 physical reads
608 redo size
678 bytes sent via SQL*Net to client
586 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
scott@192.168.0.123:ORCL>
所以有缓存时会降低物理读；在某些情况下，可以使用keep pool，将全表缓存，以提升性能；
当前位置：
论坛首页 »
自由讨论区 »
MySQL, Oracle及数据库讨论区
»
发起投票
技术讨论
加好友
发消息
2011-9-2 14:18:06 |
赞(
操作
