Transcript SQL Tuning Training

SQL Performance and Optimization



SQL Overview
Performance Tuning Process
SQL-Tuning
–
–
–
–
–
–
EXPLAIN PLANs
Tuning Tools
Optimizing Table Scans
Optimizing Indexes
Optimizing Joins and Subqueries
Optimizing Sorting and Grouping
SQL Overview
–
–
–
–
–
Everything is in Tables
SQL is Non-procedural
Integrity
Normalization
Types of SQL Statements
• Set Operations
• Aggregates and Vector Aggregates
• Joins
• Subqueries
– Views
– Indexes
– How SQL Queries Are Processed
Everything is in Tables
Relations are composed of tuples and attributes. =
Files are composed of records and fields. =
Tables are composed of rows and columns.
 Each row in a table describes one occurrence of an entity.
Each column describes one characteristic, or attribute, of
the entity. A set of related tables forms a database.
 A database is even managed and secured through tables
(often referred to as user tables and system tables).
SQL is Non-Procedural


SQL was designed specifically to be a language
that describes what data is wanted, but not how to
physically get at that data. That is left up to the
database server.
In order to do SQL-tuning, one must understand
that the computer must still use sequential,
procedural steps to get that data.
Integrity

Entity Integrity
– To do with database design. Specifies that no primary
key be allowed to have a null value.

Relational Integrity
– Means pieces of information repeated in more than one
table are consistent.

Data Integrity
– To do with transaction control, maintaining the ACID
properties of data, ensuring that data doesn’t get lost or
corrupted.
Normalization


Means protection of data integrity by avoiding
duplicate data.
Five normal forms
•
•
•
•
Each row and column intersection has one and only one value.
Every non-key column must depend on the entire primary key.
No non-key column must depend on another non-key column.
No one-to-many relationship between primary key columns
and non-key columns.
• Breaks tables into the smallest pieces possible in order to
eliminate all redundancy.
– Most designers stick with 3rd or 4th normal form.
Types of SQL Statements

DDL - Data definition language
– CREATE TABLE/INDEX/VIEW, etc.

DML - Data manipulation language
– UPDATE/INSERT/DELETE

Data administration or data control statements
– ALTER SESSION, GRANT ROLE, etc.

Query
– SELECT. Sets, Aggregates, Joins and Subqueries
Set Operations

UNION
– Combines similar result sets from two tables,
eliminating duplicate rows.

UNION ALL
– Does not eliminate duplicates, does no sorting

MINUS
– Returns all rows in the first result set which are not in
the second set.

INTERSECT
– Returns only the rows which appear in both sets.
Aggregate Operations


Scalar Aggregates allow summary information to
be generated: AVG, SUM, COUNT, MAX, MIN,
STDDEV
When used with GROUP BY and HAVING, they
become Vector Aggregates
Joins

Inner Join
– Most common, like service order to component, joined on service order number.

Theta Join
– Uses other than = to join, like >, BETWEEN, and !=

Outer Join
– Allows rows from the table being outer-joined to be included in the result even if
they have no match in the first table or result set.

“Anti-Join”
– Gets all rows from a table which no not have a match in the first table or result set.

Self Join
– A table is joined to itself, must use aliases to work properly.
Subqueries



A SQL statement within another. May occur in
SELECT, UPDATE, DELETE or INSERT.
May make use of IN, EXISTS or regular
comparison operators like = and >.
A Correlated Subquery is one in which the
subquery refers to values in the parent query.
– Can be used where a join cannot, like in UPDATE,
INSERT and DELETE.
Views




Created with a SELECT statement, sometimes
highly complex.
Is not a table, but a “stored query” or “virtual
table”
Used to focus, simplify and customize user’s
perception of the database.
May also be used to provide security mechanism
around table access.
Indexes





Just like an index in a book: a key value (like “Battle of
Waterloo”) associated with the physical location (like vol
14, page 1023). It is a key composed of one or more
columns with the physical location, called ROWID, stored
alongside it.
Lots with OLAP systems, fewer with OLTP systems.
Unique, Primary, Foreign, Clustered
Composite, Unique, Function-based
B-Tree, Bitmap
How SQL Queries Are Processed


Steps of parsing, executing and fetching.
Fetches the row(s) using:
– Full table scan, where every row is read into memory
– Using and index lookup

Joins the results set(s) with:
– Sort merge join
– Nested loops join
– Hash join

Orders the results set(s)
Performance Tuning Process




Goal is to give users what they want, everything
else is peripheral.
Performance must be in minds of all at every
stage.
Earlier the less costly and most beneficial.
We are at the Tune SQL and Tune Access Path
points in the performance tuning process.
(cont’d)
Performance Tuning Process



Gather table, index and data volume information.
Build reference and transaction tables; if too large,
use 10 - 25% sample, ideally use full or 50%.
Run query through optimization tools.
– Decide if optimized. If not reword SQL, add/change
indexes, change table structure, use PL/SQL shortcuts,
use Hints or back way up to design and logical model.

Calculate production execution time, if good stop.
If not, start at the top of this list again.
SQL-Tuning

In order to do any SQL-Tuning several things
must happen first:
– Understand SQL in general
– Understand how your database processes and executes
its SQL
– Understand how to use the various optimization tools
available to you.
– Understand your options for improving performance.
EXPLAIN PLANS


Tells us the paths and steps Oracle will take to get
the requested result set.
Rules for reading:
– The more heavily indented an access path is, the earlier
it is executed.
– If two steps are indented at the same level, the
uppermost statement is executed first. However, one
access path may be composed of more than one step.
The uppermost’s child step(s) will then be executed
first.
Tuning Tools




Oracle EXPLAIN PLAN
Oracle AUTOTRACE
Oracle SQL_TRACE and tkprof
SQL-Station, Plan Analyzer, SQLab
Table Scan vs. Index Lookup


Index lookup when small subset (one row or less
than 10 - 20% of the rows) is desired.
Full scan when large subset or all rows is desired.
Optimizing Table Scans

Reduce number of blocks to be scanned
– Lower high water mark
– Squeeze more into each block, reducing PCTFREE and
increasing PCTUSED.
– Moving large infrequently accessed columns to separate
sub-table, join by key.
– Using the caching mechanism
Optimizing Indexes








Avoid accidental full table scans
Analyze the tables/columns regularly
Help Oracle determine the right path with Hints
Create new indexes
Modify concatenated indices
Make use of histograms
Use alternative techniques, like hash clustering
Range scans, LIKE, Index Merges, tkprof
Optimizing Joins and Subqueries




Understand when Nested Loops vs. Sort
Merge/Hash joins are appropriate.
When in doubt, use hints to test each route.
Make sure driving table is the most selective
If subquery (IN, EXISTS, hierarchical), make sure
columns after can be resolved by index lookup
alone if possible
Optimizing Sorting and Grouping




Be aware of when sorting is performed (GROUP
BY, ORDER BY, DISTINCT, UNION,
INTERSECT, MINUS). Sorting is expensive,
eliminate if possible.
COUNT(*) vs. COUNT(indexed_unique_column)
MAX/MIN tricks
UNION vs. UNION ALL
Tales from the Front Line