Performance Tuning - Part 2

7. Identifying missing indexes

To find the top SQL statements that have caused most block buffer reads:

Select buffer_gets, sql_text
from v$sqlarea
where buffer_gets > 15000
order by buffer_gets desc;
 If this returns a large number of rows then increase the number of ‘buffer_gets’ required, if it returns no rows then decrease this threshold.
Typically, most of these will be select statements of some sort. Considering each in turn, identify what indexes would help with the query and then check that those indexes exist. Create them if necessary.

To find the most frequently executed SQL:

Select executions, buffer_gets, sql_text from v$sqlarea where executions > 10000 order by executions desc;
If this returns a large number of rows then increase the number of ‘executions’ required. If it returns no rows then decrease the number of executions required.

8. Identify index fragmentation

analyze index <index_name> validate structure;
This populates the table ‘index_stats’. It should be noted that this table contains only one row and therefore only one index can be analysed at a time.
An index should be considered for rebuilding under any of the following conditions:

• The percentage of deleted rows exceeds 30% of the total, i.e. if
  del_lf_rows / lf_rows > 0.3.
• If the ‘HEIGHT’ is greater than 4.

select HEIGHT, decode (LF_ROWS,0,1,LF_ROWS), DEL_LF_ROWS from sys.index_stats;

• If the number of rows in the index (‘LF_ROWS’) is significantly smaller than ‘LF_BLKS’ this can indicate a large number of deletes, indicating that the index should be rebuilt.

9. Identifying free list contention

To identify the percentage of requests that resulted in a wait for a free block run the following query:

select round( (sum(decode(w.class,'free list',count,0))/ (sum(decode(name,'db block gets', value, 0))
+ sum(decode(name,'consistent gets', value, 0)))) * 100,2)
from v$waitstat w, v$sysstat;
This should be less than 1%. To reduce contention for a table’s free list the table must be recreated with a larger value in the FREELISTS storage parameter

10.  Identify significant reparsing of SQL

The shared-pool contains (amongst other things) previously parsed SQL, and this allows Oracle to avoid re-parsing SQL unnecessarily.
The following SQL identifies those SQL statements that have needed to be re-parsed numerous times:

select executions, t.sql_text
from v$sqlarea a, v$sqltext t
where parse_calls >1 and parse_calls = executions and a.address=t.address and executions > 10000
order by executions desc;
If this returns a large number of rows then increase the number of ‘executions’ required. If it returns no rows then perhaps decrease the number of executions required.
If there is SQL that is being repeatedly reparsed then consider increasing the value of SHARED_POOL_SIZE.

11.  Reducing database fragmentation

Excessively fragmented tables or indexes can adversely affect performance. Use the following SQL to identify those database objects that have over 10 extents allocated:

select * from dba_segments where extents > 10;
In general, if a table or index has more than 10 extents then rebuild it to fit into one extent.
A table can only be rebuilt by exporting and then importing it. The database will be unavailable for use by applications during this time. The steps to accomplish this are:

1. Export the table with COMPRESS=Y
2. Drop the table
3. Import the table.

An index can be rebuilt without preventing others from still using it. Firstly change the storage parameters to make the ‘next’ storage parameter larger (perhaps double it). The initial storage value cannot be changed. Then rebuild the index. Refer http://oraclemamukutti.blogspot.com/2011/03/rebuild-index.html

I hope this might be an useful document for a beginner Oracle Database Performance Tuning Admin...