Oracle Architecture

• Database and instance

• Buffer Cache and Shared Pool

• Logical storage: tablespaces and segments

Indexes

• B*Tree index structure

• Indexes vs. full table scans

• Unique vs. non-unique indexes

• Single-column and composite indexes

SQL Tuning & Execution Plans

• Identifying high-load SQL (OEM, AWR, tracing)

• Retrieving execution plans (Autotrace,DBMS_XPLAN, EXPLAIN PLAN)

• Oracle Optimizer concepts: cost, cardinality,selectivity

• Constraints and keys for data integrity

• Oracle client tools: SQL*Plus, SQL Developer

• Performance considerations and new Oracle features

Influencing Execution Plans

• Hints, optimizer statistics, parameters, SQL rewrites

Access Paths & Join Methods

• Full scans, index access, RowID access

• Index scans: unique, range, full, fast full

• Join algorithms: nested loops, sort-merge, hash join

Real-Time Execution Statistics

• SQL Monitoring

• Actual vs. estimated plan values

• Logical vs. physical I/O

SQL Rewriting for Performance

• Practical tips, tricks, and real-life examples

Application Tracing

• SQL Trace and TKPROF

• DBMS_APPLICATION_INFO, DBMS_MONITOR, TRCSESS

Performance Reports

• AWR, ASH, ADDM reports

Advanced Indexing

• Efficient index usage

• Function-based indexes, composite indexes,invisible indexes

• Indexes with expressions and NULL values

• Indexes and DML considerations

Partitioned Tables

• Partitioning concepts

• Global vs. local indexes

• Partition pruning

Data Cleanup & Maintenance

• High-water mark

• Delete vs. truncate vs. drop

• Partition-based data purging

Optimizer Statistics

• Gathering statistics (table, schema, database)

• Histograms and extended statistics

• Automatic statistics gathering

• Statistics locking and restoring

Real-World SQL Tuning

• Practical optimization examples throughout the course