Oracle Tuning Approach
Controlling the behavior of Optimizer – Oracle tuning approach
To increase the performance of SQL execution, you are supposed to use the following parameters. These parameters are helpful in controlling the behavior of optimizer.
CURSOR_SHARING
This parameter is used in converting literal values of a statement to bind variables. This can promote cursor sharing and could have significant effect on execution plans. The optimizer produces the plan by using these bind variables.
OPTIMIZER_INDEX_CACHING
It can control the costing of an index probe in combination with nested loops. The values range from 0 to 100. A value of 100 denotes that 100 percent of the blocks are likely to be found in the buffer cache so that optimizer can adjust the cost of an index probe accordingly.
OPTIMIZER_INDEX_COST_ADJ
This adjusts index probe costs. The values range from 1 to 10000. The default value is 100 and it indicates that all indexes are evaluated as an access path as per the normal costing model. A value of 10 denotes that an index access path cost is one-tenth the normal cost.
PGA_AGGREGATE_TARGET
You can use this parameter to control the memory allocation for hash joins and sorts. We can minimize the optimizer cost by allocating more memory for sorts and hash joins.
STAR_TRANSFORMATION_ENABLED
This parameter allows the optimizer to cost a star transformation for star queries. Star transformation has the ability to integrate all the bitmap indexes on various fact table columns.
Configuring and Using Memory
Oracle normally recommends using automatic memory management. But you can also adjust the memory pools manually. The information is stored in memory caches and on disk. As Memory access is always faster than disk access (i.e. physical I/O), disk access can increase the CPU resources required. So, it would be better if the data requests of frequently accessed objects are handled by memory.
Our aim should be to minimize the physical I/O overhead as much as we can by making the data retrieval process as efficient as possible.
Automatic Shared Memory Management can simplify SGA configuration. To make good use of Automatic Shared Memory Management,
- You need to set SGA_TARGET initialization parameter to a non-zero value.
- You must set the STATISTICS_LEVEL parameter to TYPICAL.
- The SGA_TARGET parameter should be set to the amount of memory that you would like to allocate for SGA.
Note: You can manually resize memory pool by using the parameters such as SHARED_POOL_SIZE, DB_CACHE_SIZE and JAVA_POOL_SIZE.
Configuring and using the buffer cache
Oracle database can bypass the buffer cache for certain operations such as parallel reads and sorting. Buffer cache could be used effectively by properly tuning SQL statements for the application. While using parallel queries, you need to configure the database to use buffer cache instead of performing direct reads into the PGA. This configuration is more appropriate in case of servers with large amount of memory.
There are few methods to examine buffer cache activity such as V$DB_CACHE_ADVICE and Buffer Cache Hit Ratio. If you need to use V$DB_CACHE_ADVICE then the parameter DB_CACHE_ADVICE should be set to ON.
The Buffer Cache Hit Ratio estimates how frequently a requested block could be detected in the buffer cache without accessing the disk. You must use the Buffer Cache Hit Ratio to verify the physical I/O as anticipated by V$DB_CACHE_ADVICE.
Redo Log buffer configuration
Redo data is generated when server process makes changes to the data blocks in the buffer cache. While log writer (LGWR) attempts to write all the redo entries to a redo log file, the user processes can copy new entries over the previous entries in the memory.
Here are some recommendations for using the redo log buffer effectively:
- Properly batch the commit operations. Here the log writer can efficiently write all the redo log entries.
- Use NOLOGGING operations especially while loading large amount of data.
- The default size of the log buffer must be altered in case of applications that handle large amount of data. The log buffer is actually smaller than the size of SGA. An adequately sized log buffer can improve throughput on machines that perform frequent updates. For such systems, a first estimate is to the default value as shown below:
MAX(0.5M, (128K * number of cpus))
Note: In many cases, sizing the buffer more than 1M may not give performance benefits and it also uses extra memory.
Optimizing the Storage Performance
Reducing the need to provision more storage is a major challenge in IT management. Storage costs could be minimized by partitioning underneath large databases. Many enterprises are using two tiers of storage. A high end storage array is generally used for mission critical applications and a lower cost storage array is used for less demanding platforms. But the reality is that most databases contain a mixture of active data such as call records for the current billing cycle etc and less active data such as orders taken last year etc. For instance, active data can represent around
Sizing the disk storage as per the workload
The random I/O operations are actuated by inserts, deletes and updates that in turn are activated by queries that scan bulk portions of table. The random I/O operations are calculated in IOPs (I/O operations per second). Sequential I/O operations are calculated in the number of megabytes of data that could be scanned (MB/s).
Storage arrays must be sized by the number of IOPs they can deliver. The total number of IOPs and MB/s required for existing Oracle databases could be determined from the workload repository reports. Few guidelines for sizing the new systems:
- In case of OLTP (On-Line Transaction Processing) environments, assume that every transaction incurs about five random IOPs.
- For data warehousing environments, assume that a reasonably modern core may require around 200 MB/s in order to be kept completely busy. So a two processor quad core machine needs around 1.6 GB/s scan rate.
- Oracle Automatic Storage Management must be utilized in maximizing the I/O bandwidth. It offers efficient realization of storage array capabilities.
- It is recommended to use Direct and Asynchronous I/O to optimize I/O performance. Reads can be done asynchronously. In case of query intensive workloads, read ahead capability for sequential scans can improve the performance.
- You are supposed to use Direct NFS Client to enhance the performance of network attached storage.