Week 6 Assignment HSIN

 

Using a word document with a cover sheet –

Complete:

1- Chapter 14 Review Questions 1-11 located at the end of your Database Systems Chapter 11 book. Please use complete sentences and proper grammar. 

1- Chapter 15 Review Questions 1-25 located at the end of your Database Systems Chapter 13 book. Please use complete sentences and proper grammar.

Database Systems: Design,
Implementation, and

Management
Eighth Edition

Chapter 11
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