1 / 18

Tam s Kerepes Senior Principal Trainer Oracle Hungary

amiel
Download Presentation

Tam s Kerepes Senior Principal Trainer Oracle Hungary

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. Welcome to the Grid open - 2282801Welcome to the Grid open - 2282801

    3. Oracle Database 10g Solution: Self-Managing Database

    4. Rule-Based Optimizer Obsolescence RBO is no longer supported: No bug fixes will be provided. Almost no regression testing of RBO for Oracle Database 10g CHOOSE and RULE values are no longer supported. ALL_ROWS is the default value for OPTIMIZER_MODE.

    5. Automatic SQL Tuning: Overview Automatic SQL Tuning automates the entire SQL tuning process and replaces manual SQL tuning. Optimizer modes: Normal mode Tuning mode or Automatic Tuning Optimizer (ATO) SQL Tuning Advisor is used to access the tuning mode. The tuning mode should be used only for high-load SQL statements.

    6. Application Tuning Challenges

    7. SQL Tuning Advisor: Overview

    8. Stale or Missing Object Statistics Object statistics are key input to the optimizer. ATO verifies object statistics for each query object. ATO uses dynamic sampling. ATO generates: Auxiliary object statistics to compensate for missing or stale object statistics Generates recommendations to gather object statistics where appropriate:

    9. SQL Statement Profiling Statement statistics are key input to the optimizer. ATO verifies statement statistics like: Predicate selectivity Optimizer settings (FIRST_ROWS versus ALL_ROWS) ATO uses: Dynamic sampling Partial execution of the statement Past execution history statistics of the statement Generates a profile if statistics were generated:

    10. Plan Tuning Flow and SQL Profile Creation

    11. SQL Tuning Loop

    12. SQL Tuning Advisor: Usage Model

    13. Database Control and SQL Tuning Advisor

    14. SQL Tuning Advisor: Options and Recommendations

    15. Database Home Page

    16. Database Performance Page

    17. Concurrency Wait Class: Drill Down

    18. Top SQL by Waits: Drill Down

More Related