SQL Tuning Advisor

View Details on GitHub

Overview

Tech Stack

C++

PL/SQL

SQL Tuning

Purpose

⚙️ Analysis Types

1. Statistics Analysis

EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');

2. SQL Profiling

3. Access Path Analysis

CREATE INDEX idx_emp_salary ON employees(salary);

4. SQL Structure Analysis

-- Bad
SELECT * FROM orders WHERE TO_CHAR(order_date, 'YYYY') = '2024';

-- Good
SELECT * FROM orders
WHERE order_date BETWEEN DATE '2024-01-01' AND DATE '2024-12-31';

Execution Flow

BEGIN
    DBMS_SQLTUNE.CREATE_TUNING_TASK(...);
END;

BEGIN
    DBMS_SQLTUNE.EXECUTE_TUNING_TASK(...);
END;

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(...) FROM dual;

Result Types

Type Description
SQL Profile Execution plan improvement
Index Recommendation Suggests index creation
Statistics Recommendation Suggests statistics collection
SQL Rewrite Query restructuring
Alternative Plan Suggests alternative execution plans

SQL Tuning Advisor Implementation

Purpose

Milestones
Core Features

Design

1. Package Design

PKG_DBMS_SQLTUNE (Body & Spec)

PKG_DBMS_SQLTUNE_INTERNAL (Body & Spec)

PKG_DBMS_SQLTUNE_UTIL

2. System Tables & Views Design

System Tables

DBA Views

  • • DBA_ADVISOR_DEFINITIONS
  • • DBA_ADVISOR_TASKS
  • • DBA_ADVISOR_OBJECTS
  • • DBA_ADVISOR_EXECUTIONS
  • • DBA_ADVISOR_LOG

3. Create Tuning Task Design

4. Execute Tuning Task Design

Statistical Analysis

Access Path Analysis

SQL Structural Analysis

5. Report Tuning Task Design

6. Accept SQL Profile Design

System Design Summary

Conclusion