Why Tune SQL Statements

1
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.

2
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.

3
Objections to SQL Tuning

• The Oracle optimizer will tune my statements for
  me automatically.
• Im not a SQL programmer, Im a ...
  VB/PowerBuilder/C/Java programmer.
• Ill write the SQL, someone else (DBA) can tune
  it for me later.
• Ill tune it later
• We cant afford to tune it

4
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.

5
Overview of Query Processing
SQL
Query Decomposition
System Catalog
Parsed SQL
Query Optimization
DatabaseStatistics
Execution Plan
CodeGeneration
Generated Query Code
QueryExecution
DatabaseTables
Output
6
SQL Tuning Process
7
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
• sorts each tables key columns
• merges data together
• does not use indexes
• Nested Loops Join
• full table scan used on smaller table
• key values of 1st table joined with the larger
  tables index
• Hash Join
• 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.

8
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.

9
Setting the Optimizer Goal

• 3 ways to change the Oracle optimizer goal
• Change the database configuration file
  (init.ora). OPTIMIZER_MODEFIRST_ROWS
• Change settings for your session in
  SQLPlus. ALTER SESSION SET OPTIMIZER_GOALRULE
  
• Influence the optimizer with hints.
• Example, recommend use of rule based
  optimizer SELECT / RULE / FROM
  EMPLOYEE WHERE SALARY gt 100000

10
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.locationIndianapolis 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.locationIndianapolis
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.locationIndianapolis and
d.dept_id e.dept_id
11
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, SQLPlus command to show execution
  plans and statistics in your SQLPlus session.
• 3rd Party Tools. Numerous GUI tools to quickly
  show this information, usually expensive. Example
  below.

12
Explain Plan
Executing explain plan forselect /RULE /
e.surname, e.firstname, e.date_of_birth from
employee e, customers c where
e.surnamec.contact_surname and
e.firstnamec.contact_firstname and
e.date_of_birthc.date_of_birth order by
e.surname, e.firstname Formatting Plan Table in
a Query select rtrim(lpad( ,2level)
rtrim(operation) rtrim(options)
object_name) as query_plan from plan_table
connect by prior idparent_id start with
id0 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