ITPub博客

首页 > Linux操作系统 > Linux操作系统 > update 与 物理读 引起的性能问题

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

原创 Linux操作系统 作者:parameters 时间:2011-08-31 10:23:00 0 删除 编辑
昨天数据库 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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: ora 01000
请登录后发表评论 登录
全部评论

注册时间:2011-04-07

  • 博文量
    45
  • 访问量
    50213