Oracle Tuning Concepts

Lesson 1

This course introduces you to some advanced Oracle performance tuning tools and concepts associated with Oracle. This course is designed to give you the skills needed to maximize the performance of your Oracle database. In this course, you will have the opportunity by means of hands-on exercises to look into the more subtle elements of the Oracle engine, and the more advanced features of Oracle tuning.

After completing the course, you will be able to:

1. Use data blocks efficiently to improve I/O performance
2. Tune using the new Oracle Data Structures
3. Use the Data Dictionary to monitor performance
4. Define and use Oracle table and index partitions
5. Tune with Oracle indexes for optimal performance
6. Tune Oracle for Web Applications
7. Describe and implement tuning techniques for Distributed Databases
8. Tune the Oracle Parallel Server (OPS)

In this course, you will learn and practice Oracle tuning concepts with two kinds of presentation methods:
the diagram and image gallery.
The following features are new in Oracle 12 Release 1:

1. In-memory aggregation: This optimization minimizes the join and GROUP BY processing required for each row when joining a single large table to multiple small tables, as in a star schema. VECTOR GROUP BY aggregation uses the infrastructure related to parallel query (PQ) processing, and blends it with CPU-efficient algorithms to maximize the performance and effectiveness of the initial aggregation performed before redistributing fact data.
2. SQL Monitor supports adaptive plans in the following ways:
   1. Indicates whether a plan is adaptive, and show its current status: resolving or resolved.
   2. Provides a list that enables you to select the current, full, or final plans

The process of Oracle performance tuning has significantly evolved from Oracle 11g R2 to Oracle 23ai, reflecting shifts in hardware, workloads, cloud adoption, and advancements in machine learning. Below is a structured comparison to highlight key differences and improvements: 🔧 1. Optimizer Enhancements

Feature	Oracle 11g R2	Oracle 23ai
Cost-Based Optimizer (CBO)	Mature, but decisions relied heavily on static statistics	Fully evolved CBO using dynamic runtime statistics, feedback loops, and AI-assisted tuning
Adaptive Query Optimization	Not available	Fully integrated, allows real-time plan changes based on actual execution
SQL Plan Directives	Not available	Automatically created and used to improve future query performance

🧠 2. Use of AI and Machine Learning

Feature	Oracle 11g R2	Oracle 23ai
Automatic SQL Tuning	Requires DBA to run DBMS_SQLTUNE manually	Fully autonomous: Oracle 23ai automatically identifies, tunes, and validates performance fixes
AI-Driven SQL Plan Management	Manual creation and baseline evolution	AI/ML used to auto-evolve plans with built-in safeguards and faster convergence

📊 3. Statistics Management

Feature	Oracle 11g R2	Oracle 23ai
Statistics Collection	Manual with DBMS_STATS jobs	Auto Task enhancements + real-time statistics and hybrid histogram support
Incremental Statistics	Introduced but not widely efficient	Greatly optimized, supports partitioned tables better and avoids full scans

☁️ 4. Cloud and Autonomous Features

Feature	Oracle 11g R2	Oracle 23ai
Cloud Integration	Traditional on-prem setup	Native to Oracle Cloud and Autonomous Database; performance tuning is AI-managed
Autonomous Performance Tuning	Not available	Available via Autonomous Performance Repository, ADB auto-indexing, and SQL plan evolution

🧮 5. Indexing Improvements

Feature	Oracle 11g R2	Oracle 23ai
Manual Indexing	DBA required to analyze and create indexes	Auto Indexing Engine identifies, creates, validates, and drops unused indexes automatically
Invisible/Virtual Indexes	Supported	Enhanced with more automation and integrated with SQL plan evolution and AI decision logic

🛠️ 6. Tools and Interfaces

Tool	Oracle 11g R2	Oracle 23ai
AWR/ASH/ADDM	Available	Enhanced integration with cloud diagnostics and better visual analytics
SQL Monitoring	Manual setup through Enterprise Manager	Real-time, continuous monitoring available through OCI Console, ADB Tools, and Data Safe

🧪 7. Execution Plan Diagnostics

Feature	Oracle 11g R2	Oracle 23ai
SQL Plan Baselines	Manual capture and evolve	Fully autonomous evolution, guided by feedback and SQL directives
Real-Time SQL Monitoring	Limited, mainly via EM	Enhanced, built into OCI console and accessible via SQL Developer Web and REST APIs

📌 Summary

Category	Oracle 11g R2	Oracle 23ai
Manual Tuning	Predominantly manual and DBA-driven	Automated and AI/ML-driven
Performance Visibility	Requires deeper manual analysis (AWR, EM)	Autonomous monitoring with proactive remediation
Optimization Strategy	Relies on accurate stats and DBA heuristics	Adaptive, self-correcting, and cloud-optimized

✅ Conclusion: In Oracle 23ai, performance tuning has become proactive, autonomous, and AI-driven, whereas in Oracle 11g R2, it was primarily manual, reactive, and DBA-intensive. DBAs now act more as performance architects and overseers, leveraging AI to maintain optimal database performance with minimal intervention.