Oracle DBA AWR and SQL Tuning Quiz

1. In SQL performance tuning, what does 'predicate pushdown' accomplish?

Filters data at the earliest stage of query execution

Delays query execution until all joins complete

Increases memory allocation for sorting

Removes all indexes from the execution plan

2. Which execution plan join method typically uses the least memory for joining large datasets?

Nested Loop Join

Hash Join

Sort Merge Join

Cartesian Join

3. What does a high 'Library Cache Hit Ratio' in AWR indicate?

Effective SQL statement reuse and low parse overhead

Excessive full table scans occurring

High disk I/O contention on data files

Poor buffer cache management

Submit

5. Which AWR metric measures the percentage of time the database was actively processing work?

Database CPU Time

Load Profile

Instance Efficiency

Wait Class Distribution

Submit

7. Which view provides Active Session History (ASH) data in Oracle Database?

V$ACTIVE_SESSION_HISTORY

V$SESSION

V$DATABASE

V$PARAMETER

8. What is the primary benefit of using hints in SQL tuning?

Override the optimizer's default execution plan decision

Automatically add indexes to tables

Reduce database parameter file size

Encrypt sensitive query data

9. The 'Top SQL' section in AWR reports is sorted by which metric by default?

Elapsed Time

Parse Count

Disk Reads

Rows Processed

10. Which parameter controls the AWR retention policy in Oracle Database?

Db_recovery_file_dest_size

Undo_retention

Db_files

Processes

11. What is the primary purpose of Oracle Automatic Workload Repository (AWR)?

Collect and store performance metrics and statistics

Automatically execute query optimization

Manage user account permissions

Monitor network connectivity

12. Which wait event commonly indicates I/O contention in AWR reports?

Db file sequential read

SQL*Net message from client

Log file sync

Pmon timer

13. What is the purpose of the 'EXPLAIN PLAN' statement in Oracle?

Display the execution plan without executing the query

Execute the query and show actual row counts

Compress query results

Encrypt SQL statements

14. The Oracle SQL Tuning Advisor is part of which feature?

Real Application Testing (RAT)

Oracle Data Guard

Oracle Golden Gate

Oracle Net Services

15. Which execution plan operation typically indicates a performance bottleneck if executed in high volume?

TABLE ACCESS FULL

INDEX UNIQUE SCAN

SORT AGGREGATE

COUNT STOPKEY

16. In SQL tuning, what does 'cardinality' refer to?

The estimated or actual number of rows returned by a query step

The number of indexes on a table

The frequency of backup operations

The total storage size of a table

17. Which tool is commonly used to generate AWR reports in Oracle Database?

Awrrpt.sql

Sqlplus.sql

Backup.sql

Audit.sql

18. What does the 'DB Time' metric in AWR represent?

Total CPU and wait time spent by database sessions

Time spent on network operations only

Time taken to complete full backups

Elapsed time since database startup

Submit

20. Which view contains the main AWR snapshot data in Oracle Database?

DBA_HIST_SNAPSHOT

V$ACTIVE_SESSION_HISTORY

DBA_SEGMENTS

V$SQL_PLAN

Oracle DBA Professional AWR and SQL Performance Tuning Quiz

1. In SQL performance tuning, what does 'predicate pushdown' accomplish?

2.

What first name or nickname would you like us to use?

2. Which execution plan join method typically uses the least memory for joining large datasets?

3. What does a high 'Library Cache Hit Ratio' in AWR indicate?

4. The SQL ____ Advisor automatically identifies tuning recommendations for high-load SQL.

5. Which AWR metric measures the percentage of time the database was actively processing work?

6. A ____ join is typically more efficient when joining a large table with a small dimension table.

7. Which view provides Active Session History (ASH) data in Oracle Database?

8. What is the primary benefit of using hints in SQL tuning?

9. The 'Top SQL' section in AWR reports is sorted by which metric by default?

10. Which parameter controls the AWR retention policy in Oracle Database?

11. What is the primary purpose of Oracle Automatic Workload Repository (AWR)?

12. Which wait event commonly indicates I/O contention in AWR reports?

13. What is the purpose of the 'EXPLAIN PLAN' statement in Oracle?

14. The Oracle SQL Tuning Advisor is part of which feature?

15. Which execution plan operation typically indicates a performance bottleneck if executed in high volume?

16. In SQL tuning, what does 'cardinality' refer to?

17. Which tool is commonly used to generate AWR reports in Oracle Database?

18. What does the 'DB Time' metric in AWR represent?

19. By default, AWR snapshots are captured every ____ minutes.

20. Which view contains the main AWR snapshot data in Oracle Database?