ADBMS Database Performance Tuning and Query Optimization Review

A classification of operation mode where the DBMS finds the most cost-effective access path without user intervention.

• Automatic Query Optimization

• Manual Query Optimization

• Rule-based Optimizer

• Cost-based Optimizer

A classification of operation mode that requires the optimization to be selected and scheduled by the end-user or programmer.

• Automatic Query Optimization

• Manual Query Optimization

• Rule-based Optimizer

• Cost-based Optimizer

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

• Automatic Query Optimization

• Manual Query Optimization

• Rule-based Optimizer

• Cost-based Optimizer

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

• Automatic Query Optimization

• Manual Query Optimization

• Rule-based Optimizer

• Cost-based Optimizer

A Query Processing Phase where the DBMS parses the SQL query and chooses the most efficient access/execution plan.

• PARSING

• EXECUTION

• FETCHING

A Query Processing Phase where the DBMS executes the SQL query using the chosen execution plan.

• PARSING

• EXECUTION

• FETCHING

A Query Processing Phase where the DBMS fetches the data and sends the result set back to the client.

• PARSING

• EXECUTION

• FETCHING

Managing DBMS processes in primary memory and the structures in physical storage.

• DBMS PERFORMANCE TUNING

• SQL PERFORMANCE TUNING

• DATABASE STATISTICS

• DATA SPARSITY

Evaluated from client’s perspective. Majority of performance problems are related to poorly written SQL code.

• DBMS PERFORMANCE TUNING

• SQL PERFORMANCE TUNING

• DATABASE STATISTICS

• DATA SPARSITY

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

• DBMS PERFORMANCE TUNING

• SQL PERFORMANCE TUNING

• DATABASE STATISTICS

• DATA SPARSITY