Chapter 11 - DB Performance Tuning and Query Optimization

Database Performance Tuning

A set of activities and procedures designed to reduce the response time of a database system

Bottlenecks

A delay in the processing of an I/O operation that causes the overall system to slow down; caused by CPU, RAM, hard disk, network or application code

SQL Performance Tuning

(Client side) Activities to help generate a SQL query that returns the correct answer in the least amount of time, using the minimum amount of resources at the server end.

DBMS performance tuning

(Server side) Activities to ensure that the clients' requests are addressed as quickly as possible while making optimum use of existing resources

Data File

A named physical storage space where all data in a database is stored

Extents

The ability of data files to expand in size automatically using predefined increments

Table Space (file group)

A logical grouping space used to group related data

Data Cache (Buffer Cache)

A shared, reserved memory area that stores the most recently accessed data blocks in RAM; faster than working with data files

SQL Cache (Procedure Cache)

A shared, reserved memory area that stores the most recently executed SQL statements or PL/SQL procedures

Input/Output (I/O) Request

A low-level data access operation that reads or writes data to and from computer devices

Listener process

DBMS process that listens for clients' requests and handles the processing of the SQL requests to other DBMS processes

User process

DBMS process that manages each client session and handles requests submitted to the server

Scheduler process

DBMS process that organizes the concurrent execution of SQL requests

Lock Manager process

DBMS process that manages all locks placed on database objects including diskpages

Optimizer process

DBMS process that analyzes SQL queries and finds the most efficient way to access the data

What principles are query optimization algorithms based on?

• Selection of optimum order to achieve fastest execution time

• Selection of sites to be accessed to minimize communication costs

What are query optimization algorithms evaluated on?

Operation mode and timing of its optimization

Automatic Query Optimization

An operation mode where the DBMS finds the most efficient access path for the execution of a query

Manual Query Optimization

An operation mode that requires the end user or programmer to define the access path for the execution of the query

Static Query Optimization

A timing strategy where the access path to a database is predetermined at compilation time

Dynamic Query Optimization

A timing strategy where the most up-to-date information about the database is used to determine the SQL access strategy at run time

Statistically based Query Optimization

An algorithm that uses statistical information about a database to determine the best access strategy

Dynamic Statistical Generation Mode

A statistically based query optimization algorithm mode where the DBMS automatically evaluates and updates the statistics after each data access operation

Manual Statistical Generation Mode

A statistically based query optimization algorithm mode where the database administrator must run a periodic routine to generate the data access statistics

Rule-based Query Optimization

An algorithm that uses a set of user-defined rules to determine the best approach to executing a query

Database Statistics

A number of measurements about database objects such as number of processers used, processor speed and temporary space available

Phases of DBMS Query Processing

Parsing, Execution and Fetching

Parsing Phase

The DBMS breaks down the SQL query into smaller units and transform the original query into a fully equivalent and more efficient version

Query Optimizer

A DMBS process that analyzes SQL queries and generates the most efficient access/execution plan; performs the parsing activites

Rule-based optimizer

A query optimization mode based on the rule-basd query optimization algorithm

Cost-based Optimizer

A query optimization mode that uses an algorithm based on statistics about the objects being accessed

Optimizer Hints

Special instructions for the optimizer that are embedded inside the SQL command text

Access Plan

A set of instructions generated at application compilation time that the DBMS will use to execute the query and return the result set in the most efficient way

Table scan (full)

An access plan operation that reads the entire table sequentially, from first to last row, one at a time (slowest)

Table access (row ID)

An access plan operation that reads a table row directly, using the row ID value (fastest)

Index Scan (range)

An access plan operation that reads the index first to obtain the row Ids and then accesses the table rows directly (faster than full table scan)

Index Access (unique)

An access plan operation that reads and compares a set of values to another set of values, using a nested loop style (slow)

Merge

An access plan operation that merges two data sets (slow)

Sort

An access plan operation that sorts a data set (slow)

Execution Phase

The DBMS executes the SQL query using the chosen execution plan

Fetching Phase

The DBMS fetches the data and sends the result back to the client

Indexes

An ordered set of values that contain the index key and pointers; used when a subset of rows from a large table is to be selected based on a given condition

Advantages of Indexes

• Helps speed up data access by facilitating searching, sorting, using aggregate functions and join operations

• More efficient than full table scan

Data Sparsity

Number of different values a column could have

Hash Indexes

Based on an ordered list of hash values; good for simple and fast lookups based on equality conditions

B-Tree Index

Common and default index organized as an upside-down tree; good for tables where column values repeat a small number of times

Bitmap index

An index that uses bit arrays (0s and 1s) to represent the existence of a value or condition; good for tables with large amount of rows and small number of repeated column values

Index Selectivity

A measure of the likelihood that an index will be used in query processing

Function-based Index

A type of index based on a specific SQL function or expression.

Conditional expressions

Expressed within WHERE or HAVING clauses of a SQL statement

In-memory database

A database optimized to store large portions of a database in primary RAM storage rather than secondary (disk)

I/O Accelerator

A device used to improve throughput for I/O operations

Redundant Array of Independent Disks (RAID)

Uses multiple systems to create virtual disks from several individual disks

Index Organized Table

A type of table storage organization that stores end-user data and index data in consecutive locations in permanent storage (aka clustered index table)