Online Access Free 1Z0-117 Practice Test

You need to upgrade you Oracle Database 10g to 11g. You want to ensure that the same SQL plans that are currently in use in the 10g database are used in the upgraded database initially, but new, better plans are allowed subsequently.
Steps to accomplish the task:
1.Set the OPTIMIZER_USE_SQL_BASELINE and OPTIMIZER_CAPTURE_SQL_PLAN_BASELINE to TRUE.
2.Bulk load the SQL Management Base as part of an upgrade using an STS containing the plans captured in Oracle Database 10g.
3.Evolve the plan baseline using the DBMS_SPM.EVOLVE_PLAN_BASELINE procedure.
4.Fix the plan baseline - using the DBMS_SPM.ALTER_SQL_PLANBASELINE
procedure.
5.Accept new, better plans using the DBMS_SPM.ALTER_SQL_PLAN_BASELINE procedure and manually load them to the existing baseline.
6.Set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to FALSE.
Identify the required steps.

• A.1, 3, 4, 5
• B.1, 6, 3
• C.1, 6, 3, 4, 5
• D.1, 2, 3, 5
• E.1, 2, 3, 4
• F.1 and 2

While tuning a SQL statement, the SQL Tuning Advisor finds an existing SQL profile for a statement that has stale statistics. Automatic optimizer statistics is enabled for the database.
What does the optimizer do in this situation?

• A.Writes a warning message in the alert log file
• B.Updates the existing SQL profiles for which the statistics are stale.
• C.Makes the statistics information available to GATHER_DATABASE_STATS_JOB_PROC
• D.Starts the statistics collection process by running GATHER_STATS_JOB

You are logged in as the HR user and you execute the following procedure:
SQL > exec DBMS_STATS.SET_TABLE_PREFS ('HR', 'EMPLOYEES', 'PUBLISH', 'FALSE');
SQL> exec DBMS_STATS.GATHER_TABLE_STATS ('HR', 'EMPLOYEES');
Which statement is true about the newly gathered statistics?

• A.They are marked as pending and stored in the pending statistics table.
• B.They are temporary and purged when the session exits.
• C.They are locked and cannot be overwritten.
• D.They are used by the optimizer for all sessions.

Which type of SQL statement would be selected for tuning by the automatic SQL framework?

• A.Serial queries that top the AWR Top SQL in the past week only and whose poor performance can be traced to concurrency issues.
• B.Serial and parallel queries that are among the costliest in any or all of the four categories: the past week, and day in the past week, any hour in the past week, or a single response, and that can benefit from access method changes.
• C.Serial and parallel queries that top the AWR Top SQL in the past week only and have been SQL profiled by the SQL Tuning Advisor.
• D.Serial queries that are among the costliest in any or all of the four categories: the past week, any day in the past week, any hour in the past week, or single response, and have the potential for improvement
• E.Serial queries that have been tuned within the last 30days and have been SQL profiled by the SQL tuning Advisor.

A database instance is configured in the shared server mode and it supports multiple applications running on a middle tier. These applications connect to the database using different services. You enabled the statistics gathering for the service by using the following command: SQL > EXECUTE DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE ('APPS1', NULL, NULL);
Which two statements are true regarding statistics gathered for APPS1 service?

• A.The statistics are collected at the session level for all sessions connected using the service.
• B.The statistics are gathered for all the modules using the service only when DBMS_APPLICATION_INFO.SET_MODULE is executed to register with the service.
• C.Statistics gathering is enabled only for the subsequent sessions using the service.
• D.The statistics are gathered for all the applications using the service only when DBMS_APPLICATION_INFO.SET_ACTION is executed to register with the service.
• E.The statistics are aggregated and stored in the V$SERV_MOD_ACT_STATS view.
• F.The statistics are gathered for all the modules and actions within the service.