Thursday, July 12, 2012

Database Performance Monitoring Parameters: (Oracle 11 g)

Database Monitoring Parameters: (Oracle 11 g)

DataBase Monitorig(Oracle 11 g)
Monitoring Counters
Wait Time Stats
DB CPU waits
Wait events are statistics that are incremented by a server process/thread to indicate that it had to wait for an event to complete before being able to continue processing.
Application waits
Network waits
Concurrency waits
Commit waits.
Cache Sizes.
Log Buffer
The Oracle log buffer is a small area of RAM where updates (DML) are processed for roll-forward and flashback functionality.
SGA Size
The System Global Area (SGA) is a group of shared memory areas that are dedicated to an Oracle “instance” (an instance is your database programs and RAM).
PGA Size
A PGA is a memory region that contains data and control information for a server process.
Capturing Resource intensive SQL statements
Queries with high execution time
Queries with high elapsed time per execution.
Queries with high CPU  time
Queries with high CPU time per execution.
Queries with high Read time
Queries with high Read time per execution.
Queries with high Write time
Queries with high Write time per execution.
Capturing Redundent Queries
Repeated queries for a single activity
Any repeated query for single activity.
Missing indexes/Full Table scan Monitoring
Missing indexes/Full Table scans
Execution plan to be generated or any index analyzer tool to be used for capturing missing indexes and to find table full scan.
AWR Report monitoring.
Automatic Workload Repository
The AWR collects and stores database statistics relating to problem detection and tuning.
ADDM Report Monitoring
Rowlock contention.
Queries to be identified causing any row lock contention on tables.
Queries with high parsing time
To verify any hard parsing in queries.
ASH Report monitoring
Active Session History
It contains the information about blocker and waiter identifiers and their associated transaction IDs and SQL.
Alert Log File Monitoring
Deadlock contention
Alert log file to traced for confirming any dead lock in application.
Network Log Monitoring
Network Usage
For Monitoring Network usage on database
Session monitoring
Session State,Current Query and Blocked Sessions
Monitoring of Blocked oracle sessions along with the waits stats.


plz give ur comments !!!!!!