Whether you are a developer creating web applications, a DBA, or a tester involved in web testing, you should know SQL. It is an important skill for both areas including database programming and validation. Hence, we took up this challenge to come up with 25 SQL performance interview questions.
SQL Performance Interview Questions and Answers
SQL performance tuning is a tough task and key in handling the increased load on a web application. So the interviewer would certainly dig you in and check how well you know about the subject.
Therefore, we’ve selectively picked SQL performance-tuning interview questions that could give you adequate coverage of the SQL performance-tuning concept.
Check out the 50 most-asked SQL query interview questions.
Q:-1. What is SQL Query Optimization?
Ans. Query Optimization is the process of writing the query in a way that it could execute quickly. It is a significant step for any standard application.
Q:-2. What are some tips to improve the performance of SQL queries?
Ans. Optimizing SQL queries can bring a substantial positive impact on performance. It also depends on the level of RDBMS knowledge you have. Let’s now go over some of the tips for tuning SQL queries.
1. Prefer to use views and stored procedures despite writing long queries. It’ll also help in minimizing network load.
2. It’s better to introduce constraints instead of triggers. They are more efficient than triggers and can increase performance.
3. Make use of table-level variables instead of temporary tables.
4. For faster results, use UNION ALL. It combines data sets without removing duplicates, unlike UNION which filters them.
5. Prevent the usage of DISTINCT and HAVING clauses.
6. Avoid excessive use of SQL cursors.
7. Use SET NOCOUNT ON in stored procedures to signify the affected rows by a T-SQL statement. It would lead to reduced network traffic.
8. It’s a good practice to select only the columns you need from a table, rather than retrieving all of them.
9. Prefer not to use complex joins and avoid disproportionate use of triggers.
10. Create indexes for tables and adhere to the standards.
Q:-3. What are the bottlenecks that affect the performance of a Database?
Ans. The database layer often becomes the final hurdle in achieving optimal scalability for a web application. Database performance leaks can act as bottlenecks, significantly impacting responsiveness. Here are some common performance issues to be aware of:
1. Abnormal CPU usage is the most obvious performance bottleneck. However, you can fix it by adding more CPU units or switching to an advanced CPU. It may look like a simple issue but abnormal CPU usage can lead to other problems.
2. Low memory (RAM) is the next most common bottleneck. If the server can’t manage the peak load, it poses a big question mark on the performance. Memory is a critical resource for applications to run optimally as it’s way faster than persistent memory. Also, when the RAM goes down to a specific threshold, the OS turns to utilize the swap memory. But it makes the application run very slow.
You can resolve it by expanding the physical RAM, but it won’t solve memory leaks if there are any. In such a case, you should profile the application to identify the potential leaks within its code.
3. Too much dependency on external storage like SATA disk could also make it a bottleneck. Its impact becomes visible while writing large data to the disk. For example, when you see the output operations are quite slow, it indicates the disk is becoming the bottleneck.
In such cases, you need to do scaling. Replace the existing drive with a faster one. Try upgrading to an SSD hard drive or something similar.
Q:-4. What are the steps involved in improving the SQL performance?
Ans.
Discover – First of all, find out the areas of improvement. Explore tools like Profiler, Query execution plans, SQL tuning advisor, dynamic views, and custom stored procedures.
Review – Brainstorm the data available to isolate the main issues.
Propose – Here is a standard approach one can adopt to boost performance. However, you can customize it further to maximize the benefits.
1. Identify fields and create indexes.
2. Modify large queries to make use of indexes created.
3. Refresh the table and views and update statistics.
4. Reset existing indexes and remove unused ones.
5. Look for dead blocks and remove them.
Validate – Test the SQL performance tuning approach. Monitor the progress at regular intervals. Also, track if there is any adverse impact on other parts of the application.
Publish – Now, it’s time to share the working solution with everyone on the team. Let them know all the best practices so that they can apply when needed.
Q:-5. What is an “explain” plan?
Ans. The “explain” plan is a term used in Oracle. It is a type of SQL clause in Oracle that displays the execution plan that its optimizer plans for executing the SELECT/UPDATE/INSERT/DELETE statements.
Q:-6. How do you analyze an “explain” plan?
Ans. While analyzing the “explain” plan, check the following areas.
1. Driving table
2. Join order
3. Join method
4. Unintentional cartesian product
5. Nested loops, merge sort, and hash join
6. Full table scan
7. Unused indexes
8. Access paths
Q:-7. How do you tune a query using the “explain” plan?
Ans. The “explain” plan shows a complete output of the query costs including each subquery. This is directly proportional to the query execution time. The plan also depicts the problem in queries or sub-queries while fetching data from the query.
Q:-8. What is a Summary Advisor and what type of information does it provide?
Ans. A summary advisor serves as a tool for analyzing and recommending materialized views. It can significantly boost SQL performance by selecting the optimal set for a given workload. Additionally, it offers insights into the recommended materialized views.
Q:-9. What is the most probable reason for a SQL query to run as slowly as 5 minutes?
Ans. One of the main reasons for a query taking over 5 minutes could be a sudden increase in data volume within a table it accesses. To diagnose further, gather statistics on the affected table and monitor any recent changes at the database or object level.
Q:-10. What is a Latch Free Event? And when does it occur? Also, how does the system handle it?
Ans. In Oracle, the Latch Free wait event occurs when a session requires a latch, and attempts to get it but fails because someone else has it.
So it sleeps with a wait eying for the latch to get free, wakes up, and tries again. The time duration for it was inactive is the wait time for Latch Free. Also, there is no ordered queue for the waiters on a latch, so the one who comes first gets it.
Q:-11. What are Proactive tuning and Reactive tuning?
Ans.
Proactive tuning – The architect or the DBA determines which combination of system resources and available Oracle features fulfill the criteria during Design and Development.
Reactive tuning – This is the bottom-up approach to discover and eliminate bottlenecks. The objective is to make Oracle respond faster.
Q:-12. What are Rule-based Optimizer and Cost-based Optimizer?
Ans. Oracle determines how to get the required data for processing a valid SQL statement. It uses one of the following two methods to make this decision.
Rule-based Optimizer – When a server doesn’t have internal statistics supporting the objects referenced by the statement, the RBO method gets preference. However, Oracle will deprecate this method in future releases.
Cost-based Optimizer – When internal statistics are abundant, the CBO gets precedence. It selects an execution plan with the lowest cost based on system resources.
Q:-13. What are several SQL performance tuning enhancements in Oracle?
Ans. Oracle provides many performance enhancements, some of them are:
1. Automatic Performance Diagnostic and Tuning Features
2. Automatic Shared Memory Management – It gives Oracle control of allocating memory within the SGA.
3. Wait-model improvements – Several views have come to boost the Wait-model.
4. Automatic Optimizer Statistics Collection – Collects optimizer statistics using a scheduled job called GATHER_STATS_JOB.
5. Dynamic Sampling – Enables the server to enhance performance.
6. CPU Costing – It’s the basic cost model for the optimizer (CPU+I/O), with the cost unit as per the time optimizer.
7. Rule-Based Optimizer Obsolescence – No more used.
8. Tracing Enhancements – End-to-end tracing allows a client process to be identified via the Client Identifier while not using the typical Session ID.
Q:-14. What are the tuning indicators Oracle proposes?
Ans. The following high-level tuning indicators are available to establish if a database is experiencing bottlenecks or not:
1. Buffer Cache Hit Ratio.
It uses the following formula.
Hit Ratio = (Logical Reads – Physical Reads) / Logical Reads
Action: Advance the DB_CACHE_SIZE (DB_BLOCK_BUFFERS before 9i) that improves the hit ratio.
2. Library Cache Hit Ratio.
Action: Advance the SHARED_POOL_SIZE to increase the hit ratio.
Q:-15. What do you check first when the SYSTEM tablespace has multiple fragments?
Ans. Firstly, check if the users don’t have the SYSTEM tablespace as their TEMPORARY or DEFAULT tablespace assignment by verifying the DBA_USERS view.
Q:-16. When would you add more Copy Latches? What are the parameters that control the Copy Latches?
Ans. If there is excessive contention for the Copy Latches, check from the “redo copy” latch hit ratio.
In such a case, add more Copy Latches via the initialization parameter LOG_SIMULTANEOUS_COPIES to double the number of CPUs available.
Q:-17. How do you confirm if a tablespace has disproportionate fragmentation?
Ans. You can confirm it by checking the output of SELECT against the dba_free_space table. If it points out that the no. of a tablespace extent is more than the count of its data files, then it proves excessive fragmentation.
Q:-18. What can you do to optimize the %XYZ% queries?
Ans. Firstly, set the optimizer to scan all the entries from the index instead of the table. You can achieve it by specifying hints.
Please note – crawling the smaller index takes less time than scanning the entire table.
Q:-19. Where do the I/O statistics per table exist in Oracle?
Ans. There is a report known as UTLESTAT which displays the I/O per tablespace. However, it doesn’t help pinpoint the table with the highest I/O activity.
Q:-20. When is the right time to rebuild an index?
Ans. Firstly, select the target index and run the ‘ANALYZE INDEX VALIDATE STRUCTURE’ command. Every time you run it, a single row will get created in the INDEX_STATS view.
But the row gets overwritten the next time you run the ANALYZE INDEX command. So move the contents of the view to a local table. After that, analyze the ratio of ‘DEL_LF_ROWS’ to ‘LF_ROWS’ and see if you need to rebuild the index.
Q:-21. What exactly would you do to check the performance issue of SQL queries?
Ans. Most probably, the database isn’t slow, but it’s the worker session dragging the performance. It is the abnormal session accesses that cause the bottlenecks.
1. Review the events that are in wait or listening mode.
2. Hunt down the locked objects in a particular session.
3. Check whether the SQL query is pointing to the right index or it is not.
4. Launch SQL Tuning Advisor and analyze the target SQL_ID for making any performance recommendation.
5. Run the “free” command to check the RAM usage. Also, use the TOP command to identify any process hogging the CPU.
Q:-22. What is the information you get from the STATSPACK Report?
Ans. We can get the following statistics from the STATSPACK report.
1. WAIT notifiers
2. Load profile
3. Instance Efficiency Hit Ratio
4. Latch Waits
5. Top SQL
6. Instance Action
7. File I/O and Segment Stats
8. Memory allocation
9. Buffer Waits
Q:-23. What are the factors to consider for creating an Index on the Table? Also, How do you select a column for the Index?
Ans. The creation of an index depends on the following factors.
1. Size of the table,
2. Volume of data
If the Table size is large and you need a smaller report, create an Index.
To select a column for indexing, as per the business rule, you should either go with a primary or if not, then use a unique key.
Q:-24. What is the main difference between Redo, Rollback, and Undo?
Ans.
Redo – Log that records all changes made to data, including all uncommitted and committed changes.
Rollback – Segments to store the previous state of data before the changes.
Undo – Helpful in building a read-consistent view of data. The data gets stored in the undo tablespace.
Q:-25. How do you identify shared memory/semaphores for a specific DB instance on a multi-server system?
Ans. Set the following parameters to distinguish between the in-memory resources of a DB instance.
1. SETMYPID
2. IPC
3. TRACEFILE_NAME
Use the ORADEBUG command to explore their underlying options.
Summary – Interview Questions on SQL & Database Performance
We hope you found these SQL performance tuning interview questions helpful. While the initial focus was on Oracle, we’ve also included general questions applicable to all databases.
Please share any specific topics you’d like us to cover so we can provide more valuable content for you.
Find some useful SQL tips to improve the performance of your queries.
We need your support to run this blog, so share this post on your social media accounts like Facebook / Twitter. This is how you will encourage us to come up with more informative stuff.
Keep learning,
TechBeamers.
