Tuning 101: PARAMS_2014-02-24.log

Hello folks

Sochi games are over and now its time for me to add to the tuning series. In the last post we talked about SHARED POOL, and continuing with where we left, we shall come across few other parameters that can be checked upon for a brief performance analysis of our Oracle database. Before starting, let us view another query to examine the performance of the Oracle database system:

-- DB Block Buffer Cache Hit Ratio
  SELECT (1 - (phys.value / (db.value + cons.value))) * 100
  INTO   v_value
  FROM   v$sysstat phys,
         v$sysstat db,
         v$sysstat cons
  WHERE  phys.name  = 'physical reads'
  AND    db.name    = 'db block gets'
  AND    cons.name  = 'consistent gets';

If the above query gives you value less than 90, then it is advised to increase DB_BLOCK_BUFFERS parameter to bring the value above 90. Having said so, let us understand what the parameter DB_BLOCK_BUFFER is and how it affects the Oracle database performance.

The DB_BLOCK_BUFFERS specifies the number of database buffers in the buffer cache. This parameter, together with DB_BLOCK_SIZE, determines the total size of the buffer cache. Effective use of the buffer cache can greatly reduce the I/O load on the database. But there is a small catch, DB_BLOCK_SIZE can be specified only when the database is first created, so we have to use DB_BLOCK_BUFFERS to control the size of the buffer cache. This parameter although has been deprecated since Oracle 9i and the parameter DB_CACHE_SIZE is used in its place. Whichever parameter is to be played, the size of the buffer cache affects performance. Larger cache sizes generally reduce the number of disk reads and writes. However, a large cache may take up too much memory and induce memory paging or swapping. The cache has a limited size, so not all the data on disk can fit in the cache. When the cache is full, subsequent cache misses cause Oracle database to write dirty data already in the cache to disk to make room for the new data. Subsequent access to any data that was written to disk and then overwritten results in additional cache misses.

The size of the cache affects the likelihood that a request for data results in a cache hit. If the cache is large, it is more likely to contain the data that is requested. Increasing the size of a cache increases the percentage of data requests that result in cache hits. We can change the size of the buffer cache while the instance is running, without having to shutdown the database.

Next lets look at the query below:

-- Disk Sort Ratio
  SELECT (disk.value/mem.value) * 100
  INTO   v_value
  FROM   v$sysstat disk,
         v$sysstat mem
  WHERE  disk.name = 'sorts (disk)'
  AND    mem.name  = 'sorts (memory)';

If the output from the above query is greater than 5, means that most of the sorting operations is being done in the disk rather than the memory. If such a scenario is being seen in your Oracle database, then it is advised to increase the value of of the SORT_AREA_SIZE parameter to bring the value below 5, meaning most of the sorting is being done in the memory. So lets look at what this SORT_AREA_SIZE is? and how it affects the performance of the Oracle database system.

The SORT_AREA_SIZE specifies the maximum amount or memory Oracle will use for a sort. After the sort is complete, but before the rows are returned, Oracle releases all of the memory allocated for the sort, except the amount specified by the SORT_AREA_RETAINED_SIZE parameter. After the last row is returned, Oracle releases the remainder of the memory. Increasing SORT_ARE_SIZE improves the efficiency of large sorts. Larger values of SORT_AREA_SIZE permit more sorts to be performed in memory. If more space is required to complete the sort than will fit into the memory provided, then temporary segments on disk are used to hold the intermediate sort runs. And it is well known fact that, working in memory serves faster response than working on disks, a larger SORT_AREA_SIZE will benefit most of the times. The default is adequate for most OLTP operations, but one might want to adjust this parameter for decision support systems, batch jobs, or large CREATE INDEX operations.

Having said so, the usage for SORT_AREA_SIZE changed when PGA_AGGREGATE_TARGET was introduced. The SORT_AREA_SIZE is ignored then PGA_AGGREGATE_TARGET is set and then WORKAREA_SIZE_POLICY = AUTO, unless one is using an old feature such as the MTS (shared servers). 

That is all for this post, we shall dwell deeper into tuning forage in the next post and a quick alternative will be presented for tuning an Oracle database system and keeping it healthy.

This entry was posted in Oracle and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s