ITPub博客

首页 > 数据库 > Oracle > IZ0-053 Q279

IZ0-053 Q279

原创 Oracle 作者:abstractcyj 时间:2016-03-09 11:13:13 0 删除 编辑
279.Identify the activities performed as part of the Automatic SQL Tuning process in the maintenance
window? (Choose all that apply.)
A. generating the SQL profile
B. testing and accepting the SQL profile
C. generating a list of candidate SQLs for tuning
D. adding tuned SQL plans into the SQL plan baseline
E. tuning each SQL statement in the order of importance
F. generating baselines that include candidate SQLs for tuning
Answer: ABCE

官方文档:
https://docs.oracle.com/cd/B28359_01/server.111/b28274/sql_tune.htm

Once automatic SQL tuning begins, which by default runs for at most one hour during a maintenance window, the following steps are performed:


Identify SQL candidates in the AWR for tuning.


Oracle Database analyzes statistics in the AWR and generates a list of potential SQL statements that are eligible for tuning. These statements include repeating high-load statements that have a significant impact on the system. Only SQL statements that have an execution plan with a high potential for improvement will be tuned. Recursive SQL and statements that have been tuned recently (in the last month) are ignored, as are parallel queries, DMLs, DDLs, and SQL statements with performance problems that are caused by concurrency issues. The SQL statements that are selected as candidates are then ordered based on their performance impact. The performance impact of a SQL statement is calculated by summing the CPU time and the I/O times captured in the AWR for that SQL statement in the past week.


Tune each SQL statement individually by calling the SQL Tuning Advisor.


During the tuning process, all recommendation types are considered and reported, but only SQL profiles can be implemented automatically.


Test SQL profiles by executing the SQL statement.


If a SQL profile is recommended, test the new SQL profile by executing the SQL statement both with and without the SQL profile. If the performance improvement improves at least threefold, the SQL profile will be accepted (when the ACCEPT_SQL_PROFILES task parameter is set to TRUE). Otherwise, only the recommendation to create a SQL profile will be reported in the automatic SQL tuning reports.


Optionally implement the SQL profiles provided they meet the criteria of threefold performance improvement.


Note that other factors are considered when deciding whether or not to implement the SQL profile. For example, a SQL profile is not implemented if the objects referenced in the SQL statement have stale optimizer statistics. You can identify which SQL profiles have been implemented automatically as their type will be set to AUTO in the DBA_SQL_PROFILES view.


If SQL plan management is used and there is already an existing plan baseline for the SQL statement, a new plan baseline will be added when a SQL profile is created. As a result, the new and improved SQL execution plan will be used by the optimizer immediately after the SQL profile is created. For information about SQL plan management, see Chapter 15, "Using SQL Plan Management".

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8520577/viewspace-2051718/,如需转载,请注明出处,否则将追究法律责任。

上一篇: IZ0-053 Q499
下一篇: IZ0-053 Q473
请登录后发表评论 登录
全部评论
曾从事java方向开发多年。近年已经转入数据库方向。主要擅长SQL优化,Oracle数据库问题诊断,Oracle备份与恢复等。服务于医药物流,医院等行业

注册时间:2010-01-26

  • 博文量
    555
  • 访问量
    836917