Oracle 1Z0-054 Online Practice Questions and Exam Preparation

Oracle 1Z0-054 Online Questions & Answers

• Question 51:

  You work for a company as a DBA. The company has an application to manage the details of its business and customer base. However, application users complain that updating the SALES record takes more time than it used to earlier. On investigation, you notice that the application performance degrades when a call is made to the UPDATE_INV procedure. Further, you execute the following query to investigate: SQL> SELECT name, type, sharable_mem, kept 2 FROM v$db_object_cache 3 WHERE sharable_mem > 4000 4 AND EXECUTIONS > 5 5 AND (type='FUNCTION' OR type='PROCEDURE') 6 / NAME TYPE SHARABLE_MEM KEPT ------------------- -------------- --------------- ----------- GETEMKEY FUNCTION 13695 YES UPDATE_INV PROCEDURE 14766 NO SETEMUSERCONTEXT PROCEDURE 13703 YES DECRYPT FUNCTION 17790 YES UPDATE_DEPT PROCEDURE 18765 NO What would you do to improve the performance?

  A. Enable the result cache if not already enabled.
  B. Flush the shared pool to make space for this procedure.
  C. Keep the UPDATE_INV procedure by using the DBMS_SHARED_POOL.KEEP procedure.
  D. Increase the size of the keep buffer pool to accommodate the UPDATE_INV procedure.
  E. Ask the developers to modify and use literals instead of bind variables in the UPDATE_INV procedure.
  C. Keep the UPDATE_INV procedure by using the DBMS_SHARED_POOL.KEEP procedure.

• Question 52:

  You plan to use adaptive thresholds as part of the performance tuning activity. You decide to increase the window size of the default moving window baseline for all metric observations and comparisons in your database. The following error occurs when you try to increase the window size through Enterprise Manager: Failed to commit: ORA-13541: system moving window baseline size (1296000) greater than retention (1036800) ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 601 ORA-06512: at line 2 Which action would allow you to perform the preceding task successfully?

  A. increasing the flashback retention period
  B. increasing the retention period for SQL Management Base
  C. increasing the undo retention period for the database instance
  D. increasing the Automatic Workload Repository (AWR) retention period
  D. increasing the Automatic Workload Repository (AWR) retention period

• Question 53:

  Identify situations in which collecting statistics at the service level helps diagnosing performance problems. (Choose all that apply.)

  A. in a database where you want to monitor and diagnose individual sessions
  B. in a database where you want to statistically measure the workload of jobs
  C. in a database that supports an application where the client directly creates a session
  D. in multitier environments where a request from an end client is routed to different database sessions
  B. in a database where you want to statistically measure the workload of jobs
  D. in multitier environments where a request from an end client is routed to different database sessions

• Question 54:

  You are working on the Database using file system RAID level 0 striping. You want to migrate the database files to the Automatic Storage Management (ASM).

  The applications supported by the database primarily perform small, random I/Os in which each foreground process reads a data block into the buffer cache for updates and the changed blocks are written in batches by the DBWR process.Identify two outcomes of ASM striping in combination with RAID 0. (Choose two.)

  A. It provides higher bandwidth.
  B. It negatively impacts the write-intensive workloads.
  C. It allows you to evenly distribute disks for your data.
  D. It causes contention on Data and Flash Recovery Area (FRA).
  A. It provides higher bandwidth.
  C. It allows you to evenly distribute disks for your data.

• Question 55:

  Identify the correct statements about the SYSTEM_MOVING_WINDOW baseline. (Choose two.)

  A. It is used for adaptive threshold setting.
  B. The statistics computation cannot be scheduled explicitly for it.
  C. Only significance-level thresholds can be set for metrics under it.
  D. It is the only baseline that can be used on the performance page.
  A. It is used for adaptive threshold setting.
  B. The statistics computation cannot be scheduled explicitly for it.

• Question 56:

  You observed that some of the queries are performing poorly on the SALES_RECORDS table in your database.

  On further investigation, you find that at the end of each day the contents of the SALES_RECORDS table are transferred to the SALES table and deleted from the

  SALES_RECORDS table. The deleted operations cause the table to be sparsely populated. You decided to use the ALTER TABLE...SHRINK SPACE COMPACT

  command to shrink the table. Why would you choose this method? (Choose all that apply.)

  A. because it can be used during peak hours
  B. because it avoids unnecessary cursor invalidation
  C. because it adjusts the high-water mark (HWM) immediately
  D. because you have long-running queries that might span the shrink operation
  E. because it does not allow any data manipulation language (DML) operations, thereby making the shrink operation faster
  A. because it can be used during peak hours
  B. because it avoids unnecessary cursor invalidation
  D. because you have long-running queries that might span the shrink operation

• Question 57:

  View the Exhibit and examine the Load Profile from the AWR report of a test database.The application must process an average of 10,000 very small transactions per day. The bulk of the processing activity is for DML operations with minimal to nil query processing.Which two statements are correct about the values of the Physical reads events? (Choose two.)

  

  A. The per second value indicates the total number of data blocks read from disk to only the buffer cache.
  B. The per transaction value includes both the db block gets and the consistent gets that occurred during the last sample period.
  C. The per transaction value indicates that write or update transactions have been committed or rolled back during the last sample period.
  D. The per second value indicates the total number of data blocks read from disk to the buffer cache including reads bypassing the buffer cache, and reads into process private buffers.
  C. The per transaction value indicates that write or update transactions have been committed or rolled back during the last sample period.
  D. The per second value indicates the total number of data blocks read from disk to the buffer cache including reads bypassing the buffer cache, and reads into process private buffers.

• Question 58:

  You work for a small manufacturing company as a DBA. The company has various applications to manage the details of its business and customer base. The company has only one sales outlet where the operator updates the SALES table in the sales application with the details when a product is sold. The company has many other applications for various other businesses that use the same database. The sales operator, while generating a report, noticed that there are some gaps generated in the serial number in the SLNO column during peak hours of business. On investigating, you find that the SLNO column in the SALES table uses the SLNSEQ sequence to generate the serial numbers when a record is inserted. During peak hours, you noticed the following: When the operator entered the sales details the last time, the sequence number was 1056300, but when you checked the sequence as follows, you found a gap of 100 numbers: SQL> SELECT sequence_name, last_number FROM user_sequences WHERE sequence_name='SLNSEQ'; SEQUENCE_NAME LAST_NUMBER

  SLNSEQ 1056400

  You observed this behavior many times and only during peak hours.

  What could be the problem and solution for this?

  A. The reserved pool is not configured. Configure the reserved pool.
  B. The keep buffer pool is not configured. Configure the keep buffer pool.
  C. The sequence was created with a CACHE of 100 numbers. Re-create the sequence with a higher CACHE number.
  D. The sequence is aging out of the shared pool. Keep the sequence in the shared pool using the DBMS_SHARED_POOL.KEEP procedure.
  D. The sequence is aging out of the shared pool. Keep the sequence in the shared pool using the DBMS_SHARED_POOL.KEEP procedure.

• Question 59:

  You are working on an online transaction processing (OLTP) system. By day most of the application users perform queries accessing the most recently added or modified rows. The applications have most of the queries based on multiple tables. But at night, some batch processing is also done. Which two actions would you recommend to choose a goal for the optimizer based on the needs of your application? (Choose two.)

  A. setting the OPTIMIZER_MODE parameter to ALL_ROWS at the instance level
  B. setting the OPTIMIZER_MODE parameter to FIRST_ROWS_n at the instance level
  C. asking the developer to add a hint /*ALL_ROWS*/ in the long-running batch processing queries
  D. asking the developer to add a hint /*FIRST_ROWS_n*/ in the long-running batch processing queries
  B. setting the OPTIMIZER_MODE parameter to FIRST_ROWS_n at the instance level
  C. asking the developer to add a hint /*ALL_ROWS*/ in the long-running batch processing queries

• Question 60:

  You created two services HR and FINANCE on a single instance database. These services are used by the middle-tier applications to connect to the database by using connection pools. The applications have various modules and you asked

  the developers to invoke the DBMS_APPLICATION_INFO.SET_MODULE procedure to set the module name and action in the code for the application.

  As part of performance management, you want to gather statistics for these modules and see the amount of workload created by these modules. What would you recommend to accomplish the task?

  A. Using the DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE procedure to collect statistics for a module and querying the V$SERVICE_EVENT view
  B. Using the DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE procedure to collect statistics for a module and querying the V$SERVICE_STATS view
  C. Using the DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE procedure to collect statistics for a module and querying the V$SERV_MOD_ACT_STATS view
  D. Querying the V$SERVICE_STATS view because the statistics for the modules are collected automatically when you invoke the DBMS_APPLICATION_INFO.SET_MODULE procedure
  C. Using the DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE procedure to collect statistics for a module and querying the V$SERV_MOD_ACT_STATS view