CH 11

SQL

Database Performance-Tuning Concepts
Goal of database performance is to execute queries as fast as possible

Database performance tuning

Set of activities and procedures designed to reduce response time of database system
All factors must operate at optimum level with minimal bottlenecks
Good database performance starts with good database design
Database Performance-tuning
Activities and procedures designed to reduce the response time of database systems.

Goal: Execute queries as fast as possible; Operate at optimum level with minimal bottlenecks
Good database performance starts with good database design

Database Performance Tuning
Client
Server – Hardware, Software
Architecture - Network
Database Processor – Optimizer, Database Statistics
Query Processing
SQL Statements
Indexes
Caching
Guidelines for better performance
DBMS Architecture
All data in database are stored in data files
Data files
Automatically expand in predefined increments known as extends
Grouped in file groups or table spaces
Table space or file group
Logical grouping of several data files that store data with similar characteristics
Performance Tuning: 
Client and Server
Client side – Examples?
Examples:
Connection time and connection speed
SQL Query result set (size)
SQL Query Processing – using minimal amount of server resources
Solution: SQL Query performance tuning, Network Performance
Server side – Examples?
Examples:
Server processing capabilities
Database processing of requests
DBMS – optimal use of resources
Solution: DBMS performance tuning, Server Capacity Planning
DBMS Architecture
DBMS Architecture
Data cache or buffer cache: shared, reserved memory area. Stores most recently accessed data blocks in RAM
SQL cache or procedure cache: stores most recently executed SQL statements
DBMS retrieves data from permanent storage and places it in RAM.
Typical DBMS processes:
Listener, user, scheduler, lock manager, optimizer

#Performance Tuning:
DBMS Architecture
All data in database are stored in data files.
Data files automatically expand in predefined increments known as extends. Grouped in file groups or table spaces.
Input/output request: low-level data access operation to/from computer devices
Data cache is faster than data in data files. DBMS does not wait for hard disk to retrieve data.
Majority of performance-tuning activities focus on minimizing I/O operations

#Performance Tuning:
DB Processing
Query Optimizer
Database Statistics
Query Processing
Indexing

Database Query Optimizer

The optimizer is built-in software that determines the most efficient way to execute a SQL statement.
Cost-based query optimization algorithm
Uses statistical information about the database
Algorithms based on statistics about objects being accessed
Adds up processing cost, I/O costs, resource costs to derive total cost
Rule-based query optimization algorithm
Based on a set of user-defined rules to determine the best query access strategy
Rules assign a fixed cost to each operation

What does Oracle use? CBO

Database Query optimizer

The database can execute a SQL statement in multiple ways
full table scans
index scans
nested loops
hash joins
The optimizer considers many factors related to the objects and the conditions in the query when determining an execution plan.
This determination is an important step in SQL processing and can greatly affect execution time.

r
When the user submits a SQL statement for execution, the optimizer performs the following steps:
The optimizer generates a set of potential plans for the SQL statement.
The optimizer estimates the cost of each plan based on statistics in the data dictionary.
The cost is an estimated value proportional to the expected resource use needed to execute the statement with a particular plan. The optimizer calculates the cost of access paths and join orders based on the estimated computer resources, which includes I/O, CPU, and memory.
The optimizer compares the plans and chooses the plan with the lowest cost.
The output from the optimizer is an execution plan  that describes the optimum method of execution.
Database Query optimizer
Database Statistics

Measurements about database objects and available resources:
Tables
Indexes
Number of processors used
Processor speed
Temporary space available
Make critical decisions about improving query processing efficiency
Can be gathered manually by DBA or automatically by DBMS

Query Processing

DBMS processes queries in three phases
Parsing
DBMS parses the query and chooses the most efficient access/execution plan
Execution
DBMS executes the query using chosen execution plan
Fetching
DBMS fetches the data and sends the result back to the client

Database statistics
Query Processing

SQL Parsing Phase

Break down query into smaller units
Transform original SQL query into slightly different version of original SQL code
Query optimizer analyzes SQL query and finds most efficient way to access data
Validated for syntax compliance
Validated against data dictionary (Tables and column names are correct, User has proper access rights)
Analyzed and decomposed into components
Optimized
Prepared for execution

SQL Parsing Phase
Access plans are DBMS-specific
Translate client’s SQL query into a series of complex I/O operations
Required to read the data from the physical data files and generate result set
DBMS checks if access plan already exists for query in SQL cache
DBMS reuses the access plan to save time
If not, optimizer evaluates various plans and chosen plan is placed in SQL cache.

Data Access Paths

Full table scan used even when there are indexes.
Why is a full table scan faster for accessing large amounts of data?
Full table scans are cheaper than index range scans when accessing a large fraction of the blocks in a table. Full table scans can use larger I/O calls, and making fewer large I/O calls is cheaper than making many smaller calls.
Rowid scan: The rowid of a row specifies the data file and data block containing the row and the location of the row in that block. Locating a row by specifying its rowid is the fastest way to retrieve a single row, because the exact location of the row in the database is specified.
Index scan: More efficient to use index to access table than to scan all rows in table sequentially

Query Processing Bottlenecks

Delay introduced in the processing of an I/O operation that slows the system
CPU
RAM
Hard disk
Network
Application code
SQL Execution Phase & Fetching Phase
All I/O operations indicated in access plan are executed
Locks acquired
Data retrieved and placed in data cache
Transaction management commands processed
Rows of resulting query result set are returned to client
DBMS may use temporary table space to store temporary data

Indexes and Query Optimization

Indexes
Crucial in speeding up data access
Facilitate searching, sorting, and using aggregate functions as well as join operations
Ordered set of values that contains index key and pointers
Row is retrieved by traversing the index, using the indexed column values specified by the statement. An index scan retrieves data from an index based on the value of one or more columns in the index. If the statement accesses only columns of the index, then Oracle Database reads the indexed column values directly from the index, rather than from the table.

Indexes and Query Optimization
Data sparsity: number of different values a column could possibly have
Indexes implemented using:
Hash indexes
B-tree indexes
Bitmap indexes
DBMSs determine best type of index to use

#Using Hints to Affect 
Optimizer Choices
Optimizer might not choose best plan
Makes decisions based on existing statistics
Statistics may be old
Might choose less-efficient decisions
Optimizer hints: special instructions for the optimizer embedded in the SQL command text

SQL Performance Tuning

Evaluated from client perspective
Most current relational DBMSs perform automatic query optimization at the server end
Most SQL performance optimization techniques are DBMS-specific
Rarely portable
Majority of performance problems are related to poorly written SQL code
Carefully written query usually outperforms a poorly written query
Index Selectivity
Indexes are used when:
Indexed column appears by itself in search criteria of WHERE or HAVING clause
Indexed column appears by itself in GROUP BY or ORDER BY clause
MAX or MIN function is applied to indexed column
Data sparsity on indexed column is high
Measure of how likely an index will be used
General guidelines for indexes:
Create indexes for each attribute in WHERE, HAVING, ORDER BY, or GROUP BY clause
Do not use in small tables or tables with low sparsity
Declare primary and foreign keys so optimizer can use indexes in join operations
Declare indexes in join columns other than PK/FK

Conditional Expressions

Normally expressed within WHERE or HAVING clauses of SQL statement
Restricts output of query to only rows matching conditional criteria
Common practices for efficient SQL:
Use simple columns or literals in conditionals
P_PRICE > 10 is faster than P_QOH > P_MIN * 1.10
Numeric field comparisons are faster
Equality comparisons are faster than inequality
DB can do a direct search when using =
NULL and LIKE are the slowest of all
Transform conditional expressions to use literals
P_PRICE – 10 instead use P_PRICE = 17

Conditional Expressions (cont’d.)
Write equality conditions first
AND: use condition most likely to be false first
OR: use condition most likely to be true first
Avoid NOT
NOT(P_PRICE > 10.00) instead P_PRICE <= 10.00

SQL Performance Tips

Avoid correlated subqueries
Avoid foreign key constriants
Drop indexes before loading data
Pull only the columns you need (avoid select *)
Avoid outer joins
Avoid calculated fields in Join and Where clauses

http://web.synametrics.com/top10performancetips.htm

DBMS Performance Tuning
Includes managing DBMS processes in primary memory and structures in physical storage
DBMS performance tuning at server end focuses on setting parameters used for the:
Data cache
SQL cache
Sort cache
Optimizer mode
DBMS Performance Tuning (cont’d.)
Some general recommendations for creation of databases:
Use RAID (Redundant Array of Independent Disks) to provide balance between performance and fault tolerance
Minimize disk contention
Put high-usage tables in their own table spaces
Assign separate data files in separate storage volumes for indexes, system, and high-usage tables
DBMS Performance Tuning (cont’d.)
Take advantage of table storage organizations in database
Partition tables based on usage
Use denormalized tables where appropriate
Store computed and aggregate attributes in tables
DBMS Performance Tuning
Includes managing DBMS processes in primary memory and structures in physical storage
DBMS performance tuning at server end focuses on setting parameters used for the:
Data cache
SQL cache
Sort cache
Optimizer mode
DBMS Performance Tuning
Some general recommendations for creation of databases:
Use RAID (Redundant Array of Independent Disks) to provide balance between performance and fault tolerance
Minimize disk contention
Put high-usage tables in their own table spaces
Assign separate data files in separate storage volumes for indexes, system, and high-usage tablesTake
Take advantage of table storage organizations in database
Partition tables based on usage
Use denormalized tables where appropriate
Store computed and aggregate attributes in tables

Query Optimization Example

Example illustrates how query optimizer works
Based on QOVENDOR and QOPRODUCT tables
Uses Oracle SQL*Plus
SQL Commands for DB Performance
Create or Update Statistics
ANALYZE TABLE employee COMPUTE STATISTICS;

Explain plan

EXPLAIN PLAN FOR (your query);
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Create Index
CREATE INDEX empnum_NDX1 ON employee(emp_num);

Interpreting an Explain Plan
http://www.morganslibrary.org/pres/lad10/peoug_xplan.pdf
http://docs.oracle.com/cd/B28359_01/server.111/b28274/ex_plan.htm
http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-explain-the-explain-plan-052011-393674.pdf
Explain plan video - https://www.youtube.com/watch?v=SwKIqRV5l3k; https://www.youtube.com/watch?v=bcVbR_FPWKk
Autotrace - http://www.dba-oracle.com/t_OracleAutotrace.htm

Database Statistics
DBA_TABLES
DBA_OBJECT_TABLES
DBA_TAB_STATISTICS
DBA_TAB_COL_STATISTICS
DBA_TAB_HISTOGRAMS
DBA_INDEXES
DBA_IND_STATISTICS
DBA_CLUSTERS
DBA_TAB_PARTITIONS
DBA_TAB_SUBPARTITIONS
DBA_IND_PARTITIONS
DBA_IND_SUBPARTITIONS
DBA_PART_COL_STATISTICS
DBA_PART_HISTOGRAMS
DBA_SUBPART_COL_STATISTICS
DBA_SUBPART_HISTOGRAMS