View the exhibit and examine the findings provided by the SQL Tuning Advisor for SELECT Statement.
A SWL plan baseline already exists for the execution plan.
What two methods can you use to ensure that an alternate plan becomes an accepted plan?
Exhibit:
A. Use the DBMS_SPM.ALTER_SQL_PLAN_BASELINE function.
B. Use the DBMS_SQLTUNE.CREATE_SQL_PLAN_BASELINE function.
C. Use the DBMS_SQLTUNE.CREATE_SQL_PLAN_BASELINE function and run the DBMS_STATS to manually refresh stale statistics.
D. Use the DBMS_SPM.LOAD_PLANS_FROM_SQLSET function.
Correct Answer: CD
Explanation/Reference:
C:To adopt an alternative plan regardless of whether SQL Tuning Advisor recommends it, call DBMS_SQLTUNE.CREATE_SQL_PLAN_BASELINE. You can use this procedure to create a SQL plan baseline on any existing reproducible plan.
D:LOAD_PLANS_FROM_SQLSET Function
This function loads plans stored in a SQL tuning set (STS) into SQL plan baselines. The plans loaded from STS are not verified for performance but added as accepted plans to existing or new SQL plan baselines. This procedure can be used to seed SQL management base with new SQL plan baselines.
Note:
*While tuning a SQL statement, SQL Tuning Advisor searches real-time and historical performance data for alternative execution plans for the statement. If plans other than the original plan exist, then SQL Tuning Advisor reports an alternative plan finding.
SQL Tuning Advisor validates the alternative execution plans and notes any plans that are not reproducible. When reproducible alternative plans are found, you can create a SQL plan baseline to instruct the optimizer to choose these plans in the future.
Incorrect:
Not A:ALTER_SQL_PLAN_BASELINE Function
This function changes an attribute of a single plan or all plans associated with a SQL statement using the attribute name/value format.
Usage Notes
When a single plan is specified, one of various statuses, or plan name, or description can be altered. When all plans for a SQL statement are specified, one of various statuses, or description can be altered. This function can be called numerous times, each time setting a different plan attribute of same plan(s) or different plan(s).
Reference:Oracle Database Performance Tuning Guide,Alternative Plan Analysis
|
Download Printable PDF. VALID exam to help you PASS.
|
 |
