Oracle 1Z0-054 Online Practice Questions and Exam Preparation

Oracle 1Z0-054 Online Questions & Answers

• Question 151:

  After an Automatic Database Diagnostic Monitor (ADDM) run, you receive a recommendation for buffer busy wait issue. View the Exhibit. What is the significance of the Benefit(%) column in the Exhibit?

  

  A. It produces better query plans if the recommendation is implemented.
  B. It gives the maximum reduction in the number of input/output (I/O) operations.
  C. It gives the maximum reduction in resource usage if the recommendation is implemented.
  D. It gives the maximum reduction in database elapsed time if the recommendation is implemented.
  D. It gives the maximum reduction in database elapsed time if the recommendation is implemented.

• Question 152:

  You want to capture the performance statistics during a system test that is scheduled for the weekend. Which action would help you to automatically capture the performance statistics?

  A. Creating SQL tuning sets
  B. Setting up SQL plan baselines
  C. Configuring new services for the database
  D. Creating Automatic Workload repository (AWR) baseline templates
  D. Creating Automatic Workload repository (AWR) baseline templates

• Question 153:

  View Exhibit1 to examine the description of the CUSTOMERS table.

  

  The CUSTOMERS table has been updated heavily today. In a frequently used SQL statement, you notice that estimated rows and the actual number of rows

  fetched differ greatly. The COUNTRY_ID column has an index.

  View Exhibit2 and examine the query execution plan.

  

  What would you recommend to improve the optimizer's estimation?

  A. setting the STATISTICS_LEVEL parameter to ALL
  B. setting the OPTIMIZER_USE_PENDING_STATISTICS parameter to FALSE
  C. creating extended statistics for the CUST_LAST_NAME, CUST_ID, and CUST_TOTAL columns
  D. updating the statistics for the CUSTOMERS table by using the DBMS_STATS.GATHER_TABLE_STATS procedure
  D. updating the statistics for the CUSTOMERS table by using the DBMS_STATS.GATHER_TABLE_STATS procedure

• Question 154:

  You identified poorly performing SQL statements that use bind variables. You want to examine the execution plans for the most recently executed statements for further diagnosis. Which method would you recommend to view the execution plans?

  A. SQL*Plus Autotrace
  B. the dbms.xplan package
  C. EXPLAIN PLAN for SQL statements
  D. the SQL trace utility to generate trace files and read the formatted output using the tkprof utility
  B. the dbms.xplan package

• Question 155:

  You have the automatic optimizer statistics gathering enabled for a 24x7 database as part of the automatic maintenance task. Because the automatic optimizer statistics collection runs during maintenance window(in the night), the statistics on tables that are significantly modified throughout the day becomes stale. This leads to poor query performance on these tables and you want to minimize the overhead in statistics collection. Identify a solution to this problem.

  A. Change the STALE_PERCENT preference to zero for the tables.
  B. Set the OPTIMIZER_USE_PENDING parameter statistics to TRUE.
  C. Set the OPTIMIZER_DYNAMIC_SAMPLING parameter to zero during day time.
  D. Gather statistics on the table when the tables have a representative number of rows, and then lock the statistics.
  D. Gather statistics on the table when the tables have a representative number of rows, and then lock the statistics.

• Question 156:

  Which statement is true regarding the setting of the PCTFREE value for an index?

  A. It decides how much of a block to fill when the index is created.
  B. It has no role to play when segment space management is automatic.
  C. It keeps space reserved in a block for possible updates to existing rows in that block.
  D. It is the minimum percentage of a block that can be used for row data and overhead before new rows are added to the block.
  A. It decides how much of a block to fill when the index is created.

• Question 157:

  You are managing an online transaction processing (OLTP) database with multiple applications. The applications support a large number of users and most of the applications execute groups of SQL that run together.Which option would you use to monitor the performance issues for users of these applications?

  A. Monitor statistics for individual sessions in an application by querying V$SESSION.
  B. Specify service-level thresholds and priorities for users of applications and monitor the alerts.
  C. Monitor statistics and wait events collected for a MODULE and ACTION by performing service aggregation.
  D. Configure application users as part of the resource consumer group and monitor the resource usage statistics for the resource consumer group.
  C. Monitor statistics and wait events collected for a MODULE and ACTION by performing service aggregation.

• Question 158:

  You are a DBA in ABC Corp. You are working on an online transaction processing (OLTP) system.The applications running on the database use connection

  pooling to connect to the database to perform transactions.

  The company wants to upgrade the CPU and the memory for machine on which the production database is running. To test the realistic workload on the new

  machine with increased CPU and memory, you identified the peak workload time for the database and captured the workload on the production system.

  You set up the test machine with the upgraded CPU and memory, and performed the following operations for replay:

  1) You preprocessed the captured workload.

  2) You restored the replay database to match the capture database at the start of the workload capture.

  3) You resolved all external references.

  4) You set up replay clients.

  You have the following replay considerations:

  1) minimal data divergence to be ensured

  2) all sessions to connect immediately.

  3) user waits between issuing calls to be kept to a minimum What replay options would you choose to accomplish the replay?

  A. SYNCHRONIZATION = TRUE, THINK_TIME_SCALE = 0, CONNECT_TIME_SCALE = 0 and THINK_TIME_AUTO_CORRECT = TRUE
  B. SYNCHRONIZATION = TRUE, THINK_TIME_SCALE = 0, CONNECT_TIME_SCALE = 100 and THINK_TIME_AUTO_CORRECT = TRUE
  C. SYNCHRONIZATION = TRUE, THINK_TIME_SCALE = 100, CONNECT_TIME_SCALE = 0 and THINK_TIME_AUTO_CORRECT = FALSE
  D. SYNCHRONIZATION = TRUE, THINK_TIME_SCALE = 100, CONNECT_TIME_SCALE = 100 and THINK_TIME_AUTO_CORRECT = TRUE
  A. SYNCHRONIZATION = TRUE, THINK_TIME_SCALE = 0, CONNECT_TIME_SCALE = 0 and THINK_TIME_AUTO_CORRECT = TRUE

• Question 159:

  You work as a DBA for a company and as a performance improvement measure, you implemented the result cache in your database. Many users in the company

  say that performance has improved on the queries they use, but some users complain that they have not got any performance benefit on the queries they use.

  You checked all the queries they use and the following is one of them:

  SQL> SELECT /*+ RESULT_CACHE */ slnoq.currval as "SLNO", prod_id, pdname, 2 cust_name FROM sales WHERE sl_date < sysdate;

  View the Exhibit and examine the testing performed to check this.

  

  Why is the result cache not used? (Choose all that apply.)

  A. because the query uses SYSDATE
  B. because the query uses an alias for a column
  C. because the query uses the SLNOQ.CURRVAL sequence
  D. because the table might have an index on the SL_DATE column
  A. because the query uses SYSDATE
  C. because the query uses the SLNOQ.CURRVAL sequence

• Question 160:

  In which two scenarios would you recommend using RAID level 5 as an option for database storage?(Choose two.)

  A. For a database with lower recovery (MTTR) overhead
  B. For a database in which the applications primarily perform read operations and predominantly sequential I/O is performed
  C. For a database in which performance is the primary goal of the database because applications are demanding in terms of I/O operations
  D. For a database in which the database size grows rapidly, the cost of storage is critical and data accessibility is more important than performance
  B. For a database in which the applications primarily perform read operations and predominantly sequential I/O is performed
  D. For a database in which the database size grows rapidly, the cost of storage is critical and data accessibility is more important than performance