Tuning your applications with the Quest Central Performance pack

1
Health Check your Database A Performance tuning
Methodology with Quest Central
Azeem Mohamed Product Marketing Manager
2
WHAT IS PERFORMANCE TUNING?

• A combination of identifying and reacting to
  performance problems
• Proactively preventing such problems from
  occurring
• Key responsibility for Oracle DBAs
• Ensures that service levels are being met
• Avoids costly hardware upgrades

3
Why is performance tuning important?

• At the core of business logic, and at the core
  of business data for most production
  applications, is a relational database management
  system
• Ray Paquet of Gartner, July, 2002
• No access no data no business
• Includes poor performance, missed SLAs

4
MAJOR CAUSES OF PROBLEMS

• Poorly tuned application code SQL PL/SQL
• Contention for internal Oracle Resources locks,
  latches buffers
• IO bottlenecks
• Inadequate hardware resources

Gartner, July 2002
5
Procrastination?

• Only when the application and RDBMS is tuned can
  the possibility of adding hardware be considered
• Don Burleson
• Number one resolution to performance issues in
  past three years was to upgrade hardware, add
  memory and add disk.
• Problems still persist!

6
Health Check - Best Practices?

• Where are the most common problems past and
  present?
• How do I resolve those problems?
• How much benefit can I get from fixing the
  problems identified?
• How much effort is involved in resolution?
• What can I do to prevent future issues?

7
Tuning Methodology Best Practices

• Identify issues happening now
• Resolve quickly
• Find problems in the past
• Resolve methodically
• How to prevent future occurrences
• Set Goals
• What and How to tune

8
Using the Health Check
9
Database TuningStep by Step

• Check for resource-intensive SQL
• Check for contention
• Optimise IO
• Apply best practices

10
Looking for Problem SQL

• Best place to start is in the VSQL view
  showing all cached SQL statements
• Look for statements that
• Consume a large proportion of the buffer gets
• Have a relatively high buffer_get/executions ratio

11
Tuning the SQL

• Using tkprof or Quest Central SQL tuning,
  identify operations that have a particularly high
  rowcountthese steps may be tuning opportunities
• If the rowcount exceeds the number of rows in the
  table or index you know you have problems

12
Examining Contention

• Look at the values in the table VSYSTEM_EVENT
• Eliminate any idle events (SQLNet waiting for
  client, etc)
• Calculate the amount of time spent waiting for
  the remaining events.

13
Abnormal Events

• The following events should not contribute
  significantly to the total wait time
• Latch free
• Buffer busy
• Free buffer
• Write complete
• Enqueue
• Parse time (from vsysstat)
• Log switch
• Log buffer

14
Buffer Busy Waits

• Most typically occur when a table has too few
  freelists and is subject to concurrent inserts
• This shows up as data block waits in vsysstat
  not Freelist waits
• Solution is simply to add more freelists

15
Free Buffer and Write Complete Waits

• Free buffer waits occur when a session wants to
  bring a block from disk into the cache, but has
  to wait for an unmodified buffer to become
  available
• Write complete waits occur when a session wants
  to update a block that is currently in the
  process of being written to disk
• In both cases, database writer configuration is
  probably responsible

16
Configuring Database Writers

• Only the database writer is allowed to write to
  database files
• If you have too few database writers, then the
  DBWR cannot write as fast as user sessions can
  make changes
• Implement asynchronous IO (preferably) or
  configure multiple database writersat least one
  per disk

17
Log Switch

• When a redo log fills, Oracle switches to the
  next log
• The switch cannot occur if the new log has not
  yet been archived or checkpointed
• Following best practice for redo log layout can
  avoid this

18
Best Practices for Redo Logs

• Size the redo logs so that log switches do not
  occur too frequently (5 minutes)?
• Put redo logs on dedicated devices
• Alternate redo logs between two devices (so that
  while one is being written, the archiver can be
  reading the other)
• Create enough redo logs so that even if the
  archiver falls behind during peak processing,
  users are not affected

19
Log Buffer waits

• These occur when a session wants to make a redo
  log entry, but no space is available in the
  buffer
• Could be caused by a too-small log buffer, but is
  usually the result of un-optimised redo layout
• Dont make the redo buffer too large
• Get waits for same reason

20
Reduce physical IO

• After tuning SQL and eliminating contention, the
  logical IO rate of your database will be nominal
  
• Try to minimize the percentage of this IO that
  requires a disk read
• Key techniques are
• Optimise your buffer cache size
• Deploy keep and recycle pools
• Keep scanned tables as small as possible

21
Optimise IO

• Basic principle is to allocate sufficient disks
  to support your workload and spread IO evenly
  across these disks
• Typical disk devices can do about 50 IO/second
• If you divide your physical IO rate by 50, you
  have a first cut estimate of the number of disks
  you will need
• Your disk devices need to support the IO rate as
  much as the physical storage requirements
• Its OK to have sparsely populated disks!

22
Adopt Best Practices

• Adopting best practices for database
  configuration may reduce your current overhead,
  and also reduce the risk of future performance
  problems
• The database health check will recommend many of
  these
• Using true temporary tables
• Setting appropriate values for sequence caches
• Indexing foreign keys

23
Introducing
24
Quest Central Health Check

• A free service from Quest in which our
  consultants use Quest Central Performance
  management to diagnose the health of your
  database.
• In this presentation we will show how to do your
  own database health check.

25
What does it do?

• Software collects metrics using PL/SQL packages
  at a user defined interval
• Stores that information in a repository
• Allows you to run a packaged analysis against any
  number of those stored collections
• Produces outline of problems, recommendations,
  solutions, reports, and goals for tuning

26
How does it work technically?

• Begins with a profiling process to understand
  your specific database environment
• Runs collected stats through a rules engine to
  produce advice about where to tune and how to tune

27
Results

• A series of Reports, both technical and
  managerial
• Ranking of problems
• Recommendations
• Solutions
• Advice
• Goals
• An Iterative Tuning outline

28
Quest Central Detect, Diagnose Resolve
Database Application problems
29
Quest Central for Oracle

• An integrated database management tool that
  provides all the functionality that Oracle DBAs
  need to proactively manage their database
  environments all day, every day
• Database Administration
• Performance Diagnostics (Spotlight on Oracle)
• SQL Tuning
• Live Space Management

30
Quest Central for Oracle All THE SOLUTIONS YOU
NEED

• Detect
• Alert notification and reporting
• Diagnose
• Alert drill-downs
• Advice
• Analysis
• Resolve
• SQL Tuning
• Live Space Management
• Database Administration

31
SUMMARY Quest Central

• Improves productivity
• Single console solution for Oracle and DB2
  database environments
• Cross train staff for heterogeneous application
  support
• Speeds application response time
• Detect, Diagnose and resolve issues seamlessly
  from a single console
• Heightens availability
• Proactive management of performance related
  issues
• Resolve issues without users knowing there was an
  issue

32
THANK YOU FOR LISTENING
Register for a Free Database Health Check
http//www.quest.com/healthcheck/
Azeem Mohamed Product Marketing Manager