Health Check your Database
1 / 24

Robert Wijnbelt - PowerPoint PPT Presentation

  • Uploaded on

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 such problems from occurring Key responsibility for Oracle DBAs

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'Robert Wijnbelt' - darrion

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Tuning your applications with the quest central performance pack

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 such problems from occurring

  • Key responsibility for Oracle DBAs

    • Ensures that service levels are being met

    • Avoids costly hardware upgrades

Why is performance tuning important
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

Major causes of problems

  • Poorly tuned application code: SQL & PL/SQL

  • Contention for internal Oracle Resources: locks, latches buffers

  • IO bottlenecks

  • Inadequate hardware resources


  • 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!

  • Health check best practices
    Health 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?

    Tuning methodology best practices
    ‘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’

    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

    Tuning your applications with the quest central performance pack

    4 Step Action Plan

    For a Database Healthcheck

    Step 1 optimize the application workload
    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

    Step 2 reduce contention
    Step 2: Reduce Contention

    • Identify and reduce Latch contention

    • Identify and reduce Lock contention

    • Optimize Redo Log configuration

    • Reduce other internal contention

    Identify and reduce latch contention
    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

    Identify and reduce lock contention
    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

    Optimize redo log configuration
    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

    Reduce shared pool contention
    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.

    Reduce shared pool contention continued
    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.

    Step 3 physical io optimization
    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

    Step 4 consider best practices
    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.

    Best practices continued
    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.

    Best practices continued1
    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.

    Quest central health check
    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

    What does it do
    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

    What is quest central

    • An integrated comprehensive solution for Oracle DBAs

      • Real-time and Historical Diagnostics and Analysis

      • Space Management

      • SQL Tuning

      • Database Administration

    Tuning your applications with the quest central performance pack



    Register for a Free Database Health Check

    Robert Wijnbelt