Oracle 1Z0-054 Online Practice Questions and Exam Preparation

Oracle 1Z0-054 Online Questions & Answers

• Question 71:

  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. The SALES_RECORDS table has Automatic Segment Space Management (ASSM) and row movement enabled. The table is accessible in 24x7 mode. What is the most efficient method to improve the performance?

  A. Perform EXPORT, DROP, and IMPORT operations on the SALES_RECORDS table sequentially.
  B. Shrink the SALES_RECORDS table by using the ALTER TABLE...SHRINK SPACE command.
  C. Move the SALES_RECORDS table to a different location by using the ALTER TABLE...MOVE command.
  D. Deallocate the space in the SALES_RECORDS table by using the ALTER TABLE...DEALLOCATE UNUSED command.
  B. Shrink the SALES_RECORDS table by using the ALTER TABLE...SHRINK SPACE command.

• Question 72:

  You work in an online transaction processing (OLTP) environment and users are experiencing slow response time on their queries. These users connect as FINANCE to run their application and run hundreds of sessions.

  Your investigation shows that there is lot of I/O contention happening on only one temporary tablespace and there is no load on other temporary tablespaces. View the Exhibit and examine the details.

  After some time you observed that sort operations are running out of temporary space.

  What would be a long-term solution?

  

  A. Change the temporary tablespace assigned to this user ID.
  B. Create and assign temporary tablespace group to this user ID.
  C. Increase the size of the temporary tablespace assigned to this user ID.
  D. Make the bigger temporary tablespace the default temporary tablespace ID.
  B. Create and assign temporary tablespace group to this user ID.

• Question 73:

  Which two inferences are correct about the CPU time and the wait time that appear in the Automatic Workload Repository AWR and Statspack reports in the Top 5 Event section? (Choose two.)

  A. Adding CPUs to systems showing increased wait time due to contention will improve response time.
  B. Systems where the CPU time is dominant in comparison to the wait time have optimal response time.
  C. Increase in wait time when the CPU time increases or remains the same indicates an increase in the load on the system.
  D. Systems where the proportion of CPU time does not decrease significantly with increasing load would most likely benefit from adding CPUs.
  E. The appearance of CPU time together with wait time in the Top 5 Event section of the AWR report of a system always indicates high contention.
  C. Increase in wait time when the CPU time increases or remains the same indicates an increase in the load on the system.
  D. Systems where the proportion of CPU time does not decrease significantly with increasing load would most likely benefit from adding CPUs.

• Question 74:

  You are working on a decision support system (DSS). The index is available on the COUNTRY_ID column of the CUSTOMERS table.View the Exhibit and examine the parameter settings and the query execution plan.

  

  Why is the query using a full table scan instead of an index scan?

  A. because the histogram statistics for the COUNTRY_ID column are not updated
  B. because the index statistics for the index on the COUNTRY_ID column are not current
  C. because the DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter is set to a higher value
  D. because the optimizer predicts that most of the blocks in the table are accessed. Therefore, it uses a full table scan, even though indexes are available.
  D. because the optimizer predicts that most of the blocks in the table are accessed. Therefore, it uses a full table scan, even though indexes are available.

• Question 75:

  You are working in an online transaction processing (OLTP) environment. You received many complaints from users about degraded performance. Your senior

  DBA asked you to execute the following command to improve the performance:

  SQL> ALTER TABLE subscribe_log STORAGE(BUFFER_POOL recycle); You checked the data in the SUBSCRIBE_LOG table and found that it is a large table

  having one million rows. Which factor could be a reason for this recommendation?

  A. The keep pool is not configured.
  B. The automatic Program Global Area (PGA) is not configured.
  C. The CURSOR_SPACE_FOR_TIME initialization parameter is set to FALSE.
  D. The most of the rows in SUBSCRIBE_LOG table are accessed once a week.
  E. All the queries on the SUBSCRIBE_LOG table are rewritten using a materialized view.
  D. The most of the rows in SUBSCRIBE_LOG table are accessed once a week.

• Question 76:

  Examine the following query and its output:

  SQL>SELECT namespace, gets, gethits, gethitratio, pins, pinhits, reloads,invalidations FROM v$librarycache WHERE namespace = 'SQL AREA'; NAMESPACE GETS GETHITS GETHITRATIO PINS PINHITS RELOADS INVALIDATIONS --------- ----- ------- ----------- ----- ------- -------- ------------- SQL AREA 11942 2832 .237146207 65900 62486 28 118

  Which two observations are correct? (Choose two.)

  A. About 24% of the parse calls were able to find a cursor to share.
  B. DML locks were requested 65,900 times for objects in the SQL AREA.
  C. Library cache misses happened 118 times because invalidations occurred due to DDL on the dependant objects.
  D. Reparsing of SQL statements before they had finished executing happened 28 times due to modification of a dependent object.
  A. About 24% of the parse calls were able to find a cursor to share.
  C. Library cache misses happened 118 times because invalidations occurred due to DDL on the dependant objects.

• Question 77:

  View the Exhibit and examine the query based on the DBA_THRESHOLDS view and its partial output : Which observation about the output is correct?

  

  A. Logons Per Sec triggers an alert when the number of users logging on in an interval of one second is equal to or greater than 100.
  B. Blocked User Session Count triggers an alert based on the ELAPSED_TIME_PER_CALL and DB_TIME_WAITING metrics collection per minute.
  C. Database Time Spent Waiting(%) triggers an alert when more than 30% of the DB time per second for three consecutive seconds is spent on lock waits.
  D. Average Users Waiting Counts triggers an alert when the count is more than 30 for 3 consecutive 1 minute intervals and is related to the log file sync wait event.
  D. Average Users Waiting Counts triggers an alert when the count is more than 30 for 3 consecutive 1 minute intervals and is related to the log file sync wait event.

• Question 78:

  You work as a consultant DBA for various clients. A performance issue in one of the online transaction processing (OLTP) systems is reported to you and you received the Automatic Workload Repository (AWR) report generated in the database. The main sections of the AWR report is shown in the Exhibits. View the Exhibit named DBTIME and note % DB Time.

  

  View the Exhibit named TIMEMODEL and note what has contributed to % DB Time.

  

  View the Exhibit named EFFICIENCY and examine the various percentages shown.

  

  Which option describes the correct understanding of the main problem?

  A. The Exhibit DBTIME shows that the DB CPU event consumed very high % DB Time, which indicates that the CPU is very slow.
  B. sql execute elapsed time and parse time elapsed are consuming very high % DB Time in the TIMEMODEL Exhibit. This indicates too many soft parses.
  C. hard parse elapsed time has the majority time in parse time elapsed, which is shown in the TIMEMODEL Exhibit. This indicates inadequate database buffer cache.
  D. % Non-Parse CPU: and Soft Parse %: are low in the EFFICIENCY Exhibit, which shows that very few statements are found in the cache. This indicates that there were too many hard parses.
  D. % Non-Parse CPU: and Soft Parse %: are low in the EFFICIENCY Exhibit, which shows that very few statements are found in the cache. This indicates that there were too many hard parses.

• Question 79:

  View the Exhibit and examine the content.

  

  ALLOCATED_SPACE and FREE_SPACE indicate that a large amount of space is allocated to the temporary tablespace that is being used. Which action would reclaim the space and refresh these values?

  A. shrinking the tablespace
  B. closing all the current sessions
  C. restarting the database instance
  D. forcing a checkpoint or waiting for a checkpoint to occur
  A. shrinking the tablespace

• Question 80:

  You look at the Top 5 Timed Events section of the Automatic Workload Repository (AWR) report and find that the wait event buffer busy waits is consuming a significant portion of DB time. What does this wait event indicate?

  A. buffer cache too small
  B. insufficient DBWn processes
  C. excessive block replacement
  D. a large number of locally-managed tablespaces in the database
  E. block contention on segment headers, data blocks, undo header, or undo blocks
  E. block contention on segment headers, data blocks, undo header, or undo blocks