Oracle Oracle Certifications 1Z0-053 Questions & Answers

• Question 451:

  Your company wants to upgrade the current production database to the RAC environment. To perform testing before migrating to the RAC environment, you performed the workload capture on the production database to record the peak

  workload. You set up the test RAC database and want to replay the recorded workload on the testmachine. Note the following steps that you may require to replay the database workload:

  1) Preprocess the captured workload.

  2) Restart the database in RESTRICTED mode.

  3) Set up the Replay Clients.

  4) Restore the test database to the point when the capture started.

  5) Remap connections.

  Arrange the steps required in the correct sequence to accomplish this task on the test machine.

  A. 1, 4, 5, 3 (2 is not required.)

  B. 1, 4, 3, 5 (2 is not required.)

  C. 1, 2, 4, 5 (3 is not required.)

  D. 2, 1, 5, 3, 4

  E. 1, 2, 4, 5, 3

  Correct Answer: A

  11.1 Steps for Replaying a Database Workload

  Proper planning of the workload replay and preparation of the replay system ensures that the replay will be accurate. Before replaying a database workload, review and complete the following steps as appropriate:

  Setting Up the Replay Directory Restoring the Database Resolving References to External Systems Remapping Connections User Remapping Specifying Replay Options Using Filters with Workload Replay Setting Up Replay Clients

• Question 452:

  In a Database Replay workload capture, what client request information is gathered? (Choose all that apply.)

  A. SQL text

  B. Shared server requests (Oracle MTS)

  C. Bind variable values

  D. Information about transactions

  E. Remote DESCRIBE and COMMIT operations

  Correct Answer: ACD

• Question 453:

  Which of the following advisors is run in every maintenance window by the auto-task system?

  A. The Memory Advisor

  B. The SQL Tuning Advisor

  C. The Undo Advisor

  D. The SQL Access Advisor

  Correct Answer: B

  About Automated Maintenance Tasks (link)

  Oracle Database has three predefined automated maintenance tasks:

  Automatic Optimizer Statistics Collection

  Automatic Segment Advisor

  Automatic SQL Tuning Advisor

  By default, all three automated maintenance tasks are configured to run in all maintenance windows.

• Question 454:

  You work with a newly created database. Presently, there is no application load on the database instance.

  You want to create a baseline for tuning the application, so you decide to collect recommendations that can be implemented to improve application performance.

  What action must you take to achieve this?

  A. Run Segment Advisor

  B. Run the SQL Tuning Advisor (STA)

  C. Run the Automatic Workload Repository (AWR) report

  D. Run the SQL Access Advisor with a hypothetical workload

  Correct Answer: D

• Question 455:

  Which three functions are performed by the SQL Tuning Advisor? (Choose three.)

  A. Building the SQL profile

  B. Recommending optimization of materialized views

  C. Checking query objects for missing and stale statistics

  D. Recommending bitmap, function-based, and B-tree indexes

  E. Recommending restructuring SQL queries that are using bad plans

  Correct Answer: ACE

• Question 456:

  Which two statements regarding a SQL profile are true? (Choose two.)

  A. It is built by Automatic Tuning Optimizer.

  B. It cannot be stored persistently in the data dictionary.

  C. It can be used by the query optimizer automatically.

  D. It can be created manually by using the CREATE PROFILE command.

  Correct Answer: AC

• Question 457:

  Your company wants to upgrade the production database to a Real Application Clusters (RAC) environment.

  You set up the best RAC database and want to replay a recorded workload captured from the production database on the test machine.

  The following steps may be used to replay the database workload:

  1) Preprocess the captured workload

  2) Restart the database in RESTRICTED mode.

  3) Set up Replay Clients.

  4) Restore the test database to the point when the capture started.

  5) Remap connections.

  Which is the correct sequence of the required steps?

  A. 1, 4, 3, 5

  B. 1, 4, 5, 3

  C. 2, 1, 5, 3, 4

  D. 1, 2, 4, 5, 3

  Correct Answer: B

  11.1 Steps for Replaying a Database Workload

  Proper planning of the workload replay and preparation of the replay system ensures that the replay will be accurate. Before replaying a database workload, review and complete the following steps as appropriate:

  Setting Up the Replay Directory Restoring the Database Resolving References to External Systems Remapping Connections User Remapping Specifying Replay Options Using Filters with Workload Replay Setting Up Replay Clients

• Question 458:

  Which of the following cannot be used as input to the SQL Tuning Advisor? (Choose all that apply.)

  A. A single SQL statement provided by a user

  B. An existing SQL Tuning Set (STS)

  C. A preprocessed Database Replay workload

  D. A schema name

  E. SQL statement identified in EM as using excessive resources

  Correct Answer: CD

• Question 459:

  Which three statements about performance analysis by SQL Performance Analyzer are true? (Choose three.)

  A. It detects changes in SQL execution plans.

  B. It produces results that can be used to create the SQL plan baseline.

  C. The importance of SQL statements is based on the size of the objects accessed.

  D. It generates recommendations to run SQL Tuning Advisor to tune regressed SQLs.

  E. It shows only the overall impact on workload and not the net SQL impact on workload.

  Correct Answer: ABD

  Introduction to SQL Performance Analyzer (link)

  SQL Performance Analyzer compares the performance of SQL statements before and after the change and produces a report identifying any changes in execution plans or performance of the SQL statements.

  Fixing Regressed SQL Statements

  If the performance analysis performed by SQL Performance Analyzer reveals regressed SQL statements, then you can make changes to remedy the problem. For example, you can fix regressed SQL by running SQL Tuning Advisor or using

  SQL plan baselines. You can then repeat the process of executing the SQL statements and comparing its performance to the first execution. Repeat these steps until you are satisfied with the outcome of the analysis

• Question 460:

  You work in a data warehouse environment that involves the execution of complex queries. The current content of the SQL cache contains the ideal workload for analysis. You want to analyze only a few most resource-intensive statements. What must you do to receive recommendations on efficient use of indexes and materialized views to improve query performance?

  A. Run the SQL Access Advisor.

  B. Run the SQ; Tuning Advisor (STA).

  C. Run the Automatic Workload Repository (AWR) report.

  D. Run the Automatic Database Diagnostic Monitor (ADDM).

  Correct Answer: A

  In addition, SQL Access Advisor can recommend partitioning on an existing unpartitioned base table to improve performance. Furthermore, it may recommend new indexes and materialized views that are themselves partitioned. While

  creating new partitioned indexes and materialized view is no different from the unpartitioned case, partitioning existing base tables should be executed with care. This is especially true when indexes, views, constraints, or triggers are defined

  on the table. See "Special Considerations when Script Includes Partitioning Recommendations" for a list of issues involving base table partitioning for performing this task online.

  Refer to here.