PERFORMANCE TUNING GUIDE

                           ORACLE 19c DATABASE

PERFORMANCE TUNING GUIDE

Step-by-Step Reference for DBAs and Performance Engineers    

Step 1: Establish a Performance Baseline

 

Before tuning anything, you must understand how the database currently behaves. A baseline gives you a reference point to measure improvement.

 

1.1  Enable the Automatic Workload Repository (AWR)

AWR is the primary performance data collection mechanism in Oracle 19c. Verify it is licensed and active:

SELECT * FROM dba_hist_wr_control;

 

Set the snapshot interval and retention period:

BEGIN

  DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(

    retention => 43200,   -- 30 days in minutes

    interval  => 60       -- Snapshot every 60 minutes

  );

END;

/

 

1.2  Take a Manual AWR Snapshot

Capture an immediate snapshot to start baseline collection:

EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

 

1.3  Generate an AWR Report

After a representative workload period (at least 1 hour), generate the AWR HTML report:

@$ORACLE_HOME/rdbms/admin/awrrpt.sql

 

Key sections to analyze in the AWR report:

•       Top 10 Foreground Events — shows where sessions are waiting

•       SQL Statistics — identifies high-load SQL statements

•       Instance Activity Statistics — I/O, buffer cache, redo activity

•       Memory Statistics — SGA/PGA sizing information

•       Segment Statistics — top segments by logical/physical reads

 

1.4  Review Active Session History (ASH)

ASH provides real-time and historical session-level detail:

SELECT sample_time, session_id, event, wait_class,

       sql_id, blocking_session

FROM   v$active_session_history

WHERE  sample_time > SYSDATE - 1/24

ORDER  BY sample_time DESC;

 

 

Step 2: Tune Memory — SGA and PGA

 

Memory configuration has the most significant impact on database performance. Oracle 19c uses Automatic Memory Management (AMM) or manual component sizing.

 

2.1  Choose a Memory Management Strategy

 

Parameter / Strategy	Description
MEMORY_TARGET	Full AMM — Oracle manages SGA + PGA automatically. Simplest but less predictable.
SGA_TARGET	ASMM — Oracle auto-tunes SGA components within a fixed ceiling. Recommended for most workloads.
Manual sizing	You set each component explicitly. Best for very large or specialized systems.

 

2.2  Size the Buffer Cache

The buffer cache holds recently used data blocks. Target a high buffer cache hit ratio (> 99% for OLTP):

SELECT 1 - (phy.value / (cur.value + con.value)) AS hit_ratio

FROM   v$sysstat phy, v$sysstat cur, v$sysstat con

WHERE  phy.name = 'physical reads'

AND    cur.name = 'db block gets'

AND    con.name = 'consistent gets';

 

Increase if hit ratio is below 99% (and RAM is available):

ALTER SYSTEM SET db_cache_size = 8G SCOPE=BOTH;

 

2.3  Size the Shared Pool

The shared pool caches parsed SQL, PL/SQL code, and dictionary data. Monitor free memory:

SELECT name, bytes/1024/1024 AS mb_free

FROM   v$sgastat

WHERE  pool = 'shared pool'

AND    name = 'free memory';

 

Low free memory (< 10% of shared pool) indicates you need to increase it or reduce hard parsing:

ALTER SYSTEM SET shared_pool_size = 2G SCOPE=BOTH;

 

2.4  Configure PGA Memory

PGA is used for sort operations, hash joins, and bitmap operations. Use automatic PGA management:

ALTER SYSTEM SET pga_aggregate_target = 4G SCOPE=BOTH;

ALTER SYSTEM SET pga_aggregate_limit  = 8G SCOPE=BOTH;

 

Monitor PGA utilization:

SELECT name, value/1024/1024 AS mb

FROM   v$pgastat

WHERE  name IN ('aggregate PGA target parameter',

                'total PGA allocated',

                'cache hit percentage');

 

2.5  Review SGA Component Sizes (ASMM)

SHOW PARAMETER sga_target

SELECT component, current_size/1024/1024 AS mb_current,

       min_size/1024/1024 AS mb_min, max_size/1024/1024 AS mb_max

FROM   v$sga_dynamic_components

ORDER  BY current_size DESC;

 

 

Step 3: Identify and Tune High-Load SQL

 

Poorly written or unoptimized SQL is the most common cause of Oracle performance problems. Finding and fixing top SQL statements typically yields the highest ROI.

 

3.1  Find Top SQL by Elapsed Time

SELECT sql_id, executions, elapsed_time/1e6 AS elapsed_sec,

       elapsed_time/NULLIF(executions,0)/1e6 AS sec_per_exec,

       buffer_gets, disk_reads, SUBSTR(sql_text,1,80) AS sql_text

FROM   v$sqlstats

ORDER  BY elapsed_time DESC

FETCH  FIRST 20 ROWS ONLY;

 

3.2  Capture the Execution Plan

Always capture the actual (not estimated) execution plan:

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', NULL, 'ALLSTATS LAST'));

 

Explain plan for new SQL statements:

EXPLAIN PLAN FOR

  <your SQL here>;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

 

3.3  Common Plan Issues and Fixes

 

Plan Operation	Recommended Action
FULL TABLE SCAN	Add or rebuild indexes. Consider partitioning for large tables.
NESTED LOOPS on large sets	Evaluate HASH JOIN via hints or optimizer stats refresh.
High BUFFER GETS	Check for missing indexes, inefficient predicates, or bind variable peeking issues.
CARTESIAN JOIN	Missing or incorrect JOIN conditions — review the SQL logic.
High ESTIMATED vs ACTUAL rows	Gather fresh statistics: DBMS_STATS.GATHER_TABLE_STATS.
FILTER (wrong order)	Use SQL Profiles or hints to force better join order.

 

3.4  Gather Fresh Optimizer Statistics

BEGIN

  DBMS_STATS.GATHER_SCHEMA_STATS(

    ownname          => 'SCHEMA_NAME',

    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

    method_opt       => 'FOR ALL COLUMNS SIZE AUTO',

    degree           => 8,

    cascade          => TRUE

  );

END;

/

 

3.5  Use SQL Tuning Advisor

Oracle 19c's SQL Tuning Advisor automates analysis and recommends SQL Profiles, new indexes, or restructured queries:

DECLARE

  l_task VARCHAR2(30);

BEGIN

  l_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '&sql_id');

  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => l_task);

  DBMS_OUTPUT.PUT_LINE(DBMS_SQLTUNE.REPORT_TUNING_TASK(l_task));

END;

/

 

3.6  Reduce Hard Parsing — Use Bind Variables

Hard parsing consumes CPU and shared pool memory. Ensure applications use bind variables, not literal values:

-- BAD (hard parse on every execution):

SELECT * FROM orders WHERE order_id = 12345;

 

-- GOOD (soft parse on repeated executions):

SELECT * FROM orders WHERE order_id = :order_id;

 

Monitor hard vs. soft parse rates:

SELECT name, value FROM v$sysstat

WHERE  name IN ('parse count (hard)', 'parse count (total)');

 

 

Step 4: Optimize Indexes

 

Correct index strategy eliminates full table scans and reduces I/O dramatically. Oracle 19c supports B-tree, bitmap, function-based, and invisible indexes.

 

4.1  Identify Missing Indexes

Use the AWR SQL report or look for high buffer get SQL. You can also query the SQL Access Advisor:

BEGIN

  DBMS_ADVISOR.QUICK_TUNE(

    advisor_name => DBMS_ADVISOR.SQLACCESS_ADVISOR,

    task_name    => 'idx_advisor_task',

    attr1        => 'SELECT * FROM orders WHERE cust_id = :x'

  );

END;

/

 

4.2  Find Unused Indexes

Unused indexes waste space and slow down DML operations:

SELECT index_name, table_name, monitoring, used

FROM   v$object_usage

WHERE  used = 'NO';

 

Enable monitoring before checking:

ALTER INDEX idx_name MONITORING USAGE;

 

4.3  Rebuild Fragmented Indexes

Check index fragmentation with ANALYZE:

ANALYZE INDEX idx_name VALIDATE STRUCTURE;

SELECT name, del_lf_rows, lf_rows,

       ROUND(del_lf_rows/NULLIF(lf_rows,0)*100,2) AS pct_deleted

FROM   index_stats;

 

Rebuild if pct_deleted exceeds 20%:

ALTER INDEX idx_name REBUILD ONLINE PARALLEL 4;

 

4.4  Use Invisible Indexes for Safe Testing

Test a new index without impacting the current workload:

CREATE INDEX idx_test ON orders(status, created_date) INVISIBLE;

-- Test with optimizer_use_invisible_indexes = TRUE at session level

ALTER SESSION SET optimizer_use_invisible_indexes = TRUE;

 

4.5  Consider Partitioned Indexes for Large Tables

For tables with hundreds of millions of rows, local partitioned indexes align with table partitions and dramatically reduce I/O:

CREATE INDEX idx_orders_date

ON orders (order_date)

LOCAL COMPRESS 1;

 

 

Step 5: Tune I/O and Storage

 

Disk I/O is often the final bottleneck after memory and SQL are optimized. Oracle 19c integrates tightly with ASM (Automatic Storage Management) and supports Smart Flash Cache.

 

5.1  Identify I/O Bottlenecks

SELECT file#, name,

       phyrds, phywrts,

       readtim/NULLIF(phyrds,0) AS avg_read_ms,

       writetim/NULLIF(phywrts,0) AS avg_write_ms

FROM   v$filestat f, v$datafile d

WHERE  f.file# = d.file#

ORDER  BY phyrds + phywrts DESC;

 

5.2  Spread Hot Datafiles Across Disks

Move hot datafiles to separate spindles or SSDs using ASM rebalancing or OS-level file moves:

-- In ASM: rebalance disk group

ALTER DISKGROUP data REBALANCE POWER 4;

 

5.3  Configure db_file_multiblock_read_count

For datawarehouses with many full scans, increase the multiblock read count:

ALTER SYSTEM SET db_file_multiblock_read_count = 128 SCOPE=BOTH;

 

5.4  Monitor and Tune Redo Log I/O

Excessive log switches cause checkpoint storms and I/O spikes:

SELECT sequence#, members, archived,

       (sysdate - first_time)*24*60 AS age_minutes

FROM   v$log

ORDER  BY first_time;

 

Target: redo logs should switch no more than 4-6 times per hour. Increase log file size if switching more frequently:

-- Add new larger log group

ALTER DATABASE ADD LOGFILE GROUP 4

  ('/oradata/redo04a.log', '/oradata/redo04b.log') SIZE 1G;

 

5.5  Enable Direct Path Reads for Large Segments

For parallel query and large table scans, direct path reads bypass the buffer cache and reduce cache pollution:

ALTER SESSION SET "_serial_direct_read" = TRUE;  -- session level testing

 

 

Step 6: Manage Waits and Contention

 

Wait events indicate where sessions are blocked. Understanding the top wait events from AWR guides the next tuning actions.

 

6.1  Categorize Wait Events

 

Wait Event	Meaning & Action
db file sequential read	Single-block I/O — usually index scans. Optimize indexes or add storage speed.
db file scattered read	Multi-block full scans. Consider partitioning or adding indexes.
log file sync	Commits waiting for redo flush. Batch commits, or move redo to faster storage.
library cache lock/pin	Hard parsing or DDL contention. Use bind variables, avoid DDL under load.
buffer busy waits	Hot blocks — multiple sessions reading the same block. Reduce row chaining or use reverse indexes.
enq: TX - row lock contention	Row-level locking conflict. Review application transaction logic and commit frequency.
latch: cache buffers chains	Buffer cache latch — typically caused by hot blocks. Use reverse key indexes or partitioning.
direct path read/write	Parallel query or sort/temp I/O. Check PGA sizing and parallel degree.

 

6.2  Identify Blocking Sessions

SELECT l1.sid AS waiter, l2.sid AS blocker,

       l1.type, l1.id1, l1.id2

FROM   v$lock l1, v$lock l2

WHERE  l1.block  = 0

AND    l2.block  = 1

AND    l1.id1    = l2.id1

AND    l1.id2    = l2.id2;

 

6.3  Reduce Latch Contention

Increase the number of buffer cache chains (requires instance restart) or reduce hot block access patterns:

-- Check latch hit ratio

SELECT name, gets, misses,

       ROUND((1 - misses/NULLIF(gets,0))*100,2) AS hit_pct

FROM   v$latch

WHERE  gets > 0

ORDER  BY misses DESC

FETCH  FIRST 10 ROWS ONLY;

 

 

Step 7: Tune the Oracle Optimizer

 

The Cost-Based Optimizer (CBO) in Oracle 19c is highly sophisticated. Ensuring it has accurate statistics and correct configuration is essential.

 

7.1  Key Optimizer Parameters

 

Parameter	Guidance
optimizer_mode	CHOOSE (default). Use ALL_ROWS for batch/OLAP; FIRST_ROWS_n for OLTP with pagination.
optimizer_features_enable	Should match DB version: '19.1.0'. Never lower this unless debugging a regression.
optimizer_adaptive_plans	TRUE (default in 19c). Allows mid-execution plan switches. Set FALSE if causing instability.
optimizer_adaptive_statistics	FALSE (default in 19c). Enable only if you understand adaptive statistics impact.
optimizer_dynamic_sampling	2 (default). Increase to 4-6 for tables without fresh statistics.
_optimizer_use_feedback	TRUE. Feedback mechanism corrects cardinality errors over repeated executions.

 

7.2  Extended Statistics for Correlated Columns

When the optimizer underestimates cardinality for combined column predicates, create column group statistics:

SELECT DBMS_STATS.CREATE_EXTENDED_STATS(

  ownname => 'SCOTT',

  tabname => 'ORDERS',

  extension => '(STATUS, REGION)'

) FROM DUAL;

 

Then gather stats again:

EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','ORDERS');

 

7.3  Lock Statistics for Critical Tables

Prevent statistics from changing for tables where a known-good plan is critical:

EXEC DBMS_STATS.LOCK_TABLE_STATS('SCOTT', 'ORDERS');

 

7.4  Use SQL Plan Management (SPM)

SPM captures and stabilizes execution plans, preventing plan regressions after upgrades or stats refreshes:

-- Load plans from cursor cache into SQL Plan Baselines

DECLARE

  l_cnt PLS_INTEGER;

BEGIN

  l_cnt := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => '&sql_id');

  DBMS_OUTPUT.PUT_LINE('Plans loaded: ' || l_cnt);

END;

/

 

 

Step 8: Configure Parallelism

 

Oracle 19c supports intra-operation parallelism for large queries, DML, and DDL. Correct parallelism tuning avoids resource starvation while accelerating long-running operations.

 

8.1  Key Parallel Parameters

 

Parameter	Description
parallel_max_servers	Maximum PX servers. Set to CPU_COUNT x 2 for most systems.
parallel_degree_policy	MANUAL (default), LIMITED, or AUTO. AUTO lets Oracle auto-compute degree.
parallel_min_percent	Minimum % of requested degree; query waits or runs serial if not met. Default 0.
parallel_adaptive_multi_user	TRUE — reduces degree under heavy concurrency to prevent overloading.
parallel_force_local	TRUE in RAC to prevent cross-instance PX traffic (use if RAC interconnect is slow).

 

8.2  Set Parallel Degree on Objects

-- Enable parallel query on a table

ALTER TABLE fact_sales PARALLEL 8;

 

-- Run a specific query in parallel

SELECT /*+ PARALLEL(t,8) */ * FROM fact_sales t

WHERE  region = 'APAC';

 

8.3  Monitor Parallel Execution

SELECT qcsid, server#, degree, req_degree,

       status, last_query

FROM   v$px_session

WHERE  qcsid IS NOT NULL;

 

 

Step 9: Tune Temporary Tablespace and Sorts

 

Disk sorts in the TEMP tablespace indicate insufficient PGA. Excessive TEMP usage causes severe I/O overhead.

 

9.1  Monitor TEMP Usage

SELECT tablespace_name,

       total_blocks * 8192/1024/1024 AS total_mb,

       used_blocks  * 8192/1024/1024 AS used_mb,

       free_blocks  * 8192/1024/1024 AS free_mb

FROM   v$sort_segment;

 

9.2  Identify Sessions Using TEMP

SELECT s.sid, s.username, s.sql_id,

       u.tablespace, u.blocks * 8192/1024/1024 AS mb_used

FROM   v$session s, v$tempseg_usage u

WHERE  s.saddr = u.session_addr

ORDER  BY u.blocks DESC;

 

9.3  Add Temp Space or Increase PGA

If TEMP is exhausted, add a tempfile:

ALTER TABLESPACE temp ADD TEMPFILE '/oradata/temp02.dbf'

SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE 50G;

 

Or increase PGA to reduce disk sorts:

ALTER SYSTEM SET pga_aggregate_target = 8G SCOPE=BOTH;

 

 

Step 10: Implement Ongoing Monitoring

 

Tuning is not a one-time exercise. Implement automated monitoring to detect regressions before users report them.

 

10.1  Schedule Regular AWR Reports

Use DBMS_SCHEDULER to generate and email AWR reports nightly:

BEGIN

  DBMS_SCHEDULER.CREATE_JOB(

    job_name   => 'NIGHTLY_AWR_SNAPSHOT',

    job_type   => 'PLSQL_BLOCK',

    job_action => 'DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();',

    start_date => SYSTIMESTAMP,

    repeat_interval => 'FREQ=HOURLY',

    enabled    => TRUE

  );

END;

/

 

10.2  Configure Oracle Enterprise Manager (OEM)

Oracle 19c integrates with OEM Database Express (free) and OEM Cloud Control (licensed). Key alerts to configure:

•       Tablespace usage > 85%

•       CPU utilization > 90% sustained for > 5 minutes

•       Active sessions > 3x normal baseline

•       Response time degradation > 50% from baseline

•       Redo log switch frequency > 8 per hour

 

10.3  Key V$ Views for Ongoing Monitoring

 

View	Purpose
V$SESSION	Active/inactive sessions, wait events, SQL being executed.
V$SQLSTATS	Aggregate SQL execution statistics across all cursors.
V$SYSMETRIC	System-level metrics: DB time, CPU, IOPS, buffer cache hit ratio.
V$ACTIVE_SESSION_HISTORY	1-second sampled ASH data — last 1 hour in memory.
DBA_HIST_ACTIVE_SESS_HISTORY	Persisted ASH history in AWR (requires Diagnostics Pack).
V$EVENT_HISTOGRAM	Distribution of wait event duration — useful for latency profiling.
V$OSSTAT	OS-level CPU, memory, and I/O statistics from the OS layer.

 

10.4  Health Monitor Checks

Oracle 19c includes built-in Health Monitor checks. Run a comprehensive health check:

BEGIN

  DBMS_HM.RUN_CHECK(

    check_name => 'DB Structure Integrity Check',

    run_name   => 'manual_health_check'

  );

END;

/

 

View results:

SELECT run_id, name, status, start_time, end_time

FROM   v$hm_run

ORDER  BY start_time DESC;

 

 

Quick Reference: Tuning Checklist

 

 

Pre-Tuning Checklist

1.    Enable AWR with 60-minute interval and 30-day retention

2.    Take baseline AWR snapshot before and after peak load

3.    Generate and analyze AWR + ADDM reports

4.    Review ASH for top wait events over last 24 hours

5.    Identify top 10 SQL by elapsed time and buffer gets

 

Memory Tuning Checklist

6.    Verify buffer cache hit ratio > 99%

7.    Check shared pool free memory > 10% of total

8.    Confirm PGA aggregate target is 20-25% of available RAM

9.    Review SGA dynamic component history for auto-tuning accuracy

 

SQL Tuning Checklist

10.  Capture actual execution plans for top 10 SQL

11.  Check for full table scans on large tables

12.  Verify statistics are less than 7 days old on critical tables

13.  Run SQL Tuning Advisor on high-load SQL IDs

14.  Confirm application uses bind variables — no literal SQL

 

Index Checklist

15.  Identify and drop unused indexes (after monitoring > 7 days)

16.  Rebuild indexes with > 20% deleted leaf rows

17.  Add missing indexes identified by explain plan analysis

18.  Verify composite indexes match query predicate column order

 

I/O Checklist

19.  Identify hottest datafiles by physical reads/writes

20.  Verify redo log switches < 4-6 per hour

21.  Check TEMP tablespace utilization and add space if > 80%

22.  Confirm ASM disk groups are balanced

 

 

End of Oracle 19c Performance Tuning Guide

Refer to Or