Oracle Application Management Packs

Document Sample
Oracle Application Management Packs Powered By Docstoc
					   <Insert Picture Here>

Oracle Enterprise Manager 10g R4
Diagnose and Tune Your Oracle Database
Performance Problems Faster

•   Database Diagnostic Pack
•   Database Tuning Pack
•   Live Demonstration
•   Q&A
            Forrester Study
            ROI for Diagnostic and Tuning Packs

                Financial Results                   Non Risk-Adjusted              Risk-Adjusted

          ROI                                                          122%                     100%
          Payback Period                                         15 months                16 months

          Total costs (PV)                                    ($1,431,116)             ($1,431,116)
          Total costs savings benefit (PV)                      $3,176,040               $2,858,436

          Total net present value (NPV)                         $1,744,924                $1,427320

      •    Results summarized over 3 year period
      •    SMB sized sample organization
      •    Standard discounts applied (20%)
      •    Discount rate of 12% used to calculate PV and NPV
Database Diagnostic Pack
 Database Diagnostic Pack
• Part of the database management pack family
   • Diagnostic, Tuning, Configuration Management and Change
     Management, Provisioning and Data Masking Packs
• Provides unique functionalities
   • Industry’s first performance self-diagnosing engine
   • Lightweight, Automatic, Out-of-box performance data warehouse
   • Guided problem resolution
• Introduces a new, easy to use performance tuning methodology
   • Not just another tool
• Core functionality built right into the database server
   • Makes it most efficient, integrated and accurate
 Oracle’s Performance Methodology
• Methodology has evolved iteratively with each release
  • Wait events instrumentation (7.3)
  • STATSPACK (8i/9i), YAPP Methodology (8i)
  • Enhanced Time-Wait Model (10g)
• “Database Time (DB)” Based Methodology
• How to tune your system for a given workload?
  • Identify operations consuming most DB Time
  • Identify resource/capacity related bottlenecks
  • Reduce “DB Time” consumed for the workload
• Scoped analysis possible
  • Instance wide
  • Targeted for session/process or SQL, etc.
 Database Time

1. Total DB Time = Sum of Time Spent Processing All User Requests
          = Sum of Time (Running on CPU + Waiting for Resources)
2. DB Time/Sec (Avg. Active Sessions) = Total DB Time / Wall Clock Time
 Oracle’s Performance Methodology

• EM interface encodes Methodology + Best Practice
  • Workflows based on Methodology
  • Problem determination is few mouse clicks away
• How do you tune an Oracle database using
  Performance Page?
  • Simplest Answer: “Follow ADDM Recommendations”
  • Simple Answer: “Click on the biggest block of color”
• I’m a novice, where do I get started?
  • 2-Day+ Performance Guide
 Database Diagnostic Pack
 Key Features

• Automatic Workload Repository (AWR)
• Active Session History (ASH)
• Automatic Database Diagnostic Monitor (ADDM)
   Automatic Workload Repository (AWR )
  automatic                                              ADDM finds
 performance                                            top problems
statistics data                    MMON
  warehouse                               SYSAUX
                                                WR Schema
 …            In-memory
                                    7:00 a.m.   Snapshot 1
 BG                                 8:00 a.m.                Seven
            AWR                                 Snapshot 2
                     ASH            9:00 a.m.                days
          Statistics               10:00 a.m.
  FG                                            Snapshot 3
 FG                SGA                          Snapshot 4

                  8:00am                    DBA_%
Active Session History (ASH)

   Query for      Browse and     Add      Checkout
  Melanie Craft      Read      item to     using
    Novels         Reviews       cart    ‘one-click’

    DB Time
Active Session History (ASH)

           Query for                  Browse and                     Add         Checkout
          Melanie Craft                  Read                      item to        using
            Novels                     Reviews                       cart       ‘one-click’

             DB Time

Time              SID     Module                   SQL ID             State                   Event
                                                                                              db file sequential
7:38:26           213     Book by author           qa324jffritcf      WAITING                 read

7:42:35           213     Get review id            aferv5desfzs5      CPU

7:50:59           213     Add to cart              hk32pekfcbdfr      WAITING                 buffer busy wait

7:52:33           213     One click                abngldf95f4de      WAITING                 log file sync
     How Does ADDM Work?
                                            • Top Down Analysis Using
                 Snapshots in                 AWR Snapshots
              Automatic Workload
                  Repository                • Throughput centric - Focus on
                                              reducing time ‘DB time’
          Automatic Diagnostic Engine
            Self-Diagnostic Engine          • Classification Tree - based on
                                              decades of Oracle
                                              performance tuning expertise
                                            • Real-time results
                                                 •   Don’t need to wait hours to
                                                     see the results)
                                            • Pinpoints root cause
High-load        IO / CPU
                              RAC issues         •   Distinguishes symptoms
  SQL             issues
                                                     from the root cause
                                            • Reports non-problem areas
                 System         Network +
                Resource        DB config        •   E.g. I/O is not a problem
                 Advice          Advice
          ADDM for RAC

                 ADDM                                •   Automatic Database
                 11g                                     Diagnostics Manager (ADDM)
                                                         for Real Applications Cluster
                   Self-Diagnostic Engine
                                                     •   Performance expert in a box
                                                     •   Identifies performance
                                                         problems for the entire RAC
                                                         cluster database
                                                     •   Database-wide analysis of:
Instance-Level                                             •   Global cache interconnect
                                                           •   Global resource contention,
                                                               e.g. IO bandwidth, hot
                     Inst 2             Inst 3             •   Globally high-load SQL
 Inst 1                                                    •   Skew in instance response
       AWR 1               AWR 2             AWR 3
EM GC 10g support for 9i databases

  • EM GC 10g provides support for 9i monitoring and tuning
  • Similar performance workflows
       •   Same “Click on the biggest color block” methodology
       •   ASH simulation in middle tier
       •   No additional learning curve required
       •   ADDM and SQL Tuning Advisor functionality available in
           10g only
    • Performance bottlenecks can still be diagnosed
    • Bad SQL can be identified and assessed
    • TWP: Managing Oracle 8i and 9i with Enterprise
      Manager 10g Grid Control (available on OTN)
  9i Vs 10g: Performance Workflows
• 9i DB Perf. Page: ASH
 simulation displays wait categories
                                       • 10g DB Perf. Page
 like in 10g
 SQL Tuning: Oracle 9i

• Which SQL statements to tune?
  • Identify problematic SQL through Perf. Page Drill down
  • Historical SQL
• Run “Assess SQL” to get recommendations
• “Assess SQL” uses heuristics-based approach, indicates
  potential problems that can be addressed
  •   Checks for Missing/Stale Statistics
  •   Expensive full table scans/index scans
  •   Cartesian joins
  •   Rewrite if necessary (e.g., UNION to UNION ALL)
• SQL Details Page allows detecting plan change behavior
  • Historical SQL Details also available
 SQL Tuning: Oracle 9i

• Top SQL and Assessment
 SQL Tuning: Oracle 9i

• SQL Assessment Results
 SQL Tuning: Oracle 9i

• Assessment Results
 SQL Details Page: Oracle 9i
• 9i SQL Details Page is similar to 10g, it shows plan
  change behavior as in 10g
Database Tuning Pack
 Database Tuning Pack

• Part of the database management pack family
  • Diagnostic, Tuning, Configuration Management and Change
    Management Packs
• Provides unique, automatic and deterministic SQL
  tuning functionality
• Core functionality built in the Oracle Database 10g
  • SQL Tuning Advisor
  • SQL Access Advisor
• Requires diagnostic pack
    SQL Tuning & Access Advisor

           How do I tune                                SQL Tuning &
             my SQL                                    Access Advisors

                                                             of CBO
                              No expert
                       DBA    required
  SQL                                         script
                     <Insert Picture Here>

SQL Tuning Advisor
    SQL Tuning Advisor
                                          SQL Tuning
Automatic Tuning Optimizer                Recommendations
                             SQL Tuning
       Statistics             Advisor      Gather Missing or
       Analysis                            Stale Statistics

       Profiling                           Create a SQL

       Access Path                         Add Missing
       Analysis                            Indexes

       SQL Structure                       Modify SQL
    SQL Tuning Advisor
                                          SQL Tuning
Automatic Tuning Optimizer                Recommendations
                             SQL Tuning
       Statistics             Advisor      Gather Missing or
       Analysis                            Stale Statistics

       Profiling                           Create a SQL

       Access Path                         Add Missing
       Analysis                            Indexes

       SQL Structure                       Modify SQL
 SQL Profiles

 Contains auxiliary information specific to a given SQL
  –   Customized optimizer settings
       Based on past execution history
  –   Compensation for missing or stale statistics
  –   Compensation for errors in optimizer estimates
       Estimation errors occur due to data skews and correlations,
        complex filters and joins
 Doesn’t freeze the execution plan
 Doesn’t require any change to the SQL text
  –   Ideal for Packaged Apps
 Persistence: Works across shutdowns & upgrades
 Transportable across databases (10.2)
SQL Profiles
SQL Profiling

                submit                   create
                         (Tuning Mode)

   SQL Tuning                                          SQL
    Advisor                                           Profile

After …

                submit                    output
                         (Normal Mode)
  Database                                           Plan
   SQL Tuning Advisor Usage Scenario
Automatic Selection

                         ADDM         High-load SQL

SQL Sources       Manual Selection


                                                         SQL Tuning
Cursor Cache
                                        SQL Tuning Set
                      Filter / Rank         (STS)

      Automatic SQL Tuning in Database 11g
               Packaged          Customizable
                 Apps                Apps       • Automatically chooses high-load
                                                  SQL based on response time,
                      High-Load SQL               throughput and frequency criteria
                           AWR                  • Automatically tunes SQL by
Nightly           Automatic SQL Tuning            creating SQL Profiles
                SQL           Index, Stats,     • Automatically test executes the
              Profiling    Structure Analysis     tuned plans to verify improvement
                                                • Automatically implements greatly
            SQL Profiles                          (3X) improved plans (optional)
                                                • Automatically reports missing
            Test Execute                          access structures, stale stats, poorly
                                                  structured SQL
             Implement                          • Automatically runs during
                                                  maintenance window or manually
                               Advisor Report
              Well-tuned SQL
  SQL Profile Verification/ Testing

• SQL Profile recommendation can be tested before
• Profile Category allows user to test profile in private
  session before making it public

    task_name => ‘<tuning task name>’,
    category => ‘MY_CATEGORY’);


• Once satisfied, set category to DEFAULT
Enabling and Disabling Profiles
SQL Tuning Set (STS)

 New object in Oracle Database 10g for capturing &
  managing SQL workloads
 Stores SQL statements along with:
    –   Execution context: parsing user, bind values, etc.
    –   Execution statistics: CPU time, elapse time, # of
        executions, etc.
 Transportable across databases (10.2):
    –   Enables remote tuning
 Created from any SQL source
    –   AWR, cursor cache, user-defined workload, STS
     Remote SQL Tuning

 •    Performed to shield production system from
      performance impact of SQL advisors

Production System                                 Test System

                       Move SQL
                    Transportable STS

                                                  Tune SQL
                       Move SQL Profile
                      Transportable SQL Profile
SQL Access Advisor

                         SQL Access
 Workload                 Advisor

 Indexes    Materialized Views
                                 Materialized View Logs
 SQL Tuning and Access Advisor
 Functionality Comparison At-A-Glance

              Analysis Types            Performed By
Statistics                            SQL Tuning Advisor
SQL Profile                           SQL Tuning Advisor
SQL Structure                         SQL Tuning Advisor
Access Path: Indexes                  SQL Tuning/Access
Access Path: Materialized Views       SQL Access Advisor
Access Path: Materialized View Logs   SQL Access Advisor
       Data Masking – New in 10gR4!

What                                        LAST_NAME SSN              SALARY
• The act of anonymizing customer,          AGUILAR      203-33-3234     40,000
  financial, or company confidential data
                                            BENSON       323-22-2943     60,000
  to create new, legible data which
  retains the data's properties, such as    D’SOUZA      989-22-2403     80,000
  its width, type, and format.              FIORANO      093-44-3823     45,000

• To protect confidential data in test
  environments when the data is used        LAST_NAME SSN              SALARY
  by developers or offshore vendors         ANSKEKSL     111—23-1111     40,000
• When customer data is shared with         BKJHHEIEDK   111-34-1345     60,000
  3rd parties without revealing             KDDEHLHESA   111-97-2749     80,000
  personally identifiable information
                                            FPENZXIEK    111-49-3849     45,000

Database Diagnostics and Tuning Packs
  For More Information……
• Oracle Enterprise Manager 10g Grid Control home page

• Oracle Enterprise Manager 10g Release 4 Documentation

• Enterprise Manager 10g R4 live and recorded webcasts

• 2 Day + Performance Tuning Guide
• Data Masking Pack: Comply with privacy and confidentiality regulations by
  masking sensitive data
• Data Masking Online Tutorial
For any questions related to product or presentation slides, please send email to ent-
   Recorded Webcasts – Play on Demand
• Simplifying Configuration Complexities

• Lowering Management Costs through Grid Automation

• Managing Application Service Levels

• Managing third party products in your Oracle Environment

• Monitoring Microsoft in your Oracle Environment

• Diagnostics and Tuning Pack Deep Dive

• Application Diagnostics for Java (AD4J)

• Application Management Pack For E-Business Suite

• Managing Peoplesoft, Siebel and E-Business Suite

                       For Details Visit the Technical Webcasts page on OTN !