Oracle Performance tuning: Real world sample

1
Oracle Performance tuning Real world sample

• Sergey Porokh
• Oakton
• Oracle Technical Consultant

2
Agenda

• Functional overview of real world task
• Existing technical architecture and
  implementation overview prior to Performance
  Tuning
• Bottlenecks of existing solution
• Techniques to work around performance issues
• Results of tuning
• Couple more techniques for PL/SQL programs
  optimisation

3
Functional overview Ageing of Inventory stocks

• Program prepares permanent data for custom Ageing
  Discoverer report within Oracle Applications
• Program is launched for closed financial periods.
  Say at 15th March it is run for 28th February to
  create February data
• Once period is closed relevant financial data
  cannot be added/erased/modified

4
Functional overview Inventory and Cost
Management data
27th Feb
28th Feb
01st Mar
Receipt 1, 800 per each, 10 each
Issue from Receipt2, -1 each
Receipt 2, 880 per each, 10 each
Current date onhand quantities and costs
Receipt 3, 800 per each, 10 each
t
5
Existing technical architecture

• Data on a given date Onhand quantites/costs
  transaction data up to end period date
• Data is stored at denormalised table partitioned
  by Period

6
Existing implementation

• Main insert command to end partitioned table
• Further fields detalisation using complex
  calculation algorithms
• Calculated fields are populated by update commands

7
Bottlenecks of existing implementation

• Multiple UPDATEs
• Nested loops forcing
• Complex recursion algorithm that treated small
  groups of data
• No checkpoints saved to restore the data already
  gathered/calculated in case of abortions

8
Recursion detalisation
9
Techniques to work around performance issues

• Cascade INSERT commands to interim tables
  UPDATE commands/Nested Loops forcing removal
• BULK processing recursion and storing its results
  at a permanent table
• Checkpoints
• Storage parameters tricks Nologging, Compress,
  pctfree 0, APPEND hint
• Stable execution plans techniques up to you
  (Stored Outlines, SQL Profiles, SQL Plan
  Baselines)

10
Cascade INSERT commands
Table3
Merge
Partition for MAR-2010
Table1
Table4
Table5
Table2
11
Bulk processing recursion
12
Tuning results

• Overall number of block reads decreased by 14
  times
• Elapsed time on Production decreased by 6 times

13
Appendix what else can be applied for OLTP

• RETURNING INTO clauses (BULK COLLECT option is
  available)
• a) Replace
• update t set f1 where n
• Select f1 into l_f1 from t where n
• By 1 clause
• update t set f1 where n returning f1 into
• b) update t set f1 where complex condition
  returning n bulk collect into ..

14
Appendix what else can be applied for OLTP

• MULTI TABLE INSERTS
• a) Data conversion tasks denormalised table to
  few normalised tables
• b) Cache INSERT as subquery statement results
  avoiding RETURNING INTO restrictions applied for
  subquery INSERTs