Top tips for oracle sql tuning
Download
1 / 41

Top tips for Oracle SQL tuning - PowerPoint PPT Presentation


  • 339 Views
  • Uploaded on

Top tips for Oracle SQL tuning. BUY GUY’S BOOK. BUY QUEST PRODUCTS. BUY QUEST PRODUCTS. Guy Harrison Senior Software Architect, Quest Software. Top 10 Oracle SQL tuning tips. Design and develop with performance in mind Establish a tuning environment Index wisely Reduce parsing

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Top tips for Oracle SQL tuning' - fadey


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
Top tips for oracle sql tuning

Top tips for Oracle SQL tuning

BUY GUY’S

BOOK

BUY QUEST PRODUCTS

BUY QUEST PRODUCTS

Guy Harrison

Senior Software Architect,

Quest Software


Top 10 oracle sql tuning tips
Top 10 Oracle SQL tuning tips

  • Design and develop with performance in mind

  • Establish a tuning environment

  • Index wisely

  • Reduce parsing

  • Take advantage of Cost Based Optimizer

  • Avoid accidental table scans

  • Optimize necessary table scans

  • Optimize joins

  • Use array processing

  • Consider PL/SQL for “tricky” SQL


Hint 1 design and develop with performance in mind
Hint #1: Design and develop with performance in mind

  • Explicitly identify performance targets

  • Focus on critical transactions

    • Test the SQL for these transactions against simulations of production data

  • Measure performance as early as possible

  • Consider prototyping critical portions of the applications

  • Consider de-normalization and other performance by design features early on


Hint 2 establish a tuning and development environment
Hint #2: Establish a tuning and development environment

  • A significant portion of SQL that performs poorly in production was originally crafted against empty or nearly empty tables.

  • Make sure you establish a reasonable sub-set of production data that is used during development and tuning of SQL

  • Make sure your developers understand EXPLAIN PLAN and tkprof, or equip them with commercial tuning tools.


Understanding sql tuning tools
Understanding SQL tuning tools

  • The foundation tools for SQL tuning are:

    • The EXPLAIN PLAN command

    • The SQL Trace facility

    • The tkprof trace file formatter

  • Effective SQL tuning requires either familiarity with these tools or the use of commercial alternatives such as SQLab


Explain plan
EXPLAIN PLAN

  • The EXPLAIN PLAN reveals the execution plan for an SQL statement.

  • The execution plan reveals the exact sequence of steps that the Oracle optimizer has chosen to employ to process the SQL.

  • The execution plan is stored in an Oracle table called the “plan table”

  • Suitably formatted queries can be used to extract the execution plan from the plan table.


A simple explain plan
A simple EXPLAIN PLAN

SQL> EXPLAIN PLAN FOR select count(*) from sales

where product_id=1;

Explained.

SQL> SELECT RTRIM (LPAD (' ', 2 * LEVEL) || RTRIM (operation)

||' '||RTRIM (options) || ' ' || object_name) query_plan

2 FROM plan_table

3 CONNECT BY PRIOR id = parent_id

4* START WITH id = 0

QUERY_PLAN

--------------------------------------------

SELECT STATEMENT

SORT AGGREGATE

TABLE ACCESS FULL SALES


Interpreting explain plan
Interpreting EXPLAIN PLAN

  • The more heavily indented an access path is, the earlier it is executed.

  • If two steps are indented at the same level, the uppermost statement is executed first.

  • Some access paths are “joined” – such as an index access that is followed by a table lookup.


A more complex explain plan
A more complex EXPLAIN PLAN

SELECT STATEMENT

VIEW SYS_DBA_SEGS

UNION-ALL

NESTED LOOPS

NESTED LOOPS

NESTED LOOPS

NESTED LOOPS

NESTED LOOPS

VIEW SYS_OBJECTS

UNION-ALL

TABLE ACCESS FULL TAB$

TABLE ACCESS FULL TABPART$

TABLE ACCESS FULL CLU$

TABLE ACCESS FULL IND$

TABLE ACCESS FULL INDPART$

TABLE ACCESS FULL LOB$

TABLE ACCESS FULL TABSUBPART$

TABLE ACCESS FULL INDSUBPART$

TABLE ACCESS FULL LOBFRAG$

TABLE ACCESS BY INDEX ROWID OBJ$

INDEX UNIQUE SCAN I_OBJ1

TABLE ACCESS CLUSTER SEG$

INDEX UNIQUE SCAN I_FILE#_BLOCK#

TABLE ACCESS BY INDEX ROWID FILE$

INDEX UNIQUE SCAN I_FILE2

TABLE ACCESS CLUSTER TS$

INDEX UNIQUE SCAN I_TS#

TABLE ACCESS CLUSTER USER$

INDEX UNIQUE SCAN I_USER#

NESTED LOOPS

NESTED LOOPS

NESTED LOOPS

NESTED LOOPS

TABLE ACCESS FULL UNDO$

TABLE ACCESS BY INDEX ROWID FILE$

INDEX UNIQUE SCAN I_FILE2

TABLE ACCESS CLUSTER SEG$

INDEX UNIQUE SCAN I_FILE#_BLOCK#

TABLE ACCESS CLUSTER TS$

INDEX UNIQUE SCAN I_TS#

TABLE ACCESS CLUSTER USER$

INDEX UNIQUE SCAN I_USER#

NESTED LOOPS

NESTED LOOPS

NESTED LOOPS

TABLE ACCESS FULL FILE$

TABLE ACCESS CLUSTER SEG$

INDEX RANGE SCAN I_FILE#_BLOCK#

TABLE ACCESS CLUSTER TS$

INDEX UNIQUE SCAN I_TS#

TABLE ACCESS CLUSTER USER$

INDEX UNIQUE SCAN I_USER#


Sql trace and tkprof
SQL_TRACE and tkprof

  • ALTER SESSION SET SQL_TRACE TRUE causes a trace of SQL execution to be generated.

  • The TKPROF utility formats the resulting output.

  • Tkprof output contains breakdown of execution statistics, execution plan and rows returned for each step. These stats are not available from any other source.

  • Tkprof is the most powerful tool, but requires a significant learning curve.


Tkprof output
Tkprof output

count2 cpu3 elapsed4 disk5 query6 current7 rows8

------ ------ ------ -------- ------- -------- -------- ------

Parsea 1d 0.02 0.01 0 0 0 0

Executeb 1e 0.00 0.00 0 0 0 0

Fetchc 20j 141.10 141.65 1237 1450011 386332 99i

------ ------ ------ -------- ------- -------- -------- ------

total 22 141.12 141.66 1237k 1450011f 386332g 99h

Rowsl Execution Planm

------- ---------------------------------------------------

0 SELECT STATEMENT GOAL: CHOOSE

99 FILTER

96681 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'CUSTOMERS'

96582 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'EMPLOYEES'


Using sqlab
Using SQLab

  • Because EXPLAIN PLAN and tkprof are unwieldy and hard to interpret, third party tools that automate the process and provide expert advice improve SQL tuning efficiency.

  • The Quest SQLab product:

    • Identifies SQL your database that could benefit from tuning

    • Provides a sophisticated tuning environment to examine, compare and evaluate execution plans.

    • Incorporates an expert system to advise on indexing and SQL statement changes


Sqlab sql tuning lab
SQLab SQL tuning lab

  • Display execution plan in a variety of intuitive ways

  • Provide easy access to statistics and other useful data

  • Model changes to SQL and immediately see the results


Sqlab expert advice
SQLab Expert Advice

  • SQLab provides specific advice on how to tune an SQL statement


Sqlab sql trace integration
SQLab SQL trace integration

  • SQLab can also retrieve the execution statistics that are otherwise only available through tkprof


Hint 3 index wisely
Hint #3: Index wisely

  • Index to support selective WHERE clauses and join conditions

  • Use concatenated indexes where appropriate

  • Consider overindexing to avoid table lookups

  • Consider advanced indexing options

    • Hash Clusters

    • Bit mapped indexes

    • Index only tables


Effect of adding columns to a concatenated index
Effect of adding columns to a concatenated index

  • Novice SQL programmers often are satisfied if the execution plan shows an index

  • Make sure the index has all the columns required


Bit map indexes
Bit-map indexes

  • Contrary to widespread belief, can be effective when there are many distinct column values

  • Not suitable for OLTP however


Hint 4 reduce parsing
Hint #4: Reduce parsing

  • Use bind variables

    • Bind variables are key to application scalability

    • If necessary in 8.1.6+, set cursor CURSOR_SHARING to FORCE

  • Reuse cursors in your application code

    • How to do this depends on your development language

  • Use a cursor cache

    • Setting SESSION_CACHED_CURSORS (to 20 or so) can help applications that are not re-using cursors


Hint 5 take advantage of the cost based optimizer
Hint #5: Take advantage of the Cost Based Optimizer

  • The older rule based optimizer is inferior in almost every respect to the modern cost based optimizer

  • Using the cost based optimizer effectively involves:

    • Regular collection of table statistics using the ANALYZE or DBMS_STATS command

    • Understand hints and how they can be used to influence SQL statement execution

    • Choose the appropriate optimizer mode: FIRST_ROWS is best for OLTP applications; ALL_ROWS suits reporting and OLAP jobs


Hint 6 avoid accidental tablescans
Hint #6: Avoid accidental tablescans

  • Tablescans that occur unintentionally are a major source of poorly performing SQL. Causes include:

    • Missing Index

    • Using “!=“, “<>” or NOT

      • Use inclusive range conditions or IN lists

    • Looking for values that are NULL

      • Use NOT NULL values with a default value

    • Using functions on indexed columns

      • Use “functional” indexes in Oracle8i


Hint 7 optimize necessary table scans
Hint #7: Optimize necessary table scans

  • There are many occasions where a table scan is the only option. If so:

    • Consider parallel query option

    • Try to reduce size of the table

      • Adjust PCTFREE and PCTUSED

      • Relocate infrequently used long columns or BLOBs

      • Rebuild when necessary to reduce the high water mark

    • Improve the caching of the table

      • Use the CACHE hint or table property

      • Implement KEEP and RECYCLE pools

    • Partition the table (if you really seek a large subset of data)

    • Consider the fast full index scan


Fast full index scan performance
Fast full index scan performance

  • Use when you must read every row, but not every column

  • Counting the rows in a table is a perfect example


Hint 8 optimize joins
Hint #8: Optimize joins

  • Pick the best join method

    • Nested loops joins are best for indexed joins of subsets

    • Hash joins are usually the best choice for “big” joins

  • Pick the best join order

    • Pick the best “driving” table

    • Eliminate rows as early as possible in the join order

  • Optimize “special” joins when appropriate

    • STAR joins for data-warehousing applications

    • STAR_TRANSFORMATION if you have bitmap indexes

    • ANTI-JOIN methods for NOT IN sub-queries

    • SEMI-JOIN methods for EXISTS sub-queries

    • Properly index CONNECT BY hierarchical queries


Oracle 8 semi joins
Oracle 8 semi-joins

  • Optimizes queries using EXISTS where there is no supporting index

    select *

    from customers c

    where exists

    (select 1 from employees e

    where e.surname=c.contact_surname

    and e.firstname=c.contact_firstname

    and e.date_of_birth=c.date_of_birth)

No index on employees


Oracle 8 semi joins1
Oracle 8 semi-joins

  • Without the semi-join or supporting index, queries like the one on the preceding slide will perform very badly.

  • Oracle will perform a tablescan of the inner table for each row retrieved by the outer table

  • If customers has 100,000 rows, and employees 800 rows then 80 MILLION rows will be processed!

  • In Oracle7, you should create the index or use an IN-based subquery

  • In Oracle8, the semi-join facility allows the query to be resolved by a sort-merge or hash join.


To use semi joins
To Use semi-joins

  • Set ALWAYS_SEMI_JOIN=HASH or MERGE in INIT.ORA, OR

  • Use a MERGE_SJ or HASH_SJ hint in the subquery of the SQL statement

    SELECT *

    FROM customers c

    WHERE exists

    (select /*+merge_sj*/ 1

    from employees e

    where ….)


Oracle8 semi joins
Oracle8 semi-joins

  • The performance improvements are impressive (note the logarithmic scale)


Star join improvements
Star Join improvements

  • A STAR join involves a large “FACT” table being joined to a number of smaller “dimension” tables


Star join improvements1
Star Join improvements

  • The Oracle7 Star join algorithm works well when there is a concatenated index on all the FACT table columns

  • But when there are a large number of dimensions, creating concatenated indexes for all possible queries is impossible.

  • Oracle8’s “Star transformation” involves re-wording the query so that it can be supported by combinations of bitmap indexes.

  • Since bitmap indexes can be efficiently combined, a single bitmap index on each column can support all possible queries.


To enable the star transformation
To enable the star transformation

  • Create bitmap indexes on each of the FACT table columns which are used in star queries

  • Make sure that STAR_TRANSFORMATION_ENABLED is TRUE, either by changing init.ora or using an ALTER SESSION statement.

  • Use the STAR_TRANSFORMATION hint if necessary.


Drawback of star transformation
Drawback of Star transformation

  • Bitmap indexes reduce concurrency (row-level locking may break down).

  • But remember that large number of distinct column values may not matter


Star transformation performance
Star transformation performance

  • When there is no suitable concatenated index, the Star transformation results in a significant improvement


Hint 9 use array processing
Hint #9: Use ARRAY processing

  • Retrieve or insert rows in batches, rather than one at a time.

  • Methods of doing this are language specific


Hint 10 consider pl sql for tricky sql
Hint #10: Consider PL/SQL for “tricky” SQL

  • With SQL you specify the data you want, not how to get it. Sometime you need to specifically dictate your retrieval algorithms.

  • For example:

    • Getting the second highest value

    • Doing lookups on a low-high lookup table

    • Correlated updates

    • SQL with multiple complex correlated subqueries

    • SQL that seems to hard to optimize unless it is broken into multiple queries linked in PL/SQL


Oracle8 i pl sql improvements
Oracle8i PL/SQL Improvements

  • Array processing

  • NOCOPY

  • Temporary tables

  • The profiler

  • Dynamic SQL


Bonus hint when your sql is tuned look to your oracle configuration
Bonus hint: When your SQL is tuned, look to your Oracle configuration

  • When SQL is inefficient there is limited benefit in investing in Oracle server or operating system tuning.

  • However, once SQL is tuned, the limiting factor for performance will be Oracle and operating system configuration.

  • In particular, check for internal Oracle contention that typically shows up as latch contention or unusual wait conditions (buffer busy, free buffer, etc)

  • Third party tools – such as Quest’s Spotlight on Oracle product – can be invaluable