SQL Tuning Briefing

1
SQL Tuning Briefing

• Null is not equal to null but null is null

2
What are Best Practices ?

• A set of guiding principles proposed by
  industry leading experts
• Acknowledgement that the application is part
  of an evolving lifecycle
• which will continue to iterate over a period of
  time
• Determination to deliver a quality product or
  what we used to call
• taking pride in your work!
• Objectively and consistently leads to
• better structured, more robust code
• more accurate code
• better performing code
• better maintained code
• more readable code
• Much less reliance on individual developers
• Repeatability and measurability

3
Single Developer Best Practices
4
Development team best practice
One-tenth of a second is about the limit for
having the user feel that the system is reacting
instantaneously
Team Developers Process
Prepare
Write Compile
Code Review
Test Code
Debug
Optimize SQL
Team Review ScheduledCode Review Benchmark
test SQL Scan
Pre-deployment QA Regression Testing and
Scalability/ Performance Tuning
USDevelopmentTeam
ProductionDatabase
HKDevelopmentTeam
VersionControl(Team Coding)
ManagementReporting
Code ReviewSuccessful

Manager
ZHA/SHADevelopmentTeam
Bad Code or Problematic SQL DetectedReturn to
Development
5
The challenges and problems of application
Challenges Higher speed means more business Less
resource consumption means more concurrent
users Problems
6
What affects performance?

• Hardware
• Disk space
• RAM
• Network
• CPU
• Operating System settings
• Database Server parameter settings
• Database Design
• Indexes on Database Tables
• SQL statement

7
Why SQL statement affecting performance
8
Performance Problems
According to industrial experts 60 of the
databases performance problems are caused by
applications
Others
Application
9
DBMS Optimize - Find the best way to process your
SQL Statements
10
Why does SQL need tuning?

• It is easy to write functional SQL.
• It is harder to write efficient, high
  performing SQL.

11
Why is it so hard?
You must know

• your database structure
• how SQL statements work in general
• how Database executes SQL
• database changes over time

Then its trial and error until you get a
satisfactory result.
12
Why dont people tune their SQL?

• Too busy now. Ill do it later.
• Is that what the Oracle optimizer is for?
• Im a Java, not an SQL, programmer.
• I dont know how.
• Generated by Toplink, not my business.
• Its works. Ive got my data. Im happy.

13
Top Link problem

• Generate SQL code based on its own
  object-oriented functions
• Toplink will transform the object-oriented
  requests into object-liked SQL statements
• Relational based SQL engine like Oracle to
  execute an object-liked SQL statements may cause
  unprecedented large system resources utilization
  and bad system performance

14
When is the best time to tune SQL?

• At the time it is written
• As the database changes

15
How does Oracle process SQL?
Server receives SQL
Parses
Oracle optimizer determines the execution path
Binds variables
Executes
16
How Oracle Optimizer works
SQL
Execution
17
Limitations of Optimizer

• Limited SQL rewrite
• Resource limitation
• Cost estimation is only an estimation
• Problems too complex for optimizer

18
Basic Concept of SQL Execution - Driving Path
SELECT FROM A, B, C WHERE A.key1 B.key1
AND B.key2 C.key2 AND C.f2 0 AND A.f1
0
Driving Path C?B?A
19
Driving Path C?B?A
Driving Path A?B?C
20
Why join path matter
select from A, B / table A has 100,000
records / where A.key B.key / table B has
1,000 records /

• Path from table A to table B which means that we
  open table A, looking at each row to then use an
  index to search for matching rows in table B

• Number of Operations (A?B) 100,000
  RoundUp(LN(1000) / LN(2)) / 2 100,000 10 / 2
  500,000

• Path from table B to table A which means that we
  open B table, looking at each row to then use an
  index to search for matching rows in table A

• Number of Operations (B?A) 1000
  RoundUp(LN(100,000) / LN(2)) / 2 1000 17 / 2
  8,500

Path from B?A is around 59 times faster than the
speed of A?B
21
Why join path matter

• 500,000 operations 8500 operations

Index Scan A
Full Table Scan A
B
B
Index Scan
Full Table Scan
22
Specific Tips for writing SQL
23
Use table join in place of sub-query If A,B is
many to one or one to one relationship Replace
Select from A where A.CITY in (select
B.City from B) With Select A. from A, B
where A.CITY B.CITY
24
Joins
Select the smallest table and/or smallest result
set first. SELECT FROM A,B WHERE A.STATE
CA AND B.CITY CONCORD
25
JoinsChange table driving path
If A is a large table and B is small. Small table
should drive the large table. Disable index on
B. This changes the table driving path. Replace
SELECT FROM A,B WHERE A.STATE
B.STATE With SELECT FROM B,A WHERE A.STATE
B.STATE ''
26
Avoid Views
For some complex SQL(Group function, Set
function), Views SQL is normally cannot be
merged to your upper layer SQL. So, Oracle
normally processes the view first, creates a temp
table which does not have any indexes, and you
cannot tune the view because Oracle always
handles the view in the same manner.
27
Explain Plan

• Analyze your Explain Plan.
• Always test the SQL statement to find its actual
  time.

28
Indexed Fields
Know your indexes and use them to your advantage.
29
Indexed Fields
If you want the index used, dont perform an
operation on the field. Replace SELECT from A
where SALARY 1000 NEWSALARY with SELECT
from A where SALARY NEWSALARY -1000
30
Indexed Fields
Index will not be used when a function is
used. SELECT from A where substr(name, 1, 3)
'Wil'
31
Indexed Fields
WHERE clause Avoid using ! (not equal
to) Like 'SA'
32
Indexed Fields
Sometimes DO disable the index SELECT FROM A
WHERE SALARY 0 '10000' AND DEPT
'IT' SELECT FROM A WHERE EMP_SEX '' 'm'
33
Indexed Fields
Do not have default value set to NULL. If it is
a number field and lowest value is 0,
then Replace SELECT FROM A WHERE NUMBER IS
NOT NULL with (normally faster response
time) SELECT FROM A WHERE NUMBER gt0
34
Index Fields

Replace Outer Join with Union. If both A.State
and B.State have a unique indexed Replace
SELECT A.CITY, B.CITY FROM A,B WHERE
A.STATEB.STATE() With SELECT A.CITY, B.CITY
FROM A,B WHERE A.STATEB.STATE UNION SELECT
NULL, B.CITY FROM B WHERE NOT EXISTS (SELECT
'X' FROM A Where A.STATEB.STATE)
35
Optimization Hints
Learn to use optimization hints Rule Star First
_Row Star_Transformation All_Rows Index_Combine
Ordered Merge Use_Hash No_Merge Use_Concat No_Ex
pand Parallel Driving_Site
36
ORDER BY clause
Use concatenated index. If A table is indexed
with lastname, firstname. Replace SELECT FROM
A WHERE lastname 'Smith' ORDER BY
firstname With SELECT FROM A WHERE lastname
'Smith' ORDER BY lastname, firstname
37
EXIST and IN Sub-query
Assume table A,B relationship is one to
many. The following statements have the same
results. SELECT FROM A WHERE A.CITY IN
(SELECT B.CITY FROM B) SELECT FROM A WHERE
EXISTS (SELECT CITY FROM B WHERE A.CITY
B.CITY)
38
Use IN Sub-query
SELECT FROM A WHERE A.CITY IN (SELECT
B.CITY FROM B)

• A.CITY is indexed, B.CITY is not indexed, and
  table B has much less rows than A.

SELECT FROM A WHERE A.CITY IN (SELECT
B.CITY FROM B)

• A.CITY is indexed, B.CITY is indexed, and table
  B has much less rows than A.

39
Use EXISTS Sub-query
SELECT FROM A WHERE EXISTS (SELECT CITY
FROM B WHERE A.CITY B.CITY)

• A.CITY is not indexed and B.CITY is indexed, and
  B has more rows than A.

SELECT FROM A WHERE EXISTS (SELECT CITY
FROM B WHERE A.CITY B.CITY)

• A.CITY is indexed and B.CITY is indexed, and
  table B has more rows than A.

40
Variables Problem

• select from Employee
• where
• (Emp_idgtrange_lower or range_lower is null)
• and (A.keyltrange_upper or range_upper is null)

To enable index range search with a input range
boundaries.
select from Employee where Emp_idgtnvl(range_l
ower,min , Emp_id) and A.keyltnvl(range_upper,
A.key)
41
Transitivity
Select from A,B,C where A.keyB.key and
B.keyC.key and C.keytext
To make Oracles optimizer more intelligent by
increasing the transitivity(as Oracles Optimizer
cannot do very complicated transitivity
improvement internally)
Select from A,B,C where A.keyB.key and
B.keyC.key and A.keyC.key and C.keytext and
A.keytext and B.keytext
42
Complex SQL Transformation
select from A where (A.key1,A.key2) not
in (select B.key1,B.key2 from B)
Sometimes the following transformation may give
you surprise !
select from A where (A.key1,A.key2) in (select
B.key1,B.key2 from B minus select
A.key1,A.key2 from A)
43
Which SQL is best?
SQL2
SQL1
44
How many ways can you rewrite this statement?
select emp_name, dpt_name,
grd_desc from employee, department
DEPARTMENT1, grade where emp_grade
grd_id and emp_dept dpt_id and EXISTS
(SELECT 'X' from department
DEPARTMENT2 WHERE dpt_avg_salary in
(select min(dpt_avg_salary)
from department DEPARTMENT3)
AND dpt_id
EMPLOYEE.emp_dept)
45
1261 semantically equivalent SQL statements
46
Thank You