Performance
How to check the data dictionary cache hit ratio in Oracle
Compute the Oracle data dictionary (row) cache hit ratio across all instances from GV$ROWCACHE.
How to generate CPU load in Oracle for testing
PL/SQL loop that generates CPU load in an Oracle session, useful for testing resource manager, load balancing, or ASH sampling.
How to find SQL doing full table scans with row and block context in Oracle
List Oracle SQL doing FULL table scans with SQL text, execution count, and target table row/block/buffer_pool context from V$SQL_PLAN.
How to show indexes for a table in Oracle
List Oracle indexes for a specific table (or all tables in a schema) with tablespace, num_rows, status, and index type.
How to find SQL with multiple execution plans in Oracle AWR
Rank Oracle SQL_IDs that ran under more than one plan by the elapsed-time gain of the fastest plan vs the average from DBA_HIST_SQLSTAT.
How to list SQL profiles in Oracle
List every SQL profile from DBA_SQL_PROFILES with creation date, type, category, and status.
How to find SQL doing full table scans in Oracle
List Oracle SQL_IDs currently in the shared pool that perform FULL table scans from V$SQL_PLAN, excluding SYS-owned objects.
How to find tables without a primary key in Oracle
List Oracle tables that have no primary key constraint by joining DBA_TABLES with DBA_CONSTRAINTS.
How to check top Oracle wait classes since instance startup
Rank Oracle wait classes by cumulative time waited from V$SYSTEM_WAIT_CLASS to see where the instance is spending time.
How to find unusable indexes and generate REBUILD statements in Oracle
List Oracle unusable indexes (including partitions and subpartitions) and generate ALTER INDEX REBUILD statements ready to run.
How to check MEMORY_TARGET advice in Oracle
Show V$MEMORY_TARGET_ADVICE predictions for sizing the Oracle Automatic Memory Management MEMORY_TARGET parameter.
How to run all Oracle memory advisors in one script
Print current memory parameters plus DB cache, MEMORY_TARGET, PGA, and SGA target advice — all spooled to one file.
How to check DB buffer cache advice in Oracle
Predict how changes to the Oracle buffer cache size will affect physical reads, from V$DB_CACHE_ADVICE.
How to check SGA target advice in Oracle
Show V$SGA_TARGET_ADVICE predictions for sizing the Oracle SGA_TARGET parameter.
How to check PGA target advice in Oracle
Predict how changing PGA_AGGREGATE_TARGET will affect PGA cache hit percentage and over-allocation, from V$PGA_TARGET_ADVICE.
How to check historical SGA and PGA allocation from AWR in Oracle
Show Oracle SGA and PGA allocation per AWR snapshot from DBA_HIST_SGASTAT and DBA_HIST_PGASTAT, ordered newest first.
How to check SGA used, free, and total memory in Oracle
Break Oracle SGA into used and free MB from V$SGASTAT, alongside total SGA size.
How to check current and maximum SGA and PGA usage in Oracle
Quick view of Oracle current SGA size, current PGA in use, maximum SGA size, and maximum PGA allocated.
How to check PGA and UGA memory per session in Oracle
Rank Oracle sessions by PGA and UGA memory (current and peak) from V$SESSTAT and V$STATNAME.
How to check total SGA and PGA memory in Oracle
One-row summary of Oracle SGA and PGA memory with a rollup grand total.
How to check SQL execution history from AWR in Oracle
Show per-snapshot execution counts and averages (reads, gets, rows, CPU, elapsed) for a given Oracle SQL_ID over the last ~14 days from AWR.
How to find top CPU-consuming sessions in Oracle
Rank the top 10 active Oracle sessions by CPU used from V$SESSTAT, with OS PID, module, and SQL_ID.
How to find top SQL by disk reads per execution in Oracle
Rank Oracle SQL from V$SQLAREA by disk reads per execution to surface heavy statements without needing AWR.
How to find top SQL by CPU in the last 24 hours in Oracle
Rank the top 20 Oracle SQL statements by cumulative CPU time over the last 24 hours from DBA_HIST_SQLSTAT.
How to find top SQL by I/O waits in the last 24 hours in Oracle
Rank Oracle SQL statements by db file sequential and scattered read wait time in the last 24 hours from ASH.
How to find top SQL by wait time in the last hour in Oracle
Rank the top 10 Oracle SQL statements by total wait time in the last hour from V$ACTIVE_SESSION_HISTORY.