1 / 24

Advanced Oracle DB tuning

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. Response time. Sum of service time + wait time

tait
Download Presentation

Advanced Oracle DB tuning

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related