SQL QUERY TUNING FOR ORACLE

                                         SQL QUERY TUNING FOR ORACLE

     
 An important facet of database system performance tuning is the tuning of SQL statements.It involves to focus on .

 1. Focus on the correct SQL statements
 2. Utilize response time analysis
 3. Gather accurate execution plans 
 4. Use SQL diagramming

If SQL statement tuning can provide the most benefit, the next question is “Which SQL statement should I focus on". So  Choosing the correct SQL statement can involve looking for SQL statements that:
 » Perform the most logical I/O
 » Consume the most CPU 
 » Perform costly full table or index range scans 
 » Use an execution plan with a cost over X
 » And many more…

A query similar to the following will retrieve a list from Oracle with the queries taking the longest to execute:

select sql_text, elapsed_time, sql_id, hash_value, plan_hash_value, program_id, module, action  from v$sql where module =' '  and username = ' '  order by elapsed_time 

When end-users complain about performance they will say something similar to: “When I click the submit button on this web page, it takes 30-40 seconds.” They use elapsed time as a way to describe the problem, so why not use elapsed time when finding the queries to tune.
           Once the most problematic SQL is known, understanding why the SQL is slow is paramount. Is the problem an unnecessary full table scan, a locking issue, disk I/O related, etc? Oracle wait events very clearly show the problem. Oracle database and going through a series of steps. These steps may include reading data from disk, waiting on a locking issue, waiting for Oracle to commit data, and many more. Measuring the total execution time for a SQL statement reveals which SQL to tune and measuring how much time that SQL waits on specific events provides the best clues for fixing the issue. For example, if a SQL statement executes for 40 seconds, what is the major cause of that delay? A SQL that waits on an event named “db file scattered read” means it is most likely performing a full table scan, while waits on “enq: TX – row lock contention” indicate the SQL is experiencing a locking issue. The solutions for these problems are much different and are where wait events help the most.

some of the wait-events are as follows:

db file sequential reads:

Possible Causes : 
· Use of an unselective index  
· Fragmented Indexes 
· High I/O on a particular disk or mount point 
· Bad application design  

Actions : 
· Check indexes on the table to ensure that the right index is being used
· Check the column order of the index  with the WHERE clause of the Top SQL statements
· Rebuild indexes with a high clustering factor
· Use partitioning to reduce the amount of blocks being visited
· Make sure optimizer statistics are up to date
· Relocate ‘hot’ datafiles
· Consider the usage of multiple buffer pools and cache frequently used indexes/tables in the KEEP pool
· Inspect the execution plans of the SQL statements that access data through indexes



db file scattered reads

Possible Causes : 
· The Oracle session has requested and is waiting for multiple contiguous database blocks (up to DB_FILE_MULTIBLOCK_READ_COUNT)  to be  read into the SGA from disk. 
· Full Table scans
· Fast Full Index Scans

Actions : 
· Optimize multi-block I/O by setting the parameter DB_FILE_MULTIBLOCK_READ_COUNT
· Partition pruning to reduce number of blocks visited
· Consider the usage of multiple buffer pools and cache frequently used indexes/tables in the KEEP pool
· Optimize the SQL statement that initiated most of the waits. The goal is to minimize the number of physical and logical reads.
· Make sure all statistics are representative of the actual data. Check the LAST_ANALYZED date


log file parallel write

Possible Causes : 
· LGWR waits while writing contents of the redo log buffer cache to the online log files on disk
· I/O wait on sub system holding the online  redo log files

Actions : 
· Reduce the amount of redo being generated
· Do not leave tablespaces in hot backup mode for longer than necessary
· Do not use RAID 5 for redo log files
· Use faster disks for redo log files
· Ensure that the disks holding the archived redo log files and the online redo log files are separate so as to avoid contention
· Consider using NOLOGGING or UNRECOVERABLE options in SQL statements


log file sync:

Possible Causes : 
· Oracle foreground processes are waiting for a COMMIT or ROLLBACK to complete
Actions : 
· Tune LGWR to get good throughput to  disk eg: Do not put redo logs on  RAID5
· Reduce overall number of commits by batching transactions so that there are fewer distinct COMMIT operations

Actions :
Tune LGWR to get good throughput to  disk eg: Do not put redo logs on RAID5
 Reduce overall number of commits by batching transactions so that there are fewer distinct COMMIT operations


buffer busy waits:

Possible Causes : 
· Buffer busy waits are common in an I/O-bound Oracle system.
· The two main cases where this can occur are: 
· Another session is reading the block into the buffer 
· Another session holds the buffer in an incompatible mode to our request 
· These waits indicate read/read, read/write, or write/write contention.
· The Oracle session is waiting to pin a buffer .A buffer must be pinned before it can be read or modified. Only one process can pin a 
buffer at any one time.
· This wait can be intensified by a large block  size as more rows can be contained within the block
· This wait happens when a session wants to access a database block in the buffer cache but it cannot as the buffer is “busy

Actions : 
· The main way to reduce buffer busy waits is to reduce the total I/O on the system
· Depending on the block type, the actions will differ
· Eliminate HOT blocks from the application. Check for repeatedly scanned / unselective indexes.
· Try rebuilding the object with a higher PCTFREE so that you reduce the number of rows per block.
·  Check for ‘right- hand-indexes’ (indexes that get inserted into at the same point by many processes).
· Reduce the number of rows per block

free buffer waits:

Possible Causes : 
· This means we are waiting for a free buffer but there are none available in the cache because there are too many dirty buffers in  the cache
· Either the buffer cache is too small or the DBWR is slow in writing modified buffers to disk
· DBWR is unable to keep up to the write  requests
· Large sorts and full table scans are filling the cache with modified blocks faster than the  DBWR is able to write to disk
· If the  number of dirty buffers that need to be  written to disk is larger than the number that DBWR can write per batch, then these waits  can be observed

Actions : 
Reduce checkpoint frequency  – increase the size of the online redo log files
Examine the size of the buffer cache – consider increasing the size of the buffer cache in the SGA
Set disk_asynch_io = true set
If not using asynchronous I/O increase the number of db writer processes or dbwr slaves

enqueue waits:

Possible Causes : 
· This wait event indicates a wait for a lock  that is held by another session (or sessions) in an incompatible mode to the requested mode.
 TX Transaction Lock
· Generally due to table or application set up issues
· This indicates contention for row-level lock. This wait occurs when a transaction tries to update or delete rows that are currently
 locked by another transaction.
· This usually is an application issue.
TM DML enqueue lock
· Generally due to application issues, particularly if foreign key constraints have not been indexed.


Actions : 
· Reduce waits and wait times
· The action to take depends on the lock  type which is causing the most problems
· Whenever you see an enqueue wait event for the TX enqueue, the first step is to find out who the blocker is and if there are multiple waiters for the same resource

· Waits for TM enqueue in Mode 3 are primarily due to unindexed foreign key columns.

· Create indexes on foreign keys  < 10g

· Following are some of the things you can do to minimize ST lock contention in your database:

· Use locally managed tablespaces
· Recreate all temporary tablespaces  using the CREATE TEMPORARY TABLESPACE TEMPFILE… command.

Cache buffer chain latch

Possible Causes : 
· Processes need to get this latch when they  need to move buffers based on the LRU block replacement policy in the buffer cache
· The cache buffer lru chain latch is acquired in order to introduce a new block into the buffer cache and when writing a buffer 
back to disk, specifically when trying  to scan the LRU (least recently used) chain containing all the dirty blocks in the buffer 
cache. Competition for the cache buffers lru chain .

· latch is symptomatic of intense buffer cache  activity caused by inefficient SQL  statements. Statements that repeatedly scan

· large unselective indexes or perform full table scans are the prime culprits. 

· Heavy contention for this latch is generally  due to heavy buffer cache activity which  can be caused, for example, by:
 Repeatedly scanning large unselective indexes

Actions :
 Contention in this latch can be avoided implementing multiple buffer pools or increasing the number of LRU latches with the  parameter DB_BLOCK_LRU_LATCHES (The default value is generally  sufficient for most systems).

Its possible to reduce contention for the cache buffer lru chain latch by increasing the  size of the buffer cache and  thereby reducing the rate at which new blocks are  introduced into the buffer cache.

 Direct Path Reads

Possible Causes : 
· These waits are associated with direct read operations which read data directly into the sessions PGA bypassing the SGA

· The “direct path read” and “direct path write” wait events are related to operations that are performed in PGA like sorting, group by operation, hash join

· In DSS type systems, or during heavy batch periods, waits on “direct path read” are quite normal However, for an OLTP system these waits are significant
· These wait events can occur during sorting operations which is not surprising as direct path reads and writes usually occur in connection with temporary tsegments
· SQL statements with functions that require sorts, such as ORDER BY, GROUP BY, UNION, DISTINCT, and ROLLUP, write sort runs to the temporary tablespace when the input size is larger than the work area in the PGA
Actions : 
Ensure the OS asynchronous IO is configured correctly. 
Check for IO heavy sessions / SQL and see if the amount of IO can be reduced. 
Ensure no disks are IO bound. 
Set your PGA_AGGREGATE_TARGET to appropriate value (if the parameter WORKAREA_SIZE_POLICY = AUTO) Or set *_area_size manually (like sort_area_size and then you have to set WORKAREA_SIZE_POLICY = MANUAL
Whenever possible use UNION ALL instead of UNION, and where applicable use HASH JOIN instead of SORT MERGE and NESTED LOOPS instead of HASH JOIN.
 Make sure the optimizer selects the right driving table. Check to see if the composite index’s columns can be rearranged to match the ORDER BY clause to avoid sort entirely.

Also, consider automating the SQL work areas using PGA_AGGREGATE_TARGET in Oracle9i Database.

Query V$SESSTAT> to identify sessions with high “physical reads direct”

Remark: 
· Default size of HASH_AREA_SIZE  is twice that of SORT_AREA_SIZE

· Larger HASH_AREA_SIZE will influence optimizer to go for hash joins instead of nested loops

· Hidden parameter DB_FILE_DIRECT_IO_COUNT can impact the direct path read performance.It sets the maximum I/O buffer size of direct read and write operations. Default is 1M in 9i
Direct Path  Writes:

Possible Causes : 
· These are waits that are associated with direct write operations that write data from users’ PGAs to data files or temporary  tablespaces
· Direct load operations (eg: Create Table as  Select (CTAS) may use this) 
· Parallel DML operations 
· Sort IO (when a sort does not fit in memory

Actions : 
If the file indicates a temporary  tablespace check for unexpected disk sort operations. 
Ensure 
<Parameter:DISK_ASYNCH_IO> is TRUE . This is unlikely to reduce wait times from the wait event timings but 
may reduce sessions elapsed times (as synchronous direct IO is not accounted for in wait event timings). 
Ensure the OS asynchronous IO is configured correctly. 
Ensure no disks are IO bound

Latch Free Waits
Possible Causes : 
· This wait indicates that the process is waiting for a latch that is currently busy  (held by another process). 
· When you see a latch free wait event in the V$SESSION_WAIT view, it means the process failed to obtain the latch in the 
willing-to-wait mode after spinning  _SPIN_COUNT times and went to sleep. When processes compete heavily for  latches, they will also consume more CPU resources because of spinning. The result is a higher response time

Actions :
· If the TIME spent waiting for latches is significant then it is best to determine which latches are suffering from contention.
Remark:
· A latch is a kind of low level lock. Latches apply only to memory structures in the SGA. They do not apply to database objects. An Oracle SGA has many latches, and they exist to protect various memory structures from potential corruption  by concurrent access.

· The time spent on latch waits is an effect, not a cause; the cause is that you are doing too many block gets, and block gets require cache buffer chain latching

 Library cache latch

Possible Causes : 
· The library cache latches protect the  cached SQL statements and objects definitions held in the library cache within the shared pool. The library cache latch must be acquired in order to add a new statement to the library cache.

· Application is making heavy use of literal SQL- use of bind variables will reduce this latch considerably

Actions :
· Latch is to ensure that the application is reusing as much as possible SQL statement representation. Use bind variables whenever ossible in the application.

· You can reduce the library cache latch hold time by properly setting the SESSION_CACHED_CURSORS parameter.
· Consider increasing shared pool.
Remark: 
· Larger shared pools tend to have  long free lists and processes that need to allocate space in them must  spend extra time scanning the long free lists while holding the shared pool latch

· if your database is not yet on  Oracle9i Database, an oversized shared pool can increase the contention for the shared pool latch..
Shared pool latch

Possible Causes : 
The shared pool latch is used to protect critical operations when allocating and freeing memory in the shared pool

Contentions for the shared pool and library cache latches are mainly due to intense hard  parsing. A hard parse applies to new cursors and cursors that are aged out and must be re-executed

The cost of parsing a new SQL statement is expensive both in terms of CPU requirements and the number of times  the library cache and shared pool latches  may need to be acquired and released.

Actions :
· Ways to reduce the shared pool latch  are, avoid hard parses when possible, parse once, execute many.

· Eliminating literal SQL is also useful to avoid the shared pool latch. The size  of the shared_pool and use of MTS  (shared server option) also greatly  influences the shared pool latch.
· The workaround is to set the initialization parameter  CURSOR_SHARING to FORCE. This  allows statements that differ in literal
 values but are otherwise identical to share a cursor and therefore reduce latch contention, memory usage, and  hard parse.

Row cache objects latch

Possible Causes : 
This latch comes into play when user processes are attempting to  access the cached data dictionary values.

Actions : 
· It is not common to have contention in this latch and the only way to reduce contention for this latch is by increasing the size of the shared pool (SHARED_POOL_SIZE).

· Use Locally Managed tablespaces for your application objects especially indexes 

· Review and amend your database logical design , a good example is to merge or decrease the number of  indexes on tables with heavy inserts
Remark: 
· Configuring the library cache to an acceptable size usually ensures that the data  dictionary cache is also properly sized. So tuning Library Cache will tune Row Cache indirectly.