478th questions, 053478

478th questions, 053478

478. Identify the two direct sources from where SQL plans can be loaded into the SQL plan baselines.
(Choose two .)
A. Cursor cache
B. Stored outline
C. SQL Tuning Set
D. Automatic Workload Repository (AWR) snapshots
Answer:AC

You can evolve an existing SQL plan baseline by manually loading plansFrom 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

15.2.1.2.1 Loading Plans from SQL Tuning Sets and AWR Snapshots

To load plans from a SQL tuning set, useLOAD_PLANS_FROM_SQLSETFunction ofDBMS_SPMPackage. The following example loads the plans stored in the SQL tuning set namedtset1:

DECLARE  my_plans PLS_INTEGER;BEGIN  my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( sqlset_name => 'tset1');END;/

To load plans from Automatic Workload Repository (AWR), load the plans stored in AWR snapshots into a SQL tuning set before usingLOAD_PLANS_FROM_SQLSETFunction as described in this section.

15.2.1.2.2 Loading Plans from the Shared SQL Area

To load plans from the shared SQL area, useLOAD_PLANS_FROM_CURSOR_CACHEFunction ofDBMS_SPMPackage. In the following example, Oracle Database loads the plans located in the shared SQL area for the SQL statement identified by itssql_id:

DECLARE  my_plans PLS_INTEGER;BEGIN  my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( sql_id => '99twu5t2dn5xd');END;/

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.