《TheSelf-ManagingDatabaseGuidedApplicationandSQL》.ppt

The Self-Managing Database:Guided Application and SQL Tuning Mohamed ZiauddinConsulting Member of Technical Staff Oracle Corporation Oracle 10g – SQL Tuning Solution SQL Source: Top SQL SQL Tuning Options Conclusion 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 Recommended Sessions/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 Reminder – Please complete the OracleWorld online session survey. This was Session # 40173Thank you. 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 SQL Tuning Recommendations — Overview SQL Tuning Recommendations — Details 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 Implement Recommendations Implement Recommendations 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 DBMS_SQLTUNE PL/SQL Package Contains API for SQL Tuning Create Tuning Task Execute Tuning Task Display Advisor Recommendations Drop Tuning Task Create STS Populate STS Query STS Contents Drop