PART 01.
Oracle Basic Architecture

section 01 Oracle Database Architecture
section 02 Memory Architecture
section 03 Process Architecture
section 04 Oracle Storage Structures

PART 02.
Fundamentals of Oracle Performance Optimization

Section 01 Overview of Oracle SQL Performance Optimization
Section 02 Overview of DB Tuning Core Principles
Section 03 Library Cache Efficiency: Reducing SQL Parsing Load
Section 04 Minimizing Database Calls
Section 05 I/O Efficiency

PART 03.
Performance Tuning Tools and Execution Plan Analysis

section 01 DBMS_XPLAN.DISPLAY_CURSOR
section 02 DBMS_XPLAN.DISPLAY_AWR
Section 03 SQL_MONITOR
Section 04 Basic Analysis Method of Execution Plan Sequence
Section 05 Analysis of Exceptions to Execution Plan Sequence

PART 04. INDEX ACCESS PATTERN

Section 01 B-Tree INDEX Structure
Section 02 INDEX RANGE SCAN
section 03 INDEX RANGE SCAN DESCENDING
Section 04 INDEX UNIQUE SCAN
section 05 INDEX RANGE SCAN(MIN/MAX)
Section 06 ROWID ACCESS
Section 07 INDEX Column Processing
Section 08 Clustering Factor
Section 09 FULL TABLE SCAN
Section 10 INDEX ACCESS Conditions, FILTER Conditions, Selectivity
section 11 INDEX SKIP SCAN
section 12 INDEX INLIST INTERATOR
Section 13 INDEX FULL SCAN
section 14 INDEX FULL SCAN(MIN/MAX)
section 15 INDEX FAST FULL SCAN
Section 16 INDEX COMBINE
Section 17 INDEX JOIN
Section 18 Comparing the Differences Between INDEX COMBINE and INDEX JOIN
Section 19 INDEX FILTERING EFFECTS

PART 05. INDEX DESIGN STRATEGY

Section 01 Selectivity and Cardinality
section 02 INDEX column input, deletion, and update
Section 03 INDEX Selection Criteria
Section 04 Index design criteria by table type
Section 05 Combined Column INDEX Features and Column Order Determination Criteria
Section 06 INDEX Selection Procedure
Section 07 INDEX Design Example

PART 06. JOIN

section 01 NESTED LOOP JOIN
Section 02 HASH JOIN
Section 03 SORT MERGE JOIN
section 04 JPPD(Join Predicate Push Down)
Section 05 The Impact of JOIN Order on Performance

PART 07.
Subquery

section 01 FILTER subquery
section 02 EARLIER FILTER subquery
section 03 NL SEMI / ANTI JOIN
Section 04 Using Correlated Subqueries (FILTER, NL SEMI JOIN)
section 05 HASH SEMI / ANTI JOIN
section 06 SORT MERGE SEMI / ANTI JOIN
Section 07 Scalar Subqueries
Section 08 Non-correlated subqueries

PART 08.
Separate execution plans

Section 01 Separating execution plans using CONCATNATION
Section 02 Separating execution plans using UNION ALL

PART 09.
Paging processing

Section 01 Partial range processing, full range processing
Section 02 How to Use Standard PAGENATION
Section 03 Using Standard PAGENATION - Existence of Optimal INDEX
Section 04 Using Standard PAGENATION - No Optimal INDEX
Section 05 Using Standard PAGENATION - Processing Order
Section 06 PAGING Processing Application
Section 07 PAGING Processing in Web Bulletin Board Format

PART 10. PGA TUNING

Section 01 SORT ORDER BY
section 02 SORT ORDER BY & SORT ORDER BY STEOPKEY (STOPKEY)
section 03 SORT GROUP BY & HASH GROUP BY
section 04 SORT UNIQUE & HASH UNIQUE
section 05 HASH JOIN, HASH SEMI JOIN & HASH ANTI JOIN
section 06 SORT MERGE JOIN, MERGE SEMI JOIN & MERGE ANTI JOIN

PART 11.
Analytical functions and execution plans

Section 01 WINDOW SORT
section 02 WINDOW SORT PUSHED RANK
Section 03 WINDOW NOSORT
section 04 WINDOW NOSORT STOPKEY
Section 05 WINDOW BUFFER
Section 06: Deep Dive into Analytical Function Execution Plans

PART 12.
Repeated access tuning of the same data

Section 01 Repeating Access via Subquery OR Inline View - Using Analytical Functions
Section 02 UNION ALL Repeat ACCESS - SQL Integration
Section 03 UNION ALL Repeat ACCESS - Cartesian JOIN
Section 04 UNION ALL Repeat ACCESS - Utilizing the subtotal processing function
Section 05 UNION ALL Repeat ACCESS - WITH Statement Utilization
Section 06 UPDATE Statement Subquery Repeat ACCESS - Utilizing MERGE Statement
section 07 MERGE target table iteration ACCESS

PART 13.
Other application tuning

Section 01 Multiple rows → Group into one row and column
Section 02 Data grouped into one row and column → Split into multiple rows
section 03 Cumulative product between rows
Section 04 Cartesian JOIN Application - Daily, Weekly, and Monthly Status
Section 05 INDEX JOIN Application
section 06 Tuning using OUTLINE information

PART 14.
Optimizer

Section 01 What is an Optimizer?
section 02 10053 Trace
section 03 Heuristic Query Transformation

PART 15.
Oracle Transaction and Redo Log Tuning

Section 01 Transaction
Section 02 Redo & Undo
Section 03 Data Changes and Redo & Undo
Section 04 Tuning Practice Cases

PART 16.
Partitioning

Section 01 Overview
Section 02 Basic Concepts
Section 03 Partitioning Types
Section 04 Partition Key Strategy
section 05 INDEX of partitioned table
Section 06 Partition Management
Section 07 Partition Pruning

PART 17.
Oracle Exadata Basic

Section 01 Exadata Overview
Section 02 Offloading
Section 03 STORAGE INDEX
section 04 HCC (Hybrid Columner Compression)
Section 05 SMART FLASH CACHE
Section 06 Parallel Processing
Section 07 Considerations for Development on Exadata

PART 18.
Oracle Performance Analysis Basic Methodology

Section 01 Overview of Performance Analysis Methodology
Section 02 Understanding Core Performance Data
Section 03 Performance Analysis Utility
Section 04 Basic Performance Analysis

PART 19.
Tuning practice examples

Section 01 Related Sections - 4. INDEX ACCESS Pattern
Section 02 Related Sections - 4. INDEX ACCESS Pattern
Section 03 Related Sections - 6. JOIN
Section 04 Related Sections - 6. JOIN (JPPD)
Section 05 Related Sections - 7.
Subquery
Section 06 Related Sections - 6. JOIN, 7.
Subquery, 12.
Repeating the same data ACCESS Tuning section 07 Related section - 8.
Separate execution plans
Section 08 Related Sections - 6. JOIN, 8.
Separate execution plans
Section 09 Related Sections - 7.
Subquery, 10. PGA Tuning
Section 10 Related Sections - 6. JOIN, 7.
Subqueries, 10. PGA Tuning
Section 11 Related Sections - 12.
Repeated access tuning of the same data
Section 12 Related Sections - 5. INDEX ACCESS Pattern, 9.
Paging processing
Section 13 Related Sections - 9.
Paging processing, 7.
Subquery
Section 14 Related Sections - 6. JOIN
Section 15 Related Sections - 6. JOIN (JPPD)
Section 16 Related Sections - 7.
Subquery

Even if you've been developing SQL for several years, the topic of SQL tuning itself isn't easy to approach.
Using the results of my on-the-job tuning, I conducted SQL tuning training and case studies for my development team. I put a lot of thought into how to organize the content and topics to make it accessible. While SQL beginners might find tuning challenging, anyone with two to three years of experience studying and developing SQL will find this book a breeze.

The role of tuning experts is crucial even in the AI ​​era.

AI can automatically analyze execution plans or attempt tuning and recommend tuning results, but it cannot accurately interpret all situations.
Complex business logic or data models require expert experience and interpretation. AI-based tuning tools (e.g., Oracle SQL Tuning Advisor) can improve performance, but if applied incorrectly, they can also degrade performance. Therefore, expert pre- and post-implementation review and verification are essential.
SQL with complex JOINs and analytic functions is difficult to auto-tune, and high-difficulty SQL still requires expert manual handling.


In short, AI is a tool for tuning experts, not a replacement, and it remains the tuning experts who will interpret and be responsible for AI's recommendations.

Developers and tuning experts must validate AI-recommended tuning techniques and design optimal SQL tailored to the business logic. While AI can automate SQL tuning, it cannot automatically determine data partitioning, index design, and optimization strategies for OLTP and OLAP environments. Even in the AI ​​era, tuning experts need to understand data models and design optimal SQL. They must understand the principles of SQL tuning and grow into experts capable of collaborating with automation tools.

Structure and Features of This Book

This book covers essential concepts and practical tuning techniques for SQL performance optimization based on Oracle 19c.
When tuning a lot of SQL, I find that 80% of the most common performance degradations have a narrow pattern.
This book focuses on how to resolve 80% of the most common performance degradation patterns encountered in practice, while also introducing advanced tuning techniques.

● Basic principles of SQL tuning

Oracle Basic Architecture (Chapter 1)
Principles and Tuning Tools for SQL Performance Optimization (Chapters 2-3)
Understanding how SQL is executed and the primary causes of performance degradation is the first step to tuning.
This chapter covers the basic principles of SQL performance optimization and covers various tuning tools (DBMS_XPLAN_DISPLAY_CURSOR, SQL Monitoring Report, etc.) for analyzing execution plans and measuring performance.

● SQL execution optimization strategy

INDEX (Chapters 4-5)
JOIN (Chapter 6)
Subqueries and Execution Plan Separation (Chapters 7-9)
One of the most important factors in SQL tuning is how you utilize indexes. Performance can vary significantly depending on how you design indexes and optimize join methods, and this article covers strategies for optimizing these aspects.
It also provides methods to improve SQL performance by detailing subqueries, execution plan splitting, and paging processing optimization techniques.

● Other SQL tuning techniques

PGA Tuning and Execution Plan Analysis (Chapters 10-11)
Data Repeat ACCESS Optimization (Chapter 12)
Understanding Other Application Tuning and Optimizers (Chapters 13-14)
We introduce strategies to reduce unnecessary memory usage during SQL execution and minimize repeated access to the same data.
We also cover the basic principles of optimizers and their application tuning.

Tuning and practical examples in large-scale data environments

Oracle Transaction and Redo Log Tuning (Chapter 15)
Partitioning and Exadata Basics (Chapters 16-17)
Oracle Performance Analysis and Practical Cases (Chapters 18-19)

Partitioning is essential in large data environments.
And Exadata utilization is increasing in large-scale data environments.
In this regard, we covered partitioning and leveraging Oracle Exadata.


And SQL tuning experts don't just do SQL tuning.
When a performance issue occurs in a database, it is responsible for identifying and analyzing the cause of the performance issue across the database.
Accordingly, we also covered the basic methodology of Oracle performance analysis.
Finally, we have structured the course to help you understand how the theory is applied in real-world work through practical examples.

Becoming a guide and compass for SQL tuning

In the age of AI, SQL tuning is assisted, but the role of tuning experts will still be necessary.
Oracle also released 23ai in its latest version, 23, but the essence of tuning remains unchanged.
I hope this book serves as a reliable compass on your journey to SQL performance optimization. Let's begin your journey to becoming a SQL tuning expert today.