Database Tuning

Tags:

• Database
• RDBMS

DB Optimizer

A DB optimizer is a core engine inside a DBMS that creates the optimal processing path to execute SQL as quickly and efficiently as possible.

• Types
  • Rule-based optimizer, cost-based optimizer
• Process
  • Finds candidate execution plans to execute the query submitted by the user.
  • Estimates the expected cost of each execution plan through object statistics and system statistics.
  • Compares each execution plan and selects the one with the lowest cost.

Hint

• A hint is an instruction statement for tuning SQL.
• When the optimizer cannot process an SQL statement with the optimal plan, the developer directly provides the optimal execution plan.
• Hints can be written after SELECT, and there are various hint clauses such as INDEX and PARALLEL.

Database Tuning and Methods

• DB tuning means improving the performance of a database system by adjusting the database structure, the database itself, the operating system, and related elements.

• Tuning can proceed in the stages of DB design tuning > DBMS tuning > SQL tuning.

Step 1: DB Design Tuning (Modeling Perspective)

• Tuning methods
  • Design with performance in mind during the database design stage
  • Data modeling, index design
  • Data files, tablespace design
  • Database capacity estimation
• Tuning examples
  • Denormalization, distributed file placement

Step 2: DBMS Tuning (Environment Perspective)

• Tuning methods
  • Specify memory or block size with performance in mind
  • Perspective related to CPU and memory I/O
• Tuning examples
  • Buffer size
  • Cache size

Step 3: SQL Tuning (Application Perspective)

• Tuning methods
  • Consider performance when writing SQL
  • Join, Indexing, SQL Execution Plan
• Tuning examples
  • Hash / Join