Database

Systems:
Design,
Implementation,
and
Management,
14e
Module 11: Database
Performance Tuning and
Query Optimization
Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 1
whole or in part.
 Chapter Objectives

By the end of this chapter, you should be able to:

1. Identify the procedures involved in database performance tuning

2. Describe how a DBMS processes SQL queries in each of its three phases

3. Explain the role of indexes in speeding up data access

4. Differentiate between a rule-based optimizer and a cost-based optimizer

5. Describe some common practices used to write efficient SQL code

6. Explain how to formulate queries and tune the DBMS for optimal performance

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 2
whole or in part.
 Database Performance-Tuning
Concepts (1 of 2)
• One of the main functions of a database system is to provide timely answers to end
users
− End users interact with the DBMS through the use of queries to generate
information, using the following sequence:
 1. End-user (client-end) application generates a query
 2. Query is sent to the DBMS (server end)
 3. DBMS (server end) executes the query
 4. DBMS sends the resulting data set to the end-user (client-end) application

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 3
whole or in part.
 Database Performance-Tuning
Concepts (2 of 2)
• The goal of database performance is to execute queries as fast as possible

• Database performance tuning is a set of activities and procedures designed to

reduce the response time of a database system

• Fine-tuning the performance of a system requires a holistic approach

− All factors must be checked to ensure that each one operates at optimum levels
with minimal bottlenecks

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 4
whole or in part.
 Performance Tuning: Client and
Server
• On the client side, the objective is to 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
− Commonly referred to as SQL performance tuning

• On the server side, the DBMS environment is configured to respond to clients’

requests as fast as possible, while making optimum use of existing resources
− Commonly referred to as DBMS performance tuning

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 5
whole or in part.
 DBMS Architecture (1 of 3)

• All data in a database are stored in data files

• Data blocks are the smallest logical units of assignable storage space in the
database

• Extents are contiguous collections of data blocks that are allocated to database
objects

• Data files are grouped in file groups or table spaces

− A table space is a logical grouping of several data files that store data with
similar characteristics

• The data cache or buffer cache is a shared, reserved memory area that stores
the most recently executed SQL statements or PL/SQL procedures

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 6
whole or in part.
 DBMS Architecture (2 of 3)

• The SQL cache, or procedure cache, is a shared, reserved memory area that
stores the most recently executed SQL statements or PL/SQL procedures

• The DBMS retrieves data from permanent storage and places it in RAM
− Data is retrieved from the data files and placed in the data cache

• An input/output request is a low-level data access operation that reads or writes

data to and from computer devices

• Working with data in the data cache is faster than working with data files

• Most performance-tuning activities focus on minimizing I/O operations

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 7
whole or in part.
DBMS Architecture (3 of 3)

Figure 11.1 Basic DBMS

Architecture

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 8
whole or in part.
 Database Query Optimization Modes
(1 of 3)
• Most of the algorithms proposed for query optimization are based on two principles:
− The selection of the optimum execution order to achieve the fastest execution
time
− The selection of sites to be accessed to minimize communication costs

• Within those two principles, a query optimization algorithm can be evaluated based
on:
− Its operation mode
− The timing of its optimization

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 9
whole or in part.
 Database Query Optimization Modes
(2 of 3)
• Operation modes can be classified by the following:
− Automatic query optimization means that the DBMS finds the most cost-
effective access path without user intervention
− Manual query optimization requires that optimization be selected and
scheduled by the end-user or programmer

• Query optimization algorithms can be classified according to when the optimization

is done
− Static query optimization takes place at compilation time
− Dynamic query optimization takes place at execution time

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 10
whole or in part.
 Database Query Optimization Modes
(3 of 3)
• Query optimization techniques can be classified according to the type of information
used to optimize the query
− A statistically based query optimization algorithm uses statistical
information about the DBMS to determine the best access strategy
− Statistical information is managed by the DBMS and is generated in one of two
different modes:
 Dynamic statistical generation mode
 Manual statistical generation mode
− A rule-based query optimization algorithm is based on a set of user-defined
rules to determine the best query access strategy

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 11
whole or in part.
 Database Statistics (1 of 2)

• Database statistics refers to a number of measurements about database objects,

such as the following:
− Number of processors used
− Processor speed
− Temporary space available

• Statistics provide a snapshot of database characteristics

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 12
whole or in part.
 Database Statistics (2 of 2)

Table 11.2 Sample Database Statistics Measurements

Database Object Sample Measurements
Tables Number of rows, number of disk blocks used, row length, number of columns in each row, number of distinct values in
each column, maximum value in each column, minimum value in each column, and columns that have indexes
Indexes Number and name of columns in the index key, number of key values in the index, number of distinct key values in
the index key, histogram of key values in an index, and number of disk pages used by the index
Environment Logical and physical disk block size, location and size of data files, and number of extends per data file
Resources

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 13
whole or in part.
 Knowledge Check Activity 11-1

• What is SQL performance tuning?

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 14
whole or in part.
 Knowledge Check Activity 11-1:
Answer
• What is SQL performance tuning?
• Answer: SQL performance tuning describes a process – on the client
side – that will generate an SQL query to return the correct answer in
the least amount of time, using the minimum amount of resources at
the server end.

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 15
whole or in part.
 Query Processing (1 of 2)

• The DBMS processes a query in three phases:

− 1. Parsing: The DBMS parses the SQL query and chooses the most efficient
access/execution plan
− 2. Execution: The DBMS executes the SQL query using the chosen execution
plan
− 3. Fetching: The DBMS fetches the data and sends the result set back to the
client

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 16
whole or in part.
 Query Processing (2 of 2)

Figure 11.2 Query Processing

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 17
whole or in part.
 SQL Parsing Phase (1 of 2)

• The optimization process includes breaking down the query into smaller units
− The original SQL query is transformed into slightly different version of original
SQL code which is fully equivalent and more efficient

• SQL parsing is performed by the query optimizer, which analyzes the SQL query
and finds the most efficient way to access data

• An access plan is the result of parsing a SQL statement; it contains a series of

steps the DBMS will use to execute the query and return the result set in the most
efficient way
− If an access plan exists for the query in SQL cache, the DBMS reuses it
− If there is no access plan, the optimizer evaluates various plans and chooses one
to be placed in SQL cache for use
Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 18
whole or in part.
 SQL Parsing Phase (2 of 2)

Table 11.3 Sample DBMS Access Plan I/O Operations

Operation Description
Table scan (full) Reads the entire table sequentially, from the first row to the last, one row at a time (slowest)

Table access (row Reads a table row directly, using the row ID value (fastest)
ID)
Index scan (range) Reads the index first to obtain the row IDs and then accesses the table rows directly (faster than a full table scan)

Index access Used when a table has a unique index in a column

(unique)
Nested loop Reads and compares a set of values to another set of values, using a nested loop style (slow)

Merge Merges two data sets (slow)

Sort Sorts a data set (slow)

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 19
whole or in part.
 SQL Execution Phase

• In this phase, all I/O operations indicated in the access plan are executed
− The proper locks are acquired for the data to be accessed
− The data is retrieved and placed in the DBMS’s data cache
− All transaction management commands are processed during the parsing and
execution phases of query processing

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 20
whole or in part.
 SQL Fetching Phase

• After the parsing and execution phases are completed, all rows that match the
specified condition(s) are retrieved, sorted, grouped, and aggregated

• During the fetching phase, the rows of the resulting query results set are returned to
the client

• The DBMS may use temporary table space to store temporary data

• In this stage, the database server coordinates the movement of the result set rows
from the server cache to the client cache

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 21
whole or in part.
 Query Processing Bottlenecks

• A query processing bottleneck is a delay introduced in the processing of an I/O

operation that slows the system

• Within a DBMS, the following five components typically cause bottlenecks:

 CPU
 RAM
 Hard disk
 Network
 Application code

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 22
whole or in part.
 Indexes and Query Optimization (1 of
4)
• Indexes help speed up data access
− They facilitate searching, sorting, using aggregate functions, and join operations

• An index is an ordered set of values that contain the index key and pointers

• An index scan is more efficient than a full table scan because the index data is
preordered and the amount of data is usually much smaller

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 23
whole or in part.
Indexes and Query Optimization (2 of
4)

Figure 11.3 Index Representation

for the Customer Table

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 24
whole or in part.
 Indexes and Query Optimization (3 of
4)
• Data sparsity refers to the number of different values a column could have

• Data structures used to implement indexes include the following:

− Hash index, which is an index based on an ordered list of hash values
− B-tree index, which is an ordered data structure organized as an upside-down
tree
− Bitmap index, which is an index that uses a bit array (0s and 1s) to represent
the existence of a value or condition

• Current-generation DBMSs are intelligent enough to determine the best type of

index to use under certain circumstances
− Provided the DBMS has updated database statistics

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 25
whole or in part.
Indexes and Query Optimization (4 of
4)

Figure 11.4 B-Tree and Bitmap

Index Representation

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 26
whole or in part.
 Knowledge Check Activity 11-2

• If indexes are so important, why not index every column in

every table?

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 27
whole or in part.
 Knowledge Check Activity 11-2:
Answer
• If indexes are so important, why not index every column in
every table?
• Answer: Indexing every column in every table will tax the DBMS too
much in terms of index-maintenance processing, especially if the
table has many attributes, many rows, and/or requires many inserts,
updates, and/or deletes.

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 28
whole or in part.
 Optimizer Choices

• The query optimizer can operate in one of two modes:

− A rule-based optimizer uses preset rules and points to determine the best
approach to execute a query
 The rules assign a “fixed cost” to each SQL operation
− A cost-based optimizer uses algorithms based on statistics about the objects
being accessed to determine the best approach to execute a query
 The optimizer process adds up the processing cost, I/O costs, and resource
costs (RAM and temporary space) to determine the total cost of a given
execution plan

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 29
whole or in part.
 Using Hints to Affect Optimizer
Choices
• In some instances, the optimizer might not choose the best execution plan
− The optimizer makes decisions based on existing statistics; statistics might be
old
− Even with current statistics, the optimizer’s choice might not be the most
efficient one

• Optimizer hints are special instructions for the optimizer that are embedded in the
SQL command text

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 30
whole or in part.
 SQL Performance Tuning

• SQL performance tuning is evaluated from client perspective

− Most current relational DBMSs perform automatic query optimization at the
server end
− Most SQL performance optimization techniques are DBMS-specific and thus
rarely portable

• The majority of performance problems are related to poorly written SQL code
− A carefully written query almost always outperforms a poorly written one

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 31
whole or in part.
 Index Selectivity

• Index selectivity is a measure of the likelihood that an index will be used in query
processing

• Indexes are used when a subset of rows from a large table is to be selected based
on a given condition

• You cannot always use an index to improve performance

• A function-based index is an index based on a specific SQL function or expression

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 32
whole or in part.
 Conditional Expressions (1 of 3)

• A conditional expression is normally placed within the WHERE or HAVING clauses of

a SQL statement
− It restricts the output of a query to only rows matching conditional criteria

• The following practices are used to write efficient conditional expressions in SQL
code:
− Use simple columns or literals as operands
− Numeric field comparisons are faster than character, date, and NULL
comparisons
− Equality comparisons are faster than inequality comparisons
− Whenever possible, transform conditional expressions to use literals

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 33
whole or in part.
 Conditional Expressions (2 of 3)

• The following practices are used to write efficient conditional expressions in SQL
code (continued):
− Write equality conditions first when using multiple conditional expressions
− When using multiple AND conditions, write the condition most likely to be false
first
− When using multiple OR conditions, put the condition most likely to be true first
− Whenever possible, try to avoid the use of NOT logical operator

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 34
whole or in part.
Conditional Expressions (3 of 3)

Conditional Criteria
Table 11.6
OPERAND1 CONDITIONAL OPERATOR OPERAND2
P_PRICE > 10.00

V_STATE = FL

V_CONTACT LIKE Smith%

P_QOH > P_MIN * 1.10

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 35
whole or in part.
 Query Formulation

• To formulate a query, you could normally follow these steps:

− 1. Identify what columns and computations are required
− 2. Identify source tables
− 3. Determine how to join the tables
− 4. Determine what selection criteria are needed
− 5. Determine the order in which to display the output

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 36
whole or in part.
 DBMS Performance Tuning (1 of 4)

• DBMS performance tuning includes managing the DBMS processes in primary

memory and managing the structures in physical storage

• DBMS performance tuning at the server end focuses on setting parameters used for:
− Data cache
− SQL cache
− Sort cache
− Optimizer mode

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 37
whole or in part.
 DBMS Performance Tuning (2 of 4)

• In-memory database systems are optimized to store large portions of the

database in primary storage

• These systems are becoming popular due to the following:

− Increasing performance demands of modern database applications
− Diminishing costs
− Technology advances of components

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 38
whole or in part.
 DBMS Performance Tuning (3 of 4)

• The following are general recommendations for physical storage of databases:

− Use I/O accelerators
− Use RAID (Redundant Array of Independent Disks) to provide a balance between
performance improvement and fault tolerance
− Minimize disk contention
− Put high-usage tables in their own table spaces
− Assign separate data files in separate storage volumes for indexes, system, and
high-usage tables

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 39
whole or in part.
 DBMS Performance Tuning (4 of 4)

• The following are general recommendations for physical storage of databases

(continued):
− Take advantage of the various table storage organizations in the database
 An index-organized table (or clustered index table) is a table that
stores the end-user data and the index data in consecutive locations on
permanent storage
− Partition tables based on usage
− Apply denormalized tables where appropriate
− Store computed and aggregate attributes in tables

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 40
whole or in part.
 Knowledge Check Activity 11-3

• What does RAID stand for?

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 41
whole or in part.
 Knowledge Check Activity 11-3:
Answer
• What does RAID stand for?
• Answer: RAID is the acronym for Redundant Array of Independent
Disks. RAID is used to provide balance between performance and fault
tolerance. RAID systems use multiple disks to create virtual disks
(storage volumes) formed by several individual disks.

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 42
whole or in part.
 Summary

Now that the lesson has ended, you should be able to:

1. Identify the procedures involved in database performance tuning

2. Describe how a DBMS processes SQL queries in each of its three phases

3. Explain the role of indexes in speeding up data access

4. Differentiate between a rule-based optimizer and a cost-based optimizer

5. Describe some common practices used to write efficient SQL code

6. Explain how to formulate queries and tune the DBMS for optimal performance

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in 43
whole or in part.