oralce优化之10大要点五六

五、Rollback(undo) Segment 优化

    1、概念
    Transaction以轮循的方式使用rollback segment里的extent，当前所在的extent满时就移动到下一个extent。可能有多个transaction同时向同一个extent写数据，但一个rollback segment block中只能保存一个transaction的数据。

    Oracle 在每个Rollback segment header中保存了一个transaction table，包括了每个rollback segment中包含的事务信息，rollback segment header的活动控制了向rollbak segment写入被修改的数据。rollback segment header是经常被修改的数据库块，因此它应该被长时间留在buffer cache中，为了避免在transaction table产生竞争导致性能下降，应有多个rollback segment或应尽量使用oracle server 自动管理的rollback segment。

    2、诊断rollback segment header的竞争
    如果rollback segment 由手工管理，下列措施诊断rollback segment header的竞争
    SELECT class,count FROM v$waitstat WHERE class LIKE '%undo%' ;
    SELECT Sum(Value) sum FROM v$sysstat WHERE NAME IN ('db block gets','consistent gets');
    任何类型的等待次数（count）与总请求数(sum)的比率，不能超过1％。
    或
    select sum(waits)*100/sum(gets) "Ratio", sum(waits) "Waits", sum(gets) "Gets" from v$rollstat;
    waits的汇总数与gets的汇总数的比率应低于1％，如果超过1％，应创建更多的rollback segment。

    下列字段数值如果大于0，则表明在rollback segment header上存在竞争：
    A、v$rollstat 中的waits
    B、v$waitstat中的undo header行
    C、v$system_event中的undo segment tx slot事件

    3、消耗更少的rollback segment
    1）如果是删除表里所有的数据，尽可能使用trauncate而不是delete。
    2）在应用中允许用户有规律的提交，尽可能不用长事务。
    3）• Import
    – Set COMMIT = Y
    – Size the set of rows with BUFFER
    • Export: Set CONSISTENT=N
    • SQL*Loader: Set the COMMIT intervals with ROWS

    4、小回滚段可能出现的问题
    A、事务由于缺少回滚空间失败
    B、由于下列原因导致的“Snapshot too old”问题：
    Block里的事务列表被刷新，block里的SCN比列表Interested Transaction List（ITL）里起始事务的SCN更新；
    Rollback segment header里的Transaction slot被重用；
    回滚数据已经被重写；

    5、9i的自动回滚管理
    Undo_managment指定了回滚空间的管理方式：Auto：自动管理；Manual：手工管理回滚段。
    Undo_retention指定了回滚数据的保留期限；
    Undo_tablespace指定了被使用的回滚表空间；

    Oracle自动管理的表空间可以在常见数据库的时候创建，也可以单独建立。回滚表空间可以相互转换（switch），但在某一时刻只能有一个回滚表空间处于活动状态。回滚表空间处于非活动状态时可以删除，如果有对处于被删除回滚表空间里的已提交事务的查询时，oracle会返回一个错误。

    估计undo tablespace大小的公式：
    Undo space = (undo_retention * (undo blocks per second * db_block_size)) + db_block_size;

    可以使用下列的sql设定undo_retention和undo tablespace：
    select (rd*(ups*overhead)+overhead) "bytes" from (select value rd from v$parameter where name ='undo_retention'),(select (sum(undoblks)/sum(((end_time-begin_time)*10800))) ups from v$undostat),(select value overhead from v$parameter where name='db_block_size');

    其中：
    Rd：undo_retention设置的时间；
    Ups：undo blocks per second；
    Overhead：rollback segment header；

 

六、Lock Contention

    1、概念
    DML事务使用row-level locks,查询不会锁定数据。锁有两种模式：exlusive、share。
    锁的类型：
    • DML or data locks:
    – Table-level locks（TM）
    – Row-level locks（TX）
    • DDL or dictionary locks
    一个transaction至少获得两个锁：一个共享的表锁，一个专有的行锁。Oracle server将所有的锁维护在一个队列里，队列跟踪了等待锁的用户、申请锁的类型以及用户的顺序信息。
    Lock在下列情况会释放：commit；rollback；terminated（此时由pmon清理locks）。Quiesced database：一个数据库如果除了sys和system之外没有其他活动session，这个数据库即处于quiesced状态。活动session是指这个session当前处于一个transaction中，或一个查询中，一个fetch中，或正占有某种共享资源。

    2、可能引起lock contention的原因
    不必要的高层次的锁；
    长时间运行的transaction；
    未提交的修改；
    其他产品施加的高层次的锁。

    解决lock contention的方法：锁的拥有者提交或回滚事务；杀死用户会话。

    3、死锁
    Oracle自动检测和解决死锁，方法是通过回滚引起死锁的语句（statement），但是这条语句对应的transaction并没有回滚，因此当收到死锁的错误信息后，应该去回滚改transaction的剩余部分。

1