Free Oracle Database 19c: Performance Management and TuningPractice Test

You are tasked with establishing a baseline for database performance metrics. Which statement best describes the purpose of establishing a performance baseline in Oracle Database 19c?

A database administrator notices that the database is experiencing performance degradation. Which view should be queried first to identify the top wait events currently affecting database performance?

Which SQL statement will display the current SGA component sizes and their current allocations in an Oracle 19c database?

What is the primary purpose of the Automatic Workload Repository (AWR) in Oracle Database 19c?

When using EXPLAIN PLAN to analyze a SQL statement, which column in the PLAN_TABLE indicates the estimated number of rows that will be returned by each step?

A production database is experiencing high wait times for 'db file sequential read' events. Which performance issue is most likely causing this wait event?

You need to analyze SQL statements that consumed the most CPU time during a specific period yesterday. Which AWR report section should you examine?

A SQL statement is performing poorly. After examining its execution plan, you notice it uses a HASH JOIN instead of a NESTED LOOPS join. Under what circumstances would a HASH JOIN typically be more efficient than NESTED LOOPS?

You observe high values for 'library cache: mutex X' wait events. What is the most likely cause of this contention?

When configuring Automatic Memory Management (AMM) in Oracle 19c, which initialization parameter must be set to enable this feature?

A developer asks you to help optimize a query that uses a bind variable. The execution plan shows a TABLE ACCESS FULL even though an appropriate index exists. What is the most likely reason for this behavior?

You want to prevent a specific SQL statement from being aged out of the shared pool. Which procedure should you use to mark the statement as 'kept'?

While analyzing a SQL statement using SQL Tuning Advisor, you receive a recommendation to create a SQL Profile. What does a SQL Profile contain?

An application is experiencing 'cursor: pin S wait on X' wait events with high frequency. What is the most effective way to address this issue?

You need to capture detailed execution statistics for a specific SQL statement including actual row counts for each operation. Which method provides the most detailed runtime execution statistics?

A database is configured with MEMORY_TARGET set to 16GB and MEMORY_MAX_TARGET set to 32GB. The DBA wants to increase memory allocation to 24GB without restarting the database. What is the correct approach?

During a performance investigation, you notice that the 'log file sync' wait time is significantly high. After examining the system, you find that 'log file parallel write' times are low. What is the most likely cause of this scenario?

You are using SQL Plan Management and want to ensure that a new, better performing plan becomes the accepted plan. The plan currently exists as a non-accepted plan in the SQL Plan Baseline. What is the correct procedure to evolve and accept this plan?

A critical query suddenly started performing poorly after statistics were gathered. The execution plan changed from using an index to a full table scan. You verify the new plan is suboptimal. Which feature would best prevent such regression while allowing the optimizer to evaluate potentially better plans in the future?

You need to diagnose a complex performance issue that occurs intermittently. The issue lasts only a few minutes and is difficult to catch. Which combination of tools and views would be most effective for capturing detailed diagnostic information during these brief performance degradations?