Oracle SQL Tuning Essentials: Joins, Execution Plans, CBO vs RBO, Backup Tips

1. Common Table Join Types

hash join, merge join, nested loop (cluster join), index join.

2. Viewing Execution Plans Without Third‑Party Tools

Use Oracle commands:

set autot on
explain plan set statement_id = &item_id for &sql;
select * from table(dbms_xplan.display);

Alternatively:

EXPLAIN PLAN FOR SELECT * FROM EMP;
SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));

3. Cost‑Based Optimizer (CBO) vs Rule‑Based Optimizer (RBO)

RBO relies on predefined rules; CBO uses statistics and cost calculations. When optimizer_mode=choose, Oracle selects CBO if statistics are available, otherwise RBO. CBO evaluates execution plans based on estimated cost, aiming for lowest resource consumption.

4. Identifying Resource‑Intensive SQL

Query v$sql for high disk reads or high buffer‑gets per execution:

select sql_text
from v$sql
where disk_reads > 1000
   or (executions > 0 and buffer_gets/executions > 30000);

5. Tracing SQL Executed by a Specific Session

Enable SQL trace for a session and set event 10046 level 12:

exec dbms_system.set_sql_trace_in_session(sid, serial#, sql_trace);
select sid, serial# from v$session where sid = (select sid from v$mystat where rownum = 1);
exec dbms_system.set_ev(sid, serial#, event_10046, level_12, '');

6. Key Focus Areas for SQL Tuning

Measure response time using metrics such as DB block gets, consistent gets, physical reads, and sorts.

7. Understanding Indexes

Types include B‑tree, bitmap, function‑based, and partitioned (local/global). Indexes improve SELECT/UPDATE/DELETE performance but can slow INSERT operations.

8. When Indexes Do Not Improve Performance

If an index is misused or the query pattern does not benefit from it, performance may not improve.

9. Bind Variables

Bind variables replace literal values, reducing hard parses and CPU usage, but they limit histogram usage and can make SQL tuning harder.

10. Stabilizing Execution Plans

Set optimizer parameters and use stored outlines:

query_rewrite_enabled = true
star_transformation_enabled = true
optimizer_features_enable = 9.2.0
-- create and use stored outline

11. Sorting Memory in Oracle 8i vs 9i and Temporary Tablespace

In 8i, sort_area_size and sort_area_retained_size control memory; overflow uses temporary tablespace. In 9i, with workarea_size_policy=auto, PGA handles sorting, typically 1/20 of pga_aggregate_target. If workarea_size_policy=manual, sort_area_size governs memory, and overflow also uses temporary tablespace.

12. Sample Query for Paginated Results

Creating a test table and retrieving rows 21‑30 ordered by column c:

create table t(a number,b number,c number,d number);
/
begin
  for i in 1..300 loop
    insert into t values (mod(i,2), i/2, dbms_random.value(1,300), i/4);
  end loop;
end;
/
select * from (
  select c.*, rownum as rn
  from (select * from t order by c desc) c
) where rn between 21 and 30;

13. Tablespace and Datafile Relationship

A tablespace can contain one or more datafiles; each datafile belongs to a single tablespace. Data is distributed across datafiles via a hash algorithm.

14. Locally Managed vs Dictionary Managed Tablespaces and ASSM

Locally Managed Tablespaces (LMT) use bitmaps for space management. Dictionary Managed Tablespaces (DMT) rely on the data dictionary. Automatic Segment Space Management (ASSM) replaces freelists with bitmap structures, improving allocation efficiency.

15. Role of Rollback Segments

Rollback segments store before‑image data for transaction undo, support recovery, and provide read consistency.

16. Purpose of Redo and Archive Logs

Redo logs record all changes for crash recovery; at least two are required. Archive logs are offline copies of redo logs used for media‑failure recovery.

17. SGA Components

Includes db_cache, shared_pool, large_pool, and java_pool, each serving caching, shared SQL/PLSQL storage, backup I/O buffering, and Java procedure execution respectively.

18. Oracle Background Processes

Key processes: DBWR (writes dirty buffers), LGWR (writes redo), SMON (recovery), PMON (process cleanup), CHKP (checkpoint), ARCn (archiving), CJQ (job scheduler), RECO (distributed transaction coordination).

19. Backup Types

Logical backup: exp/imp.

Physical backup: RMAN (full, incremental), hot backup (online tablespace), cold backup (offline).

20. Archiving Concept

Archiving moves filled online redo logs to archive logs, enabling point‑in‑time recovery. Databases operate in NOARCHIVELOG or ARCHIVELOG mode.

21. Restoring a Dropped Table with Proper Backups

Example recovery commands:

startup mount;
alter database recover automatic until time '2004-08-04:10:30:00';
alter database open resetlogs;

22. RMAN Overview

Recovery Manager provides backup and restore capabilities, supports command‑line, GUI, and API interfaces, and offers features such as block‑level incremental backups, compression, backup set packaging, automated scripts, and bad‑block detection.

23. Standby Database Characteristics

Standby databases provide high availability via log shipping. Protection modes include MAXIMIZE PROTECTION (no data loss), MAXIMIZE AVAILABILITY (asynchronous, possible data loss), and MAXIMIZE PERFORMANCE (asynchronous, best performance).

24. Designing a Backup Strategy for a 50 GB Database with 5 GB Daily Archives

Typical RMAN schedule: level 0 full backup on the first of each month, level 1 incremental on weekends, level 2 incremental on other days.

25. System Performance Diagnosis Approach

Collect statistics with STATSPACK.

Identify top events and SQL using V$SYSTEM_EVENT, V$SESSION_EVENT, V$SESSION_WAIT.

Find high‑cost SQL via V$SQL, V$SQLTEXT, V$SQLAREA.

26. Statspack Knowledge

Statspack captures performance snapshots, offering multiple levels of detail and a growing set of tables for analysis.

27. Index Creation on Large Tables – Impact Mitigation

Build indexes during low‑load periods, consider NOLOGGING (if not using Data Guard), and increase sort_area_size or pga_aggregate_target to reduce impact.

28. RAID 10 vs RAID 5

RAID 10 combines mirroring and striping for high performance and reliability, requiring at least four disks. RAID 5 distributes parity across disks, offering better storage efficiency but lower write performance, needing at least three disks.