List of Common Oracle Metrics Monitoring Methodologies Reactive Proactive Reactive monitoring is not a recommend approach but at times it becomes inevitable. Oracle 10g provide tools that aid in proactively monitoring database. Server-generated alerts and Automated Database Diagnostic Monitor(AADM) are going to be discussed in another post. Database and Instance Metrics Performance measurements [...]
Archive for the ‘Data Dictionary’ Category
24 Apr
Locks and Queries
Query to check blocking and waiting sessions SELECT holding_session bsession_id, waiting_session wsession_id, b.username busername, a.username wusername, c.lock_type type, mode_held, mode_requested, lock_id1, lock_id2 FROM sys.v_$session b, sys.dba_waiters c, sys.v_$session a WHERE c.waiting_session=a.sid and c.holding_session=b.sid To Find which session is blocking other sessions with some system info select s1.username || ‘@’ || s1.machine || ‘ [...]
2 Apr
Oracle Dynamic Performance Views
Dynamic Performance v$ Views Real-time performance metrics covering memory usage, wait events, input/output device throughput, and instance activity are available through dynamic performance views, also known as v$ views. Throughout its operation, the Oracle server records current database activity in a set of virtual tables called dynamic performance views. These virtual tables exist in memory only [...]
29 Mar
Data Dictionary
Oracle Dynamic Performance Views Oracle Data Dictionary Tables Data Dictionary A repository of information describing the data in the database. This includes the name and structures of all tables, constraints, indices, views, synonyms, sequences triggers, procedures, functions, and packages. SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, STATUS, LAST_CHANGE FROM DBA_CONSTRAINTS WHERE TABLE_NAME = ‘EMPLOYEES’ and OWNER = ‘SCOTT’ [...]
26 Mar
Constraints
Several types of Oracle constraints can be applied to Oracle tables to enforce data integrity, including: Oracle "Check" Constraint: This constraint validates incoming columns at row insert time. For example, rather than having an application verify that all occurrences of region are North, South, East, or West, an Oracle check constraint can be added to [...]
28 Feb
PGA
Program Global Area (PGA) The Program Global Area or Process Global Area (PGA) is a memory region that contains data and control information for a single server process or a single background process. The PGA is allocated when a process is created and deallocated when the process is terminated. The total PGA memory allocated by [...]
