Week 6 Assignment HSIN
Heathersimf
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