Oracle 1Z0-054 Online Practice Questions and Exam Preparation

Oracle 1Z0-054 Online Questions & Answers

• Question 31:

  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

  You notice that the one of the queries is using a full table scan (view Exhibit1) instead of index unique scan (view Exhibit2). The index is present on the column that is accessed in the WHERE clause of the query. The cost for a full table scan is more than that for an index unique scan.

  

  Why would the optimizer choose full table scan over index unique scan? (Choose all that apply.)

  A. The OPTIMIZER_INDEX_COST_ADJ initialization parameter is set to a low value.
  B. The OPTIMIZER_INDEX_COST_ADJ initialization parameter is set to a high value.
  C. The DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter is set to a low value.
  D. The statistics for the table and all the indexes associated with the table are not current.
  B. The OPTIMIZER_INDEX_COST_ADJ initialization parameter is set to a high value.
  D. The statistics for the table and all the indexes associated with the table are not current.

• Question 32:

  The SALES table in your database undergoes frequent changes. While observing the statistics for the table in the middle of the day, you find a change since the last observation. But the information about the statistics collection is not recorded in the DBA_TAB_STATS_HISTORY. Identify the reason for this.

  A. The AWR retention time was set to zero.
  B. The STATISTICS_LEVEL parameter was set to BASIC.
  C. Some of the statistics were collected using the ANALYZE command.
  D. There were user-defined statistics that were set by using the DBMS_STATS.SET_TABLE_STATS procedure.
  C. Some of the statistics were collected using the ANALYZE command.

• Question 33:

  You are working on a development database that was upgraded to Oracle Database 11g from Oracle Database 9i. An ADDM finding in this database says that the shared pool is inadequately sized, as shown in the Exhibit.

  

  You diagnosed that this is due to different kinds of workloads and this occurs only during peak hours. You tried to resize this by shrinking the database buffer cache but that caused inadequate buffer cache problems. The following are the related parameter settings: SQL> show parameter sga NAME TYPE VALUE ------------------------------ --------------- ----------------- lock_sga boolean FALSE pre_page_sga boolean FALSE sga_max_size big integer 300M sga_target big integer 0 SQL> show parameter target NAME TYPE VALUE ------------------------------ --------------- ----------------- ..... fast_start_mttr_target integer 0 memory_max_target big integer 0 memory_target big integer 0 pga_aggregate_target big integer 100M sga_target big integer 0

  You want to balance the memory between the System Global Area (SGA) components within SGA without affecting the size of the Program Global Area (PGA). Which action would solve this problem?

  A. Set the SGA_TARGET parameter to 300M.
  B. Set the SGA_MAX_SIZE parameter to 400M.
  C. Set the MEMORY_TARGET parameter to 100M.
  D. Set the MEMORY_MAX_TARGET parameter to 300M.
  A. Set the SGA_TARGET parameter to 300M.

• Question 34:

  During a proactive database performance monitoring routine, on examining the AWR report you find that log file sync appears among the top 5 wait events.What does this event indicate?

  A. Frequent logfile switches are occurring.
  B. Redo is generated faster than LGWR can write it out.
  C. Frequent commits or rollbacks are taking place in the application.
  D. Frequent incremental checkpoints are taking place in the database.
  C. Frequent commits or rollbacks are taking place in the application.

• Question 35:

  View the Exhibit.You decide to quickly configure the adaptive threshold. What would happen to the existing metric thresholds shown in the Exhibit?

  

  A. It would be overwritten.
  B. It would remain in effect.
  C. Only the thresholds set with the SYSTEM_MOVING_WINDOW baseline will remain in effect.
  D. Only the thresholds set with baselines other than the SYSTEM_MOVING_WINDOW baseline will remain in effect.
  A. It would be overwritten.

• Question 36:

  View the Exhibit.Which statement is true about applying the threshold setting in the Exhibit.

  

  A. The threshold does not depend on any baseline.
  B. The threshold is decided by comparing the warning and threshold values with the LAST_NIGHT baseline.
  C. The threshold is decided by comparing the warning and threshold values with the SYSTEM_MOVING_WINDOW baseline.
  D. The threshold is decided by comparing the warning and critical values with the baseline set on the performance page.
  A. The threshold does not depend on any baseline.

• Question 37:

  As part of a proactive tuning activity, you have the Automatic Workload Repository (AWR) and ADDM enabled for your database with the default setting of snapshot retention as 8 days and snapshot interval as 60 minutes.Which performance issue does NOT appear in the ADDM and needs alternative tools for investigation?

  A. suboptimal parameter settings
  B. application-level block contention
  C. performance issues related to the Resource Manager
  D. short-lived or intermittent performance issues lasting for less than 5 minutes
  D. short-lived or intermittent performance issues lasting for less than 5 minutes

• Question 38:

  The columns CUST_CITY, CUST_STATE_PROVINCE, and COUNTRY_ID are frequently used together in the WHERE clause of the queries. The CUSTOMERS table is a big table with 20 GB of data. You notice that the selectivity for these three columns varies from the selectivity that the optimizer calculates. What would you recommend to influence the selectivity calculated by the optimizer?

  A. creating function-based indexes by concatenating all the columns
  B. updating histogram statistics for these columns by using the DBMS_STATS.GATHER_TABLE_STATS procedure
  C. using the DBMS_STATS.CREATE_EXTENDED_STATS function to create a virtual column and create index on the virtual column
  D. using the DBMS_STATS.CREATE_EXTENDED_STATS function to create a virtual column and the DBMS_STATS.GATHER_TABLE_STATS procedure to collect statistics on the virtual column
  D. using the DBMS_STATS.CREATE_EXTENDED_STATS function to create a virtual column and the DBMS_STATS.GATHER_TABLE_STATS procedure to collect statistics on the virtual column

• Question 39:

  You work as a DBA and have the responsibility of managing a large online transaction processing (OLTP) system. You used the following query during the

  performance tuning activity:

  SQL> SELECT (1-((phy.value-phyd.value) / (cur.value + con.value))) * 100 2 "Cache Hit ratio"

  3 FROM v$sysstat cur, v$sysstat con, v$sysstat phy, v$sysstat phyd 4 WHERE cur.name = 'db block gets'

  5 AND con.name = 'consistent gets'

  6 AND phy.name = 'physical reads'

  7 AND phyd.name = 'physical reads direct';

  Cache Hit Ratio

  98.43

  What can you conclude based on this ratio?

  A. The database performance is very good because of reduced logical I/Os.
  B. The database performance cannot be determined based only on this ratio.
  C. Good database performance is guaranteed because very few physical I/Os are performed.
  D. The database performance is very good because most of the requested data blocks are found in the buffer cache.
  B. The database performance cannot be determined based only on this ratio.

• Question 40:

  As a DBA configuring a new instance, you want to run a representative workload on the instance and examine the relevant statistics to see whether the cache is underconfigured or overconfigured.Given below is a list of the initialization parameters that would affect the statistics collection and advisories.

  1.

  STATISTICS_LEVEL set to BASIC

  2.

  STATISTICS_LEVEL set to TYPICAL

  3.

  DB_CACHE_ADVICE set to ON

  4.

  TIMED_STATISTICS set to TRUE

  Which of the above initialization parameters' values must be set explicitly to get the relevant statistics with minimal impact on the overall database performance?

  A. only 2
  B. 2 and 3
  C. 1, 3, and 4
  D. 2, 3, and 4
  A. only 2