02010_ch11 - Database Systems Design Implementation and Management Eighth Edition Chapter 11 Database Performance Tuning and Query Optimization

Info iconThis preview shows pages 1–12. Sign up to view the full content.

View Full Document Right Arrow Icon
Database Systems: Design, Implementation, and Management Eighth Edition Chapter 11 Database Performance Tuning and Query Optimization
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
th 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
Background image of page 2
th 3 Objectives (continued) In this chapter, you will learn: (continued) 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
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
th 4 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
Background image of page 4
th 5
Background image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
th 6 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
Background image of page 6
th 7 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
Background image of page 7

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
th 8
Background image of page 8
th 9 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
Background image of page 9

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
th 10 DBMS Architecture (continued) Input/output request : low-level data access operation to/from computer devices 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
Background image of page 10
th 11 Database Statistics Measurements about database objects and available resources Tables Indexes Number of processors used Processor speed Temporary space available
Background image of page 11

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Image of page 12
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 08/02/2009 for the course IST 210 taught by Professor Mcconn,charlotteryoo,jungwoo during the Summer '08 term at Pennsylvania State University, University Park.

Page1 / 50

02010_ch11 - Database Systems Design Implementation and Management Eighth Edition Chapter 11 Database Performance Tuning and Query Optimization

This preview shows document pages 1 - 12. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online