Which three are the valid statements in relation to SQL plan baselines?

Which three are the valid statements in relation to SQL plan baselines? (Choose three.)

Which three are the valid statements in relation to SQL plan baselines? (Choose three.)

A.
The plans can be manually loaded to the SQL plan baseline.

B.
The plans in the SQL plan baseline are verified and accepted plans.

C.
The plans generated for every SQL statement are stored in the SQL plan baseline by
default.

D.
The plan baselines are stored temporarily in the memory as long as the database
instance is running.

E.
For the SQL plan baselines to be accessible to the optimizer, the SYSAUX tablespace
must be online.

Explanation:
The SQL management base (SMB), which is part of the data dictionary, stores the SQL plan
baselines and plan history in the SYSAUX tablespace. The SMB also contains SQL profiles.
The SMB uses automatic space management.
Capturing SQL Plan Baselines (refer to here).
You can configure the SQL Plan Baseline Capture phase for automatic capture of plan
history and SQL plan baselines for repeatable SQL statements. Alternatively, you can
manually load plans as SQL plan baselines.
Not all the new generated SQL plan can be accepted, so that all SQL plan stored in the plan
history, only after evolving the plans in the plan history, it becomes in the plan baseline.
You can evolve an existing SQL plan baseline by manually loading plans from the shared
SQL area or from a SQL tuning set. When you manually load plans into a SQL plan
baseline, the database adds these loaded plans as accepted plans.



Leave a Reply 1

Your email address will not be published. Required fields are marked *


Jake from SF

Jake from SF

A,B and E are correct:

http://docs.oracle.com/cd/E25178_01/server.1111/e16638/optplanmgmt.htm

For A: You can configure the SQL Plan Baseline Capture phase for automatic capture of plan history and SQL plan baselines for repeatable SQL statements. Alternatively, you can manually load plans as SQL plan baselines.

For B: SQL plan management is a preventative mechanism that records and evaluates the execution plans of SQL statements over time. This mechanism can build a SQL plan baseline, which is a set of accepted plans for a SQL statement. The accepted plans have been proven to perform well.

For E: Because the SMB is located entirely within SYSAUX, the database does not use SQL plan management and SQL tuning features when this tablespace is unavailable.