Oracle Oracle Certifications 1Z0-053 Questions & Answers

• Question 461:

  You installed Oracle Database 11g and are performing a manual upgrade of the Oracle9i database. As a part of the upgrade process, you execute the following script:

  SQL> @utlu111i.sql

  Which statement about the execution of this script is true?

  A. It must be executed from the Oracle Database 11g environment.

  B. It must be executed only after the SYSAUX tablespace has been created.

  C. It must be executed from the environment of the database that is being upgraded.

  D. It must be executed only after AUTOEXTEND is set to ON for all existing tablespaces.

  E. It must be executed from both the Oracle Database 11g and Oracle Database 9i environments.

  Correct Answer: C

  Script Name: utlu111i.SQL Used to: Pre-Upgrade Information Analyzes the database to be upgraded, detailing requirements and issues for the upgrade to Oracle Database 11g Release 1 (11.1)

• Question 462:

  When executing a SQL workload, you choose to generate execution plans only, without collecting execution statistics. Which two statements describe the implications of this? (Choose two.)

  A. It produces less accurate results of the comparison analysis.

  B. It automatically calls the SQL Tuning Advisor for recommendations.

  C. It shortens the time of execution and reduces the impact on system resources.

  D. Only the changes in the execution plan, and not performance regression, are detected.

  Correct Answer: AC

• Question 463:

  View the Exhibit.

  Examine the following command that is executed for the TRANSPORT table in the SH schema:

  SQL> SELECT DBMS_STATS.CREATE_EXTENDED_STATS('sh', 'customers_obe', '(country_id, _state_province)') FROM dual;

  Which statement describes the significance of this command?

  Exhibit:

  

  A. It collects statistics into the pending area in the data dictionary.

  B. It creates a virtual hidden column in the CUSTOMERS_OBE table.

  C. It collects statistics with AUTO_SAMPLE_SIZE for ESTIMATE_PERCENT.

  D. It creates a histogram to hold skewed information about the data in the columns.

  Correct Answer: B

  DBMS_STATS.CREATE_EXTENDED_STATS Function Creates a virtual column for a user specified column group or an expression in a table This function creates a column statistics entry in the system for a user specified column group or an expression in a table. Statistics for this extension will be gathered when user or auto statistics gathering job gathers statistics for the table. We call statistics for such an extension, "extended statistics". This function returns the name of this newly created entry for the extension.

• Question 464:

  Which two statements about workload capture and replay operations are true? (Choose two.)

  A. The clients must be created manually on the test machines to perform more realistic testing.

  B. Restart the database in RESTRICTED mode before beginning workload replay to enable a clean state for workload replay.

  C. Restart the database in RESTRICTED mode before beginning workload capture to enable a clean state for capturing the workload.

  D. The application state of the capture system must be identical to the application state of the replay system when the workload replay begins.

  Correct Answer: CD

• Question 465:

  View the Exhibit1 to examine the series of SQL commands. View the Exhibit2 to examine the plans available in the SQL plan baseline. The baseline in the first row of the Exhibit is created when OPTIMIZER_MODE was set to FIRST_ROWS.

  Which statement is true if the SQL query in exhibit1 is executed again when the value of OPTIMIZER_MODE is set to FIRST_ROWS?

  

  parameter-optimizer (exhibit):

  

  A. The optimizer uses a new plan because none of the plans in the exhibit2 are fixed plans.

  B. The optimizer uses the plan in the second row of the exhibit2 because it is an accepted plan.

  C. The optimizer uses the plan in the first row of the exhibit2 because it is the latest generated plan.

  D. The optimizer uses the plan in the first row of the exhibit2 because OPTIMIZER_MODE was set to

  FIRST_ROW during its creation.

  Correct Answer: B

  Setting the OPTIMIZER_MODE Initialization Parameter(Link) The OPTIMIZER_MODE initialization parameter establishes the default behavior for choosing an optimization approach for the instance.

  OPTIMIZER_MODE Initialization Parameter Values

  ALL_ROWS, The optimizer uses a cost-based approach for all SQL statements in the session regardless of the presence of statistics and optimizes with a goal of best throughput (minimum resource use to complete the entire statement).

  This is the default value.

  FIRST_ROWS_n, The optimizer uses a cost-based approach, regardless of the presence of statistics, and optimizes with a goal of best response time to return the first n number of rows, where n equals 1, 10, 100, or 1000. FIRST_ROWS,

  The optimizer uses a mix of cost and heuristics to find a best plan for fast delivery of the first few rows.

  Note that using heuristics sometimes leads the optimizer to generate a plan with a cost that is significantly larger than the cost of a plan without applying the heuristic. FIRST_ROWS is available for backward compatibility and plan stability;

  use FIRST_ROWS_n instead.

• Question 466:

  Which of these recommendations should be followed before capturing a workload? (Choose all that apply.)

  A. Make sure your replay database has the same structure as the capture database, except without data.

  B. Make sure the replay and capture databases are similar in data content.

  C. Perform a clean shutdown and restart of the capture database before beginning a workload capture.

  D. Start the capture database in UNRESTRICTED mode, then start the capture.

  E. Define inclusion and exclusion filters.

  Correct Answer: BC

• Question 467:

  Which is true concerning Database Replay in an Oracle Real Application Cluster (RAC) database?

  A. Workload capture is per instance.

  B. You only need to restart one instance to begin workload capture.

  C. Specifically in RAC, you shut down all instances, restart them individually, and begin workload capture with the last instance started.

  D. RAC does not support workload capture, but it does support workload replay.

  E. None of the above.

  Correct Answer: E

• Question 468:

  Performance divergence indicated in the Workload Replay report is most likely due to what?

  A. DML and SQL statement results that do not match between the capture and replay systems

  B. When errors that occur in the capture system don't occur in the replay system

  C. Top SQL statements

  D. Infrastructure or system-configuration differences

  E. Time-of-day differences between capture and replay systems

  Correct Answer: D

• Question 469:

  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

  Correct Answer: AC

  The AWR snapshots needs to be loaded to STS at first, then load to SQL plan.

  --------------------------------------------------------------- ( Link) You can perform manual plan loading by:

  Loading Plans from SQL Tuning Sets and AWR Snapshots

  To load plans from a SQL tuning set, use the LOAD_PLANS_FROM_SQLSET function of the DBMS_SPM package.

  The following example loads the plans stored in the SQL tuning set named tset1:

  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 using the LOAD_PLANS_FROM_SQLSET function as described in this section.

  Loading Plans from the Shared SQL Area

  To load plans from the shared SQL area, use the LOAD_PLANS_FROM_CURSOR_CACHE function of the DBMS_SPM package. In the following example, Oracle Database loads the plans located in the shared SQL area for the SQL

  statement identified by its sql_id:

  DECLARE

  my_plans PLS_INTEGER;

  BEGIN

  my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( sql_id => '99twu5t2dn5xd');

  END;

  /

• Question 470:

  Which statement most accurately describes the implementation of a SQL Access Advisor recommendation?

  A. SQL Access Advisor recommendations are automatically implemented.

  B. Individual SQL Access Advisor recommendations can be scheduled for implementation.

  C. All SQL Access Advisor recommendations for a specific task must be implemented at the same time.

  D. SQL Access Advisor recommendations are automatically scheduled for implementation during the maintenance window.

  E. None of the above.

  Correct Answer: B

  Implementing the SQL Access Advisor Recommendations (link) A SQL Access Advisor recommendation can range from a simple suggestion to a complex solution that requires partitioning a set of existing base tables and implementing a set of database objects such as indexes, materialized views, and materialized view logs. You can select the recommendations for implementation and schedule when the job should be executed.