Oracle9i Performance Tuning

1
Oracle9i Performance Tuning

• Chapter 13
• SQL Tuning

2
Chapter Objectives

• Understand the difference between optimizer modes
  and goals
• Interpret execution queries
• Understand and use optimizer hints
• Use optimizer-related parameters
• Identify SQL queries that consume the highest
  amount of resources
• Understand the need for SQL standards
• Prevent slow SQL execution
• Work through SQL analysis examples
• Learn to use outlines

3
Optimizer Analogy
4
Optimizer Tasks

• Oracle optimizer performs the following functions
  when evaluating a SQL statement
• Evaluates the expressions and conditions
  contained in the SQL statement
• Transforms complex queries into an equivalent
  join query
• Chooses an optimization goal, either the
  Cost-based Optimizer (CBO) or the Rule-based
  Optimizer (RBO)
• Determines how to access each table to retrieve
  data
• Determines the order of table joins for
  statements involving more than two tables
• Chooses the type of join to be performed for each
  pair

5
Optimizer Modes
6
Optimizer Goal
7
Rule-based Optimizer Ranks
8
Cost-based Optimizer Process

• CHOOSE Oracle chooses the best and most
  efficient path to execute and retrieve data.
  Optimizer can select a cost-access method when
  statistics are available or a rule-based access
  method if it is faster. This setting is the most
  common mode used.
• ALL_ROWS Oracle uses cost based access methods
  regardless of statistics availability. This
  optimizer priority is to retrieve all rows with
  the minimum amount of resource consumption. This
  option often used in Web applications.
• FIRST_ROWS Oracle uses a mix of cost-based
  access methods and heuristics to retrieve the
  first few rows fast. This option is often used in
  FORMS client/server applications.
• FIRST_ROWS_N Where N is 1, 10, 100, or 1000.
  Uses the cost-based access method to retrieve
  rows the most efficient way.

9
Cost-based Optimizer Process (continued)
10
Cost-based Optimizer Access Paths
11
Cost-based Optimizer Access Paths (continued)
12
Execution Plan Interpretation
13
Optimizer Parameters

• Optimizer Performance
• OPTIMIZER_DYNAMIC_SAMPLING
• OPTIMIZER_FEATURES_ENABLE
• OPTIMIZER_INDEX_CACHING
• OPTIMIZER_INDEX_COST_ADJ
• OPTIMIZER_MAX_PERMUTATIONS
• Optimizer Behavior
• CURSOR_SHARING
• DB_FILE_MULTIBLOCK_READ_COUNT
• HASH_AREA_SIZE
• HASH_JOIN_ENABLED
• PARTITION_VIEW_ENABLED
• QUERY_REWRITE_ENABLED
• SORT_AREA_SIZE
• STAR_TRANSFORMATION_ENABLED

14
Optimizer Hints
15
Optimizer Hints Syntax
16
Classification of VSQL Columns
17
Tips for Writing SQL Statements

• Here are some tips for writing SQL statements
• Follow SQL standards and syntax conventions
• Analyze tables and indexes regularly
• Use indexes and function-based indexes
• Create indexes on foreign keys columns
• Dont use indexes on small tables
• Use OR instead of UNION and vice versa

18
Tips for Writing SQL Statements (continued)

• Use parallel execution
• Use table joins instead of simple subqueries
• Use NOT EQUAL (!) or NOT IN operator instead of
  NOT EXISTS operator or vice versa
• Use Oracle hints
• Use UNION ALL instead of UNION
• Use an indexed column when counting rows

19
Preventing Slow Queries

• Here are some tips for avoiding problematic
  queries
• Watch out for Cartesian product statements
• Avoid full table scans on large tables
• Use SQL standards and conventions to reduce
  parsing
• Avoid joining too many tables
• Monitor VSESSION_LONGOPS
• Use the SHARED_CURSOR parameter
• Consider a Rule-based Optimizer goal
• Avoid unnecessary sorting

20
Preventing Slow Queries (continued)

• Monitor browned indexes
• Carefully create compound indexes
• Monitor query statistics
• Use different tablespaces for tables and indexes
• Consider table partitioning
• Use literals in the WHERE clause
• Be aware of outdated statistics

21
Using Outlines

• Outlines enable explain plan stability
• Use the OPTIMIZER_FEATURES_ENABLE parameter
• The CREATE ANY OUTLINE privilege is required
• Outlines creation methods include
• Automatically
• Manually

22
Summary

• Oracle Optimizer is an algorithm that determines
  the most efficient way to execute a SQL statement
  and retrieve data
• The OPTIMIZER_MODE initialization parameter can
  be configured for the whole system, whereas the
  OPTIMIZER_GOAL parameter sets the optimizer mode
  for a session
• The OPTIMIZER_GOAL is set by issuing ALTER
  SESSION, which impacts the current session only
• The Rule-based Optimizer (RBO) determines the
  most efficient way to execute a SQL statement by
  using a set of rules and a ranking system