Effective database monitoring and management requires a great deal of expertise. Monitoring a database server involves not just ensuring high uptime but making sure that the database is optimized for peak performance - e.g., that the different database caches are tuned to service requests fast, that the server has enough buffers to perform and sort operations in memory rather than on disk, that the database accesses are evenly distributed across the different datafiles, etc. Tuning the database by considering the behavior of applications using the database - e.g., whether the queries to the database are optimal, whether the locking behavior of the application is normal etc., can result in significant performance improvements.
| Database service monitoring |
Is the database server available for servicing requests and what is the response time for a typical request? |
| Session monitoring | How many users are accessing the Oracle database currently? Who are the active users? |
| Query monitoring | What are the current top 10 SQL queries in terms of resource utilization? |
| Transaction monitoring | What is the commit and rollback behavior of the applications using the database? |
| Alert log monitoring | Have there been any recent errors/events in the Oracle alert log? What are they? |
| Rollback segment monitoring |
Is there heavy contention for the rollback segments? |
| Lock and latch monitoring |
Is there contention for locks? Is a specific application holding a lock for a long time? Which lock(s) are these? |
| Cache monitoring | Are the library cache, dictionary cache, and the data buffer cache adequately sized? |
| Full table scan monitoring |
Is there any full table scan happening on the database? If so, how frequently? |
| Tablespace monitoring | Are any of the tablespaces reaching their storage capacity? Is the load adequately balanced across the tablespaces? |
| Hot file monitoring | Is the disk I/O (read/write) being balanced across the datafiles or is there a particular hot datafile that is handling all the requests? |
| Redo log monitoring | Is the Oracle redo-log buffer sufficiently sized, or is there a large number of requests waiting for redo log space? |
| Object monitoring | Is there any invalid object in the database? Which ones? Which objects have been recently modified and when? Are there objects that have reached their maximum extent? Which ones are these? |
Contact
