Compuware Corporation

1
Common tuning pitfalls of Oracles optimizers

• Mark Levis
• Senior Developer
• Compuware
• Mark.Levis_at_compuware.com

2
Introduction

• Who am I?
• Why am I here?
• What will be discussed?

3
Myths

• SQL tuned for RBO will run well in the
• CBO.

4
Myths

• SQL developers do not need to be
• retrained to write SQL for the CBO

5
Myths

• 8i and 9i do not support the RBO

6
Myths

• You cant run RULE and COST together

7
Myths

• Oracle says the CBO is unreliable and you
• should use RULE

8
Myths

• Hints cant be used in RULE

9
Optimizers

• RBO
• CBO

10
Optimizers

• What they use to make their decision
• Syntax
• Where clause conditions
• Tables Indexes
• Location of Tables (distributed)
• Hints
• Statistics Init.ora settings

11
RBO Rules

• 1 Single Row by Rowid
• 2 Single Row by Cluster Join
• 3 Single Row by Hash Cluster Key with Unique or
  Primary Key
• 4 Single Row by Unique or Primary Key
• 5 Clustered Join
• 6 Hash Cluster Key
• 7 Indexed Cluster Key
• 8 Composite Index

• 9 Single-Column Indexes
• 10 Bounded Range Search on Indexed Columns
• 11 Unbounded Range Search on Indexed Columns
• 12 Sort-Merge Join
• 13 MAX or MIN of Indexed Column
• 14 ORDER BY on Indexed Column
• 15 Full Table Scan

12
RBO Problems

• Incorrect driving table 40
• Incorrect index 40
• Incorrect driving index 10
• Using Order By index 10
• Instead of where

13
RBO

• When will the RBO be used?
• OPTIMIZER_MODE RULE
• CHOOSE and statistics are not present for all
  tables in SQL statement.
• Alter session has been issued
• RULE hint is present

14
Multiple Indexes

• Select col.
• From emp
• Where empno 2
• AND ename 1
• AND deptno MIS
• Indexes
• I_emp1 (empno) Unique or PK
• I_emp2 (ename, deptno) Non Unique

15
Merge Indexes

• Select col.
• From emp
• Where ename 1
• And empno 2
• And deptno MIS
• Indexes
• I_emp1 (deptno)
• I_emp2 (empno, ename)

16
Which Index?

• Select col.
• From emp
• Where ename 1
• And empno 2
• And deptno MIS
• Indexes
• I_emp1 (deptno)
• I_emp2 (empno, ename, salary)

17
Which Index?

• Select col.
• From emp
• Where ename 1
• And empno 2
• And deptno MIS
• Indexes
• I_emp1 (ename , deptno) April 23 2002 45950
• I_emp2 (ename , empno) April 23 2002 50000

18
Which Index?

• Select col.
• From emp
• Where ename LIKE 1
• AND empno 2
• AND deptno MIS
• AND salary gt 3
• Indexes
• I_emp1 (empno , deptno)
• I_emp2 (ename, deptno, salary)

19
Percentage of Columns

• Select col.
• From emp
• Where ename 1
• AND empno 2
• AND deptno MIS
• Indexes
• I_emp1 (empno , salary)
• I_emp2 (ename, deptno, salary)

20
Leading Columns

• Select col.
• From emp
• Where ename 1
• Indexes
• I_emp1 (empno , ename)
• I_emp2 (ename, deptno, salary)

21
Leading Columns

• Select col.
• From emp
• Where upper(ename) 1
• Indexes
• I_emp1 (empno , ename)
• I_emp2 (ename, deptno, salary)

22
Driving Table

• Unique or PK index
• operator on all columns of the index
• Higher of columns in the index
• Multi column index over multi single column
  indexes
• If more than one table has the same criteria
  listed above the table listed farthest from FROM

23
Condition Order

• The order of your where clause will effect the
  performance
• AND-OR merge of the indexes

24
Using Order By Index

• Check the types of Indexes available to the Query
  and adjust them.
• ORDER BY index is Unique
• Other Index is non-unique
• Create a concat index

25
CBO

• When will the CBO be used?
• OPTIMIZER_MODE CHOOSE
• CHOOSE and statistics are not present for any
  tables in SQL statement.
• Alter session set optimizer_mode (choose,
  first_rows or all_rows)
• CHOOSE, ALL_ROWS or FIRST_ROWS hint is present

26
Understanding the CBO

• Functionality
• Parse the statement
• Generate a list of all potential execution plans
• Calculate (estimate) the cost of each execution
  plan
• Select the plan with the lowest cost

27
Understanding the CBO

• Primary Key Unique Index
• Non-Unique Index
• Range evaluation (with bind variables)
• Histograms
• System Resource Usage
• Current Stats

28
Development VS. Production

• SQL runs well in development but not in
  Production.
• Simulate Stats.
• Copy Stats.

29
Skewed Data

• Oracle assumes an even distribution of data.
• Histograms

30
Analyze Wrong Data

• Tables were analyzed with incorrect data volumes.
• When does this occur?
• Table rebuilt
• Index added
• Migrate schema to production
• Analyze before a bulk load

31
Missing Stats

• Oracle will estimate the stats for you
• These stats are for this execution only
• Stats on Indexes

32
Index Selection

• CBO chooses an Inferior Index
• Index is Unique but specified as non-unique
• Try moving column order in the index
• OPTIMIZER_INDEX_COST_ADJ

33
Index Selection

• WHERE gb.code_combination_id b1
• AND gb.set_of_books_id b2
• AND gb.template_id b3
  
• AND gb.translated_flag b4
• AND gb.actual_flag
  b5
• AND gb.currency_code b6
• AND gb.period_name b7
• AND gcc.code_combination_id b8
• ..

34
Index Selection

• WHERE gb.code_combination_id b1
• AND gb.set_of_books_id b2
• AND gb.template_id between b3 and b4
• AND gb.translated_flag b4
• AND gb.actual_flag
  b5
• AND gb.currency_code b6
• AND gb.period_name b7
• AND gcc.code_combination_id b8
• ..

35
Joining too many tables

• The more tables the more work for the optimizer.
• Best plan may not be achievable

36
Joining too many tables

• Tables Permutations
• 1 1
• 2 2
• 3 6
• 4 24
• 5 120
• 6 720
• 7 5040
• 8 40320
• 9 362880

• Tables Permutations
• 10 3628800
• 39916800
• 479001600
• 6226020800
• 87178291200
• 1307674368000

37
Joining too many tables

• FROM
• pay_people_groups ppg, APPLSYS.FND_ID_FLEX_SEGMEN
  TS
• CFIFS, applsys.fnd_flex_values_tl cffvt,
  applsys.fnd_flex_values
• cffv, APPLSYS.FND_ID_FLEX_SEGMENTS LFIFS,
• APPLSYS.FND_FLEX_VALUES_TL LFFVT,
• APPLSYS.FND_FLEX_VALUES LFFV,
• PAY_COST_ALLOCATION_KEYFLEX PCAK,
• PAY_COST_ALLOCATIONS_F PCAF,
• FND_COMMON_LOOKUPS FCL, PER_PERIODS_OF_SERVICE
• PPOS, PER_PAY_PROPOSALS PPP, PER_JOBS
• PJ,PER_ALL_PEOPLE_F PPF2, PER_ALL_ASSIGNMENTS_F
• PAF, PER_ALL_PEOPLE_F PPF

38
Joining too many tables

• call count cpu elapsed
• ------- ------ -------- ----------
• Parse 1 1196.61 1273.44
• Execute 1 0.12 0.13
• Fetch 0 0.00 0.00
• ------- ------ -------- ----------
• total 2 1196.73 1273.57

39
Bind Variables

• Used to allow SQL Reuse in the cache
• BUT
• Prior to 9i --- Negative impact on execution plan

40
OR

• One little keyword.
• One big index selection impact

41
OR
42
OR
43
OR
44
Data Conversion

• Specifying the incorrect data type will suppress
  an index.

45
IN Lists

• SELECT empno FROM emp WHERE deptno IN (10,20,30)
• Rewritten as
• SELECT empno FROM emp
• WHERE deptno 10
• UNION ALL
• SELECT empno FROM emp
• WHERE deptno 20
• UNION ALL
• SELECT empno FROM emp
• WHERE deptno 30

46
Bitmap Indexes

• Can use large amounts of memory
• Use sparingly

47
Hints

• PL/SQL engine
• / HINT /
• 256 Character limit

48
Merge or Hash Join

• Hash Join can only be used with
• Merge joins work on inequality
• If all index columns are in the where clause a
  merge join will be faster

49
CBO fixes

• 8.0.4
• Ordered Nested loops
• Fast Full scans
• 8.05
• Many bug fixes
• 8.1.6
• Improved Histogram
• Partitions
• Nested Loop Processing

50
CBO Fixes

• 8.1.7
• Improved Partition handling
• Sub expression optimization
• 9.0.1
• Improved Index Joins
• Complex view merging
• Improved Bitmap index
• Subquery
• Push Join
• 9.0.2 Still looking at it

51
Init.ora

• Many things can change the way the optimizer
  works.
• We will discuss a small subset of these options.

52
OPTIMIZER_MAX_PERMUTATIONS

• Remember the too many joins?
• Default is 80,000
• Can lead to large Parse times.
• Altering can lead to non optimal plan selection.

53
OPTIMIZER_MAX_PERMUTATIONS

• call count cpu elapsed
• ------- ------ -------- ----------
• Parse 1 1196.61 1273.44
• Execute 1 0.12 0.13
• Fetch 0 0.00 0.00
• ------- ------ -------- ----------
• total 2 1196.73 1273.57

54
Init.Ora

• OPTIMIZER_SEARCH_LIMIT
• Non-Single row tables
• lt OPTIMIZER_SEARCH LIMIT1
• Permutations is larger of OPTIMIZER_MAX_PERMUTATI
  ONS or OPTIMIZER_SEARCH_LIMIT factorial
• gtOPTIMIZER_SEARCH_LIMIT
• Permutations is larger of (OPTIMIZER_MAX_PERMUTATI
  ONS or OPTIMIZER_SEARCH_LIMIT factorial )
• divided by (number of possible start tables
  1)

55
OPTIMIZER_SEARCH_LIMIT

• OPTIMIZER_MAX_PERMUTATIONS 10
• Number of non-single row tables 5
• Number of tables in query 5

56
OPTIMIZER_SEARCH_LIMIT

• Case 1 OPTIMIZER_SEARCH_LIMIT 5
• Number of non-single row tables gt
  OPTIMIZER_SEARCH LIMIT i.e. 5 gt 5
• Number of non-single row tables lt
  OPTIMIZER_SEARCH LIMIT1 i.e. 5 lt (51)
• Maximum number of permutations is larger of 10
  or 5! i.e. 10 or 128

57
OPTIMIZER_SEARCH_LIMIT

• Case 2 OPTIMIZER_SEARCH_LIMIT 4
• Number of non-single row tables gt
  OPTIMIZER_SEARCH_LIMIT i.e. 5 gt 4
• Number of non-single row tables gt
  OPTIMIZER_SEARCH_LIMIT i.e. 5 gt 4
• Maximum number of permutations is larger of 10 /
  number of start tables1
• 4! / number of start tables 1
• Assuming number of start tables 2 then larger
  of (10/3, 24/3)

58
OPTIMIZER_SEARCH_LIMIT

• Case 3 OPTIMIZER_SEARCH_LIMIT 3
• Number of non-single row tables gt
  OPTIMIZER_SEARCH_LIMIT i.e. 5gt 3
• Number of non-single row tables gt
  OPTIMIZER_SEARCH_LIMIT i.e. 5 gt 3
• Maximum number of permutations is larger of 10 /
  number of start tables 1
• 3! / number of start tables 1
• Assuming number of start tables 2 then larger
  of (10 / 3, 6 / 3)

59
OPTIMIZER_INDEX_CACHING

• Represents of blocks that can be found in the
  cache.
• Range 0 - 99
• Default is 0 implies that index access will
  require a physical read
• Should be set to 90

60
OPTIMIZER_INDEX_COST_ADJ

• Represents cost of index access to full table
  scans
• Range 1 10000
• Default is 100 Means index access is as costly
  as Full Table Scan
• Should be between 10 50 for OLTP and approx 50
  for DSS

61
OPTIMIZER_INDEX_COST_ADJ

• SELECT EVENT,
• AVERAGE_WAIT
• FROM VSYSTEM_EVENT
• WHERE EVENT LIKE db file s
• db file sequential reads / db file scattered
  reads

62
DB_FILE_MULTIBLOCK_READ_COUNT

• Setting too high can cause Full Table Scans
• Can adjust for this by setting OPTIMIZER_INDEX_COS
  T_ADJ

63
Other Parameters

• DB_KEEP_CACHE_SIZE
• DB_RECYCLE_CACHE_SIZE
• DB_BLOCK_HASH_BUCKETS
• _ UNNEST_SUBQUERY

64
Questions

• Any questions?
• Any answers?
• Anyone still awake?
• Thank you for coming today! Please fill out the
  evaluation form on your way out the door.

65
BETA

• We have several tools in BETA now please
• contact me for information
• Debugger
• .NET Debugger
• Tuner
• PL/SQL Profiler
• Mark.Levis_at_compuware.com