Oracle Performance Tuning Tips

Note the db_block_checksum parameter setting

You may have noticed that the default value for the db_block_checksum parameter is TRUE in Oracle 9i and above, whereas it was FALSE in previous versions. Although this makes it much less likely that minor data corruptions will go unnoticed, there is also a performance cost of which you should be aware.

If this parameter is set to TRUE, then whenever a data block or logfile block is about to be written to disk, the process performing the write first calculates and saves a checksum in the block header. Similarly, whenever a data block or logfile block is read from disk, the process performing the read immediately recalculates and verifies the checksum if present (but only if db_block_checksum is set to TRUE). If the parameter is set to FALSE, then checksums are only computed and checked for blocks in the SYSTEM tablespace, file header blocks and controlfile blocks. If checksum verification fails, an ORA-01578, ORA-00368 or ORA-00227 error is raised, depending on whether the checksum error affects a data block, logfile or controlfile block.

The checksum is a simple XOR of all the data in the block, and as such takes a small but noticeable amount of CPU time to compute. This has to be done for every I/O operation. The extra CPU time required for writes is of less concern than the extra CPU time required for reads, because writes are typically performed in the background by DBWn and LGWR and foreground processes should not normally wait for the services of these processes. However, the extra CPU time required for reads is sustained by foreground processes and thus impacts end user response times directly.

The Oracle documentation acknowledges this impact and indicates that the overhead is typically in the order of 1% to 2%. That is true on average! However, the impact on I/O intensive queries on a system with moderate to high CPU usage can be much worse than that. The following comparison on an otherwise idle system shows a 4% difference in elapsed time and an 8% difference in CPU usage. On a busy system with not much spare CPU capacity, the impact on the response time of such queries rises to about 10%, and it increases further if the application uses mostly index-based access paths rather than full table scans to fetch its data.

The Oracle documentation has long recommended that the checksum facility be enabled. This is now the default, and in general it should be accepted. However, if your application is I/O intensive and you are short on CPU capacity, then you might want to disable it.

SQL> show parameters db_block_checksum

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_checksum                    boolean     FALSE
SQL> set timing on
SQL> select count(*) from checksum_off;

  COUNT(*)
----------
     10000

Elapsed: 00:00:49.02
SQL> set timing off
SQL> select
  2    n.name,
  3    m.value
  4  from
  5    sys.v_$mystat  m,
  6    sys.v_$statname	n
  7  where
  8    m.statistic# in (12, 42, 164) and
  9    n.statistic# = m.statistic#
 10  /

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
CPU used by this session                                                183
physical reads                                                        10182
no work - consistent read gets                                        10360

SQL> show parameters db_block_checksum

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_checksum                    boolean     TRUE
SQL> set timing on
SQL> select count(*) from checksum_on;

  COUNT(*)
----------
     10000

Elapsed: 00:00:51.02
SQL> set timing off
SQL> select
  2    n.name,
  3    m.value
  4  from
  5    sys.v_$mystat  m,
  6    sys.v_$statname	n
  7  where
  8    m.statistic# in (12, 42, 164) and
  9    n.statistic# = m.statistic#
 10  /

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
CPU used by this session                                                198
physical reads                                                        10182
no work - consistent read gets                                        10361