我们都知道drop table, truncate table时都会先做一次checkpoint,将被删除对象的脏块写入磁盘。

客户有一套系统,Oracle 9.2.0.8,需要做数据迁移,由于种种原因,采用的是逻辑迁移的方式。由于库比较大,超过了1.5T,而停机时间又有限,因此在正式迁移之前需要做大量的测试,测试的目的,一方面是看迁移流程上是否存在问题,另一方面是看迁移的时候,哪个地方会存在性能瓶颈,并进行优化,同时估算实施迁移时间。

第一次测试后,需要把测试产生的大量用户及其对象全部删除,删除用的是drop user username cascade。不幸的是这种方式删除得相当地慢。一个9000多个表的用户,删除了1个半小时才删除了4000多个表。为什么这么慢?有没有办法提高速度?

drop table既然要做checkpoint,那么在db cache非常大的情况下,这需要消耗的时间是比较长的。如果能够减少这个时间无疑将大幅提高速度。首先尝试做一次checkpoint,将buffer cache全部刷新出去:

alter system checkpoint;
alter session set events 'immediate trace name flush_cache level 1';

发现没什么效果。

由于db_cache_size有50GB左右,db_keep_cache_size有6G左右。重新设置参数,将db_keep_cache_size设为0,将db_cache_size设为200M,重启一下数据库,重新执行删除用户的操作,操作很快完成。

在另一次同样的过程中,采用同样的修改参数的方式,效果同样非常明显。

这是个简单的案例,与君共享。

Trackback

15 comments untill now

  1. alter system flush buffer_cache会不会有同样效果?

    [回复]

  2. 抱歉没看仔细。原来9i用的是event trace。
    为什么flush buffer_cache没效果?

    [回复]

    老熊 回复:

    @gengmao, 这个也是我纳闷的地方,或者是有效果,但不是特别明显。

    [回复]

  3. wangliang @ 2009-12-01 10:53

    为什么降低db_cache_size和db_keep_cache_size的大小就能减少checkpoint的消耗时间呢?checkpoint产生的归档日志文件不会有丝毫减少啊!而且降低db_cache_size,drop table, truncate table的时候,日志归档切换时间非常短,不知道我说的对不对?

    [回复]

    老熊 回复:

    @wangliang, 这里不在于日志,在于checkpoint时搜索db cache的过程时间减少。

    [回复]

  4. 非常好:)

    [回复]

  5. 东西一大一多都不好搞,最近偶也在逻辑的迁移数据,如人饮水,冷暖自知。

    [回复]

    老熊 回复:

    @zhouyf, 我服务的这个客户这里,有10套以上的库要迁移,都是超过1T的库,累。

    [回复]

  6. 如果是RAC,只启动1个instance drop应该会快点

    [回复]

  7. 坏白菜 @ 2009-12-12 21:20

    1、是说由于把oracle内存改小了,每次checkpoint时候搜索的总量少了减少了时间么??
    2、如果我内存没有调少,数据库基本不做任何操作,这样脏数据比较少,那是否删除的时间也会比较短的?
    另外如果修改这两个值得话是不是也会影响数据库开启的速度?

    [回复]

    老熊 回复:

    @坏白菜,
    1. 看上去正是如此
    2. 测试过,在db cache很大的情况下,即使重启数据库,drop表时仍然比较慢。还测试过,在db cache开始很小的情况下,删除比较快,但是我在线把db cache设到很大,速度马上就会下降得很厉害。
    其实sga小的情况下,启停数据库都会更快一些,我理解你的“开启”为启动数据库。

    [回复]

  8. 每天来一次,能学好多东西。。。

    [回复]

  9. 现在有事没事就上老熊这儿来看看,能得到很多启示。
    刚还有点困,看到这篇文章一点都不困了,哈哈

    [回复]

  10. 有个疑问:
    既然是drop table,我想应该只是操作数据字典,既然你手工做了checkpoint,为何有这么多的dirty blocks呢?
    还有,oracle8i以后不是有checkpoint queue取代了dirty LUR list吗,为何还要去buffer cache去搜索呢,它应该直接将dirty blocks放在checkpoinit queue吧?

    [回复]

    老熊 回复:

    @cc, 按照你的说法应该是这样,可惜从实际操作来看,显然要复杂得多。

    [回复]

Add your comment now