You are using SQL Tuning Advisor (STA) to tune SQL workload in your database. Which four types of recommendations do you get from Automatic Tuning Optimizer (ATO) via the STA? (Choose four.)
A.
recommendation to create indexes
B.
recommendation to use stored outlines
C.
recommendation to create a SQL profile
D.
recommendation to gather relevant statistics
E.
recommendation to denormalize the schema
F.
recommendation to restructure SQL statements
Explanation:
For the A answer see the point n3.
For the C answer see the point n2.
Oracle 10g allows the optimizer to run in tuning mode where it can gather additional information and make recommendations about how specific statements can be tuned further. This process may take several minutes for a single statement so it is intended to be used on high-load resource-intensive statements.
In tuning mode the optimizer performs the following analysis:
1. Statistics Analysis – The optimizer recommends the gathering of statistics on objects with missing or stale statistics. Additional statistics for these objects are stored in an SQL profile.
2. SQL Profiling – The optimizer may be able to improve performance by gathering additional statistics and altering session specific parameters such as theOPTIMIZER_MODE. If such improvements are possible the information is stored in an SQL profile. If accepted this information can then used by the optimizer when running in normal mode. Unlike a stored outline which fixes the execution plan, an SQL profile may still be of benefit when the contents of the table alter drastically. Even so, it’s sensible to update profiles periodically. The SQL profiling is not performed when the tuning optimizer is run in limited mode. The SQL profile doesn’t recommend to create a SQL profile.
3. Access Path Analysis – The optimizer investigates the effect of new or modified indexes on the access path. It’s index recommendations relate to a specific statement so where necessary it will also suggest the use of the SQL Access Advisor to check the impact of these indexes on a representative SQL workload.
4. SQL Structure Analysis – The optimizer suggests alternatives for SQL statements that contain structures that may impact on performance. The implementation of these suggestions requires human intervention to check their validity.Access Path Analysis – The optimizer investigates the effect of new or modified indexes on the access path. It’s index recommendations relate to a specific statement so where necessary it will also suggest the use of the SQL Access Advisor to check the impact of these indexes on a representative SQL workload.
Not E: Denormalizing would not be useful.