Oracle Performance Tuning - Part I

1
Oracle Performance Tuning - Part I

• Krasen Paskalev
• Oracle 8i Certified DBA

2
Why are we here?(The list is generated by the
participants input.)

• Gain More knowledge
• Make work better
• Satisfy the customers requests for performance
  tiuning
• Proactive tuning
• Learn methods for performance bottleneck
  identification

3
Why Oracle performance tuning is important?

• The Oracle database is different from the
  competing products!
• Our customers and our competition cannot do it.

4
Oracle and otherwise
5
Oracle inside
6
Oracle has many switches allowing to tune its
work. But how to use them?

• To be a good tuner you need
• Knowledge
• Understanding
• Experience

7
Agenda

• Roles associated with tuning
• What is being tuned. Trade-offs in tuning
• Tuning steps and methods
• Tuning goals
• Common guidelines

8
Roles associated with tuning

• Users identify
• Developers change code
• DBA, SA find bottleneck, reconfigure
• Management change organisation, give money

9
Organisation tuning budget
Poor
Will be resolved
May be resolved if budget permits
Application Performance
Probably wont be resolved
Good
Non critical
Mission critical
Organisations dependence on application
10
What is being tuned?

• Response time
• Throughput
• Wait time
• Critical resources CPU, Memory, I/O, Network

11
Tuning steps

• Business rules
• Design and Architecture data and application
• Application SQL, PL/SQL

12
Tuning steps - continued

• Memory structures
• Disk I/O usage
• Resource contention
• OS
• Hardware

13
Tuning goals

• Tune SQL statement
• Tune process
• Tune specific application
• Tune all applications in an instance

14
Common guidelines

• Set clear tuning goals define success
• Create repeatable mesurable tests
• Try to automate
• Keep records
• Test hypothesys

15
Avoid common errors

• Test hypothesys one at a time
• Consult the tuning measures in a team
• Dont guess prove by test
• Ask users to describe problems not to explain
  them
• Stop when objectives are met or a little later

16
What next? (The list is generated by the
participants input.)

• Oracle optimizer
• Memory structures, Process structures, Manage
  Oracle processes
• Explain plan, access paths, Join methods
• Oracle configuration options initialization
  parameters.
• Design for performance
• Indexes
• Parallel execution
• Oracle specific feature for tuning
• Physical design and layout
• Connection pooling (Networking)