1 / 44

The Self-Managing Database: Guided Application and SQL Tuning

Session id: 40713. The Self-Managing Database: Guided Application and SQL Tuning. Mohamed Ziauddin Consulting Member of Technical Staff Oracle Corporation. Agenda. SQL Tuning Challenges Automatic SQL Tuning Overview Usage Scenarios High load SQL tuning Custom SQL workload tuning

sumayah-buthainah
Download Presentation

The Self-Managing Database: Guided Application and SQL Tuning

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. Session id: 40713 The Self-Managing Database:Guided Application and SQL Tuning Mohamed ZiauddinConsulting Member of Technical Staff Oracle Corporation

  2. Agenda • SQL Tuning Challenges • Automatic SQL Tuning Overview • Usage Scenarios • High load SQL tuning • Custom SQL workload tuning • User Interface • Enterprise Manager • DBMS_SQLTUNE PL/SQL package • Conclusion

  3. Manual SQL Tuning Challenges How can I selecthigh-load SQL? How can I tune high-load SQL? High-LoadSQL DBA DBA SQL Workload

  4. Manual SQL Tuning Challenges • 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 • Each SQL statement is unique • Potentially large number of statements to tune • Never ending task • SQL workload always evolving • Plan regressions

  5. An Example Tuning Scenario Problem: Incorrect Optimizer Mode Selection Manual Tuning Get explain plan Examine query objects and their sizes Review and compare explain plan statistics with execution statistics (stored in V$SQL view) Identify that it is a “first rows” issue because only recent data is ever displayed despite large history being queried Contact application vendor Produce test case for vendor Get a patch with “first rows” hint from the vendor Install the patch in next maintenance cycle

  6. Oracle 10g– SQL Tuning Solution Application & SQL Management Storage Management System Resource Management Space Management Backup & Recovery Management Database Management Database Control Intelligent Infrastructure

  7. Oracle 10g Automates the SQL Tuning Process I can do it for you ! ADDM DBA High-LoadSQL SQL Workload SQL Tuning Advisor

  8. Agenda • SQL Tuning Challenges • Automatic SQL Tuning Overview • Usage Scenarios • High load SQL tuning • Custom SQL workload tuning • User Interface • Enterprise Manager • DBMS_SQLTUNE PL/SQL package • Conclusion

  9. Automatic SQL Tuning Overview 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

  10. Automatic Tuning Optimizer (ATO) • It is the query optimizer running in tuning mode • Uses same plan generation process but performs additional steps that require lot more time • It performs verification steps • To validate statistics and its own estimates • Uses dynamic sampling and partial executions to validate • It performs exploratory steps • To investigate the use of new indexes that could provide significant speed-up • To analyze SQL constructs that led to expensive plan operators

  11. Statistics Analysis 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

  12. Statistics Analysis • Motivation • Statistics are key input to the query optimizer • Their availability and accuracy is very important • In Oracle10g, the Automatic statistics collection maintains statistics up to date… • But it may not be enabled or properly configured! • The ATO verifies statistics that it needs/uses • Generates auxiliary information to compensate for missing or stale statistics • Generates recommendations to gather statistics where appropriate

  13. SQL Profiling 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

  14. SQL Profiling • Motivation • Empower the query optimizer to find a better plan • The query optimizer has time constraints • Makes compromises while finding right plan • The ATO is allowed a lot more time • Uses the time to gather customized information about the SQL statement, known as SQL Profile • Builds a SQL Profile and recommends it • Once implemented, SQL Profile is used by the query optimizer to generate a well-tuned plan

  15. SQL Profiling Flow SQL Profiling submit create Optimizer (Tuning Mode) SQL Profile SQL TuningAdvisor use After … output submit Optimizer (Normal Mode) Well-Tuned Plan DatabaseUsers

  16. SQL Profile • It contains auxiliary information collected by the ATO for a SQL statement • Customized optimizer settings • Based on past execution history (e.g., first_rows vs. all_rows) • 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 • It doesn’t require any change to the SQL text • Ideal for Packaged Apps • It is persistent • Works across shutdowns and upgrades

  17. Access Path Analysis 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

  18. Access Path Analysis • Motivation • Adding an index may significantly improve the performance of a SQL statement • Problem: A critical access path is missing • Index not created or mistakenly dropped • ATO explores the use of new indexes • Recommends an index if it provides a major performance boost • Also recommends to run SQL Access Advisor to get comprehensive index analysis based on a workload • SQL Access Advisor also uses this analysis mode

  19. SQL Structure Analysis 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

  20. SQL Structure Analysis • Motivation • Help application developers identify poorly written SQL statements • Suggest restructuring of SQL for efficiency • Problem categories • Semantic changes of SQL operators (e.g., use UNION ALL instead of UNION) • Subject to user acceptance of new result • Syntactic changes to predicates on indexed columns (e.g., remove type mismatch in column = :bind) • Design issues (e.g., add missing join predicate to eliminate a large Cartesian join)

  21. Agenda • SQL Tuning Challenges • Automatic SQL Tuning Overview • Usage Scenarios • High load SQL tuning • Custom SQL workload tuning • User Interface • Enterprise Manager • DBMS_SQLTUNE PL/SQL package • Conclusion

  22. SQL Tuning Usage Scenarios Automatic Selection AWR High-load SQL ADDM SQL Sources Manual Selection AWR SQL Tuning Advisor Cursor Cache SQL Tuning Set(STS) Filter / Rank User-defined

  23. SQL Tuning Set (STS) • Motivation • Enable user to tune a custom set of SQL statements • It is a new object in Oracle10g for capturing SQL workload • It stores SQL statements along with.. • Execution context: parsing user, bind values, etc. • Execution statistics: buffer gets, CPU time, elapse time, number of executions, etc. • It is created from a SQL source • Sources: AWR, cursor cache, user-defined SQL workload, another STS

  24. SQL Tuning Set Benefits • Allows selective, on-demand, custom SQL workload tuning • It simplifies tuning of a large number of SQL statements • It is persistent • Provides a common infrastructure for dealing with SQL workloads • Can be used as a source for different tuning tasks

  25. Agenda • SQL Tuning Challenges • Automatic SQL Tuning Overview • Usage Scenarios • High load SQL tuning • Custom SQL workload tuning • User Interface • Enterprise Manager • DBMS_SQLTUNE PL/SQL package • Conclusion

  26. Enterprise Manager Interface • Launch SQL Tuning Advisor from a SQL Source page • ADDM Finding page, or • Top SQL page, or • SQL Tuning Set (STS) page • View SQL Tuning Recommendations • Implement SQL Tuning Recommendations

  27. SQL Source: ADDM Finding

  28. SQL Source: Top SQL

  29. SQL Source: SQL Tuning Set

  30. SQL Tuning Options

  31. Enterprise Manager Interface • Launch SQL Tuning Advisor from a SQL Source page • ADDM Finding page, or • Top SQL page, or • SQL Tuning Set (STS) page • View SQL Tuning Recommendations • Implement SQL Tuning Recommendations

  32. SQL Tuning Recommendations — Overview

  33. SQL Tuning Recommendations — Details

  34. Enterprise Manager Interface • Launch SQL Tuning Advisor from a SQL Source page • ADDM Finding page, or • Top SQL page, or • SQL Tuning Set (STS) page • View SQL Tuning Recommendations • Implement SQL Tuning Recommendations

  35. Implement Recommendations

  36. Implement Recommendations

  37. Agenda • SQL Tuning Challenges • Automatic SQL Tuning Overview • Usage Scenarios • High load SQL tuning • Custom SQL workload tuning • User Interface • Enterprise Manager • DBMS_SQLTUNE PL/SQL package • Conclusion

  38. DBMS_SQLTUNE PL/SQL Package • Contains API for SQL Tuning Tuning Task Management STS Management • Create STS • Populate STS • Query STS Contents • Drop STS • Create Tuning Task • Execute Tuning Task • Display Advisor Recommendations • Drop Tuning Task SQL Profile Management • Accept SQL Profile • Drop SQL Profile • Alter SQL Profile Attribute

  39. Manual Tuning Get explain plan Examine query objects and their sizes Review and compare explain plan statistics with execution statistics (stored in V$SQL view) Identify that it is a “first rows” issue because only recent data is ever displayed despite large history being queried Contact application vendor Produce test case for vendor Get a patch with “first rows” hint from the vendor Install the patch in next maintenance cycle Automatic Tuning Run SQL Tuning Advisor Implement SQL profile Conclusion Problem: Incorrect Optimizer Mode Selection

  40. Recommended Sessions/Demos Technical Sessions Campground Demos • Automatic SGA Memory Management (Tuesday, 5 PM, Room 103) • The Invisible Oracle: Deploying Oracle DB in Embedded Environments (Wednesday, 4:30 PM, Room 103 • Automatic Health Monitoring (Thursday, 11:00 AM, Room 103 • Proactive Performance Management • Automatic Memory Management • Proactive Space Management • Invisible Installation & Deployment • Automatic Storage Management • Easy Upgrade

  41. Reminder – Please complete the OracleWorld online session survey. This was Session # 40173Thank you.

  42. Q & Q U E S T I O N S A N S W E R S A

More Related