Oracle Oracle Certifications 1Z0-053 Questions & Answers

• Question 471:

  Which of the following represents the correct sequence of events for Database Replay?

  A. Capture, analyze, preprocess, replay

  B. Capture, preprocess, analyze, replay

  C. Capture, preprocess, replay, analyze

  D. Analyze, capture, preprocess, replay

  E. None of the above

  Correct Answer: C

  

• Question 472:

  Which tuning tool recommends how to optimize materialized views so that these views can take advantage of the general query rewrite feature?

  A. Segment Advisor

  B. SQL Access Advisor

  C. Undo Advisor

  D. SQL Tuning Advisor

  Correct Answer: B

• Question 473:

  You perform the following activities during the database upgrade from Oracle Database 10g to Oracle Database 11g:

  Capture plans for a SQL workload into a SQL Tuning Set (STS) before upgrade. Load these plans from the STS into the SQL plan baseline immediately after the upgrade.

  What is the reason for performing these activities?

  A. to minimize plan regression due to the use of a new optimizer version

  B. to completely avoid the use of new plans generated by a new optimizer version

  C. to prevent plan capturing when the SQL statement is executed after the database upgrade

  D. to keep the plan in the plan history so that it can be used when the older version of the optimizer is used

  Correct Answer: A

  15.1.1 Purpose of SQL Plan Baselines (Link)

  Common scenarios where SQL plan management can improve or preserve SQL performance include:

  A database upgrade that installs a new optimizer version usually results in plan changes for a small percentage of SQL statements. Most of these plan changes result in either no performance change or improvement. However, some plan

  changes may cause performance regressions. SQL plan baselines significantly minimize potential regressions resulting from an upgrade. Ongoing system and data changes can impact plans for some SQL statements, potentially causing

  performance regressions. SQL plan baselines help minimize performance regressions and stabilize SQL performance. Deployment of new application modules means introducing new SQL statements into the database. The application

  software may use appropriate SQL execution plans developed in a standard test configuration for the new statements. If the system configuration is significantly different from the test configuration, then the

  database can evolve SQL plan baselines over time to produce better performance.

• Question 474:

  View the Exhibit for some of the parameter settings. You start a session and issue the following command:

  SQL>CREATE INDEX emp_ename ON emp(ename)

  TABLESPACE users INVISIBLE;

  What is the outcome of the above command?

  Exhibit:

  

  A. The index is not used by the optimizer but is maintained during DML operations.

  B. The index is not used by the optimizer and is not maintained during DML operations.

  C. The index is used by the optimizer only if a hint is specified in the query statement and is maintained during DML operations.

  D. The index is used by the optimizer only if a hint is specified in the query statement but is not maintained during DML operations.

  Correct Answer: A

• Question 475:

  While tuning a SQL statement, the SQL Tuning Advisor finds an existing SQL profile for the statement that has stale statistics available.

  What would the optimizer do in this situation?

  A. It updates the existing SQL profiles with current statistics.

  B. It makes the statistics information available to GATHER_STATS_JOB.

  C. It initiates the statistics collection process by running GATHER_STATS_JOB.

  D. It logs a warning message in the alert log so that the DBA can perform statistics collection manually.

  Correct Answer: B

• Question 476:

  When creating a SQL tuning set, which of the following steps allows the DBA to reduce the size of the SQL set by selecting specific operators and values?

  A. Filter versions

  B. Filter loads

  C. Filter tasks

  D. Filter options

  Correct Answer: D

• Question 477:

  To view the results of a manual SQL Tuning Advisor task, which steps should the DBA take?

  A. From the Advisor Central home page, select the tuning task from the Advisor Tasks section.

  B. From Advisor Central, choose SQL Advisors, SQL Tuning Advisors, Manual Tuning Task Results.

  C. From Advisor Central, choose SQL Advisors, Manual SQL Tuning Advisors, Tuning Task Results.

  D. Either B or C.

  Correct Answer: A

  

• Question 478:

  Which of these appropriately describes the results of a manual SQL Tuning Advisor task?

  A. A list of SQL statements and recommendations for tuning

  B. A list of SQL statements that have been tuned by the Advisor, with before and after metrics

  C. Graphs showing the actual performance improvement made by the Advisor after it implemented the recommended changes

  D. All of the above

  Correct Answer: A

• Question 479:

  Which of the following is a potential performance tuning recommendation from the SQL Access Advisor?

  A. Create new indexes.

  B. Modify existing indexes.

  C. Implement partitioning on a nonpartitioned table.

  D. Create materialized views.

  E. All of the above

  Correct Answer: E

  18.1 Overview of SQL Access Advisor

  Materialized views, partitions, and indexes are essential when tuning a database to achieve optimum performance for complex, data-intensive queries. SQL Access Advisor helps you achieve your performance goals by recommending the proper set of materialized views, materialized view logs, partitions, and indexes for a given workload. Understanding and using these structures is essential when optimizing SQL as they can result in significant performance improvements in data retrieval. The advantages, however, do not come without a cost. Creation and maintenance of these objects can be time consuming, and space requirements can be significant. In particular, partitioning of an unpartitioned base table is a complex operation that must be planned carefully.

• Question 480:

  You want to analyze a SQL Tuning Set (STS) using SQL Performance Analyzer in a test database.

  Which two statements are true regarding the activities performed during the test execution of SQLs in a SQL Tuning Set? (Choose two.)

  A. Every SQL statement in the STS is considered only once for execution.

  B. The SQL statements in the STS are executed concurrently to produce the execution plan and execution statistics.

  C. The execution plan and execution statistics are computed for each SQL statement in the STS.

  D. The effects of DDL and DML are considered to produce the execution plan and execution statistics.

  Correct Answer: AC