OCP 4 SQL Plan Management
Home > Flashcards > Print Preview
The flashcards below were created by user
on FreezingBlue Flashcards
. What would you like to do?
Does the optimizer automatically add new plans to a fixed SQL plan baseline?
What must be true of a SQL plan for the optimizer to use it?
It must be enabled and accepted.
In which two ways can you load SQL plan baselines?
Manually, using the DBMS_SPM package or automatically, by setting the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES initialization parameter to TRUE
If you use the DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE procedure to load a SQL plan baseline, what will be its default status?
The ACCEPTED status
What is a fixed SQL plan baseline?
A SQL plan baseline that contains one or more fixed plans
To what value does the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES initialization parameter default?
From which two sources can you directly load SQL execution plans into the SQL plan baseline using the DBMS_SPM package?
From an existing SQL Tuning Set (STS) or from the cursor cache
If you accept a SQL profile recommended by STA for a SQL statement that has a fixed SQL plan baseline, will the plan be added to the fixed plan baseline as a fixed plan or a non-fixed plan?
A non-fixed plan
Which initialization parameter can you set to force the optimizer to function as if the database was a 10g database?
The OPTIMIZER_FEATURES_ENABLE initialization parameter
Where is the SQL Management Base (SMB) stored?
In the data dictionary in the SYSAUX tablespace
Which tablespace must be online for the optimizer to access SQL plan baselines?
The SYSAUX tablespace
Which built-in package can you use to manually change the status of a baseline plan in the SQL Management Base (SMB) from not accepted to accepted?
The DBMS_SPM package
Which plans are included in the SQL plan baseline?
Plans that have been verified and accepted
What is the result of issuing the DBMS_SPM.CONFIGURE('SPACE_BUDGET_PERCENT',45) statement?
Warnings are written to the alert log when the SMB exceeds 45 percent of the SYSAUX tablespace's space.
Which SQL statements are considered if you call the EVOLVE_SQL_PLAN_BASELINE procedure without passing it any parameters?
All SQL statements with non-accepted plans in their SQL plan baselines
If you have the OPTIMIZER_USE_PLAN_BASELINES initialization parameter set to TRUE, what actions does the optimizer perform if it generates a plan for a SQL statement, but does not find a matching plan in the SQL plan baseline?
It adds the plan to the plan history, costs each plan in the SQL plan baseline that has been accepted, and selects the one that has the lowest cost.
What information is stored in the SQL Management Base (SMB)?
The statement log, plan history, plan baselines, and SQL profiles
If a fixed SQL plan baseline contains both fixed and non-fixed plans, which plan will the optimizer select?
The fixed plan that offers best performance
Which feature allows you to use SQL plan baselines to control evolving SQL execution plans and avoid execution plan regressions?
SQL Plan Management
Which parameter forces the optimizer to behave like an earlier version?
If OPTIMIZER_USE_PLAN_BASELINES=TRUE what happens if the Optimizer generates a plan, but no matching plan is found in the SQL plan baseline?
- The plan is added to the plan history
- Costs each SQL plan that's been accepted
- Selects the plan with the lowest cost
What would you like to do?
Home > Flashcards > Print Preview