Oracle Database 19c: Performance Management and Tuning Practice Tests and PDFs

In the V$SESSION view, Oracle provides information about the session waits: B) When the WAIT_TIME column has a value of 0, it signifies that the session is currently waiting for a resource. This column represents the duration of the current or last wait. C) If the session is not actively waiting, the WAIT_TIME column shows the time the session spent waiting for the last wait event. If the STATE column is showing "WAITED KNOWN TIME", it means the session is not currently waiting, but it indicates the time for which it had waited. Reference: Oracle Database Reference, 19c Oracle Database Performance Tuning Guide, 19c

A) When Cursor_sharing is set to FORCE, Oracle tries to avoid hard parses by replacing literals in SQL statements with bind variables, even if the original statement didn't include bind variables. This can lead to the use of a single execution plan for multiple executions of a statement with different literal values, which might not be optimal for all executions. D) Setting cursor_sharing to EXACT ensures that SQL statements must match exactly for them to share a cursor. This setting prevents the use of Adaptive Cursor Sharing (ACS) since ACS relies on the ability to share cursors among similar statements that differ only in their literal values. With EXACT, there's no cursor sharing for statements with different literals, hence no opportunity for ACS to operate. Reference: Oracle Database SQL Tuning Guide, 19c Oracle Database Reference, 19c

SQL Performance Analyzer (SPA) can be used to assess the impact of different types of changes on SQL performance. These changes can include database initialization parameters, which can significantly affect how SQL statements are executed and therefore their performance. SPA allows you to capture a workload before and after the change and compare the performance of each SQL statement. Database consolidation, including moving to pluggable databases (PDBs), can also affect SQL performance. SPA can analyze the SQL workload to see how consolidation impacts performance, by comparing metrics such as elapsed time and CPU time before and after the consolidation. Reference: Oracle Database SQL Tuning Guide, 19c Oracle Database Performance Tuning Guide, 19c

For this SQL statement and output, we can analyze the EVENT column to understand the type of wait: B) The event "SQL*Net message from client" typically indicates that the session is waiting for a response from the client. This can be due to a network issue, user response, or an application processing delay. E) The event "SQL*Net message from client" also implies that the session is idle waiting for the client (a user or an application) to send a request to the server. This event usually indicates that the session is not actively working but is instead waiting for the next command. F) The wait event "enq: TX - row lock contention" suggests that session 9822 is waiting for a row-level lock held by another session. If the holding session issues a COMMIT or ROLLBACK, the lock will be released, and session 9822 will stop waiting. Since this session is experiencing row lock contention, it implies it's waiting for a specific transaction to complete. Reference: Oracle Database Reference, 19c Oracle Wait Events Documentation

To produce a consolidated formatted trace file from multiple trace files generated by all clients for a single service, the combination of trcsess and TKPROF utilities is used. The trcsess utility consolidates trace files based on specified criteria such as session, client identifier, or service name. This results in a single trace file that combines the desired tracing information. Next, TKPROF is used to format the output of the trace file generated by trcsess, providing a readable summary of the trace, including execution counts, execution times, and SQL statement text along with execution plans. Steps: Use trcsess to combine trace files: Command: trcsess output=consolidated.trc service=your_service_name *.trc Use TKPROF to format the consolidated trace file: Command: tkprof consolidated.trc output.txt explain=user/password sys=no sort=prsela,fchela Reference: Oracle Database Performance Tuning Guide, 19c Oracle Database Utilities, 19c