Oracle 1Z0-054 Online Practice Questions and Exam Preparation

Oracle 1Z0-054 Online Questions & Answers

• Question 161:

  While configuring the snapshot data capture for your database, you have specified the snapshot level setting as 6.Which statistics would be captured by the Statspack report? (Choose all that apply.)

  A. resource limit
  B. memory statistics
  C. parent and child latches
  D. segment-level statistics
  E. optimizer execution plans
  A. resource limit
  B. memory statistics
  E. optimizer execution plans

• Question 162:

  You are a DBA in ABC Corp. You are working on a DSS system.. The applications directly connect to the database to perform transactions. The application users also perform direct loads. The company wants to upgrade from Oracle Database 10g version 10.2.0.1 to Oracle Database 11g. You want to test the performance of the SQL statements in the new environment before upgrading the production database. To accomplish this, you set up the test database with Oracle Database 11g, with the same data and schemas as in production.

  What would you recommend to accomplish the task?

  A. gathering all the SQL statements and using SQL Tuning Advisor on the test database for performance analysis
  B. capturing the workload on the production database, replaying it on the test machine, and comparing the performance
  C. creating a SQL Tuning Set by capturing the SQL workload on the production database and using SQL Performance Analyzer for performance analysis
  D. configuring AWR to capture the maximum number of SQL statements, transporting it to the test system, and using SQL Access Advisor for performance analysis
  C. creating a SQL Tuning Set by capturing the SQL workload on the production database and using SQL Performance Analyzer for performance analysis

• Question 163:

  Which three statements are true about Statspack usage? (Choose three.)

  A. If both Statspack and AWR are used, they cannot be scheduled to capture snapshots at the same time.
  B. Time Model data cannot be captured by Statspack unless the STATISTICS_LEVEL parameter is set to ALL.
  C. Both Statspack and AWR can be used on the same database only if the Database Diagnostics pack is present.
  D. Statspack can be used to generate a report for a specific instance as well as a specific SQL statement in an instance.
  E. For best performance when running the performance reports, optimizer statistics should be gathered on the Statspack schema.
  C. Both Statspack and AWR can be used on the same database only if the Database Diagnostics pack is present.
  D. Statspack can be used to generate a report for a specific instance as well as a specific SQL statement in an instance.
  E. For best performance when running the performance reports, optimizer statistics should be gathered on the Statspack schema.

• Question 164:

  You are working as a DBA in ABC Corp. You are working on online transaction processing (OLTP) system. The database uses ASM storage. One of the ASM disk goes offline because of hardware failure. When the disk is added back again after the fix, because the database is active, rebalance operations impact the I/O subsystem.Which two recommendations would you give to lower the impact on the I/O subsystem? (Choose two.)

  A. Increase the number of ASMB processes.
  B. Increase the number of DBWR_IO_SLAVES.
  C. Decrease the value for the ASM_POWER_LIMIT parameter.
  D. Increase the value for the ASM_POWER_LIMIT parameter.
  E. Set the DISK_REPAIR_TIME disk attribute to a lower value.
  F. Specify the POWER clause with a lower value in an ALTER DISKGROUP statement.
  C. Decrease the value for the ASM_POWER_LIMIT parameter.
  F. Specify the POWER clause with a lower value in an ALTER DISKGROUP statement.

• Question 165:

  Your database has the ACCTG service configured for an accounting application that runs on a middle tier. The application has three modules LEDGER, ACCOUNTS, and BILLING. You enabled tracing at the service level by executing the

  following command:

  SQL> exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE('ACCTG');

  Which statement is true?

  A. A single trace file would be created for all the sessions for the service.
  B. Three trace files would be created for the three modules in the application.
  C. The trace file would not be created because the module name is not specified.
  D. A trace file is created for each session that uses the service, regardless of the module and action.
  D. A trace file is created for each session that uses the service, regardless of the module and action.

• Question 166:

  View the Exhibit and examine the Instance Efficiency Percentages that you got on generating an Automatic Workload Repository (AWR) report for your online transaction processing (OLTP) database.What can you deduce from this part of the AWR report? (Choose all that apply.)

  

  A. A low Library Hit % indicates that cursors are not being reused.
  B. A low Execute to Parse % indicates that most SQL statements are reusing the existing execution plans.
  C. A high In-memory Sort % indicates that most sorts are being done in memory, thereby causing the performance to degrade.
  D. A high Buffer Hit % indicates that required data blocks were found in the Buffer Cache memory, thereby avoiding expensive disk I/O.
  A. A low Library Hit % indicates that cursors are not being reused.
  D. A high Buffer Hit % indicates that required data blocks were found in the Buffer Cache memory, thereby avoiding expensive disk I/O.

• Question 167:

  You enabled tracing for a session by executing the following command:

  SQL>SET AUTOTRACE TRACEONLY;

  You issued a SQL query in the same session. Which two statements are true? (Choose two.)

  A. It generates only a report on the execution path used by the SQL optimizer.
  B. Trace for the statement would be created without displaying the query output.
  C. It generates only the statement execution statistics without executing the query.
  D. It generates a report on the execution path used by the SQL optimizer and the statement execution statistics.
  B. Trace for the statement would be created without displaying the query output.
  D. It generates a report on the execution path used by the SQL optimizer and the statement execution statistics.

• Question 168:

  View the Exhibit and examine the details of the Top 5 Timed Events from an Automatic Workload Repository (AWR) report.What can be the three possible causes for the latch-related wait events? (Choose three.)

  

  A. Shared pool size is too small.
  B. Cursors are not being shared.
  C. There are frequent logons/logoffs.
  D. A large number of COMMITs are being performed.
  E. A large number of data definition language (DDL) and query statements are being executed simultaneously by different sessions.
  A. Shared pool size is too small.
  B. Cursors are not being shared.
  C. There are frequent logons/logoffs.

• Question 169:

  You are working on an online transaction processing (OLTP) system. You detected free buffer waits events for your database instance running in a machine that has multiple CPUs. You increased the database buffer cache size as the first step. After a few hours of work on the database, further investigation shows that the same event is being recorded. What would be your next step to avoid this event in future?

  A. Decrease the value of the DBWR_IO_SLAVES parameter.
  B. Set the USE_INDIRECT_DATA_BUFFERS parameter to TRUE.
  C. Increase the value of the DB_WRITER_PROCESSES parameter.
  D. Increase the value of the DB_FILE_MULTIBLOCK_READ_COUNT parameter.
  C. Increase the value of the DB_WRITER_PROCESSES parameter.

• Question 170:

  Identify two correct statements about the Active Session History (ASH) data. (Choose two.)

  A. A part of SGA memory is used to store ASH data as rolling buffer.
  B. The ASH data can be analyzed between any two small time intervals.
  C. All ASH data in memory are flushed to disk by MMON in every 3 seconds.
  D. All ASH data in memory are flushed to disk by MMNL process whenever the buffer is full.
  A. A part of SGA memory is used to store ASH data as rolling buffer.
  B. The ASH data can be analyzed between any two small time intervals.