Which two statements are true about session wait information contained in v$session or v$session_wait?
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.
Oracle Database Reference, 19c
Oracle Database Performance Tuning Guide, 19c
An Oracle 19c database uses default values for all optimizer initialization parameters.
After a table undergoes partition maintenance, a large number of wait events occur for:
cursor: pin S wait on X
Which command reduces the number of these wait events?
The cursor: pin S wait on X wait event suggests contention for a cursor pin, which is associated with mutexes (a type of locking mechanism) that protect the library cache to prevent concurrent modifications. This issue can often be alleviated by deferring the invalidation of cursors until the end of the call to reduce contention. The correct command to use would be:
C (Correct): ALTER SYSTEM SET CURSOR_INVALIDATION=DEFERRED; This setting defers the invalidation of dependent cursors until the end of the PL/SQL call, which can reduce the cursor: pin S wait on X wait events.
The other options are incorrect in addressing this issue:
A (Incorrect): Setting CURSOR_SHARING to FORCE makes the optimizer replace literal values with bind variables. It doesn't address the contention for cursor pins directly.
B (Incorrect): CURSOR_SPACE_FOR_TIME=TRUE aims to reduce the parsing effort by keeping cursors for prepared statements open. It may increase memory usage but does not directly resolve cursor: pin S wait on X waits.
D (Incorrect): Increasing SESSION_CACHED_CURSORS caches more session cursors but doesn't necessarily prevent the contention indicated by the cursor: pin S wait on X wait events.
Oracle Database Reference: CURSOR_INVALIDATION
Oracle Database Performance Tuning Guide: Reducing Cursor Invalidation
Which two options are part of a Soft Parse operation?
During a soft parse, Oracle checks the shared SQL area to see if an incoming SQL statement matches one already in the shared pool. This operation includes syntax and semantic checks. The syntax check ensures the statement is properly formed, and the semantic check confirms that all the objects referenced in the SQL statement exist and that the user has the necessary privileges to access them. Reference:
Oracle Database Concepts, 19c
Oracle Database SQL Tuning Guide, 19c
You must write a statement that returns the ten most recent sales. Examine this statement:
Users complain that the query executes too slowly. Examine the statement's current execution plan:
What must you do to reduce the execution time and why?
The execution plan shows a full table access for the SALES table. To reduce the execution time, creating an index on SALES.TIME_ID would be beneficial as it would allow the database to quickly sort and retrieve the most recent sales without the need to perform a full table scan, which is I/O intensive and slower. By indexing TIME_ID, which is used in the ORDER BY clause, the optimizer can take advantage of the index to efficiently sort and limit the result set to the ten most recent sales.
B (Incorrect): Replacing FETCH FIRST with ROWNUM would not necessarily improve the performance unless there is an appropriate index that the optimizer can use to avoid sorting the entire result set.
C (Incorrect): There is no indication that the current statistics are inaccurate; hence, collecting new statistics may not lead to performance improvement.
D (Incorrect): While adaptive plans can provide performance benefits by allowing the optimizer to adapt the execution strategy, the main issue here is the lack of an index on the ORDER BY column.
E (Incorrect): Creating an index on SALES.CUST_ID could improve join performance but would not address the performance issue caused by the lack of an index on the ORDER BY column.
Oracle Database SQL Tuning Guide: Managing Indexes
Oracle Database SQL Tuning Guide: Using Indexes and Clusters
Database performance degraded between 23:15 and 23:30 for the last three nights. The awr snapshot interval is one hour. The AODM report contains nothing about this performance problem.
With which tool can you further analyze this problem?
The Active Session History (ASH) report is a tool that provides detailed information about active sessions for the time period specified. Since the AWR snapshot interval is one hour and does not capture the granularity needed for this issue, ASH reports are more suitable as they contain more granular data for sessions that were active during the period of interest.
Oracle Database Performance Tuning Guide, 19c
