Transcript lecture11 - Department of Computer and Information Science

Why Tune SQL Statements
• Improve response time of interactive programs.
• Improve batch throughput.
• To ensure scalability of applications
load vs. performance.
• Reduce system load for other uses besides DB.
• Avoid hardware upgrades.
11-1
Scalability
•
•
•
Well-tuned applications deliver good performance as
number of users or data volume increases.
Applications which have a linear degradation pattern
degrade predictably, usually they are problems
responsive to hardware upgrades.
Exponential degradation patterns are more serious.
They tend to be problems that creep up over time,
starting benign and unrecognized, later becoming huge
problems.
Bottlenecks are performance problems which are
abrupt, like “hitting a brick wall”. Usually no warning,
and no hardware solution.
Application Scalability
Performance
•
Tuned
Linear
Exponential
Bottleneck
System Load
11-2
Objections to SQL Tuning
• “The Oracle optimizer will tune my statements
for me automatically.”
• “I’m not a SQL programmer, I’m a ...
VB/PowerBuilder/C++/Java programmer.”
• “I’ll write the SQL, someone else (DBA) can tune
it for me later.”
• “I’ll tune it later”
• “We can’t afford to tune it”
11-3
When to Tune SQL
• Early is least costly & better.
• Changing SQL/table designs in the design phase
means that no applications need to be re-written.
• Tuning SQL performance when SQL is first
written usually means lower testing costs.
• In production systems, testing SQL can
sometimes be difficult... change control,
production system availability, extra time to deal
with larger data volumes.
• Tune SQL early as possible to be most effective
and economical.
11-4
Overview of Query Processing
SQL
Query
Decomposition
System
Catalog
Parsed SQL
Query
Optimization
Database
Statistics
Execution Plan
Code
Generation
Generated Query Code
Query
Execution
Database
Tables
Output
11-5
SQL Tuning Process
SQL Tuning Process
Prepare for Tuning:
gather statistics
Finished
Yes
Gather Data:
 Explain plan
 SQL_TRACE and
tk_prof
SQL
Optimized?
No
Tune the SQL:




Reword SQL
Use Hints
Add or change indexes or clusters
Alter table structure(s)
11-6
Retrieving and Joining Data
Retrieving Data:




Full Table Scan - gets data from row 1 to high water
mark
Row ID - gets data by physical location. Quickest way to
get a row.
Index Lookup - matches up key value with Row ID
Hash Key Lookup - computes a Row ID with a
mathematical formula applied to key value.
Joining Data:

Sort Merge Join
1)
2)
3)

Nested Loops Join
1)
2)

sorts each table’s key columns
merges data together
does not use indexes
full table scan used on smaller table
key values of 1st table joined with the larger table’s index
Hash Join
1)
2)
3)
hash key built for larger table, constructs index on the fly
smaller table is then fully scanned
data from smaller table is joined with the hash key index.
11-7
RBO and CBO
Oracle Optimizers:
 Rule Based Optimizer (RBO)
• older optimizer, used since first versions of Oracle.
• set of rules which rank access paths.
• always picks an index over doing full table scan.
 Cost Based Optimizer (CBO)
• new in Oracle 7.
• uses database statistics to pick optimal access path.
• To collect table statistics:
ANALYZE TABLE tablename CALCULATE STATISTICS;
Optimizer Goals:
 RULE, picks only rule based optimizer.
 CHOOSE, picks cost based optimizer if any table in
query has been analyzed.
 ALL_ROWS, picks the cost based optimizer and finds
an execution plan which is best for the entire query.
Good for batch reporting.
 FIRST_ROWS, pick the cost based optimizer and
finds an execution plan which is best for the first row.
Good for interactive applications.
11-8
Setting the Optimizer Goal
3 ways to change the Oracle optimizer goal:
1.
Change the database configuration file (init.ora).
OPTIMIZER_MODE=FIRST_ROWS
2.
Change settings for your session in SQL*Plus.
ALTER SESSION SET OPTIMIZER_GOAL=RULE;
3.
“Influence” the optimizer with “hints”.
Example, recommend use of rule based optimizer:
SELECT /*+ RULE */ *
FROM EMPLOYEE
WHERE SALARY > 100000;
11-9
Other Common Hints
Example, use full table scans:
select /*+ FULL(E) FULL(D) */
e.employee_id, e.surname, e.firstname
from employee e,
department d
where d.location=‘Indianapolis’
and d.dept_id = e.dept_id;
Example, recommend any index:
select /*+ index(E) */
e.employee_id, e.surname, e.firstname
from employee e,
department d
where d.location=‘Indianapolis’
and d.dept_id = e.dept_id;
Example, recommend a specific index:
select /*+ index(E, emp_dept_idx) */
e.employee_id, e.surname, e.firstname
from employee e,
department d
where d.location=‘Indianapolis’
and d.dept_id = e.dept_id;
11-10
Tuning Tools
 EXPLAIN PLAN, shows the execution plan.
 SQL_TRACE, generates a trace file containing SQL
executed by your session and resources used.
 tkprof, formats SQL_TRACE output.
 AUTOTRACE, SQL*Plus command to show
execution plans and statistics in your SQL*Plus
session.
 3rd Party Tools. Numerous GUI tools to quickly show
this information, usually expensive. Example below.
11-11
Explain Plan
Executing:
explain plan for
select /*+RULE */
e.surname, e.firstname, e.date_of_birth
from employee e, customers c
where e.surname=c.contact_surname
and e.firstname=c.contact_firstname
and e.date_of_birth=c.date_of_birth
order by e.surname, e.firstname;
Formatting Plan Table in a Query:
select rtrim(lpad(‘ ‘,2*level)||
rtrim(operation)||‘ ‘||
rtrim(options)||‘ ‘||
object_name) as query_plan
from plan_table
connect by prior id=parent_id
start with id=0;
Execution Plan Query:
query_plan
---------------------------------------SELECT STATEMENT
SORT ORDER BY
NETED LOOPS
TABLE ACCESS FULL CUSTOMERS
TABLE ACCESS BY ROWID EMPLOYEES
INDEX RANGE SCAN EMP_BOTH_IDX
11-12