Database Performance Tuning and Query Optimization (a) Week 3 1 (MO2, Chapter 11-1 to 11-2)

Question 1

Database Performance-Tuning
Concepts, and sequence.

Answer 1

One of the main functions of a database system is to provide
timely answers.
* End users interact with the DBMS through the use of queries to
generate information, using the following sequence:
1. End-user (client-end) application generates a query
2. Query is sent to the DBMS (server end)
3. DBMS (server end) executes the query
4. DBMS sends the resulting data set to the end-user
(client-end) application

Question 2

Database performance tuning concepts

Answer 2

• Goal of database performance is to execute queries as fast as
  possible
• Database performance tuning: set of activities and procedures that
  reduce response time of database system
• Fine-tuning the performance of a system requires a holistic
  approach
• All factors must operate at optimum level with minimal bottlenecks

Question 3

Performance tuning: Client and Server.

Answer 3

Client side: SQL performance tuning
* Generate SQL query that returns correct answer in least amount of
time
* Using minimum amount of resources at server
* Server side: DBMS performance tuning
* DBMS environment is configured to respond to clients’ requests as fast
as possible
* While making optimum use of existing resour

Question 4

DBMS Architecture

Question 5

Data files

Answer 5

All data is a database is stored in datafiles

Question 6

Extends

Answer 6

IN DBMS Enviroment refers to the ability of data files to expand in size automatically using predefined increments

Question 7

Table space or file group:

Answer 7

logical storage place used to group related data

Question 8

Data cache or buffer cache

Answer 8

shared, reserved memory area that stores the most recently accessed data blocks in RAM

Question 9

SQL cache or procedure cache

Answer 9

is a shared, resereved memory area that stores the most recently executed SQL statements

Question 10

An input output request

Answer 10

Low level data access operation that reads or writes data to and from computer devices

Question 11

Database query optimization modes, what are the two principles of the algorithms proposed for query optimization

Answer 11

• Optimum order to achieve the fastest execution time
• Sites to be accessed to minimize communication costs

Question 12

What is the evaluation based on?

Answer 12

• Evaluated based on:
• Operation mode
• Timing of its optimization

Question 13

What are the two ways optimization modes can be classified into?

Answer 13

Automatic query optimization: Query finds most cost efective path without user intervention
Manual query optimization: Requires optimization to be selected and scheduled by the end user.

Question 14

Classification based on timing of optimization?

Answer 14

Static query optimization: best optimization strategy is selected when
the query is compiled by the DBMS
* Takes place at compilation time
* Dynamic query optimization: access strategy is dynamically
determined by the DBMS at run time, using the most up-to-date
information about the database
* Takes place at execution time

Question 15

Classification based on type of information used to optimize the
query

Answer 15

Statistically based query optimization algorithm: statistics are used by
the DBMS to determine the best access strategy
* Statistical information is generated by DBMS through:
* Dynamic statistical generation mode
* Manual statistical generation mode
* Rule-based query optimization algorithm: based on a set of
user-defined rules to determine the best query access strategy

Question 16

Database statistics?

Answer 16

• Measurements about database objects; provide a snapshot of
  database characteristics
• Number of processors used
• Processor speed
• Temporary space available

Question 17

Query processing

Answer 17

What happens at the DBMS server end when the clients sql event is received?
1. Parsing
2Execution
3Fetching

Question 18

Parsing

Answer 18

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

Question 19

Execution

Answer 19

DBMS executes the SQL query using the chosen execution plan

Question 20

Fetching

Answer 20

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

Question 21

SQL Parsing phase

Answer 21

Query is broken down into smaller units
* Original SQL query transformed into slightly different version of original
SQL code which is fully equivalent and more efficient
* Query optimizer: analyzes SQL query
* Finds most efficient way to access data

Question 22

Access plans?

Answer 22

• Access plans: result of parsing a SQL statement; contains a
  series of steps the DBMS will use to execute the query and
  return the result set in the most efficient way
• Access plan exists for query in SQL cache: DBMS reuses it
• No access plan: optimizer evaluates various plans and chooses one to
  be placed in SQL cache for use

Question 23

SQL execution phase

Answer 23

All I/O operations indicated in the access plan are executed
* Locks are acquired
* Data are retrieved and placed in data cache
* Transaction management commands are processed

Question 24

SQL fetching phase

Answer 24

• Rows of resulting query result set are returned to client
• DBMS may use temporary table space to store temporary data
• Database server coordinates the movement of the result set
  rows from the server cache to the client cache

Question 25

Query processing bottlenecks

Answer 25

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

Question 26

What are the five components that typically cause bottlenecks

Answer 26

COU
RAM
Hard disk
Network
Application code

Question 27

Indexes

Answer 27

• Rows of resulting query result set are returned to client
• DBMS may use temporary table space to store temporary data
• Database server coordinates the movement of the result set
  rows from the server cache to the client cache

Question 28

Data sparsity

Answer 28

Refers to the number of of different values a column could have.

Question 29

Give the data structures used to implement indexes

Answer 29

• Data structures used to implement indexes:
• Hash indexes;
• B-tree indexes;
• Bitmap indexes.
• DBMS determines best type of index to use

Question 30

Hash indexes

Answer 30

Based on a ordered list of hash values. A hash algorithm is used to create a hash value from a key column. This value points to an entry in a hash table, which in turn points to the actual location is the data row.

Question 31

B-Tree index

Answer 31

ordered data structure, organized as an upside down tree. The index tree is stored seperately from the data The lower level leaves of the index tree contain pointers to the actual data row

Question 32

Bitmap index

Answer 32

Uses a bit array to represent the existance of a value or condition.

Question 33

What are the two optimizer choices?

Answer 33

Rule-based optimizer:uses preset rules and points to
determine the best approach to execute a query:
* Rules assign a “fixed cost” to each SQL operation.
* Cost-based optimizer: uses algorithms based on statistics
about objects being accessed to determine the best approach
to execute a query:
* Optimizer process adds up the processing cost, I/O costs,
and resource costs (RAM and temporary space) to
determine the total cost of a given execution plan.

Question 34

Using Hints to Affect Optimizer
Choices

Answer 34

Optimizer might not choose the best execution plan:
* Makes decisions based on existing statistics; might be old
* Might choose less-efficient decisions.
* Optimizer hints: special instructions for the optimizer.
* Embedded in the SQL command text.

Question 35

Give three optimizer hints and explain

Answer 35

ALL_ROWS Instructs the optimizer to minimize the overall execution time—that is, to minimize the time
needed to return all rows in the query result set. This hint is generally used for batch mode
processes. For example:
SELECT /*+ ALL_ROWS / *
FROM PRODUCT
WHERE P_QOH < 10;
FIRST_ROWS Instructs the optimizer to minimize the time needed to process the first set of rows—that is,
to minimize the time needed to return only the first set of rows in the query result set. This hint is
generally used for interactive mode processes. For example:
SELECT /+ FIRST_ROWS / *
FROM PRODUCT
WHERE P_QOH < 10;
INDEX(name) Forces the optimizer to use the P_QOH_NDX index to process this query. For example:
SELECT /+ INDEX(P_QOH_NDX) */ *
FROM PRODUCT
WHERE P_QOH < 10

Question 36

Index Selectivity

Answer 36

• Measure of the likelihood that an index will be used in query
  processing:
• Indexes are used when a subset of rows from a large table is
  to be selected based on a given condition.
• Cannot always be used to improve performance.

Question 37

• Function-based index:

Answer 37

based on a specific SQL function or
expression