Transcript Chapter 11

11
Chapter 11
Database Performance Tuning and
Query Optimization
Database Systems:
Design, Implementation, and Management,
Seventh Edition, Rob and Coronel
1
11
In this chapter, you will learn:
• Basic database performance-tuning concepts
• How a DBMS processes SQL queries
• About the importance of indexes in query
processing
• About the types of decisions the query
optimizer has to make
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
2
11
In this chapter, you will learn (continued):
• Some common practices used to write
efficient SQL code
• How to formulate queries and tune the DBMS
for optimal performance
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
3
11
Database Performance-Tuning Concepts
• Goal of database performance is to execute
queries as fast as possible
• Database performance tuning
– Set of activities and procedures designed to
reduce response time of database system
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
4
11
Database Performance-Tuning
Concepts (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
5
11
Database Performance-Tuning
Concepts (continued)
• All factors must be checked to ensure that
each one operates at its optimum level and
has sufficient resources to minimize
occurrence of bottlenecks
• Good database performance starts with good
database design
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
6
11
Performance Tuning:
Client and Server
• Database performance-tuning activities can
be divided into:
– Client side
• Objective is to generate SQL query that returns
correct answer in least amount of time, using
minimum amount of resources at server end
• SQL performance tuning
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
7
11
Performance Tuning:
Client and Server (continued)
• Database performance-tuning activities can
be divided into (continued):
– Server side
• DBMS environment must be properly
configured to respond to clients’ requests in
fastest way possible, while making optimum
use of existing resources
• DBMS performance tuning
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
8
11
DBMS Architecture
• All data in database are stored in data files
• Data files
– Automatically expand in predefined
increments known as extends
– Generally grouped in file groups of table
spaces
• Table space or file group is logical grouping of
several data files that store data with similar
characteristics
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
9
11
DBMS Architecture (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
10
11
DBMS Architecture (continued)
• DBMS retrieve data from permanent storage and
place it in RAM
• Data cache or buffer cache is shared, reserved
memory area that stores most recently accessed
data blocks in RAM
• SQL cache or procedure cache is shared,
reserved memory area that stores most recently
executed SQL statements or PL/SQL
procedures, including triggers and functions
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
11
11
DBMS Architecture (continued)
• An input/output request is low-level (read or
write) data access operation to/from computer
devices
• Working with data in data cache is many times
faster than working with data in data files
because DBMS doesn’t have to wait for hard disk
to retrieve data
• Majority of performance-tuning activities focus on
minimizing number of I/O operations
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
12
11
DBMS Architecture (continued)
• Listener
• User
• Scheduler
• Lock manager
• Optimizer
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
13
11
Database Statistics
• Refers to number of measurements about
database objects and available resources
–
–
–
–
–
Tables
Indexes
Number of processors used
Processor speed
Temporary space available
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
14
11
Database Statistics (continued)
• Make critical decisions about improving query
processing efficiency
• Can be gathered manually by DBA or
automatically by DBMS
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
15
11
Database Statistics (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
16
11
Query Processing
• DBMS processes queries in three phases
– Parsing
– Execution
– Fetching
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
17
11
SQL Parsing Phase
• Breaking down (parsing) query into smaller
units and transforming original SQL query into
slightly different version of original SQL code
– Fully equivalent
• Optimized query results are always the same
as original query
– More efficient
• Optimized query will almost always execute
faster than original query
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
18
11
SQL Parsing Phase (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
19
11
SQL Parsing Phase (continued)
• Query optimizer analyzes SQL query and
finds most efficient way to access data
• Access plans are DBMS-specific and
translate client’s SQL query into series of
complex I/O operations required to read the
data from the physical data files and generate
result set
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
20
11
SQL Parsing Phase (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
21
11
SQL Execution Phase
• All I/O operations indicated in access plan are
executed
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
22
11
SQL Fetching Phase
• Rows of resulting query result set are
returned to client
• DBMS may use temporary table space to
store temporary data
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
23
11
Indexes and Query Optimization
• Indexes
– Crucial in speeding up data access
– Facilitate searching, sorting, and using
aggregate functions as well as join operations
– Ordered set of values that contains index key
and pointers
• More efficient to use index to access table
than to scan all rows in table sequentially
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
24
11
Indexes and Query Optimization
(continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
25
11
Optimizer Choices
• Rule-based optimizer
– Uses set of preset rules and points to
determine best approach to execute query
• Cost-based optimizer
– Uses sophisticated algorithms based on
statistics about objects being accessed to
determine best approach to execute query
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
26
11
Optimizer Choices (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
27
11
Optimizer Choices (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
28
11
Using Hints to Affect Optimizer Choices
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
29
11
SQL Performance Tuning
• Evaluated from client perspective
– Most current-generation relational DBMSs
perform automatic query optimization at the
server end
– Most SQL performance optimization
techniques are DBMS-specific and are rarely
portable
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
30
11
Index Selectivity
• Indexes are likely used when:
– Indexed column appears by itself in search
criteria of WHERE or HAVING clause
– Indexed column appears by itself in GROUP
BY or ORDER BY clause
– MAX or MIN function is applied to indexed
column
– Data sparsity on indexed column is high
• Measure of how likely an index will be used
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
31
11
Index Selectivity (continued)
• General guidelines for creating and using
indexes:
– Create indexes for each attribute in WHERE,
HAVING, ORDER BY, or GROUP BY clause
– Do not use in small tables or tables with low
sparsity
– Declare primary and foreign keys so optimizer
can use indexes in join operations
– Declare indexes in join columns other than
PK/FK
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
32
11
Conditional Expressions
• Normally expressed within WHERE or
HAVING clauses of SQL statement
• Restricts output of query to only rows
matching conditional criteria
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
33
11
Conditional Expressions (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
34
11
Query Formulation
• Identify what columns and computations are
required
• Identify source tables
• Determine how to join tables
• Determine what selection criteria is needed
• Determine in what order to display output
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
35
11
DBMS Performance Tuning
• Includes global tasks such as managing
DBMS processes in primary memory and
structures in physical storage
• Includes applying several practices examined
in previous section
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
36
11
DBMS Performance Tuning (continued)
• DBMS performance tuning at server end
focuses on setting parameters used for:
–
–
–
–
Data cache
SQL cache
Sort cache
Optimizer mode
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
37
11
DBMS Performance Tuning (continued)
• Some general recommendations for creation
of databases:
– Use RAID (Redundant Array of Independent
Disks) to provide balance between
performance and fault tolerance
– Minimize disk contention
– Put high-usage tables in their own table
spaces
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
38
11
DBMS Performance Tuning (continued)
• Some general recommendations for creation
of databases (continued):
– Assign separate data files in separate storage
volumes for indexes, system, and high-usage
tables
– Partition tables based on usage
– Use denormalized tables where appropriate
– Store computed and aggregate attributes in
tables
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
39
11
DBMS Performance Tuning (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
40
11
DBMS Performance Tuning (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
41
11
DBMS Performance Tuning (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
42
11
DBMS Performance Tuning (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
43
11
DBMS Performance Tuning (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
44
11
DBMS Performance Tuning (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
45
11
DBMS Performance Tuning (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
46
11
DBMS Performance Tuning (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
47
11
DBMS Performance Tuning (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
48
11
DBMS Performance Tuning (continued)
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
49
11
Summary
• Database performance tuning
– Refers to set of activities and procedures
designed to ensure that end-user query is
processed by DBMS in minimum amount of
time
• SQL performance tuning
– Refers to activities on client side designed to
generate SQL code that returns correct
answer in least amount of time, using
minimum amount of resources at server end
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
50
11
Summary (continued)
• DBMS performance tuning refers to activities
on server side oriented to ensure that DBMS
is properly configured to respond to clients’
requests in fastest way possible while making
optimum use of existing resources
• DBMS architecture is represented by many
processes and structures (in memory and in
permanent storage) used to manage a
database
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
51
11
Summary (continued)
• Database statistics refers to a number of
measurements gathered by the DBMS that
describe snapshot of database objects’
characteristics
• DBMS processes queries in three phases:
Parsing, Execution and Fetching
• Indexes are crucial in process that speeds up
data access
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
52
11
Summary (continued)
• During query optimization, DBMS must
choose what indexes to use, how to perform
join operations, what table to use first, and so
on
• Hints are used to change optimizer mode for
current SQL statement
• SQL performance tuning deals with writing
queries that make good use of statistics
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
53
11
Summary (continued)
• Query formulation deals with how to translate
business questions into specific SQL code to
generate required results
• DBMS performance tuning includes tasks
such as managing DBMS processes in
primary memory and structures in physical
storage
Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
54