The flashcards below were created by user
Tralala
on FreezingBlue Flashcards.
-
Does the optimizer automatically add new plans to a fixed SQL plan baseline?
No
-
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?
FALSE
-
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?
OPTIMIZER_FEATURES_ENABLE
-
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
|
|