Tag Archives: Tuning

Buffer gets increase on the same SQL

同样一条SQL ,有的时候 buffer get 会暴增?! Oracle-L 中有人提了一个这样的问题：

I have a batch process that executes individual transactions, normally
a transaccion e.g. a simple select would take 8-10 buffer gets but in
the batch processing it takes 45 buffer gets.

Zhu Chao (Chao_ping,这家伙现在一篇文章都不写，只能从邮件列表里看到他的踪迹) 给了一个解释：

the job is processing some very hot blocks. So it always need to reverse back and find the CR block from buffer, so it will generate some more buffer gets for that execution.

如果是因为Hot Block 的原因,那么主要的症状应该是 Wait. 如果这个 SQL 在运行的时候数据已经发生了变化,那么为了维持一致性不可避免的会生成回滚,所以这个解释更为准确一些:

If a query does a consistent get on a block that has been changed since that query began or that had uncommitted changes at the time that that query began, then it is necessary to rollback those changes for read consistency. The consistent changes statistics counts the number changes rolled back. However, most consistent gets do not require any such rollback, and so it is normal for the number of consistent gets to be much greater than the number of consistent changes. This is reflected in the no work – consistent read gets statistic

我们不妨来做个例子.假定我们现在有两个Session,首先在第一个窗口做如下操作

继续阅读 →

RMAN Tuning checklist (Quick & Dirty version)

RMAN 调整 Checklist：

  • 合适的Large_Pool_Size .需要给 RMAN 的 Large_Pool_Size 计算公式(9i)： 
    LARGE_POOL_SIZE = number_of_allocated_channels *
(16MB+( 4 * size_of_tape_buffer))
  • 如果直接备份到磁带上，增加BLKSIZE到合适大小;尝试使用
    BACKUP_TAPE_IO_SLAVES 初始化参数
  • 若系统不支持异步 I/O(asynchronous I/O) ，需要备份到磁盘上考虑使用 DBWR_IO_SLAVES 初始化参数模拟异步 I/O 。系统支持异步 I/O，则可忽略此步骤。要小心涉及到异步 I/O 的 Bug。
  • 考虑使用RMAN的 multiplexing 特性。
  • 通过动态视图 V$BACKUP_ASYNC_IO 调查有关备份异步 IO 性能的问题。激活DBWR_IO_SLAVES参数后相关信息也会在该视图有所体现。

继续阅读 →

Oracle DBMS_SUPPORT HOW-to

DBMS_SUPPORT是Oracle提供的一个软件包。供内部支持人员使用以更有效地跟踪SQL。这个包没有正式的说明文件，默认情况下，系统不安装这个包。如果需要使用的话，需进行单独设置。在你的$ORACLE_HOME/rdbms/admin/目录下应该存在dbmssupp.sql，prvtsupp.plb这两个文件。

SQL> connect / as sysdba
Connected.
SQL> @$ORACLE_HOME/rdbms/admin/dbmssupp.sql

Package created.

Package body created.
SQL>

如果要其他用户也可以使用这个包，可以考虑提交如下授权操作(PUBLIC可以替换为具体的用户)并创建同义词:

SQL> GRANT EXECUTE ON dbms_support TO PUBLIC;

Grant succeeded.

SQL> CREATE PUBLIC SYNONYM dbms_support FOR dbms_support;

继续阅读 →