Oracle Performance tuning: Real world sample - PowerPoint PPT Presentation

About This Presentation
Title:

Oracle Performance tuning: Real world sample

Description:

Oracle Performance tuning: Real world ... Ageing of Inventory stocks Program prepares permanent data for custom Ageing Discoverer report within Oracle Applications ... – PowerPoint PPT presentation

Number of Views:268
Avg rating:3.0/5.0
Slides: 15
Provided by: Aprilp9
Category:

less

Transcript and Presenter's Notes

Title: 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
Write a Comment
User Comments (0)
About PowerShow.com