Rahul Sah

We often think that hardware issues, like lesser RAM or CPU speed are the main culprits behind slow database performance. So, we end up upgrading the hardware, which is always not the best solution. We can do a lot apart from that.

Here are some techniques that DBAs can use to enhance the performance of their crippled database. These include optimizing some parameters for having performance improvements and also tuning the database with some database tuning tools.

Optimizing the Cache Size

Oracle database maintains a cache, which is a part of the Shared Global Area (SGA) in memory for each Single Database Instance (SID). This cache is used to store data blocks and indexes that are currently in use. In the init.ora file under the Oracle installation directory there are two parameters, namely DB_CACHE_SIZE and DB_BLOCK_BUFFERS that determine the size of buffer cache. We can adjust these parameters to optimize the Cache Hit Ratio (CHR). The CHR determines how many blocks were read from the memory against how many blocks were to be read from the disk.

The blocks from memory are called logical reads and those from disk are called physical reads. A logical read occurs whenever a user requests data from the database and that data is in the buffer cache, whereas if the data must be read from disk then a physical read occurs. In the V$SYSSTAT table, Oracle keeps a record of the logical as well as physical reads. Through the following query, this ratio can be calculated.

select pr.value PR, dbg.value DB_BLOCK, cg.value CONS_GETS, (1-(pr.value/( dbg.value+cg.value)))*100 HIT_RATIO from v$sysstat pr, v$sysstat dbg, v$sysstat cg where pr.name = 'physical reads' and dbg.name = 'db block gets' and cg.name = 'consistent gets';

The logical reads include 'db block gets' for blocks you are presently using in memory space and 'consistent gets' of original blocks from rollback segments that others are updating. This ratio will be more representative when the database has been running for hours with normal application and user transactions taking place. If this ratio is below 90%, then the parameters DB_CACHE_SIZE and DB_BLOCK_BUFFERS can be adjusted to make performance improvements. It is a good practice to try out several values for extra buffers, so as to end up with an optimal value for the database workload.