Database Performance Tuning and Query Optimization

Database Performance Tuning and Query Optimization

Database Systems, 8th Edition 2

Objectives

• 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 – Some common practices used to write efficient SQL

code – How to formulate queries and tune the DBMS for

optimal performance – Performance tuning in SQL Server 2005

Database Systems, 8th Edition 3

11.1 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

• All factors must operate at optimum level with minimal bottlenecks
• Good database performance starts with good database design

Database Systems, 8th Edition 4

Database Systems, 8th Edition 5

Performance Tuning: Client and Server

• Client side – Generate SQL query that returns correct answer

in least amount of time • Using minimum amount of resources at server

– SQL performance tuning

• Server side – DBMS environment configured to respond to

clients’ requests as fast as possible • Optimum use of existing resources

– DBMS performance tuning

Database Systems, 8th Edition 6

DBMS Architecture

• All data in database are stored in data files • Data files

– Automatically expand in predefined increments known as extends

– Grouped in file groups or table spaces • Table space or file group:

– Logical grouping of several data files that store data with similar characteristics

Database Systems, 8th Edition 7

Basic DBMS architecture

Database Systems, 8th Edition 8

DBMS Architecture (continued)

• Data cache or buffer cache: shared, reserved memory area – Stores most recently accessed data blocks in RAM
• SQL cache or procedure cache: stores most recently executed SQL statements – Also PL/SQL procedures, including triggers and

functions

• DBMS retrieves data from permanent storage and places it in RAM

Database Systems, 8th Edition 9

DBMS Architecture (continued)

• Input/output request: low-level data access operation to/from computer devices, such as memory, hard disks, videos, and printers
• Data cache is faster than data in data files – DBMS does not wait for hard disk to retrieve data
• Majority of performance-tuning activities focus on minimizing I/O operations
• Typical DBMS processes: – Listener, User, Scheduler, Lock manager, Optimizer

Database Systems, 8th Edition 10

Database Statistics

• Measurements about database objects and available resources – Tables, Indexes, Number of processors used,

Processor speed, Temporary space available • Make critical decisions about improving query

processing efficiency • Can be gathered manually by DBA or automatically by

DBMS – UPDATE STATISTICS table_name [index_name] – Auto-Update and Auto-Create Statistics option

• 資料庫屬性 -> 自動更新統計資料 • 資料庫屬性 -> 自動建立統計資料

Database Systems, 8th Edition 11

Database Systems, 8th Edition 12

Ch08: dbcc show_statistics (customer, PK__CUSTOMER__24927208 )

Ch08: dbcc show_statistics (customer, CUS_UI1)

補充 SQL Server 2005

Database Systems, 8th Edition 13

11.2 Query Processing

• DBMS processes queries in three phases – Parsing
• DBMS parses the query and chooses the most efficient access/execution plan

– Execution • DBMS executes the query using chosen

execution plan

– Fetching • DBMS fetches the data and sends the result back

to the client

Database Systems, 8th Edition 14 Query Processing

Database Systems, 8th Edition 15

SQL Parsing Phase

• Break down query into smaller units • Transform 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, 8th Edition 16

SQL Parsing Phase (continued) • Query optimizer analyzes SQL query and finds most

efficient way to access data – Validated for syntax compliance

– Validated against data dictionary • Tables, column names are correct • User has proper access rights

– Analyzed and decomposed into more atomic components

– Optimized through transforming into a fully equivalent but more efficient SQL query

– Prepared for execution by determining the execution or access plan

Database Systems, 8th Edition 17

SQL Parsing Phase (continued)

• Access plans are DBMS-specific – 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 • DBMS checks if access plan already exists for

query in SQL cache • DBMS reuses the access plan to save time • If not, optimizer evaluates various plans

– Chosen plan placed in SQL cache

Database Systems, 8th Edition 18

Database Systems, 8th Edition 19

SQL Execution and Fetching Phase

• All I/O operations indicated in access plan are executed – Locks acquired – Data retrieved and placed in data cache – Transaction management commands processed
• Rows of resulting query result set are returned to client
• DBMS may use temporary table space to store temporary data – The server may send only the first 100 rows of 9000 rows

Database Systems, 8th Edition 20

Query Processing Bottlenecks

• Delay introduced in the processing of an I/O operation that slows the system – CPU

– RAM

– Hard disk

– Network

– Application code

Database Systems, 8th Edition 21

SQL 敘述 輸入完成 後先不要 執行查 詢 , 請按 下工具列 的顯示估 計執行計

劃鈕 ：

Database Systems, 8th Edition 22

11.3 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, 8th Edition 23

Indexes and Query Optimization • Data sparsity: number of different values a column

could possibly have • Indexes implemented using: ( 課本 p. 453)

– Hash indexes

– B-tree indexes: most common index type. Used in tables in which column values repeat a small number of times. The leaves contain pointers to records It is self-balanced.

– Bitmap indexes: 0/1

• DBMSs determine best type of index to use – Ex: CUST_LNAME with B-tree and REGION_CODE with

Bitmap indexes

Database Systems, 8th Edition 24B-tree and bitmap index representation

25

Index Representation for the CUSTOMER table

SELECT CUS_NAME FROM CUSTOMER WHERE CUS_STATE=‘FL’ Requires only 5 accesses to STATE_INDEX, 5 accesses to CUSTOMER

Database Systems, 8th Edition 26

11.4 Optimizer Choices

• Rule-based optimizer – Preset rules and points

– Rules assign a fixed cost to each operation

• Cost-based optimizer – Algorithms based on statistics about objects

being accessed

– Adds up processing cost, I/O costs, resource costs to derive total cost

Example

Database Systems, 8th Edition 27

SELECT P_CODE, P_DESCRIPT, P_PRICE, V_NAME, V_STATE FROM PRODUCT P, VENDOR V WHERE P.V_CODE=V.V_CODE AND V.V_STATE=‘FL’;

• With the following database statistics: – The PRODUCT table has 7000 rows – The VENDOR table has 300 rows – 10 vendors come from Florida – 1000 products come from vendors in Florida

Database Systems, 8th Edition 28

Example

Database Systems, 8th Edition 29

• Assume the PRODUCT table has the index PQOH_NDX in the P_QOH attribute

SELECT MIN(P_QOH) FROM PRODUCT

could be resolved by reading only the first entry in the PQOH_NDX index

Database Systems, 8th Edition 30

Using Hints to Affect Optimizer Choices

• Optimizer might not choose best plan • Makes decisions based on existing statistics

– Statistics may be old – Might choose less efficient decisions

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

Database Systems, 8th Edition 31

Oracle 版本

Database Systems, 8th Edition 32

MS SQL Server 的語法請參考：

http://msdn.microsoft.com/en-us/library/ms187713.aspx

SQL Server Query Hints Example

select o.customerid,companyname from orders as o inner MERGE join customers as c on o.customerid = c.customerid

select o.customerid,companyname from orders as o inner HASH join customers as c on o.customerid = c.customerid

select o.customerid,companyname from orders as o inner LOOP join customers as c on o.customerid = c.customerid

select city, count(*) from customers group by city OPTION (HASH GROUP)

• Database Systems: Design, Implementation, and Management Eighth Edition
• Objectives
• 11.1 Database Performance-Tuning Concepts
• PowerPoint Presentation
• Performance Tuning: Client and Server
• DBMS Architecture
• Slide 7
• DBMS Architecture (continued)
• Slide 9
• Database Statistics
• Slide 11
• Slide 12
• 11.2 Query Processing
• Slide 14
• SQL Parsing Phase
• SQL Parsing Phase (continued)
• Slide 17
• Slide 18
• SQL Execution and Fetching Phase
• Query Processing Bottlenecks
• Slide 21
• 11.3 Indexes and Query Optimization
• Indexes and Query Optimization
• Slide 24
• Slide 25
• 11.4 Optimizer Choices
• Example
• Slide 28
• Slide 29
• Using Hints to Affect Optimizer Choices
• Slide 31
• Slide 32

PLACE THE ORDER WITH US TODAY AND GET A PERFECT SCORE!!!