Database Reference
In-Depth Information
Gathering of Statistics
While gathering statistics, crucial items to monitor include disk space, free space left
for tables, number of users, memory usage, and I/O performance. You should also
monitor database usage by individuals, trace data accesses, and database update pat-
terns. Let us list the main types of statistics to be collected. Some of these are auto-
matically collected and stored by the DBMS itself. For others, you have to use
monitoring systems.
•
Sizes of individual tables
•
Number of active tables
•
Number of distinct values in table columns
•
Number of times a particular query is submitted and completed successfully or
unsuccessfully within a period
•
Execution times for different phases of queries and transactions
•
Storage statistics (tablespaces, indexspaces)
•
Number of times DBMS is looking for space in blocks or causes fragmentation
•
I/O device performance statistics—total times for read/write operations
•
Query processing statistics—execution times, wait times, times for optimization
and preparation, I/Os
•
Transaction processing statistics—execution times, wait times, times for opti-
mization and preparation, I/Os
•
Lock processing statistics—rates of issuing different types of locks
•
Logging statistics—log file activity
•
Indexing statistics—index creation and accesses
•
Input/output usage by user
•
Count of authorized users
•
Number of read and write operations executed during a set interval
•
Number of completed transactions during a set interval
•
Audit trail details for selected activities
•
Number of page faults during a prescribed interval
•
Number of times buffers full condition reached during a given interval
•
Profiles of CPU usage during each day
Review of Operations
In addition to monitoring the database system and collecting statistics, the DBA and
other team members must continue to review the operations on an ongoing basis.
They will be able to spot trends and changes by means of such reviews.
Review the following:
•
For each query, tables accessed, attributes used for selection conditions, attrib-
utes used for join conditions, and attributes whose values are retrieved
•
For each update transaction, type of operation (insert, write, or delete), attrib-
utes used in selection conditions, and attributes whose values are changed
Search WWH ::
Custom Search