Judicious use of Histograms for Oracle Applications Tuning

1
Judicious use of Histograms for Oracle
Applications Tuning

• John Kanagaraj
• Cisco Systems Inc.
• ora_apps_dba_y_at_yahoo.com

2
Learning Objectives

• As a result of this presentation, you will be
  able to know
• What Histograms are
• How Histograms influence SQL performance
• How EBS R12/11i uses Histograms
• How to configure Histogram collection in EBS
• A real life example in an EBS environment

3
Speakers Qualifications

• IT Architect _at_ Cisco Systems Inc
• Executive Editor for IOUGs SELECT Journal
• Oracle ACE
• Author and Technical Editor
• Oracle Database 10g Insider Solutions
• Oracle Wait Interface
• Oracle RAC Handbook
• Skilled in Oracle Database and Applications
  Tuning
• Link up with me on LinkedIn ?

4
Presentation Agenda

• Introduction to Optimizer and Histograms
• Histograms influence on SQL Execution
• Caveats and overheads for Histograms
• Applicability to EBS R12/11i
• Collection methods in EBS R12/11i
• A real-life example
• Wrap up, Q A

5
The Oracle Optimizer

• The Optimizer twins - CBO and RBO
• CBO available since 7.0
• RBO de-supported in Oracle Db 10g
• EBS11i first to use CBO
• Optimizer determines data access path for SQL
  statements
• CBO uses Cost of access (Table/Index Stats)
• RBO uses a set of rules (Set of 15 Rules)

6
CBO

• CBO characteristics
• Requires Table, Index, Column statistics
• Histograms introduced in Oracle 7.3 (more later)
• Caters to new object types and accesses
• Flexible access path
• Traced via the 10053 event
• Statistics needs to be kept up to date
• Sensitive to Optimizer parameters and algorithms
• When she is good, she is very, very good..

7
What are Histograms?

• Histograms describe data skew
• Columns with non-unique, repeating keys
• Few distinct values forming sizeable portion of
  the row count
• Typically on secondary keys such as Organization
  IDs, Type IDs
• Histograms quantify spread of distinct values
• Described in terms of buckets
• Height-balanced or Width-balanced

8
How are Histograms used?

• Simplest example FTS vs Indexed read
• Large table indexed by few distinct values
• Histogram enables choice of FTS vs Index
• CBO considers total cost of I/O
• Complex example Multi-table join
• Histograms help determine Table join order
• Join producing least number of rows first
• Histograms greatly influence Selectivity
• Helps tune SQL without code change

9
Histograms and the DD

• Collected using DBMS_STATS (all levels)
• Exposed via the following views
• DBA_TAB_HISTOGRAMS
• DBA_PART_HISTOGRAMS (partitioned tab)
• Equivalent ALL_ and USER_ views
• Based on HISTGRM and HIST_HEAD
• Uses memory in the Data Dictionary Cache
  (SHARED_POOL_SIZE)
• Affects parse time (minimal)

10
Caveats

• Version specifics
• Histograms considered only when hard-coded values
  used in Oracle 8i
• Histograms considered at first parse even if bind
  variables are used in versions gt Oracle 9i
• Bind variable peeking in Oracle 9i/10g
• Initial parse determines future paths!
• Widely varying and unpredictable performance
• Performance could vary across RAC nodes
• Controlled by _optim_peek_user_binds
• 10g increased this problem (METHOD_OPT default)
• Fixed in Oracle 11g using Adaptive Cursor
  Sharing

11
Histograms and EBS

• Histograms are used in EBS R12/11i
• But we collect them differently! (good)
• Stats gathering Gather ltLevelgt Statistics
• Calls FND_STATS which calls DBMS_STATS
• Guided collection of Histograms
• Seeded table FND_HISTOGRAM_COLS
• Contains Application ID, Table, Column and
  Maximum Number of Histograms (254)
• Inserted into by (internal use only)
  FND_STATS.LOAD_HISTOGRAM_COLS
• Initial seed data additions via patches

12
Histogram candidates

• Use not-so-well documented FND_STATS.CHECK_HISTOGR
  AM_COLS
• Algorithm from code
• Checks leading cols in non-unique indexes
• Single value occupies gt1/75th of sample
• select decode(floor(sum(tot)/(max(cnt)75)),0,
• 'YES','NO') HIST
• from (select count(col) cnt , count() tot
• from tab sample (S)
• where col is not null group by col)
• Count of at least 3000 recommended

13
A real life example

• Inventory Report Performance
• Widely varying run-times
• 1.5 hours for some orgs, 2-3 minute for others
• Differentiating parameter Org ID
• Complex SQL handling various parameters
• Top CPU consumer when run
• Large tables involved (CST_ITEM_COSTS,
  RCV_SHIPMENT_LINES, MTL_ONHAND_QUANTITIES_)

14
A real life example (contd.)

• The investigation
• Large amount of PIO and LIO (VSESSTAT)
• Used 10046 Level 12 extended SQL Trace
• Level 12 (84) shows both bind (4) and wait
  events (8)
• Determine objects accessed and count
• TKPROF results
• call cnt cpu elapsed disk query current
  rows
• ----- --- ------- ------- ----- ------ -------
  -----
• Parse 2 0.04 0.06 0 0 0
  0
• Execute 1 0.01 0.01 0 0 0
  0
• Fetch 1 5102.81 5425.30 37506 226144503 5
  47
• ----- --- ------- ------- ----- ---------- ---
  ------
• total 4 5102.86 5425.37 37506 226144503 5
  47

15
A real life example (contd.)

• The investigation (continued)
• Extended trace shows exact fetches and values
• Refer Optimizing Oracle (Cary Millsap) and the
  OWI book by Kirti Deshpande and others
• Metalink notes 39817.1 and 171647.1
• BINDS 18
• bind 0 dty1 mxl128(40) mal00 scl00 pre00
  oacflg03 oacfl210 size128 offset0
• bfp01b16e70 bln128 avl03 flg05
• value"558"
• ltsnippedgt
• WAIT 18 nam'file open' ela 0 p10 p20 p30
• WAIT 18 nam'db file sequential read' ela 4
  p1432 p2169056 p31
• WAIT 18 nam'db file sequential read' ela 1
  p172 p2195769 p31
• WAIT 18 nam'db file sequential read' ela 1
  p173 p2197743 p31
• WAIT 18 nam'db file scattered read' ela 2
  p165 p295737 p38
• FETCH 18c510281,e542530,p37506,cr226144503,c
  u5,
• mis0,r47,dep0,og4,tim2464590823

16
A real life example (contd.)

• The investigation (continued)
• SQL to show segments accessed
• select segment_type, segment_name from
  dba_extents
• where file_id 432
• and 169056 between block_id and block_id blocks
  1
• Indexed read of MTL_ONHAND_QUANTITIES
• Majority was indexed reads of the 2.6 Gb
  CST_ITEM_COSTS table
• Indexed read is good, but not when it is
  excessive
• This was probably due to wrong access path for
  that ORG_ID (confirmed via Execution plan) i.e.
  skew in data patterns for ORG_IDs

17
A real life example (contd.)

• The Solution
• Connecting the dots (patterns Data, parameter,
  I/O)
• Ran FND_STATS.CHECK_HISTOGRAM_COLS
• execute fnd_stats.check_histogram_cols('PO.RCV_SHI
  PMENT_LINES,
• INV.MTL_ONHAND_QUANTITIES_DETAIL,BOM.CST_ITEM_COST
  S',
• factorgt75,percentgt99,degreegt4)
• Showed need for Histograms on some of the columns
  
• Added rows into FND_HISTOGRAM_COLS
• Reran Gather Table Stats for changed tables
• Modified Report to use Literal values (ok since
  this is parsed only once)
• No functional or SQL structural change

18
A real life example (contd.)

• The Result
• call cnt cpu elapsed disk query current rows
• ---- ---- ---- ------- ----- ------ ------- ----
• Parse 2 0.04 0.02 3 10 0 0
• Execute 1 0.00 0.01 0 0 0 0
• Fetch 1 30.71 101.28 42423 913573 32 47
• ---- ---- ---- ------- ----- ------ ------- ----
• total 4 30.75 101.31 42426 913583 32 47
• Dramatic reduction in CPU, LIO and runtime
• Increased PIO Most of it was FTS to large tables
• Final Runtime 1 ½ to 3 minutes for all Orgs!!!

19
Conclusion

• Knowledge of CBO and Histograms
• Business knowledge (data patterns, usage)
• Use of tracing tools
• Connecting the dots
• Knowledge of sparsely documented features
• Test, Test, Test! (And Validate!!)
• Judicious use of Histograms
• Validate results, again using tracing

20
Q A