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.
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:
- Export the table with
COMPRESS=Y
- Drop the table
- Import the table.
I hope this might be an useful document for a beginner Oracle Database Performance Tuning Admin...
Great, congratulations ...
ReplyDeleteNice writeup
ReplyDeleteBeautifully compiled and explained.
ReplyDeleteThank you All :)
ReplyDeletegreat complitation and very useful
ReplyDeleteThank you Rafael :)
DeleteReally U R Helping Lot Of DBA's
ReplyDeleteThank U Sooo Much....
Keep Posting....
Thank you :)
DeleteJust to add a little, this query can be helpful for many.
ReplyDeleteFor section:
11. Reducing database fragmentation
select * from dba_segments where extents > 10;
This might give large number of rows and could be not that much helpful.
To see top 10 such segments.
select * from (select OWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,extents from dba_segments where extents > 10 order by extents desc,OWNER,SEGMENT_NAME) where rownum<=10;
Hope this helps.
Gurbrinder, that really is a very helpful query to filter what is required!! Thanks a ton for the suggestion!! :)
Delete