Database administrator troubleshooting responsibilities

The database administrator takes the initiative to resolve problems and ensures that the application is running well with respect to the database. They are familiar with schema including all Product Master tables and indexes and familiar with the create scripts in $TOP/src/db on the application server.

The database administrator performs the following responsibilities:
Back up and recovery
  • Performs periodic back ups
  • Has full knowledge of the restore procedure
Monitoring database activity
The database administrator understands the following:
  • When transaction rollbacks occur
  • When the database is out of system disk space
  • When unique constraints have been violated (this can be accomplished by using alerts)
  • When not to shut down the database while the application is running
Performance
  • Takes immediate action when performance issues arise:
    • Analyzes SQL statements and if some are taking an inordinate amount of time to run, determines the cause:
      • Explain plan
      • Checks updated statistics
  • Monitors when the database performs a rollback on a very large transaction causing performance issues with other transactions
  • Owns recalculating of database statistics
  • Verifies that the database is running in an optimized fashion, not only at the system level but at the level of tables and queries as well
  • Tunes procedure for gathering statistics to obtain optimal performance
  • Calculates how often statistics need to be updated to obtain optimal performance
  • Reorganizes the tables and indexes at regular intervals of time
Locks
  • Analyzes where locks are coming from
    • Gets trace of SQLs
    • Matches SIDs to server or process
  • Detects deadlocks
  • Checks why the source of the block is still blocking
    • If it is a long running job because of slow running SQLs
      • Why are the SQLs slow?
        • Perhaps the DB is doing a rollback on a session and the application is still generating SQLs
        • Maybe it is a bad explain plan (check SQL performance)
    • Possibly the DB is doing a rollback on a transaction
    • Size of the transaction could be a factor
Note: If the trained Database Administrator has followed all of these guidelines and still encounters difficulty, we recommend opening a PMR.