1 / 64

Simplified SQL Performance Management in Oracle Database 11g

Simplified SQL Performance Management in Oracle Database 11g. Pete Belknap.

ashanti
Download Presentation

Simplified SQL Performance Management in Oracle Database 11g

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Simplified SQL Performance Management inOracle Database 11g Pete Belknap

  2. The 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.

  3. Agenda • SQL Tuning Challenges • Oracle Database 11g Solutions • Automatic SQL Tuning • Improvements to SQL Tuning Advisor • Fully automating SQL tuning • Real-time SQL Monitoring • Track high response-time SQL • Find the most expensive plan operation • Q & A

  4. SQL 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 • Oracle Database 11g solutions • Automatic SQL Tuning • Real-time SQL Monitoring

  5. Automatic SQL Tuning <Insert Picture Here> The Self-Managing Database

  6. Challenges 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 regressions

  7. Simplifying SQL TuningSQL Tuning Advisor, since Oracle Database 10g SQL Tuning Recommendations Automatic Tuning Optimizer SQL Tuning Advisor Gather Missing or Stale Statistics Statistics Analysis SQL Profiling Create a SQL Profile DBA Add Missing Indexes Access Path Analysis SQL Structure Analysis Modify SQL Constructs

  8. Plan 1 Plan 1 Plan 3 Plan 3 Plan 1 Plan 3 Plan 2 Plan 2 Plan 2 ? SQL Profile ? ? ? ? Search Space SQL Profiling Technology Transparent SQL tuning No SQL Profile SQL Profiling Use SQL Profile(future executions) use ? ? add ? ? ? ? ? Search Space • Validates estimates using dynamic sampling and partial execution • Validates only relevant estimates

  9. AlternativePlanAnalysis SQL Profiling Technology (2) Trying alternative plans (new in 11g) Profile Selection (3) (1) Estimate Correction CorrectionFactors Corrections +O_F_E 10.2.0.3 Corrections +O_F_E 10.1.0.4 (2) Corrections +O_F_E 8.1.7 Alternate Plan Set • Try some interesting alternatives: plans from old releases • Feed correction factors into alternative plan selection • But which is the best?

  10. But what if P1 never completes? Timeout! P1 It would be great to run them concurrently…. P1 P2 P2 wins, kill P1! Testing SQL Profiles (1)Measuring actual benefit with test-execution (new in 11g) Naïve: Execute in Order P2 Finish, P2 wins! P1 But then I take 2 CPUs, and N in the general case…

  11. P2 P1 Round 1: 15 sec 15 sec P2 P1 Round 2: 16 sec 30 sec Testing SQL Profiles (2)Measuring actual benefit with test-execution Solution: Tournament Execution Your winner, with a knockout in the second round, P2!

  12. Testing SQL Profiles (3)Choosing appropriate metrics, comparison strategy • “Winner” and “Loser” depends on your point of view • Need a statistic that is repeatable and comprehensive. • ELAPSED_TIME: comprehensive, but includes row lock waits; I/O time depends on buffer cache state. Not repeatable. • CPU_TIME: very repeatable, but not comprehensive for I/O. • BUFFER_GETS: very repeatable, but ignores CPU expense; overly pessimistic for some plans • Combine the best elements of each • CPU_TIME should improve (most reliable statistic) • Benefit Reported: ratio of CPU_TIME + BUFFER_GETS*10ms Conservatively consider every buffer get to be an I/O, but allow large CPU improvements to overrule small buffer gets regressions

  13. Improvements in Oracle Database 11gBetter SQL Profiling SQL Tuning Recommendations Automatic Tuning Optimizer SQL Tuning Advisor Gather Missing or Stale Statistics Statistics Analysis SQL Profiling Create a SQL Profile • show verified benefit • Fix potential regression after upgrade • Verify benefit through test-execution DBA Access Path Analysis Add Missing Indexes SQL Structure Analysis Modify SQL Constructs

  14. Agenda • SQL Tuning Challenges • Oracle Database 11g Solutions • Automatic SQL Tuning • Improvements to SQL Tuning Advisor • Fully automating SQL tuning • Real-time SQL Monitoring • Track high response-time SQL • Find the most expensive plan operation • Q & A

  15. Evaluate Recommendations Implement DBA DBA Generate Recommendations DBA ADDM Invoke Advisor SQL Tuning Advisor SQL Tuning Candidates SQL Tuning in Oracle Database 10gEnd-to-end Workflow Workload one hour AWR A good end-to-end solution, but manual intervention is required

  16. Implement SQL Profiles Test SQL Profiles DBA Generate Recommendations Choose Candidate SQL SQL Tuning Candidates one week View Reports / Control Process AWR Improvements in Oracle Database 11gFully-Automated Tuning Workflow Workload It’s Automatic!

  17. AWR S1, 10 minutes Week’s Top SQL, Ordered by DB Time S2, 8 minutes Daily S3, 5 minutes S4, 1 minute Weekly Hourly Average Exec I could just pick from the top down… Let’s try a more balanced approach: AWR Picking Candidate SQL (1) But I will miss SQLs with important hotspots! OK, but where do I start?

  18. Daily Weekly Hourly Average Exec Candidate List Picking Candidate SQL (2) AWR 10% 20% 5% 65% • 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 changed!

  19. Tuning FlowTuning activities per SQL Candidate SQLs • Fetch next SQL • Store reporting data Accept Profile Tune SQL • If < 3X benefit, recommend for DBA consideration • Fix potential regressions • Look for indexes, statistics,as with standard tuning • Require 3X benefit • Both CPU and I/O times must improve Evaluate Profile • Tournament competition

  20. Focus 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 well!

  21. Automatic SQL Tuning DefaultsSensible 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 implements

  22. Automatic SQL Tuning Task

  23. Automatic SQL Tuning Configuration

  24. Automatic SQL Tuning Result Summary

  25. Automatic SQL Tuning Result Recommendations

  26. Automatically Tuned SQL Details Drilldown

  27. Conclusions • Manual SQL tuning is painful even for the experts • Oracle 10g SQL Tuning Advisor quickly gives DBA good choices • Oracle 11g Automatic SQL Tuning automates the process by making the easy decisions • DBA can control as much of the process as he wants

  28. Q & A

  29. Real-Time SQL Monitoring <Insert Picture Here> Shiningnew light on SQL Performance

  30. Single SQL Execution Problem:Managing High Response-Time SQLs • Monitoring: tracking high response-time SQL • What is that expensive SQL (ETL, DDL, batch, report, …) I started up to? • Do I have any high response-time SQL running on my OLTP system? • Any SQL executing parallel? • Investigating: why is this execution so expensive? • Plan has hundreds of operations -- where is the time being spent? • Why is a particular operation so expensive? • SQL runs parallel, is DOP appropriate? is there a skew?  What is going on inside a SQL execution???

  31. Single SQL Execution Solution: Real-time SQL MonitoringLooking inside the SQL • Enabled out-of-the-box with no performance impact • Automatically monitors SQL executions that: • consume more than 5 seconds of CPU or I/O time • are running parallel: PQ, PDML, PDDL • Monitors each execution independently • Exposes monitoring statistics at multiple levels • Global execution level • Plan operation level (Plan Tuning) • Parallel Execution level (PX Tuning) • Guides your tuning efforts

  32. How does it work? t = 5 t = 6 t = 7 • Update execution statistics in PGA continuously • After 5 seconds for serial / immediately for parallel, target for monitoring (reserve SGA space) • Push statistics to SGA every second • Separate entries for each Parallel Execution Server • Each execution of each SQL identifiable in ASH via execution key • PX Servers share an execution key, but have a different Session ID • Statistics available for at least 5 minutes • Not vulnerable to cursor age-outs PGA SGA

  33. New Statistics Exposed • For each SQL Execution (V$SQL_MONITOR): • Resource Consumption: ELAPSED_TIME, CPU_TIME, FETCHES, BUFFER_GETS, DISK_READS, DIRECT_WRITES, APPLICATION/CONCURRENCY/CLUSTER/USER_IO_WAIT_TIME, PLSQL/JAVA_EXEC_TIME • For each Plan Operation (V$SQL_PLAN_MONITOR): • Production: STARTS (#executions), OUTPUT_ROWS • Memory/Temp usage: WORKAREA_MEM, WORKAREA_TEMPSEG • For each second of session activity (V$ACTIVE_SESSION_HISTORY): • SQL Execution Key: SQL_ID, SQL_EXEC_START, SQL_EXEC_ID • Row source information: SQL_PLAN_LINE_ID/OPERATION/OPTIONS

  34. V$ AdditionsNew V$ Views added; existing views supplemented V$ACTIVE_SESSION_HISTORY(SAMPLE_TIME, SESSION_ID, SESSION_SERIAL#)With Execution Key, Plan Line ID/Operation DBA_HIST_ACTIVE_SESS_HISTORY(DBID, SNAP_ID, INSTANCE_NUMBER, SAMPLE_TIME, SESSION_ID, SESSION_SERIAL#) V$SQL_MONITOR(SQL_ID, SQL_EXEC_START, SQL_EXEC_ID) V$SESSION(SID, SERIAL#)With Execution Key V$SESSION_LONGOPS(SID, SERIAL#, OPNAME)With Execution Key, Plan Line ID/Operation V$SQL_PLAN_MONITOR(SQL_ID, SQL_EXEC_START, SQL_EXEC_ID, PLAN_LINE_ID) V$SQL_PLAN(SQL_ID, CHILD_NUMBER, PLAN_HASH_VALUE, ID)

  35. How do I use it? • 11g Enterprise Manager Grid Control (11.1.0.7 DB Control) • Additional reporting (available today): DBMS_SQLTUNE.REPORT_SQL_MONITOR • Get reports in HTML, XML, or Text

  36. Enterprise Manager Flow (1) SQL Details Monitoring Details Top Activity Session Details

  37. Enterprise Manager Flow (2) Monitoring Details Monitoring List

  38. SQL Monitoring List

  39. SQL Monitoring DetailsCore concepts

  40. SQL Monitoring DetailsCore concepts

  41. SQL Monitoring DetailsCore concepts

  42. SQL Monitoring DetailsCore concepts

  43. SQL Monitoring Details (Parallelism)Core concepts

  44. SQL Monitoring Details (Parallelism)Core concepts

  45. SQL Monitoring Details (Parallelism)Core concepts

  46. SQL Monitoring DetailsBig Plans

  47. SQL Monitoring DetailsBig Plans

  48. SQL Monitoring DetailsBig Plans

More Related