Master Oracle Database Performance: 10 Proven Tuning Strategies

1. Adjust Data Structure Design

Before developing an information system, programmers should evaluate whether to use Oracle partitioning, create indexes for frequently accessed tables, and consider other schema‑level optimizations.

2. Adjust Application Architecture

Decide between a two‑tier client/server model or a three‑tier browser/web/database architecture, as each imposes different database resource requirements.

3. Optimize SQL Statements

The execution efficiency of SQL determines overall Oracle performance; Oracle recommends using the Oracle Optimizer and row‑level lock manager to tune statements.

4. Tune Server Memory Allocation

DBAs can modify SGA components (buffer cache, log buffer, shared pool) and PGA size, but an oversized SGA may cause OS swapping and degrade performance.

5. Balance Disk I/O

Place datafiles of the same tablespace on different disks to achieve I/O load balancing.

6. Adjust OS Parameters

On Unix systems, DBAs can adjust the OS buffer pool size and per‑process memory limits to improve Oracle behavior.

These measures are interrelated; performance degradation often results from multiple factors, requiring comprehensive knowledge and appropriate tools.

Oracle Performance Tools

Commonly used tools include the Oracle Online Data Dictionary, OS utilities such as vmstat and iostat, SQL Trace Facility with TKPROF, Oracle Enterprise Manager, and the EXPLAIN PLAN command.

System Performance Evaluation

Different system types require focus on different parameters.

OLTP

Rollback segment size

Indexes, clusters, hash structures

SGA size adequacy

SQL efficiency

Data Warehousing

B* or bitmap indexes

Parallel query execution

PL/SQL functions for stored procedures

Parallel database configuration when needed

SQL Tuning Principles

Use EXPLAIN PLAN to compare alternatives and follow these rules:

Prefer indexed access; the example shows that a query using NOT EXISTS (Statement B) leverages an index and runs faster than a NOT IN query (Statement A).

Choose the smallest table as the driving (main) table in joins.

Prefer WHERE EXISTS or WHERE NOT EXISTS over IN / NOT IN in subqueries.

Avoid complex view joins; break them into base‑table queries for better performance.

Set SHARED_POOL_RESERVED_SIZE to reserve memory for large SQL packages.

Use DBMS_SHARED_POOL to pin frequently used procedures in the shared pool.

CPU Parameter Tuning

CPU should be heavily utilized during peak load (≈90%). Use sar -u on Unix or Windows Performance Monitor to observe usage. DBAs can query v$sysstat for CPU metrics and v$sesstat for session‑level CPU consumption. High CPU may stem from hard parses, inefficient SQL, or lock contention.

Key queries:

SELECT * FROM V$SYSSTAT WHERE NAME IN ('parse time cpu','parse time elapsed','parse count (hard)');

SELECT SQL_TEXT, PARSE_CALLS, EXECUTIONS FROM V$SQLAREA ORDER BY PARSE_CALLS;

SELECT BUFFER_GETS, EXECUTIONS, SQL_TEXT FROM V$SQLAREA;

Adjust SESSION_CACHED_CURSORS or spin_count as needed.

Memory Parameter Tuning

Focus on SGA components: shared pool, buffer cache, and log buffer.

Check shared pool usage:

SELECT (SUM(pins - reloads)) / SUM(pins) "Lib Cache" FROM v$librarycache;

Check row cache usage:

SELECT (SUM(gets - getmisses - usage - fixed)) / SUM(gets) "Row Cache" FROM v$rowcache;

Calculate buffer cache hit ratio:

SELECT name, value FROM v$sysstat WHERE name IN ('db block gets','consistent gets','physical reads');

The hit ratio should be ≥90%; otherwise increase the buffer cache size.

Check log buffer usage and failure rate:

SELECT name, value FROM v$sysstat WHERE name IN ('redo entries','redo log space requests');

The failure rate (requests/entries) should be close to zero; if not, enlarge the log buffer.