OCP-1Z0-053-200题-64题-641

最新推荐文章于 2018-10-19 19:54:32 发布

原创最新推荐文章于 2018-10-19 19:54:32 发布 · 3.6k 阅读

CC 4.0 BY-SA版权

796 篇文章

订阅专栏

本文深入探讨了SQL调优顾问的工作流程及其在数据库性能管理与调优中的作用，包括自动SQL调优过程、SQL调优顾问的推荐类别（如统计分析、SQL剖析、访问路径分析、SQL结构分析）、SQL轮廓的构建与应用，以及如何通过SQL调优顾问推荐改进查询性能。

QUESTION 64

Which three functions are performed by the SQL Tuning Advisor? (Choose three.)

A. Building the SQL profile

B. Recommending optimization of materialized views

C. Checking query objects for missing and stale statistics

D. Recommending bitmap, function-based, and B-tree indexes

E. Recommending restructuring SQL queries that are using bad plans

Answer: ACE

Explanation/Reference:

Section: Managing Database Performance & Tuning

SQL Tuning Advisor Automatic SQL tuning is exposed through SQL Tuning Advisor.

SQL Tuning Advisor runs automatically during system maintenance windows as a maintenance task. During

each automatic run, the advisor selects high-load SQL queries in the database and generates

recommendations for tuning these queries.

SQL Tuning Advisor recommendations fall into the following categories:

- Statistics analysis

- SQL profiling

- Access path analysis

- SQL structure analysis

A SQL Profile contains additional statistics specific to a SQL statement and enables the optimizer to generate

a better execution plan. Essentially, a SQL profile is a method for analyzing a query. Both access path and

SQL structure analysis are useful for tuning an application under development or a homegrown production

application.

A principal benefit of SQL Tuning Advisor is that solutions come from the optimizer rather than external tools

(see "Overview of the Optimizer" on page 7-10). Thus, tuning is performed by the database component that is

responsible for the execution plans and SQL performance. The tuning process can consider past execution

statistics of a SQL statement and customizes the optimizer settings for this statement.

http://www.remote-dba.net/oracle_10g_new_features/tuning_advisor.htm

How the SQL Tuning Advisor Works:

The optimizer will work in the new tuning mode wherein it conducts an in-depth analysis to come up with a set

of recommendations, the rationale for them and the expected benefit if you follow the recommendations.When

working in tuning mode, the optimizer is referred to as the Automatic Tuning Optimizer (ATO).