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 Oracle Performance tuning: Real world sample

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 Oracle Performance tuning: Real world sample

4. Report date Report date Source document Source document Quantity Quantity Unit cost Unit cost Ageing Ageing 28th Feb 31st Mar Receipt 1 Receipt 1 10 each 10 each $800 $800 32 days 1 day 28th Feb 31st Mar Receipt 2 Receipt 2 9 each 9 each $880 $880 1 day 32 days 31st Mar Receipt 3 10 each $800 30 days Functional overview: Inventory and Cost Management data 27th Feb 28th Feb 01st Mar Receipt 1, $800 per each, +10 each Receipt 2, $880 per each, +10 each Issue from Receipt2, -1 each Current date: onhand quantities and costs Receipt 3, $800 per each, +10 each t Oracle Performance tuning: Real world sample

5. Dec Jan Feb Onhand quantities/current costs transactions t 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 Oracle Performance tuning: Real world sample

6. Existing implementation • Main insert command to end partitioned table • Further fields detalisation using complex calculation algorithms • Calculated fields are populated by update commands Oracle Performance tuning: Real world sample

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 Oracle Performance tuning: Real world sample

8. Purchase order Receipt Inter-Organisation transfer Disassembling Recursion detalisation Oracle Performance tuning: Real world sample

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) Oracle Performance tuning: Real world sample

10. Cascade INSERT commands Table3 Merge Partition for MAR-2010 Table1 Table4 Table2 Table5 Oracle Performance tuning: Real world sample

11. ROWCOUNT > 0 No Grab all Yes Table2 Purchase order Receipt Inter-Organisation transfer Disassembling Bulk processing recursion Oracle Performance tuning: Real world sample

12. Tuning results • Overall number of block reads decreased by 14 times • Elapsed time on Production decreased by 6 times Oracle Performance tuning: Real world sample

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 .. Oracle Performance tuning: Real world sample

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 Oracle Performance tuning: Real world sample