Oracle Performance

Query Historical ASH Beyond Memory with DBA_HIST_ACTIVE_SESS_HISTORY Purpose Troubleshooting a performance spike after the fact is a different job than watching one happen live. By the time anyone opens a session the next morning to ask what was running during last night's batch window, the in-memory …

Watch Long-Running SQL in Real Time with V$SQL_MONITOR Purpose An AWR report describes what happened over the last hour. A SQL trace file describes what happened after the statement has already finished writing it. V$SQL_MONITOR answers a different question: what is running right now, and how far along is it. …

Get the Actual Execution Plan with DBMS_XPLAN.DISPLAY_CURSOR Purpose EXPLAIN PLAN never runs the statement it describes. It asks the optimizer what it would do, based on statistics and cardinality estimates, and prints that guess — which is exactly why a query that looks fine in EXPLAIN PLAN can still run slow in …

Flush the Shared Pool and Buffer Cache with ALTER SYSTEM Purpose Where DBMS_SHARED_POOL.PURGE removes one cursor or one package from the library cache, ALTER SYSTEM FLUSH SHARED_POOL clears every parsed statement, stored procedure, function, package, and trigger cached in the shared pool at once — a blunt instrument …

Check Redo Log Switch Frequency with V$LOG_HISTORY Purpose How many times does the online redo log switch in a single hour, and does that number spike during the nightly batch window or stay flat around the clock? A redo log sizing decision made from a single "switches per day" total hides the answer to that question. …

Enable a 10046 Trace for an Oracle Session Purpose A 10046 trace returns wait events only if the level includes them. Enabling the trace at level 1 and then wondering why the file shows no waits is the most common 10046 mistake in production diagnosis — the output has elapsed times and call counts, but none of the db …

Check RESUMABLE_TIMEOUT and RESUMABLE Status in V$SESSION Purpose V$SESSION carries two columns that tell you whether a session can survive running out of space: RESUMABLE and the resumable timeout that governs it. When resumable space allocation is enabled, a statement that hits a space error — a full tablespace, a …

Monitor PGA Aggregate Target with V$PGASTAT in Oracle Purpose Is PGA_AGGREGATE_TARGET set high enough for the work the instance actually does? V$PGASTAT answers that question. It is the instance-level statistics view for the Program Global Area (PGA), the private memory that server processes use for work areas. Oracle …

How to Change Oracle AWR Snapshot Interval to 30 Minutes using dbms_workload_repository.modify_snapshot_settings Purpose The Oracle Database Automatic Workload Repository (AWR) collects and stores performance statistics to help database administrators monitor and analyze database performance over time. The code …

How to Monitor Oracle Shared Pool Usage with V$SGASTAT Database Administrators (DBAs) frequently need to track memory allocation inside Oracle’s SGA (System Global Area), especially within the Shared Pool. Monitoring the shared pool helps identify memory fragmentation, excessive parsing, and potential memory-related …