Oracle 1Z0-054 Online Practice Questions and Exam Preparation

Oracle 1Z0-054 Online Questions & Answers

• Question 181:

  You find that in the Top 5 Timed Events section of the Automatic Workload Repository (AWR) report, the wait event buffer busy waits shows significantly high values.The database uses only non-ASSM locally managed tablespaces.On further investigation, you find that the contention is on data blocks.Which option would you consider first to decrease the wait event values on a long- term basis?

  A. decreasing PCTUSED
  B. decreasing PCTFREE
  C. increasing the number of DBWn processes
  D. using automatic segment space management (ASSM)
  E. increasing db_buffer_cache based on the V$DB_CACHE_ADVICE recommendation
  D. using automatic segment space management (ASSM)

• Question 182:

  Your company wants to migrate a single instance Oracle 11g Release 1 database to a RAC environment. The database supports middle-tier applications using connection pooling for connecting to database.

  Being a part of the performance management team, you want to test the performance of the SQL statements and the peak workload of the applications in the new environment before upgrading the production system.

  Given below are some of the steps in random order to accomplish the task:

  1) Capture the real workload on the existing system.

  2) Create a SQL Tuning Set (STS) for the statements executed by the application.

  3) Transport STS on the test environment and use SQL Tuning Advisor to compare the before and after performance.

  4) Replay the workload on the test environment.

  5) Analyze the divergence after replay.

  6) Use Automatic Workload Repository (AWR) to capture the information for top SQL on the existing system.

  7) Transport STS on the test environment and use SQL Performance Analyzer to compare the before and after performance.

  Identify the correct steps required to perform the test.

  A. 2 and 3 only
  B. 6 and 7 only
  C. 1, 4, and 5 only
  D. 2, 3, and 5 only
  E. 2, 3, 4, and 5 only
  C. 1, 4, and 5 only

• Question 183:

  You want to review a summary of all the wait events in an instance since it started over a month ago.

  You issued the following query:

  SQL>SELECT event, total_waits, time_waited, average_wait FROM V$SYSTEM_EVENT ORDER BY time_waited DESC;

  View the Exhibit and examine a portion of the output from the above query.

  

  Which events would you consider from the output for further investigation? (Choose all that apply.)

  A. log file sync
  B. rdbms ipc message
  C. SQL*Net message from client
  D. log file switch (checkpoint incomplete)
  A. log file sync
  D. log file switch (checkpoint incomplete)

• Question 184:

  You work as a DBA for a company and as a performance improvement measure, you implemented the result cache in your database. View the Exhibit named SETTING and note the result cache settings.

  

  Many users in the company state that performance has improved on the queries they use but some users complain that they have not got any performance benefit

  on the queries they use. You checked all the queries they use and the following is one of them:

  SQL> SELECT slnoq.currval as "SLNO", prod_id, pdname, 2 cust_name FROM sales WHERE sl_date < sysdate;

  View the Exhibit named TEST and examine the testing performed to check this.

  

  Why is the result cache not used? (Choose all that apply.)

  A. because the query uses SYSDATE
  B. because the query uses an alias for a column
  C. because the query uses the SLNOQ.CURRVAL sequence
  D. because the CLIENT_RESULT_CACHE_SIZE parameter is set to 0
  A. because the query uses SYSDATE
  C. because the query uses the SLNOQ.CURRVAL sequence

• Question 185:

  You are working as a DBA for a DSS system, on which applications primarily perform large sequential I/Os. The company wants to upgrade the storage used by the database to faster hard disks. What other factors would you consider before giving the go-ahead for the new hard disks? (Choose all that apply.)

  A. the sizing of PGA to meet the faster data access
  B. the sizing of buffer cache to meet the faster data access
  C. the time it takes for a submitted I/O request to be serviced by the storage
  D. the capacity of the I/O channel that connects the server to the storage array
  C. the time it takes for a submitted I/O request to be serviced by the storage
  D. the capacity of the I/O channel that connects the server to the storage array

• Question 186:

  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 the

  sort operations are running out of temporary space.

  What would be a long-term solution?

  

  A. Configure the large pool if it is not done already.
  B. Change the temporary tablespace assigned to this user ID.
  C. Create and assign temporary tablespace group to this user ID.
  D. Increase the number of database writer (DBWn) processes ID.
  E. Make the bigger temporary tablespace the default temporary tablespace ID.
  C. Create and assign temporary tablespace group to this user ID.

• Question 187:

  You received complaints from users about slow response time in some parts of the application. On investigation, you find that this happens whenever the application runs any query against the PROD_DET table that contains detailed information about all the products. You gathered the table statistics and executed the following statements to find more information about this table:

  SQL> SELECT chain_cnt, 2 round(chain_cnt/num_rows*100,2) pct_chained, 3 avg_row_len, pct_free , pct_used 4 FROM user_tables 5 WHERE table_name = 'PROD_DET';CHAIN_CNT PCT_CHAINED AVG_ROW_LEN PCT_FREE PCT_USED---------- ----------- ----------- ---------- ----------4789 100 3691 10 40

  Which methods can you use to improve the performance? (Choose all that apply.)

  A. online table redefinition
  B. export and import the table
  C. decrease the PCTFREE value
  D. move the table using the ALTER TABLE command
  A. online table redefinition
  B. export and import the table
  D. move the table using the ALTER TABLE command

• Question 188:

  View the Exhibit and analyze the content of V$BUFFER_POOL_STATISTICS.

  

  Why does the query on the EMP table not access buffers from the keep pool?

  A. because the query used SCOTT.EMP instead of EMP
  B. because the query with * always uses the default pool
  C. because the query with * always forces physical I/Os that bypass the keep pool
  D. because the blocks of the EMP table are already available in one of the other buffer pools
  D. because the blocks of the EMP table are already available in one of the other buffer pools

• Question 189:

  The users in your online transaction processing (OLTP) environment complain that the response time of the application has increased dramatically.To investigate the problem, view the Exhibit and examine the queries you executed.What do you infer?

  

  A. Cursor sharing is not enabled.
  B. The large pool is not configured.
  C. The temporary tablespace is full.
  D. The value set for the SHARED_POOL_SIZE initialization parameter is too small.
  E. The value set for the PGA_AGGREGATE_TARGET initialization parameter is too small.
  E. The value set for the PGA_AGGREGATE_TARGET initialization parameter is too small.

• Question 190:

  You work as a DBA for a company and manage an online transaction processing (OLTP) system. You received complaints about the performance degradation of SQL statements executed by the application that uses this database.View the Exhibit and examine the output of the queries executed to investigate the performance issues.Which two actions can improve performance? (Choose two.)

  

  A. increasing the size of the shared pool
  B. setting the CURSOR_SHARING parameter to EXACT
  C. decreasing the value set for the OPEN_CURSORS parameter
  D. replacing literals with bind variables in SQL statements with the PLAN_HASH_VALUE 1337874392
  E. replacing literals with bind variables in SQL statements with the PLAN_HASH_VALUE 1445457117
  A. increasing the size of the shared pool
  E. replacing literals with bind variables in SQL statements with the PLAN_HASH_VALUE 1445457117