DB Interview Question — You need to tune a very slow SQL ‘select’ query on Oracle DB, How would you do that ? (Part 1)

Slow running SQL queries can introduce very high latency in your apps. Either the sql statement itself are badly written or the datamodel itself might be badly designed due to which we might see slowness in pulling the data. If you want to scale your application it is easy to add more servers (application servers e.g. tomcat / jetty etc) in the middle layer but it is not easy to add more database servers.

These are some common steps that you can take to enhance the performance of your slow running sql queries

1. Start with the Execution Plan
2. Evaluate the Execution Plan
3. Check percentage of table scans — are you doing full table scans
4. Add indexes
5. Re-Run the query — check execution plan again
6. Evaluate the execution plan and repeat.

In part 2 of this article I will cover an example query and would optimize it using an execution plan