Database Performance Tuning and Query Optimization

A set of activities and procedures designed to reduce the response time of the database system.

(T/F) The goal of database performance is to execute queries as fast as possible.

(T/F) Good database performance starts with good database design.

Generate a SQL query that returns the correct answer in the least amount of time, using the minimum amount of resources at the server end.

Client side

The DBMS environment must be properly configured to respond to clients’ requests in the fastest way possible, while making optimum use of existing resources.

Server side

All data in a database are stored in

The data files can automatically expand in predefined increments

Logical groupingof several data files that store data with similar characteristics

Shared, reserved memory area that stores the most recently accesseddata blocks in RAM

Shared, reserved memory area that stores the most recently executed SQL statements or PL/SQL procedures, including triggers and functions.

(T/F) To work with the data, the DBMS must retrieve the data from permanent storage (data files in which the data are stored) and place it in RAM (data cache)

A low-level (read or write) data access operation to and from computer devices, such as memory, hard disks, video, and printers.

Represented by the processes and structures used to manage a database.

Listens for clients’ requests and handles the processing of the SQL requests to other DBMS processes.

The DBMS creates a user process to manage each client session.

Process organizes the concurrent execution of SQL requests

This process manages all locks placed on database objects, including disk pages.

Analyzes SQL queries and finds the most efficient way to access the data.

Refers to a number of measurements about database objects, such as number of processors used, processor speed, and temporary space available.

DBMS processes a query in three phases:

The DBMS parses the SQL query and chooses the most efficient access/execution plan.

The DBMS executes the SQL query using the chosen execution plan.

The DBMS fetches the data and sends the result set back to the client.

Result of parsing a SQL statement.

A delay introduced in the processing of an I/O operation that causes the overall system to slow down.

Five components that typically cause bottlenecks:

Crucial in speeding up data access because they facilitate searching, sorting, and using aggregate functionsand even join operations.

Number of different values a column could possibly have.

Indexes implement using:

Uses preset rules and points to determine the best approach to execute a query.

Uses sophisticated algorithms based on the statistics about the objects being accessed to determine the best approach to execute a query.

Special instructions for the optimizer that are embedded inside the SQL command text.

Measure of how likely an index will be used in query processing.

DBMS performance tuning at the server end focuses on setting the parameters used for:

Use ______ to provide balance between performance and fault tolerance.

Number of rows, number of disk blocks used, row length, number of columns in each row, number of distinct values in each column, maximum value in each column, minimum value in each column, and columns that have indexes.

Number and name of columns in the index key, number of key values in the index,number of distinct key values in the index key, histogram of key values in an index, and number of disk pages used by the index.

Logical and physical disk block size, location and size of data files, and number ofextends per data fileIf

Optimized query results are always the same as the original query

Optimized query will almost always execute faster than the original query.

Breaking down the query into smaller units and transforming the original SQL query into a slightly different version of the original SQL code.

In this phase, all I/O operations indicated in the access plan are executed.

After the parsing and execution phases are completed, all rows that match the specified conditions are retrieved,sorted, grouped, and/or aggregated.

More efficient than a full table scan because the index data are preordered and the amount of data is usually much smaller.

Based on an ordered list of hash values.

Ordered data structure organized as an upside-down tree.

Uses a bit array (0s and 1s) to represent the existence of a value or condition.

The central activity during the parsing phase in query processing. In this phase, the DBMS must choose what indexes to use, how to perform join operations, which table to use first, and so on.

Evaluated from the client, the goal is to illustrate some common practices used to write efficient SQL code.

Normally placed within the WHERE or HAVING clauses of a SQL statement. Also known as conditional criteria, a conditional expression restricts the output of a query to only the rows matching the conditional criteria.

DBMS performance tuning at the server end focuses on setting the parameters used for:

This is used to store the data dictionary tables.

This is used to store end-user data.

This is used to store indexes

This is used as a temporary storage area for merge, sort, or set aggregate operations.

This is used for transaction-recovery purposes.

A table that stores the end-user data and the index data in consecutive locations on permanent storage.