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 gt 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 gt limited scope to adjust
• Important targets set at outset gt 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 gtDBA 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. gt 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