Improve Oracle database performance

oracle database performance improvement techniques and performance improvement in oracle database
NicolusNorton Profile Pic
NicolusNorton,Germany,Professional
Published Date:08-07-2017
Your Website URL(Optional)
Comment
Insert Picture Here DBA’s New Best Friend: Advanced SQL Tuning Features of Oracle Database 11g Peter Belknap, Sergey Koltakov, Jack RaittoThe following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.Agenda • SQL Tuning Challenges • Oracle Database 11g Solutions • Automatic SQL Tuning • Real-time SQL Monitoring • Partition Advisor • Q & ASQL Tuning Challenges • Oracle Database 10g introduced SQL advisors to simplify application and SQL tuning • Remaining challenges • SQL Tuning still reactive • Painful to find and investigate long-running SQL • Partitioning excluded from schema optimization advice • Oracle Database 11g solutions • Automatic SQL Tuning • Real-time SQL Monitoring • Partition Advisor component of SQL Access AdvisorAutomatic SQL Tuning Insert Picture Here The Self-Managing DatabaseChallenges of Manual SQL Tuning • Requires expertise in several domains • SQL optimization: adjust the execution plan • Access design: provide fast data access • SQL design: use appropriate SQL constructs • Time consuming • Plans are complicated • Each SQL statement is unique and each execution can be different • Potentially large number of statements to tune • Testing proposed changes is labor-intensive • Many possible ways to a solution • Never ending task • SQL workload always evolving • Plan regressionsSimplifying SQL Tuning SQL Tuning Advisor, since Oracle Database 10g SQL Tuning Automatic Tuning Optimizer Recommendations SQL Tuning Advisor Statistics Gather Missing or Analysis Stale Statistics SQL Create a SQL Profiling Profile DBA Access Path Add Missing Analysis Indexes SQL Structure Modify SQL Analysis ConstructsImprovements in Oracle Database 11g Better SQL Profiling SQL Tuning Automatic Tuning Optimizer Recommendations SQL Tuning Gather Missing or Stale Statistics Advisor Statistics Analysis SQL Profiling Create a SQL Profile • Fix potential regression – show verified after upgrade benefit • Verify benefit through DBA test-execution Access Path Add Missing Indexes Analysis SQL Structure Modify SQL Constructs AnalysisTesting SQL Profiles (1) Measuring actual benefit with test-execution Naïve: Execute in Order Finish, P2 wins P P 2 1 But what if P1 never completes? Timeout P 1 It would be great to run them concurrently…. P 1 P P2 wins, kill P1 2 But then I take 2 CPUs, and N in the general case…Testing SQL Profiles (2) Measuring actual benefit with test-execution Solution: Tournament Execution P Round 1: P 2 1 15 sec 15 sec Round 2: P P 2 1 16 sec 30 sec Your winner, with a knockout in the second round, P 2SQL Tuning in Oracle Database 10g End-to-end Workflow Evaluate Implement Recommendations Workload DBA DBA Generate Recommendations one hour DBA Invoke ADDM Advisor SQL Tuning AWR Advisor SQL Tuning Candidates A good end-to-end solution, but manual intervention is requiredAutomatic SQL Tuning in Oracle 11g The Self-Managing Database Implement Test SQL Profiles SQL Profiles Workload Generate Recommendations Choose Candidate SQL Tuning SQL Candidates one It’s Automatic week View Reports / Control AWR Process DBAPicking Candidate SQL (1) S1, 10 minutes Week’s Top SQL, S2, 8 minutes Ordered by DB AWR S3, 5 minutes Time S4, 1 minute I could just pick from the top down… But I will miss SQLs with important hotspots Let’s try a more balanced approach: AWR Weekly Daily Hourly Average Exec OK, but where do I start? yyy Picking Candidate SQL (2) AWR Weekly Daily Hourly Average Exec 20% 10% 5% 65% Candidate List Eventually we need one list to tune from: merge the buckets. All buckets are not created equal: focus on the week, but don’t forget about the others. Focus on the SQLs we have not seen recently: Don’t re-tune SQLs if nothing has changedTuning Flow Tuning activities per SQL Candidate SQLs – Store findings, exec stats – Fetch next SQL Accept Profile Tune SQL – Fix potential regressions – Require 3X benefit in CPU and IO time – Look for indexes, statistics, as with standard tuning – Still recommend if 3X Test Profile – Tournament competitionFocus on SQL Profiles First step in automating SQL tuning Auto-testing/implementing is limited to profiles because: • No lengthy, expensive set-up process (building an index takes time) • Private to the current compilation • No change to user SQL (does not change semantics) • SQL-level recommendation, can be effectively tested • Easily reversed by the DBA Testing is done for regular SQL Tuning Advisor tasks as wellAutomatic SQL Tuning Defaults Sensible defaults with flexible configurations • Out-of-the-box defaults: • Runs in each maintenance window (MAINTENANCE_WINDOW_GROUP) • SQL profiles are tested but not implemented • DBA can configure using EM: • Whether / When / How long it runs • Resources it uses • Whether it implements profiles • How many profiles it implementsAutomatic SQL Tuning Task