Title: Tuning your applications with the Quest Central Performance pack
1Health Check your Database A Performance Tuning
Methodology
Robert Wijnbelt
2WHAT 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
3Why 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
4MAJOR CAUSES OF PROBLEMS
- Poorly tuned application code SQL PL/SQL
- Contention for internal Oracle Resources locks,
latches buffers - IO bottlenecks
- Inadequate hardware resources
5Procrastination?
- 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!
6Health CheckBest 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?
7Tuning 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
8APPLICATION 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
94 Step Action Plan For a Database Healthcheck
10Step 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
11Step 2 Reduce Contention
- Identify and reduce Latch contention
- Identify and reduce Lock contention
- Optimize Redo Log configuration
- Reduce other internal contention
12Identify 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
13Identify 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
14Optimize Redo Log configuration
- Use Fast disks
- avoid RAID 5, use 01 or Filesystem level
replication - Multiplex Your RedoLogs
- Use NOLOGGING table operations
- Use multiple Disks for different Group members
- Adjust Log Buffer size
15Reduce 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.
16Reduce 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.
17Step 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
18Step 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.
19Best 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 IOs. Reducing the number
of LIOs 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.
20Best 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.
21Quest Central Health Check
- A free service from Quest in which our
consultants use Quest Central Performance
management to diagnose the health of your database
22What 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
23WHAT IS QUEST CENTRAL
- An integrated comprehensive solution for Oracle
DBAs - Real-time and Historical Diagnostics and Analysis
- Space Management
- SQL Tuning
- Database Administration
24THANK YOU FOR LISTENING
Register for a Free Database Health Check
http//www.quest.com/healthcheck/
Robert Wijnbelt