MCQs on Performance Tuning and Optimization | SQL Query Efficiency

Understanding Query Execution Plans

1. What is the purpose of a query execution plan in SQL?
   • a) To display the result set of a query
   • b) To show the order of operations for executing a query
   • c) To optimize the SQL query automatically
   • d) To eliminate unnecessary joins
2. Which of the following tools is commonly used in SQL Server to analyze query execution plans?
   • a) SQL Profiler
   • b) Query Analyzer
   • c) Execution Plan Viewer
   • d) Query Optimizer
3. What does the “Cost” in a query execution plan indicate?
   • a) The time it takes to execute the query
   • b) The number of rows returned by the query
   • c) The estimated resource usage (CPU, I/O) required to execute the query
   • d) The number of joins in the query
4. Which of the following is a key benefit of using query execution plans for performance tuning?
   • a) They provide insights into which indexes are being used
   • b) They always provide the fastest query execution
   • c) They automatically fix inefficient queries
   • d) They show the exact data returned by the query
5. Which SQL command is used to generate a query execution plan in SQL Server?
   • a) EXPLAIN
   • b) SHOW PLAN
   • c) EXPLAIN PLAN
   • d) SET STATISTICS IO

Using Indexes for Performance

6. What is the primary purpose of creating indexes in a database?
   • a) To enforce data integrity
   • b) To improve the performance of data retrieval operations
   • c) To simplify database design
   • d) To ensure consistency across tables
7. Which type of index is most effective when searching for exact matches on a column in SQL?
   • a) Composite Index
   • b) Unique Index
   • c) Bitmap Index
   • d) B-Tree Index
8. What is the impact of creating too many indexes on a table?
   • a) It increases the speed of data retrieval
   • b) It can negatively impact insert, update, and delete performance
   • c) It improves the query execution plans
   • d) It reduces the complexity of the database schema
9. Which type of index is best suited for columns that contain a low cardinality of distinct values?
   • a) Bitmap Index
   • b) Hash Index
   • c) B-Tree Index
   • d) Clustered Index
10. What is the main advantage of using a composite index?
    • a) It allows indexing of multiple columns in a single index
    • b) It speeds up the update operations
    • c) It reduces disk storage
    • d) It ensures data integrity

Optimizing Joins and Subqueries

11. Which of the following is a common way to improve join performance in SQL queries?
    • a) Using SELECT *
    • b) Reducing the number of rows returned by subqueries
    • c) Avoiding the use of indexes
    • d) Using more INNER JOINs
12. Which type of join is generally the most efficient when matching rows from two tables?
    • a) LEFT OUTER JOIN
    • b) RIGHT OUTER JOIN
    • c) INNER JOIN
    • d) FULL OUTER JOIN
13. When should you use a subquery instead of a join for better performance?
    • a) When joining multiple large tables
    • b) When the subquery returns a single value and can be optimized
    • c) When working with small datasets
    • d) When the subquery is used to filter data
14. What is the disadvantage of using nested subqueries in SQL?
    • a) They always return incorrect results
    • b) They may cause performance issues, especially with large datasets
    • c) They simplify the query writing process
    • d) They are more readable than joins
15. How can the use of EXISTS in subqueries improve performance compared to using IN?
    • a) EXISTS stops processing as soon as a match is found, improving performance
    • b) IN is always faster than EXISTS
    • c) EXISTS requires more disk I/O than IN
    • d) EXISTS processes all rows in the subquery, while IN does not

Hints in Oracle SQL

16. What is the purpose of using optimizer hints in Oracle SQL?
    • a) To improve the structure of database tables
    • b) To force the optimizer to choose a specific execution plan
    • c) To automatically create indexes on tables
    • d) To validate query results
17. Which of the following hints is used to instruct Oracle to use a specific index?
    • a) USE_HASH
    • b) INDEX
    • c) USE_NL
    • d) NO_MERGE
18. Which of the following optimizer hints is used to disable the use of a particular index in Oracle?
    • a) NO_INDEX
    • b) DISABLE_INDEX
    • c) USE_NO_INDEX
    • d) INDEX_DESC
19. The LEADING hint in Oracle SQL is used to:
    • a) Specify the join order for the tables in a query
    • b) Improve the performance of subqueries
    • c) Avoid using indexes
    • d) Optimize data retrieval for large tables
20. What is the result of using the USE_NL hint in Oracle SQL?
    • a) It forces Oracle to perform a full table scan
    • b) It tells Oracle to use a nested loop join for the query
    • c) It suggests using a hash join for the query
    • d) It disables the use of parallel execution

Analyzing and Resolving Lock Contention Issues

21. What is the main cause of lock contention in a database?
    • a) Simultaneous access to the same data by multiple transactions
    • b) Lack of indexing on frequently queried columns
    • c) Incorrect query execution plans
    • d) Large data retrieval operations
22. Which of the following strategies can help reduce lock contention in a database?
    • a) Reduce the number of indexes
    • b) Increase the isolation level of transactions
    • c) Decrease the size of transactions to minimize locking
    • d) Use SELECT statements with NOLOCK
23. In SQL, what is the purpose of the NOWAIT keyword when used with a SELECT FOR UPDATE statement?
    • a) It waits indefinitely for a lock to be released
    • b) It prevents the transaction from acquiring a lock if another transaction has already locked the row
    • c) It automatically rolls back the transaction if a deadlock is detected
    • d) It locks the row without affecting other transactions
24. Which of the following can lead to deadlock in a database system?
    • a) Multiple transactions trying to update the same set of rows in a different order
    • b) Using non-clustered indexes
    • c) Running complex queries
    • d) Optimizing queries using joins
25. What can be done to resolve a deadlock situation in a database?
    • a) Increase the transaction timeout
    • b) Allow multiple transactions to access the same data concurrently
    • c) Use a higher isolation level to prevent locking
    • d) Identify and eliminate the circular wait condition
26. Which SQL statement can be used to view lock contention issues in Oracle databases?
    • a) SHOW TABLES
    • b) EXPLAIN PLAN
    • c) V$LOCK
    • d) DBMS_LOCK
27. What does the FOR UPDATE clause do in SQL?
    • a) It prevents any changes to the selected rows
    • b) It locks the selected rows for the current transaction to prevent changes by others
    • c) It updates the rows with new values
    • d) It allows only read operations on the selected rows
28. What is a potential issue with increasing the transaction isolation level to SERIALIZABLE?
    • a) It leads to increased lock contention and reduced concurrency
    • b) It improves query performance by allowing parallel execution
    • c) It speeds up the execution of the queries
    • d) It automatically resolves deadlock issues
29. In Oracle, what does the V$SESSION view help identify?
    • a) Active SQL queries
    • b) The locking sessions and their associated locks
    • c) The number of rows returned by queries
    • d) Index statistics
30. Which isolation level would reduce the likelihood of lock contention while still providing consistent results?
    • a) SERIALIZABLE
    • b) READ COMMITTED
    • c) REPEATABLE READ
    • d) READ UNCOMMITTED

Answer Key: