update 与 物理读 引起的性能问题

昨天数据库 io 忽然变的非常高，造成系统性能下降严重；经过排查发发现一个update操作的物理读竟然非常高；解决过程记录一下；下面的数据是进行的测试；
Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONE 构造表一个测试表

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，将全表缓存，以提升性能；

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

转载于:http://blog.itpub.net/25656398/viewspace-706314/