A set of activities and procedures designed to reduce the response time of the database system.
Database performance tuning
(T/F) The goal of database performance is to execute queries as fast as possible.
True
(T/F) Good database performance starts with good database design.
True
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
Client side
SQL performance tuning
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
Server side
DBMS performance tuning
All data in a database are stored in
data files
The data files can automatically expand in predefined increments
extends
Logical groupingof several data files that store data with similar characteristics
table space or file group
Shared, reserved memory area that stores the most recently accesseddata blocks in RAM
data cache or buffer cache
Shared, reserved memory area that stores the most recently executed SQL statements or PL/SQL procedures, including triggers and functions.
SQL cache, or procedure cache
(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)
True
A low-level (read or write) data access operation to and from computer devices, such as memory, hard disks, video, and printers.
input/output (I/O) request
Represented by the processes and structures used to manage a database.
DBMS architecture
Listens for clients’ requests and handles the processing of the SQL requests to other DBMS processes.
Listener
The DBMS creates a user process to manage each client session.
user
Process organizes the concurrent execution of SQL requests
Scheduler
This process manages all locks placed on database objects, including disk pages.
Lock manager
Analyzes SQL queries and finds the most efficient way to access the data.
Query Optimizer
Refers to a number of measurements about database objects, such as number of processors used, processor speed, and temporary space available.
database statistics
DBMS processes a query in three phases:
Parsing
Execution
Fetching
The DBMS parses the SQL query and chooses the most efficient access/execution plan.
Parsing
The DBMS executes the SQL query using the chosen execution plan.
Execution
The DBMS fetches the data and sends the result set back to the client.
Fetching
Result of parsing a SQL statement.
access plan
A delay introduced in the processing of an I/O operation that causes the overall system to slow down.
query processing bottleneck
Five components that typically cause bottlenecks:
CPU
RAM
Hard disk
Network
Application code
Crucial in speeding up data access because they facilitate searching, sorting, and using aggregate functionsand even join operations.
Indexes
Number of different values a column could possibly have.
Data sparsity
Indexes implement using:
Hash index
B-tree index
Bitmap index
Uses preset rules and points to determine the best approach to execute a query.
Rule-based optimizer
Uses sophisticated algorithms based on the statistics about the objects being accessed to determine the best approach to execute a query.
Cost-based optimizer
Special instructions for the optimizer that are embedded inside the SQL command text.
Optimizer hints
Measure of how likely an index will be used in query processing.
Index selectivity
DBMS performance tuning at the server end focuses on setting the parameters used for:
Data cache
SQL cache
Sort cache
Optimizer mode
Use ______ to provide balance between performance and fault tolerance.
RAID (redundant array of independent disks)
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.
Tables
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.
Indexes
Logical and physical disk block size, location and size of data files, and number ofextends per data fileIf
Environment Resources
Optimized query results are always the same as the original query
Fully equivalent
Optimized query will almost always execute faster than the original query.
More efficient
Breaking down the query into smaller units and transforming the original SQL query into a slightly different version of the original SQL code.
SQL parsing phase
In this phase, all I/O operations indicated in the access plan are executed.
SQL Execution Phase
After the parsing and execution phases are completed, all rows that match the specified conditions are retrieved,sorted, grouped, and/or aggregated.
SQL Fetching Phase
More efficient than a full table scan because the index data are preordered and the amount of data is usually much smaller.
index scan
Based on an ordered list of hash values.
Hash index
Ordered data structure organized as an upside-down tree.
B-tree index
Uses a bit array (0s and 1s) to represent the existence of a value or condition.
Bitmap index
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.
Query optimization
Evaluated from the client, the goal is to illustrate some common practices used to write efficient SQL code.
SQL performance tuning
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.
conditional expression
DBMS performance tuning at the server end focuses on setting the parameters used for:
Data cache
SQL cache
Sort cache
Optimizer mode
This is used to store the data dictionary tables.
System table space
This is used to store end-user data.
User data table space
This is used to store indexes
Index table space
This is used as a temporary storage area for merge, sort, or set aggregate operations.
Temporary table space
This is used for transaction-recovery purposes.
Rollback segment table space
A table that stores the end-user data and the index data in consecutive locations on permanent storage.
index organized table or (clustered index table)
Author
FelipeJung
ID
314065
Card Set
Database Performance Tuning and Query Optimization