Transcript Health Check your Database A Performance Tuning Methodology Robert Wijnbelt WHAT IS PERFORMANCE TUNING?  A combination of identifying and reacting to performance problems – Proactively preventing.

Health Check your
Database
A Performance Tuning Methodology
Robert Wijnbelt
1
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
2
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 SLA’s
3
MAJOR CAUSES OF PROBLEMS
 Poorly tuned application code: SQL &
PL/SQL
 Contention for internal Oracle
Resources: locks, latches buffers
 IO bottlenecks
 Inadequate hardware resources
4
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!
5
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?
6
‘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’
7
APPLICATION TUNING
Resolutions
 The greatest and most common
performance gains are attained
through SQL tuning
 A relative effort is modifying
improper parameter settings
 Just as important is resolving
management of database space and
data layout
8
4 Step Action Plan
For a Database Healthcheck
9
Step 1: Optimize the
Application Workload
 Effectively use the Oracle Optimizer
– Set optimizer mode to: RULE, COST or
CHOOSE
 Optimize SQL Statements
–
–
–
–
Set Optimizer initialization parameters
Determine SQL Code needs optimization
SQL Coding Best Practices
PL/SQL coding Best Practices
10
Step 2: Reduce Contention




Identify and reduce Latch contention
Identify and reduce Lock contention
Optimize Redo Log configuration
Reduce other internal contention
11
Identify and reduce Latch contention

Types of Latches in the SGA that suffer most contention:
– Buffer Cache Latches, caused by:
• SQL statements with very high logical or physical I/O, due to
unselective indexes (large index range scans) or many full table scans.
• DBWR not keeping up with the dirty workload, which forces the
foreground process to hold the latch longer looking for a free buffer.
• Undersized buffer cache.
– Library Cache Latches
• Misses on this latch occur when SQL is executed at very high rates.
There is little you can do to reduce the load on this latch, although
using private rather than public synonyms (or even direct object
references such as OWNER.TABLE) can help.
• Use bind variables in your code
12
Identify and reduce Lock contention
 Often caused in the application code
and Data model:
–
–
–
–
Code for Concurrent use
Index Foreign Key relations
Reduce Ad-Hoc querying
Try to reduce Updates
13
Optimize Redo Log configuration
 Use Fast disks
– avoid RAID 5, use 0+1 or Filesystem level
replication
 Multiplex Your RedoLogs
 Use NOLOGGING table operations
 Use multiple Disks for different
Group members
 Adjust Log Buffer size
14
Reduce Shared Pool contention
 Rewrite SQL
 Pin PL/SQL objects or SQL cursor objects in the shared
pool, use the DBMS_SHARED_POOL.KEEP
 Fully qualify tables and objects with the schema owner
name. This will eliminate some reparsing requirements.
 Increase the shared pool size when shared cursors are
being flushed out
 Decrease the shared pool size when the application does
not use bind variables and when cursors are not shared
and reused.
15
Reduce Shared Pool contention continued
 consider setting the parameter cursor_sharing = force
(first available in 8.1.6). When this parameter is set,
Oracle replaces embedded literal values with bind
variables prior to parsing the statement, to avoid
additional hard parses for SQL statements that differ
only by literal values.
 Add cursors to stored procedures/packages. Stored
procedures/packages can be pinned in the shared pool,
which prevents them from being flushed and
subsequently reparsed.
 Use the same bind variable length and array size in SQL
statements. When an SQL statement with different bind
variable lengths is used throughout an application, it
cannot be shared.
16
Step 3: Physical IO
Optimization







Tune Sorting parameters (Sort_Area)
Tune SQL (avoid Full Table Scans)
Reduce Row Chaining and Migration
Balance IO
Increase number of datafile devices
Implement OFA
Increase DB Block size
17
Step 4: Consider Best Practices
 Tune Top Down
– Start with Network, IO, Instance and Database design
before tuning SQL
 Set Benchmarks
– Identify some SQL statements, and rerun them as test
– Identify and monitor critical modules
 Quantify the problem and the expectations.
 Make sure there is not some problem with the
infrastructure, get everyone involved.
 Involve the developers in your tuning efforts.
18
Best Practices continued
 Understand all the features of the database
you are using. Use as many standard Oracle
provided functions as possible.
 Learn how to generate and read execution
plans.
 Tune to reduce logical IO’s. Reducing the
number of LIO’s will reduce both overall CPU
usage as well as physical IO requirements.
Generally, index usage will reduce LIO
requirements, but sometimes the reverse it
true.
19
Best Practices continued
 Consider that sometimes writing PL/SQL can
ultimately improve performance over SQL.
 If you have lots of large queries doing full
table scans, make sure that parallel query is
configured correctly.
 If Oracle is performing many hash joins, make
sure the database parameters associated with
hash joins (e.g. hash_area_size) are set
correctly.
20
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
21
What does it do?
 Collects Metrics using PL/SQL packages at
a user defined interval
 Stores that information in a repository
 Allows you to run analysis against any
number of those stored collections
 Produces problems, recommendations,
solutions, reports, and goals for tuning
22
WHAT IS QUEST CENTRAL
 An integrated comprehensive
solution for Oracle DBAs
– Real-time and Historical Diagnostics and
Analysis
– Space Management
– SQL Tuning
– Database Administration
23
THANK YOU
FOR LISTENING
Register for a Free Database Health Check
http://www.quest.com/healthcheck/
Robert Wijnbelt
24