Oracle 1Z0-054 Online Practice Questions and Exam Preparation

Oracle 1Z0-054 Online Questions & Answers

• Question 141:

  View the Exhibit and examine the output for the query on V$IOSTAT_FUNCTION.Which two statements are true about the interpretation of the output? (Choose two.)

  

  A. The I/O statistics are only for the single block read and write operations.
  B. The I/O statistics are for single block or multi-block read and write operations.
  C. If there is a conflict of I/O functions, the I/O is placed in the bucket with the lower FUNCTION_ID.
  D. If there is a conflict of I/O functions, the I/O is placed in the bucket with the higher FUNCTION_ID.
  B. The I/O statistics are for single block or multi-block read and write operations.
  C. If there is a conflict of I/O functions, the I/O is placed in the bucket with the lower FUNCTION_ID.

• Question 142:

  View the Exhibit and examine the output for the WRC command-line utility that you executed to estimate the number of replay clients.Identify three prerequisites for completing the replay. (Choose three.)

  

  A. The replay database should be in the RESTRICTED mode.
  B. You need to start at least 21 replay clients on six CPUs to start the replay.
  C. The replay directory must have all the replay files that have been preprocessed.
  D. The user should be able to use the workload replay package and should have the DBA role.
  E. The replay options for think_time_scale and connect_time_scale must be set to 100 each.
  B. You need to start at least 21 replay clients on six CPUs to start the replay.
  C. The replay directory must have all the replay files that have been preprocessed.
  D. The user should be able to use the workload replay package and should have the DBA role.

• Question 143:

  You work as a DBA for a company and you have the responsibility of managing one of its online transaction processing (OLTP) systems. The database

  encountered performance-related problems and you generated an Automatic Workload Repository (AWR) report to investigate it further.

  View the Exhibits and examine the AWR report.

  Which is the appropriate solution to the problem in this database?

  

  

  A. increasing the size of the shared pool
  B. adding one more CPU to the system
  C. setting the CURSOR_SHARING parameter to EXACT
  D. configuring Java pool because it is not configured
  A. increasing the size of the shared pool

• Question 144:

  The users in your online transaction processing (OLTP) environment complain that the query response time has increased considerably. On investigation, you find that the Program Global Area (PGA) is undersized and using temporary

  segments for sort operations. You plan to increase the size of the PGA and there is ample amount of memory available to you. You used the PGA advisor to check the correct size for PGA. View the Exhibit and examine the content of the

  PGA advisor.

  What size of PGA would give the maximum benefit?

  

  A. 125 MB
  B. 3009 MB
  C. 8024 MB
  D. 1003 MB
  E. 1204 MB
  F. 1404 MB
  B. 3009 MB

• Question 145:

  View the Exhibit exhibit1 to examine the series of SQL commands and parameter settings.

  

  View the Exhibit exhibit2 to examine the plans available in the SQL plan baseline.

  

  The first plan (in red) is created when OPTIMIZER_MODE is set to ALL_ROWS and the second plan (in blue) is created when OPTIMIZER_MODE is set to FIRST_ROWS.Which SQL plan baseline would be used if the SQL query in exhibit1 is executed again when the value of OPTIMIZER_MODE is set to FIRST_ROWS?

  A. the second plan, because it is a fixed plan
  B. the first plan, because it is an accepted plan
  C. the second plan, because it is the latest generated plan in FIRST_ROW mode
  D. A new plan, because the second plan in FIRST_ROW mode is not an accepted plan
  B. the first plan, because it is an accepted plan

• Question 146:

  You are managing an online transaction processing (OLTP) system. Many users complain about the slow response time. You investigated and found that in the Top 5 events, direct path read accounts for 73.2% of the total wait time.Which two are the possible reasons for the high direct path read time? (Choose two.)

  A. The I/O subsystem is slow.
  B. The SQL statements are using high CPU.
  C. The SQL statement has a long parsing time.
  D. The SQL statements are performing a large number of physical and logical reads.
  A. The I/O subsystem is slow.
  D. The SQL statements are performing a large number of physical and logical reads.

• Question 147:

  You have a range-partitioned table in your database. Each partition in the table contains the sales data for a quarter. The partition related to the current quarter is modified frequently, whereas other partitions undergo fewer data

  manipulations. The preferences for the table are set to their default values. You collect statistics for the table using the following command at regular intervals:

  SQL> EXECUTE

  DBMS_STATS.GATHER_TABLE_STATS('SH','SALES',GRANULARITY=>'AUTO');You need statistics to be collected more quickly.

  How do you achieve this?

  A. Set the PUBLISH preference to TRUE for the partition table.
  B. Set the NO_VALIDATE preference to TRUE for the partition table.
  C. Set the INCREMENTAL preference to TRUE for the partition table.
  D. Increase the value of the STALE_PERCENT preference for the partition table.
  C. Set the INCREMENTAL preference to TRUE for the partition table.

• Question 148:

  Which two activities can be identified as common unscalable causes of suboptimal database performance? (Choose two.)

  A. defining multiple buffer pools
  B. applications generating dynamic SQL
  C. large amounts of recursive SQL executed by SYS
  D. recursive SQL executed by a user other than SYS
  E. using cursors with bind variables that open the cursor and execute it many times
  B. applications generating dynamic SQL
  C. large amounts of recursive SQL executed by SYS

• Question 149:

  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 the 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) load testing to be performed

  2) all the 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 = FALSE, THINK_TIME_SCALE = 0, CONNECT_TIME_SCALE = 0 and THINK_TIME_AUTO_CORRECT = FALSE
  B. SYNCHRONIZATION = TRUE, THINK_TIME_SCALE = 100, CONNECT_TIME_SCALE = 0 and THINK_TIME_AUTO_CORRECT = FALSE
  C. SYNCHRONIZATION = FALSE, THINK_TIME_SCALE = 100, CONNECT_TIME_SCALE = 100 and THINK_TIME_AUTO_CORRECT = TRUE
  D. SYNCHRONIZATION = TRUE, THINK_TIME_SCALE = 100, CONNECT_TIME_SCALE = 100 and THINK_TIME_AUTO_CORRECT = FALSE
  A. SYNCHRONIZATION = FALSE, THINK_TIME_SCALE = 0, CONNECT_TIME_SCALE = 0 and THINK_TIME_AUTO_CORRECT = FALSE

• Question 150:

  View Exhibit1 and examine the indexes on the CUSTOMERS table.

  

  The statistics for the CUSTOMERS table have been updated recently by using the following command:

  SQL> EXEC

  DBMS_STATS.GATHER_TABLE_STATS('SH','CUSTOMERS',method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO');

  View Exhibit2 to examine a query plan. Even though the index is present on the COUNTRY_ID and CUST_GENDER columns, the query uses a full table scan.

  What could be the reason?

  

  A. because the histogram statistics for the COUNTRY_ID column are not updated
  B. because the DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter is set to a high value
  C. because the optimizer calculates the cost of accessing blocks by using a full table scan to be less as compared to index scans, even though indexes are available
  D. because indexes on CUST_GENDER and COUNTRY_ID columns are of different types, the index on the CUST_GENDER column is bitmap index, and on COUNTRY_ID columns is btree index.
  C. because the optimizer calculates the cost of accessing blocks by using a full table scan to be less as compared to index scans, even though indexes are available