Why tune sql statements
This presentation is the property of its rightful owner.
Sponsored Links
1 / 12

Why Tune SQL Statements PowerPoint PPT Presentation


  • 65 Views
  • Uploaded on
  • Presentation posted in: General

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

Download Presentation

Why Tune SQL Statements

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Why tune sql statements

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.


Scalability

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.


Objections to sql tuning

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”


When to tune sql

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.


Overview of query processing

Overview of Query Processing

SQL

Query

Decomposition

System Catalog

Parsed SQL

Query

Optimization

DatabaseStatistics

Execution Plan

CodeGeneration

Generated Query Code

QueryExecution

DatabaseTables

Output


Sql tuning process

SQL Tuning Process


Retrieving and joining data

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 table’s 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 table’s 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.


Rbo and cbo

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.


Setting the optimizer goal

Setting the Optimizer Goal

  • 3 ways to change the Oracle optimizer goal:

  • Change the database configuration file (init.ora). OPTIMIZER_MODE=FIRST_ROWS

  • Change settings for your session in SQL*Plus.ALTER SESSION SET OPTIMIZER_GOAL=RULE;

  • “Influence” the optimizer with “hints”.

  • Example, recommend use of rule based optimizer:SELECT /*+ RULE */ *FROM EMPLOYEEWHERE SALARY > 100000;


Other common hints

Other Common Hints

Example, use full table scans:select /*+ FULL(E) FULL(D) */ e.employee_id, e.surname, e.firstnamefrom employee e, department dwhere d.location=‘Indianapolis’ and d.dept_id = e.dept_id;

Example, recommend any index:select /*+ index(E) */ e.employee_id, e.surname, e.firstnamefrom employee e, department dwhere 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.firstnamefrom employee e, department dwhere d.location=‘Indianapolis’ and d.dept_id = e.dept_id;


Tuning tools

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.


Explain plan

Explain Plan

  • Executing:

    • explain plan forselect /*+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


  • Login