Top tips for Oracle SQL tuning

1
Top tips for Oracle SQL tuning
BUY GUYS BOOK
BUY QUEST PRODUCTS
BUY QUEST PRODUCTS

• Guy Harrison
• Senior Software Architect,
• Quest Software

2
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

3
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

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

5
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

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

7
A simple EXPLAIN PLAN

• SQLgt EXPLAIN PLAN FOR select count() from sales
• where product_id1
• Explained.
• SQLgt 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

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

9
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

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

11
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'

12
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

13
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

14
SQLab Expert Advice

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

15
SQLab SQL trace integration

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

16
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

17
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

18
Bit-map indexes

• Contrary to widespread belief, can be effective
  when there are many distinct column values
• Not suitable for OLTP however

19
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

20
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

21
Hint 6 Avoid accidental tablescans

• Tablescans that occur unintentionally are a major
  source of poorly performing SQL. Causes
  include
• Missing Index
• Using !, ltgt 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

22
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

23
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

24
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

25
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.surnamec.contact_surname
• and e.firstnamec.contact_firstname
• and e.date_of_birthc.date_of_birth)

No index on employees
26
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.

27
To Use semi-joins

• Set ALWAYS_SEMI_JOINHASH 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 .)

28
Oracle8 semi-joins

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

29
Star Join improvements

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

30
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.
• Oracle8s 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.

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

32
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

33
Star transformation performance

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

34
(No Transcript)
35
Hint 9 Use ARRAY processing

• Retrieve or insert rows in batches, rather than
  one at a time.
• Methods of doing this are language specific

36
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

37
Oracle8i PL/SQL Improvements

• Array processing
• NOCOPY
• Temporary tables
• The profiler
• Dynamic SQL

38
(No Transcript)
39
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 Quests Spotlight on
  Oracle product can be invaluable

40
(No Transcript)
41
w w w . q u e s t . c o m