Oracle 1Z0-054 Online Practice Questions and Exam Preparation

Oracle 1Z0-054 Online Questions & Answers

• Question 111:

  Examine the initialization parameter values for the instance given below: NAME TYPE VALUE ------------------------------------ ----------- ------------------ optimizer_capture_sql_plan_baselines boolean FALSE optimizer_dynamic_sampling integer 2 optimizer_features_enable string 11.1.0.6 optimizer_index_caching integer 0 optimizer_index_cost_adj integer 100 optimizer_mode string ALL_ROWS db_file_multiblock_read_count integer 64 The index created on the column used in the WHERE clause of the query. You notice that the query is not using the index. Instead of an index scan, a full table scan is used. View the Exhibit and examine the autotrace output for a query.

  

  What could be the reason for it? (Choose all that apply.)

  A. The OPTIMIZER_INDEX_COST_ADJ initialization parameter has a low value.
  B. The DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter has a low value.
  C. The statistics for the table and all the indexes associated with the table are not current.
  D. The table has less than DB_FILE_MULTIBLOCK_READ_COUNT blocks under the high- water mark.
  C. The statistics for the table and all the indexes associated with the table are not current.
  D. The table has less than DB_FILE_MULTIBLOCK_READ_COUNT blocks under the high- water mark.

• Question 112:

  The OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter is set to TRUE for a 24x7 database instance. The space consumption has significantly increased by the automatically captured plan baselines. You are suggested to run the following block:

  BEGIN dbms_spm.configure('SPACE_BUDGET_PERCENT', 30); END;

  After a few weeks of work, you observe warnings on exceeding space usage by SQL Management Base (SMB) in alert log file.

  Which two actions can be solutions for this? (Choose two.)

  A. increasing the AWR retention period
  B. purging the older SQL management objects
  C. increasing the size of the SYSTEM tablespace
  D. increasing the size of the SYSAUX tablespace
  B. purging the older SQL management objects
  D. increasing the size of the SYSAUX tablespace

• Question 113:

  The OS administrator in your company informed you about the upgrade of storage to new disk controllers and hard disks. You want to test if the new I/O devices are capable of handling the I/O load. The database is using Automatic Storage Management(ASM) for storage. To test the throughput and other I/O metrics, you performed IO calibration from the Enterprise Manager but you notice that the IO calibration job failed.

  To see the details of the error, view the Exhibit.

  

  How you can resolve the error?

  A. Set FILESYSTEMIO_OPTIONS to NONE and DISK_ASYNCH_IO to FALSE.
  B. Set FILESYSTEMIO_OPTIONS to SYNCH and DISK_ASYNCH_IO to FALSE.
  C. Set FILESYSTEMIO_OPTIONS to ASYNCH and DISK_ASYNCH_IO to TRUE.
  D. Set FILESYSTEMIO_OPTIONS to DIRECTIO and DISK_ASYNCH_IO
  C. Set FILESYSTEMIO_OPTIONS to ASYNCH and DISK_ASYNCH_IO to TRUE.

• Question 114:

  You are working on an online transaction processing (OLTP) system. You are investigating the reasons for performance degradation in the database. View the Exhibit named TOP5EVENTS and note the top five events.

  

  View the Exhibit named TOPSQL and note the problems related to these statements.

  

  View the Exhibit named INSTACT and note the table scans.

  

  Which is the most appropriate solution?

  A. Create indexes on short tables.
  B. Use the CACHE hint to access long tables.
  C. Keep the long tables in the keep buffer pool.
  D. Keep the short tables in the keep buffer pool.
  E. Keep the short tables in the recycle buffer pool.
  D. Keep the short tables in the keep buffer pool.

• Question 115:

  View the Exhibit and examine the plans in the SQL plan baseline.You observe that the last two SELECT statements (outlined in red and blue) are identical, but have two different plans. Also, the second plan (in blue) is not being used by the optimizer.Which two steps do you perform to ensure that the optimizer uses the second plan (in blue) if the plan is more efficient? (Choose two.)

  

  A. Mark the second plan (in blue) as fixed.
  B. Use SQL Tuning Advisor to tune the plan.
  C. Use SQL Access Advisor to tune the plan.
  D. Use the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function to evolve the plan.
  B. Use SQL Tuning Advisor to tune the plan.
  D. Use the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function to evolve the plan.

• Question 116:

  View the Exhibit and examine the partial output from the following query in an online transaction processing (OLTP) database: SQL>SELECT intsize_csec,metric_name,value, metric_unit FROM v$sysmetric;

  Which two conclusions can you draw about the database from these metrics? (Choose two.)

  

  A. The buffer cache is inadequately sized for the workload.
  B. Very few executions of SQL statements resulted in reparsing.
  C. There were no multipass executions in the Program Global Area (PGA).
  D. The database spends more time in wait events in comparison to statement processing.
  B. Very few executions of SQL statements resulted in reparsing.
  D. The database spends more time in wait events in comparison to statement processing.

• Question 117:

  You work as a DBA and have the responsibility of managing a large online transaction processing (OLTP) system. You used three queries to check the database performance as shown in the Exhibit.View the Exhibit and analyze the output.

  

  What conclusion can you draw from this?

  A. There are many physical I/Os happening.
  B. There are many full table scans happening.
  C. The data blocks are aging out of the buffer cache very fast.
  D. There are many cursors trying to access the same data blocks.
  E. The DBWn processes are not freeing sufficient buffers to meet the demand.
  D. There are many cursors trying to access the same data blocks.

• Question 118:

  You upgraded database from the Oracle Database 10g to Oracle Database 11g. To test the performance SQL on the upgraded database. You want to build the before-change performance data by using SQL Performance Analyzer .Which method would allow the task to execute quickly?

  A. the EXPLAIN PLAN method
  B. the TEST EXECUTE method
  C. the COMPARE PERFORMANCE method
  D. the OPTIMIZER_USE_SQL_PLAN_BASELINE parameter set to TRUE
  A. the EXPLAIN PLAN method

• Question 119:

  Your system is upgraded from Oracle Database 10g to Oracle Database 11g. You imported SQL Tuning Sets (STS) from the previous version. After running the SQL Performance Analyzer for optimizer version change, you observe performance regression for a few SQL statements. What would you suggest to avoid performance regression for these SQL statements?

  A. Include the old plans in the SQL plan baseline.
  B. Use DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE to evolve plans.
  C. Set the OPTIMIZER_USE_PENDING_STATISTICS parameter to TRUE.
  D. Increase the value for the OPTIMIZER_DYNAMIC_SAMPLING parameter.
  A. Include the old plans in the SQL plan baseline.

• Question 120:

  Examine the output of the query given below: SQL> SELECT mutex_type, location, sum(gets), sum(sleeps) FROM v$mutex_sleep_history GROUP BY mutex_type, location; MUTEX_TYPE LOCATION SUM(GETS) SUM(SLEEPS) -------------- ------------------------------------- ---------- ----------- Library Cache kglhdgn1 62 8669586 4538 Library Cache kglget2 2 2016618 24 Cursor Stat kkocsStoreBindAwareStats [KKSSTALOC8] 2975 1 Cursor Pin kkslce [KKSCHLPIN2] 666831 678 Library Cache kgllkdl1 85 3369224 110 Library Cache kglpnal1 90 224199 13 Library Cache kglic1 49 42068 10 Library Cache kglpin1 4 9620087 374 Library Cache kglpndl1 95 2065089 79 9 rows selected. Which statement is true?

  A. Each row in the output represents a SQL statement that had to wait for mutexes.
  B. The Cursor Stat and Cursor Pin SLEEPS indicate that the CURSOR_SHARING parameter is set to EXACT.
  C. The GETS column shows the number of times a mutex/location was requested by the requesting session while being held by the blocking session.
  D. The sum of numbers in the GETS and SLEEPS columns indicates the number of times a mutex/location was requested by the requesting session while being held by the blocking session.
  C. The GETS column shows the number of times a mutex/location was requested by the requesting session while being held by the blocking session.