Advanced Oracle DB tuning

1. Advanced Oracle DB tuning • Performance can be defined in very different ways (OLTP versus DSS) • Specific goals and targets must be set => clear recognition of success • Factors include response time and throughput • Oracle specific tuning

2. Response time • Sum of service time + wait time • Processing strategy has key effect on response time • Eg: Sequential processing • In a queue, wait time for a job = sum of service time of all previous jobs • If queue gets longer parallel processing required

3. response time in Sequential and Parallel processing Wait time jobs Service time time jobs

4. System Throughput • Amount of work completed in a given time • Reduced service time = increased throughput • Increase in resources = reduced response time • Under load, contention increases… • Service may remain the same • But queues get longer • In practice, wait time raises exponentially • O/S scheduler • DB dispatcher Wait time arbitration contention

5. Critical resources • CPU, memory, I/O capacity, network broadband • Capacity (how many?) • Demand (how many?) • Wait time (how long?) • Consumption (how much?) • Software issue as much as hardware issue • Quality of design / programming • Prioritise jobs Performance loss Resource shift demand

6. Performance adjustments • Reduce consumption (use fewer resources & reduce I/O per transac.) • Demand (reschedule / redistribute work) • Capacity (increase / relocate res. – eg: move to parallel proc, increase mem.)

7. Problems for DBAs • Many performance parameters are set at design stage => limited scope to adjust • Important targets set at outset => bottlenecks can be identified • Administer trade-offs • More $$$ • Else reschedule to limit contention • Properly manage user expectations • Eg DB versus network problems • Never sacrifice ability to recover data!

8. Performance tuning • Always better pro-active =>DBA in development team • On-going tasks minimised by good planning • Service time – marginal improvements only • Eg SOCRATE case study • Oracle prioritised tuning steps for application dev.

9. Oracle tuning methodology • Tune business rules • Tune data design • Tune application design • Tune logical structure • Tune database operations • Tune access paths • Tune memory allocation • Tune I/O and physical structure • Tune resource contention • Tune platform

10. Business rules • Normally other direction, but sometime business rule changed for better perf. • Also, IT deployment strategy • Distributed • Centralised • Stick to high level analysis of req. => more freedom (!?) • Cheque printing versus direct deposit • Threshold value for automatic approuval • More flexibility in design

11. Data Design • Structure for data consistency AND performance • Denormalisation • FAReports • Summary values • Data “hot spot”

12. Application Design • In view of DB • In view of Oracle • Data entry acceleration + Refer to business rules and notes on critical db operations

13. Logical DB structure • Mostly indexing • partitioning • Also locking strategy

14. DB operations • Post relational DBs – eg: oracle • Extended SQL functions • New DB functions • See Oracle stats on query execution • Also pl/sql server side programming better than application code • Query optimiser may provide clues • Eg: Temporary index on non key attribute

15. Memory allocation • Dynamic process in Oracle • Shared pool • Parameters can be set manually • Make sure to keep decent SGA • See notes on DB creation

16. I/O and physical design • Use multiple disks + parallel controllers • Optimise block size (see notes on DB creation) • Use extents large enough for indexes • Avoid use of pctincrease in OLTP tables

17. Resource contention • Block contention • Shared pool contention • Lock contention • All these can be diagnosed with Oracle stats

18. Platform • Need to talk to specialist • Eg: oracle staff • Different for different OS • Cache size • Paging strategies etc…

19. Application • Always start with specific objectives else never achieve anything! • Max response times for inquiry • maximum processing time for document • Picking list • Invoice • Month end • Goals may conflict, DBA arbitrates

20. Create repeatable tests • SQL statements • New versus old in SQL + • SQL Trace enabled • Trial and error in a trial environment • Use multiple scenarios to test effect of each change • Also test in combination • Test for scalability (growth)

21. Keep records and automate testing • Write recording into your scripts (table) • Run scripts on a timer • Measure against objectives and past perf. • Stop when goals achieved

22. Oracle diagnostic tool • Explain plan • Oracle trace • Can be run in combination to compare actuals to estimates

23. Oracle Enterprise Manager • Common interface for all utilities • Diagnosis + implementation of changes • See table 17.4

24. Benchmarking • Use in product selection • Vendor bias? • Env tuned for test only • Not repeatable without access to unlimited resources • Hidden cost of table maintenance outside the test. • Cited by every DB vendor on this planet • Creation of the TPC (Transaction Processing Performance Council) • TPC – C: order entry benchmark • TPC – H and TCP – R: decision support benchmark • TPC – A: web transaction benchmark • Open source DB benchmark