Proven Process for
SQL Tuning
Dean Richards
Senior DBA, Confio Software
1
Who Am I?
Senior DBA for Confio Software
• DeanRichards@confio.com
Current – 20+ Years in Oracle, SQL Server
Former – 15+ Years in Oracle Consulting
Specialize in Performance Tuning
Review Performance of 100’s of Databases for
Customers and Prospects
Common Thread – Paralyzed by Tuning
3
Agenda
Introduction
Challenges
Identify - Which SQL and Why
Gather – Details about SQL
Tune – Case Study
Monitor – Make sure it stays tuned
4
Introduction
SQL Tuning is Hard
This Presentation is an Introduction
• 3-5 day detailed classes are typical
Providing a Framework
• Helps develop your own processes
• There is no magic tool
• Tools cannot reliably tune SQL statements
• Tuning requires the involvement of you and other
technical and functional members of team
5
Challenges
Requires Expertise in Many Areas
• Technical – Plan, Data Access, SQL Design
• Business – What is the Purpose of SQL?
Tuning Takes Time
• Large Number of SQL Statements
• Each Statement is Different
Low Priority in Some Companies
• Vendor Applications
• Focus on Hardware or System Issues
Never Ending
6
Identify – Which SQL
Have a Method
• Tracing a Session / Process
• User / Batch Job Complaints
• Highest I/O (LIO, PIO)
• SQL Performing Full Table Scans
• Known Poorly Performing SQL
• Response / Wait Times (Ignite, AWR, etc)
7
Identify – End-to-End
Business Aspects
• Who registered yesterday for SQL Tuning
• Who uses this information?
• Why does the business need to know this?
• How often is the information needed?
Technical Information
• Review Tables, Indexes, Triggers, Views, etc
• Understand Relationships
• Know the Data (High Level)
End-to-End Process
• Understand Application Architecture
• What Portion of the Total Time is Database
8
Identify – End-to-End Time
9
Wait Event Information
V$SESSION
V$SESSION_WAIT
SID
USERNAME SID
SQL_ID EVENT
PROGRAM P1, P1RAW, P2, P2RAW, P3, P3RAW
MODULE STATE (WAITING, WAITED…)
ACTION
PLAN_HASH_VALUE • Oracle 10g added this info to V$SESSION
ROW_WAIT_OBJ#
V$SQL V$SQLAREA V$SQL_PLAN DBA_OBJECTS
SQL_ID SQL_ID SQL_ID OBJECT_ID
SQL_FULLTEXT EXECUTIONS PLAN_HASH_VALUE OBJECT_NAME
PARSE_CALLS OBJECT_TYPE
BUFFER_GETS
10
DISK_READS
Wait Event Information
SELECT s.sql_id, sql.sql_text, sql.plan_hash_value,
DECODE(s.state, 'WAITING', s.event, 'CPU/LogicalIO')
waitevent,
s.p1, s.p2, s.p3
FROM v$session s
JOIN v$sql sql ON (
s.sql_id = sql.sql_id AND s.sql_address = sql.address
)
WHERE program = ‘…’ AND s.sid = …
AND <whatever else you know>
11
Wait Time Scenario
Which scenario is worse?
SQL Statement 1
• Executed 100 times
• Caused 100 minutes of wait time for end user
• Waited 99% of time on “db file sequential read”
SQL Statement 2
• Executed 1 time
• Caused 100 minutes of wait time for end user
• Waited 99% on “enq: TX – row lock contention”
12
Identify – Simplification
Break Down SQL Into Simplest Forms
• Complex SQL becomes multiple logical SQL
• Sub-Queries Should be Tuned Separately
• UNION’ed SQL Tuned Separately
• Get the definition of views
• Are synonyms being used
Use Execution Plan (later)
• Helps isolate the portion of the query that is
performing poorly
13
Identify – Summary
Determine the SQL
Understand End-to-End
Measure Wait Time
Simplify Statement
• Based on Execution Plan
14
Gather - Metrics
Get baseline metrics
• How long does it take now
• What is acceptable (10 sec, 2 min, 1 hour)
Collect Wait Time Metrics – How Long
• Locking / Blocking
• I/O problem, Latch contention
• May be multiple issues
• All have different resolutions
Document everything in simple language
15
Gather – Execution Plan
EXPLAIN PLAN
• Estimated execution plan - can be wrong for many
reasons
V$SQL_PLAN (Oracle 9i+)
• Real execution plan
• Use DBMS_XPLAN for display
Tracing (all versions)
• Works when you know a problem will occur
ALTER SESSION SET tracefile_identifier = dean;
ALTER SESSION SET sql_trace = true;
Historical – AWR, Confio Ignite
16
All Plans Not Equal
SELECT company, attribute
FROM data_out WHERE segment = :B1
Wait Time – 100% on “db file scattered read”
Plan from EXPLAIN PLAN
Plan from V$SQL_PLAN using DBMS_XPLAN
17
Gather – Bind Values
V$SQL_BIND_CAPTURE
• STATISTICS_LEVEL = TYPICAL or ALL
• Collected at 15 minute intervals
SELECT name, position, datatype_string, value_string
FROM v$sql_bind_capture
WHERE sql_id = '15uughacxfh13';
NAME POSITION DATATYPE_STRING VALUE_STRING
----- ---------- --------------- ------------
:B1 1 BINARY_DOUBLE
Bind Values also provided by tracing
• Level 4 – bind values
• Level 8 – wait information
• Level 12 – bind values and wait information
18
Gather – Table / Index Stats
Use TuningStats.sql
• http://support.confio.com/kb/1534
Provides data on objects in execution plans.
• Table sizes
• Existing indexes
• Cardinality of columns
• Segment sizes
• Histograms and Data Skew
• Many things the CBO uses
Run it for any table involved in query
19
Example SQL Statement
Who registered yesterday for SQL Tuning
Used for billing
SELECT s.fname, s.lname, r.signup_date
FROM student s, registration r, class c
WHERE s.student_id = r.student_id
AND r.class_id = c.class_id
AND UPPER(c.name) = 'SQL TUNING'
AND r.signup_date BETWEEN
TRUNC(SYSDATE-1) AND TRUNC(SYSDATE)
AND r.cancelled = 'N'
Execution Time – 12:38
Wait Time – 95% on “db file scattered read”
20
Relationship
CLASS REGISTRATION STUDENT
class_id class_id student_id
name student_id fname
class_level signup_date lname
cancelled
21
Execution Plan
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 95 |
| 1 | NESTED LOOPS | | 1 | 167 | 95 |
| 2 | NESTED LOOPS | | 1 | 138 | 94 |
| 3 | NESTED LOOPS | | 7 | 357 | 87 |
| 4 | VIEW | VW_SQ_1 | 201 | 7035 | 87 |
|* 5 | FILTER | | | | |
| 6 | HASH GROUP BY | | 201 | 3417 | 87 |
|* 7 | FILTER | | | | |
|* 8 | TABLE ACCESS FULL | REGISTRATION | 80000 | 1328K| 76 |
|* 9 | INDEX UNIQUE SCAN | SYS_C0036920 | 1 | 16 | 0 |
|* 10 | TABLE ACCESS BY INDEX ROWID| CLASS | 1 | 87 | 1 |
|* 11 | INDEX UNIQUE SCAN | SYS_C0036919 | 1 | | 0 |
| 12 | TABLE ACCESS BY INDEX ROWID | STUDENT | 1 | 29 | 1 |
|* 13 | INDEX UNIQUE SCAN | SYS_C0036918 | 1 | | 0 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter((MAX("SIGNUP_DATE")>=TRUNC(SYSDATE@!-1) AND
MAX("SIGNUP_DATE")<=TRUNC(SYSDATE@!)))
7 - filter(TRUNC(SYSDATE@!-1)<=TRUNC(SYSDATE@!))
8 - filter("CANCELLED"='N')
9 - access("R1"."STUDENT_ID"="STUDENT_ID" AND "R1"."CLASS_ID"="CLASS_ID" AND
"SIGNUP_DATE"="VW_COL_1")
filter(("SIGNUP_DATE">=TRUNC(SYSDATE@!-1) AND "SIGNUP_DATE"<=TRUNC(SYSDATE@!)))
10 - filter(UPPER("C"."NAME“)='SQL TUNING')
11 - access("CLASS_ID"="C"."CLASS_ID")
13 - access("S"."STUDENT_ID"="STUDENT_ID")
22
Gather – Summary
Execution Plan
• V$SQL_PLAN
• Do not use EXPLAIN PLAN
• DBMS_XPLAN
Bind Values
• V$SQL_BIND_CAPTURE
• Tracing
Table and Index Statistics
ERD
23
Tune – Create SQL Diagram
SQL Tuning – Dan Tow
• Great book that teaches SQL Diagramming
• http://www.singingsql.com
registration .04
5 30
1 1
student class .002
select count(1) from registration where cancelled = 'N'
and signup_date between trunc(sysdate-1) and trunc(sysdate)
3562 / 80000 = .0445
select count(1) from class where UPPER(name) = 'SQL TUNING'
2 / 1000 = .002
24
New Plan
create index cl_uname on class (upper(name));
Index on registration was (student_id, class_id)
create index reg_alt on registration (class_id);
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 10|
|* 1 | FILTER | | | | |
| 2 | NESTED LOOPS | | 1 | 132 | 7|
| 3 | NESTED LOOPS | | 1 | 103 | 6|
|* 4 | TABLE ACCESS BY INDEX ROWID | CLASS | 1 | 87 | 5|
|* 5 | INDEX RANGE SCAN | CL_UNAME | 4 | | 1|
|* 6 | INDEX RANGE SCAN | REG_ALT | 1 | 16 | 1|
| 7 | SORT AGGREGATE | | 1 | 17 | |
|* 8 | TABLE ACCESS BY INDEX ROWID| REGISTRATION | 1 | 17 | 3|
|* 9 | INDEX RANGE SCAN | REG_ALT | 1 | | 2|
| 10 | TABLE ACCESS BY INDEX ROWID | STUDENT | 1 | 29 | 1|
|* 11 | INDEX UNIQUE SCAN | SYS_C0036918 | 1 | | 0|
--------------------------------------------------------------------------------
25
Query 2
Who cancelled classes within the week
SELECT s.lname, c.name, r.signup_date cancel_date
FROM registration r, student s, class c
where r.signup_date between sysdate and sysdate-7
AND r.cancelled = 'Y'
AND r.student_id = s.student_id
AND r.class_id = c.class_id
30% of rows are dated within last week
No index on CANCELLED column = FTS
Will an index on CANCELLED column help?
• Why or why not?
26
Tune – Create SQL Diagram
registration .007 .14
5 30
1 1
student class
select count(1) from registration where cancelled = ‘Y’
and signup_date between trunc(sysdate-1) and trunc(sysdate)
622 / 80000 = .0077
select count(1) from registration where cancelled = ‘Y‘
638 / 80000 = .0079
select count(1) from registration
where signup_date between trunc(sysdate-1) and trunc(sysdate)
11598 / 80000 = .1449
27
Query 2 Column Stats
create index reg_can on registration(cancelled);
select cancelled, count(1)
from registration group by cancelled;
C COUNT(1)
- ----------
Y 638
N 79345
Oracle will not use an index on this column
• Unless it has more information
• CBO assumes an even data distribution
Histograms give more information to Oracle
• Based on skewed data, CBO realizes an index would be
beneficial
• Works best with literal values
• Bind Variables – Oracle peeks first time only
28
Query 2 - Histogram
dbms_stats.gather_table_stats(
ownname => 'STDMGMT',
tabname => 'REGISTRATION',
method_opt=>'FOR COLUMNS cancelled SIZE AUTO')
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7|
|* 1 | FILTER | | | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| REGISTRATION | 1 | 17 | 7|
|* 3 | INDEX RANGE SCAN | REG_CAN | 754 | | 2|
----------------------------------------------------------------------------
29
Monitor
Monitor the improvement
• Be able to prove that tuning made a difference
• Take new metrics measurements
• Compare them to initial readings
• Brag about the improvements – no one else will
Monitor for next tuning opportunity
• Tuning is iterative
• There are always room for improvements
• Make sure you tune things that make a difference
Shameless Product Pitch - Ignite
30
Confio Software - Monitor
Developer of Wait-Based Performance Tools
Igniter Suite
• Ignite for SQL Server, Oracle, DB2, Sybase
Provides Help With
• Identify
• Gather
• Monitor
Based in Colorado, worldwide customers
Free trial at www.confio.com
33
