1 / 44

Turbocharge your Database: Use the Oracle Database 10 g SQLAccess Advisor

Session id: 40150. Turbocharge your Database: Use the Oracle Database 10 g SQLAccess Advisor. Dr. Lilian Hobbs Summary Management Product Manager Oracle Corporation. Database Performance. Could your database run faster? Do you have the best set of indexes?

easter
Download Presentation

Turbocharge your Database: Use the Oracle Database 10 g SQLAccess Advisor

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: 40150 Turbocharge your Database: Use the Oracle Database 10g SQLAccess Advisor Dr. Lilian HobbsSummary Management Product Manager Oracle Corporation

  2. Database Performance • Could your database run faster? • Do you have the best set of indexes? • Have you created any materialized views?

  3. Want to do this – But • Don’t have the time to gather the data

  4. Want to do this – But • Don’t have the time to gather the data • Workloads change so not sure what is best

  5. Want to do this – But • Don’t have the time to gather the data • Workloads change so not sure what is best • Not sure which indexes and mv’s to create

  6. Want to do this – But • Don’t have the time to gather the data • Workloads change so not sure what is best • Not sure which indexes and mv’s to create • Little experience creating mv’s but happy creating indexes

  7. SQL Access Advisor Overview Solution What indexes, MVs do I need to optimize my entire workload? SQL Access Advisor Component of CBO No expertrequired DBA Provides implementation script

  8. What does it Recommend SQL Access Advisor Workload Indexes Materialized Views Materialized Views Log

  9. Try the SQLAccess Advisor • Based upon the Oracle 9i Advisor • Part of Oracle Database 10g Server Mangeability • Generates recommendations • fast process, no long waits for information • choose recommendations to implement • automatically using OEM • generate a SQL script • Interfaces • completely new GUI interface • command line

  10. Steps to use SQLAccess Advisor Create a Task (transparent in Enterprise Manager) • optionally define parameters Create a Workload • optionally define workload parameters Generate Recommendations Optionally implement recommendations

  11. Start Here - Advisor Central

  12. What is the Task • Task is where all results are stored • Can keep tasks and their results • In Enterprise Manager the task is transparent • can give it a name • Configure a task as a template for new tasks (command line only)

  13. Workload • SQLAccess Advisor can use the following workload sources • Current contents of the SQL cache • Hypothetical (specify schema(s) in the database) • User-Defined (in a table) • Add SQL statements to a workload (command line only) • Oracle 9i Advisor workload • SQL Tuning Sets

  14. Workload Source using Wizard

  15. SQL Tuning Sets

  16. Filtering • Don’t have to use the entire workload • Filter by • Application or module name • Number of SQL statements • Queries during a specified time window • Username • Tables • must be in this list • not in this list

  17. Advanced Workload Options

  18. Recommendation Options

  19. Other Options

  20. Tuning Options • SQL statements will be tuned according to the resources they use

  21. Schedule the Job

  22. Final Review

  23. Monitor the Job

  24. Recommendations • SQLAccess Advisor wizard view by • Recommendations • SQL statements • Do not have to accept all recommendations • Can modify object names and locations

  25. Review the Recommendations

  26. Review Recommendations Pt 2 • Workload cost will not equal 100 because the cost is specific to a query

  27. View a Recommendation

  28. See the Statement

  29. Recommendations by SQL

  30. Recommendations by SQL Pt 2

  31. Tuning this SQL Statement

  32. Accepting Recommendations

  33. Show SQL Script

  34. Implement Recommendations

  35. The Command Line Interface

  36. Housekeeping • Don’t forget to • Change the expiration dates for tasks and workloads if you want to keep them over 30 days • Remove tasks and workloads when no longer required

  37. Features only available in the command line • Template tasks and workloads • Using or creating them • Adding/changing/deleting/searching SQL statements in a workload • Using an Oracle 9i workload • Quick Tune of a single SQL statement

  38. D E M O N S T R A T I O N SQLAccess Advisor

  39. Want to do this now? • Try the Oracle 9i Advisor • Functionality is very similar • Only recommends materialized views • Different interface • GUI available in OEM • Can use 9i workloads with SQLAccess Advisor

  40. Conclusion • SQLAccess Advisor can tune parts of the database that your DBA never knew was a problem • Easy and quick to use • Build up a pattern of usage over time • You choose what to implement • See what the SQLAccess Advisor could do to improve your system performance

  41. Reminder – please complete the OracleWorld online session surveyThank you.

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

More Related