1Z0-997-25 Question 85
Select 2Your team has reported significantly slower query performance on an Oracle Database running on a VM DB System in Oracle Cloud Infrastructure (OCI). Through preliminary logs and monitoring, you learn that concurrency-related wait events are spiking during peak workload periods. Which TWO actions should you take to effectively investigate and mitigate these concurrency waits?
- A
Run AWR or ADDM reports to identify top resource-consuming SQL statements and blocking sessions.
- B
Immediately switch the DB System shape to a larger CPU count without further analysis.
- C
Review session activity in Performance Hub alongside OS metrics to pinpoint potential lock contention or CPU saturation.
- D
Disable Oracle Automatic Indexing to reduce overhead and eliminate concurrency issues.
- E
Restart the entire database to clear all sessions and restore performance.
Show answer and explanation
Correct answers: A, C
Explanation
When concurrency waits spike, the most effective strategy is to gather diagnostic data through AWR, ADDM, and performance tools such as Performance Hub. Reviewing resource usage, session activity, and lock contention allows you to pinpoint the cause�whether it's inefficient SQL, locking, or CPU saturation�and then apply targeted fixes. Oracle documentation recommends analyzing top wait events, high-load SQL, and session-level data before deciding on any major configuration changes or upgrades.
- A. Correct.
Option 1 is correct. AWR (Automatic Workload Repository) and ADDM (Automatic Database Diagnostic Monitor) are core Oracle database diagnostics tools. They help identify the top SQL statements consuming the most resources and reveal blocking or long-running sessions. Reviewing these reports is a standard starting point for investigating concurrency-related problems.
- B. Incorrect.
Option 2 is incorrect. Simply moving to a higher CPU shape without root-cause diagnosis can mask the underlying issue and may incur unnecessary costs. Tuning and identifying the source of concurrency waits through established diagnostics is more effective before scaling resources.
- C. Correct.
Option 3 is correct. Performance Hub in OCI provides a real-time view of database and OS metrics, making it easier to identify if sessions are blocked, waiting, or using excessive CPU. Correlating these findings with AWR data can reveal whether the concurrency issues stem from lock conflicts, CPU saturation, or other bottlenecks.
- D. Incorrect.
Option 4 is incorrect. Disabling Oracle Automatic Indexing does not inherently resolve concurrency waits. In fact, automatic indexing often helps optimize SQL paths. Turning it off could lead to suboptimal execution plans, exacerbating performance issues.
- E. Incorrect.
Option 5 is incorrect. While restarting the database might temporarily clear blocking sessions, this is rarely a recommended solution for concurrency-related issues. It's a disruptive action that doesn't address the root cause of the problem.