The flashcards below were created by user
on FreezingBlue Flashcards.
What does SQL Plan Management allow?
Prevention of performance regressions for sudden changes to execution plan of a SQL statement.
What can SQL Plan execution be affected by?
- Optimizer Version
- Application of SQL profiles
- Dropping of an index
- System changes
What produces SQL profiles?
SQL Tuning Advisor
How does SQL Plan Management work?
By creating SQL plan baselines known to be efficient.
What do the baselines created by SQL Plan Management do?
Preserve the performance of SQL statements regardless of changes to a system.
Where is the SQL Management base stored?
What is the SQL Management base comprised of?
- Statement logs
- Plan histories
- SQL plan baselines
- SQL profiles
What percentage of SYSAUX is used by SQL Management base by default?
What percentage of SYSAUX can be used by SQL Management base?
How is the space used by SQL Management base monitored?
By a weekly task
If SYSAUX is not available, what happens to SQL plan management and SQL tuning features?
They are no longer available
What will happen when the space defined for SQL Management base is exceeded?
An alert is generated
How long will an unused plan remain in SMB before being purged by default?
How are unused plans in SMB identified
By the LAST_EXECUTED timestamp
How can an unused plan be prevented from being purged from SMB be altered?
What is the range of time that plan_retention_weeks can be set?
5 to 523 weeks
Which view shows the current settings for SMB?
How can an alert from SQL Management base be cancelled?
- Increase SQL Management base limit
- Increase size of SYSAUX
- Change the limits for SQL Management base
What package is used to change the percentage limit of space in SYSAUX by SQL Management base?
Which view is used to look at current SQL Management base settings?
What happens when Automatic Plan Capture is enabled?
Oracle will create a maintain a plan history provided by the optimizer
What is captured in Automatic Plan Capture?
- SQL Text
- Bind variables
- Compilation Environment
What parameter enables Automatic Plan Capture and what is default?
From what sources can SQL plan baselines loaded manually?
- SQL Tuning Sets
- AWR Snapshots
How are SQL plan baselines plan loaded from a SQL Tuning set?
How are SQL plan baselines plan loaded from Cursor Cache?
What parameter enables the use SQL plan baselines, what is the default?
What is a Fixed SQL baseline plan?
- A plan where at least one enabled plan has FIXED attribute set to Yes.
- Used to limit possible plan
What happens when there are fixed and non-fixed plans for a given SQL baseline?
- Oracle will prefer the fixed plan even if the non-fixed plan has a lower cost.
- Oracle will not add plans to a fixes SQL plan baseline
What happens is SQL Tuning Advisor creates a profile for a fixed baseline plan?
- The profile will not be used...IT'S NOT FIXED
- Need to manually fix is by setting FIXED attribute to YES.
How are SQL Plan baselines displayed?