Title: Oracle Performance tuning: Real world sample
1Oracle Performance tuning Real world sample
- Sergey Porokh
- Oakton
- Oracle Technical Consultant
2Agenda
- 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
3Functional 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
4Functional 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
5Existing 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
6Existing implementation
- Main insert command to end partitioned table
- Further fields detalisation using complex
calculation algorithms - Calculated fields are populated by update commands
7Bottlenecks 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
8Recursion detalisation
9Techniques 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)
10Cascade INSERT commands
Table3
Merge
Partition for MAR-2010
Table1
Table4
Table5
Table2
11Bulk processing recursion
12Tuning results
- Overall number of block reads decreased by 14
times - Elapsed time on Production decreased by 6 times
13Appendix 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 ..
14Appendix 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