Database tuning

Question 1

What is Database tuning ?

Answer 1

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

Question 2

Factors determines the performance

Answer 2

CPU processing power
Available primary memory (RAM)
Input / Output

Question 3

Data files and Data space

Answer 3

Data files - all data in a database are stored in data files, while data space is a logical grouping of data files with similar characteristics

Question 4

Data cache or buffer cache and SQL cache or procedure cache

Answer 4

Data cache - reserved memory area for the most recently accessed data blocks in RAM
SQL cache - shared,reserved memory for the recently executed SQL statement or procedure or functions

Question 5

IO request

Answer 5

low - level read or write data access operation from and to computer devices

Question 6

Database statistics

Answer 6

number of measurements of database objects like no. of processors used, processor speed and temp space available.

Question 7

Different factors of database statistics

Answer 7

1. Table - no. of tables, mo. of disk blocks uesd, row length, no. of columns in each row, max and min of each column
2. Indexes - no and name of columns in index key and etc
3. Environmental Resources - logical and physical disk block size, location of data blocks

Question 8

3 steps of query processing

Answer 8

Parsing, Execution and Fetching

Question 9

Parsing of query processing

Answer 9

• Breaking down and transform the original query into fully equivalent and more efficient
• performed by the query optimizer, which analyze the SQL query and find the most efficient way to ACCESS THE DATA

Question 10

Step involved in parsing

Answer 10

1. Syntax check
2. Naming check
3. Access right check
4. Decompose and analyze
5. Generate access plan
6. Store access plan in the SQL cache

Question 11

QUery processing bottlenecks and factors that cause it

Answer 11

A delay introduced in the processing of an IO operation that cause delay in the overall system to slow down.
5 factors that causes it
1. CPU 2. RAM 3. Hard disk 4. Network and 5. Application code

Question 12

3 data structures to implement data index

Answer 12

1. Hash index - based on an ordered list of hash values
2. B-tree index - ordered data structure organized as an upside - down tree (commonly used)
3. Bitmap index - uses a bit array to represent the existence of a value or condition (used in data warehousing)

Question 13

Two modes of query optimizer

Answer 13

1. Rule - based: uses preset rules and points to determine the best approach
2. Cost - based: use sophisticated approach based on statistics about the object being accessed

Question 14

Optimizer hints and its types

Answer 14

Special instructions for the optimizer embedded in the sql statements
ALL_ROWS, FIRST_ROWS and index(name)