Free Oracle 1Z0-054 Practice Test & Real Exam Questions

You notice that two wait events, Latch:cache buffer chains and Latch:cache buffer LRU chains, appear consistently in the Top 5 Timed Events for your database.Which two options describe what these wait events indicate? (Choose two.)

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?

You are working for a company that uses huge applications to manage its customer details. During your regular performance checks, you executed the following query: SQL> show parameter shared_pool_reserved_size NAME TYPE VALUE
shared_pool_reserved_size big integer 6920601 SQL> SELECT free_space, used_space, request_misses, request_failures from V$SHARED_POOL_RESERVED; FREE_SPACE USED_SPACE REQUEST_MISSES REQUEST_FAILURES
6469776 555528 0 113
You observed that REQUEST_FAILURES is 113 but not increasing, and also that
REQUEST_MISSES is not increasing.
What would you interpret from this?

You work for a company as a DBA. The company has an application to manage the details of its business and customer base. However, application users complain that updating the SALES record takes more time than it used to earlier. On investigation, you notice that the application performance degrades when a call is made to the UPDATE_INV procedure. Further, you execute the following query to investigate: SQL> SELECT name, type, sharable_mem, kept 2 FROM v$db_object_cache 3 WHERE sharable_mem > 4000 4 AND EXECUTIONS > 5 5 AND (type='FUNCTION' OR type='PROCEDURE') 6 / NAME TYPE SHARABLE_MEM KEPT
GETEMKEY FUNCTION 13695 YES UPDATE_INV PROCEDURE 14766 NO SETEMUSERCONTEXT PROCEDURE 13703 YES DECRYPT FUNCTION 17790 YES UPDATE_DEPT PROCEDURE 18765 NO What would you do to improve the performance?

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?

You are working as a DBA in ABC Corp. You are working on an online transaction processing (OLTP) database. The database size has grown to 100 GB. The database will support new applications and more users will be added. Presently, the database uses normal file system storage. However, before the new applications are added, you want to move the storage to RAID. You are in consultation with the OS Administrator for finalizing the RAID option.Performance is the primary goal of the database because applications are demanding in terms of I/O operations and it should have lower mean recovery time (MTTR) than the current MTTR. Which RAID option would you recommend?

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

Examine the output of the following query:
SQL> SELECT c.name,a.addr,a.gets,a.misses,a.sleeps,
2 a.immediate_gets,a.immediate_misses,b.pid
3 FROM v$latch a, v$latchholder b, v$latchname c
4 WHERE a.addr = b.laddr(+) and a.latch# = c.latch#
5 AND c.name LIKE '&latch_name%' ORDER BY a.latch#;
LATCH NAME ADDR GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES
shared pool 20016544 8520540 14112 3137 0 0
Which two conclusions can you draw from this? (Choose two.)

You work as a DBA in ABC Corp. You are working on an online transaction processing (OLTP)
database. The database size has grown to 100 GB. The database will support new applications
and more users will be added. Presently, the database uses normal file system storage. Before
the new applications are added, you want to move the storage to RAID. You are in consultation
with the OS Administrator for finalizing the RAID option for the storage.
You have the following requirements for the database:
1) Less mean time to recover (MTTR)
2) Striped for performance
3) Mirrored for safety
Which RAID option would you recommend?

For which two systems would you recommend configuring DBWR I/O slaves instead of multiple database writer processes? (Choose two.)

In your organization, there are two key databases. One of them is used primarily for an online transaction processing (OLTP) application and is directly accessed by the users in a client/server configuration. The application must process an average of 4,000 small transactions per day. The users enter frequent queries on whose results they depend for completing their transactions. One of the SLA requirements is that any query or transaction processing in the OLTP system should not exceed 15 seconds. The second database supports a decision support system (DSS) as well as batch report programs. It is accessed by client-side users, who submit ad hoc queries through SQL*Plus and by a browser-based,third party application that provides pooled connections for a large user base. The queries vary: some fetch only a few records, whereas others fetch and sort hundreds of records from several tables. As per the SLA specification the average turnaround time for a query on the DSS returning 10000 records should be 24 hours. For both the systems, the number of users is not consistent and may vary from time to time.The management wants to ensure maximum resource usage is allocated for the OLTP processing. Which two considerations should you give priority to when tuning these databases? (Choose two.)

You are managing an online transaction processing (OLTP) application database. Some users on the system are reporting degradation in the performance of SQL statements. To diagnose the problem, you want to identify the SQL statements that are expensive on system resources.What would you recommend to accomplish the task? (Choose all that apply.)

You identified poorly performing SQL statements that use bind variables. You want to examine the execution plans for the most recently executed statements for further diagnosis. Which method would you recommend to view the execution plans?

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?