Tips for Daily Database Health Check
As an Oracle database administrator you are responsible for averting problems with database performance and stability. So how exactly do you avert a situation that brings a database to its knees.
The answer lies within routine database health checks. You can avert unsightly outages and performance problems by inspecting critical areas of your database.
In this article we are going to talk about 20 components of the database that are inspected by the VITALS database health check:
The above illustration points out 20 vital components of the database that should be routinely inspected during any health check. Whilst the VITALS health check can inspect 100 points we are only focusing on 20 points in this article:
​
1. Execution Plan Flip Flops:
Execution plan flip flops occur due to underlying statistics of the object. In some cases the optimiser will generate a sub-optimal execution plan resulting in poor performance. Consider improving statistics or look at plan stability options such as SQL baseline\profiles.
2. Unusable Indexes:
Unusable indexes are flagged as invalid and will not be picked by the optimiser resulting in sub-optimal execution plans. Unusable indexes should be rebuilt.
3. Missing Statistics
Missing statistics can severely impact system performance. Collect statistics appropriately to ensure the optimizer has the best information available to choose the optimum execution plan.
4. Stale Statistics:
Stale statistics can severely impact system performance. Collect statistics appropriately to ensure the optimizer has the best available to choose the optimum execution plan.
5. Active Restore Points:
Active restore points retain archive log files which has the potential to fill up archive log space and freeze the database. Active restore points should be removed as soon as possible.
6. TEMP Usage:
Consider adding more space to the TEMP tablespace if there is any evidence of more that 80% consumption. Alternatively identify and tune queries responsible for consuming excessive TEMP space.
7. UNDO Usage:
Consider adding more space to the UNDO tablespace if there is any evidence of more than 80% consumption. Alternatively identify and tune queries responsible for consuming excessive UNDO space.
8. Database File Count
Insufficient slots for new datafiles can result in problems with database file creation. Increase the DB_FILES parameter if the number of existing files is approaching the DB_FILES value.
9. Flashback Mode:
Only enable flashback if there is a requirement to flashback to any previous point in time. If there is no requirement flashback will generate unnecessary I/O.
10. Force Logging Mode:
FORCE LOGGING forces data changes to generate REDO regardless of any database objects declared as NOLOGGING. FORCE LOGGING is encouraged to guarantee all changes are synchronized between primary and standby when dataguard is in use
11. Cluster Errors:
There should be no errors with cluster communications. Any cluster errors need to be investigated from the database stack right through to the infrastructure\network stack.
12. Cluster Ping Statistics:
Low latency communication between nodes in a cluster is important for performance. All average cluster latency between nodes should be less than 1 millisecond.
13. PGA Effectiveness:
Identify how excessive PGA disk paging is and tube accordingly. PGA “onepass” and “multipass” events should be used to gauge your PGA effectiveness.
14. PGA Consumption:
Consider tuning specific SQL which are consuming the most PGA. PGA consumption is a result of join operations and sorting during a query.
15. Invisible Indexes:
Invisible indexes incur normal data maintenance during data manipulation but are not used for query operations. If the index in unintentionally marked as invisible either drop it or make it visible.
16. Index Usage(Indexes not used in 60 days):
Indexes which are not used represent a performance overhead with data manipulation. Consider dropping indexes which are not used. Note that some indexes which are not used may be required to enforce data integrity rules(I.E: Primary keys).
17. Files in Backup Mode:
Any datafile left in backup mode will not be modified. Instead changes are logged to the REDO stream. Make sure no datafiles are inadvertently left in 'backup mode.
18. Media Recovery:
Any file in need of media recovery is inaccessible for general use. The file will will need to set offline or recovered.:
19. Dataguard Gap Analysis:
Dataguard is responsible for shipping transaction logs between the primary and standby database. Investigate the reason for any transaction gaps between the primary and standby database.
20. AUDIT_TRAIL_STATUS:
Certain database features such as auditing come with a small overhead. Only enable if required.