If you frequent Toad® World, then you are probably aware of the fact that Quest has a technology that offers to automatically tune your poorly performing SQL statements. When I speak to customer or prospective clients, it is generally received extremely well. Why is that? Of ALL the problems that database professionals have in common, the biggest pain everyone can relate to is a SQL statement that just doesn’t run fast enough.
What I want to talk about today is not how to use the Quest SQL Optimizer to fix the problem, but rather:
1. How you can identify bad SQL before they cause problems
2. How to develop SQL to run fast out of the box
3. How to find bottlenecks in your code or database if it has slipped into production.
When is the WORST time to identify a bad SQL?
I asked this question yesterday, and a DBA replied “At 3AM!” I couldn’t help but chuckle, as I have been there many times before. What I had in mind was actually.
The worst time to identify a bug or performance problem is when a customer points it out to you.
It is EXTREMELY expensive to fix a problem when it’s already reached production. Even if you don’t have to pay your DBA comp time for waking up at 3AM, if this happens often enough I guarantee you will need to hire another production level DBA to replace your disgruntled employees. Imagine how expensive that is!
So how can we identify these bad SQL statements before they reach the customers? Toad has a feature called CodeXpert which offers the ability to automate code reviews for your PL/SQL and embedded SQL statements. If you point CodeXpert to your source code and/or database objects, it will scan for and identify your complex and problematic SQL for you. If you are doing this on a regular basis in your development cycle, you can address these issues while the developers are actively working on a project rather than come back to it later when the developer is working on something else.
Proactively scan for bad SQL statements before they make it to QA or Production!
How can I develop FAST SQL out of the box?
Toad is popular because it makes it extremely easy to build extremely complex SQL statements in a short amount of time. However, every time you hard code a SQL statement into your application or report, you could be inserting a time-bomb. One popular study cites that 80% of database application performance problems are a direct result of bad SQL statements. I ask customers every day if they find this to be true in the real world and I have yet to run into someone who has a dramatically different opinion.
So, how do we get people to write GOOD SQL? One of the challenges that we have little control over is that people who know little about relational databases or SQL are often placed in a position to write the query statements for their program. If you are a Java developer who needs to query the database, all you care about is if you get the right data back. If it runs slow, that’s the DBA’s problem! I’m not picking on Java developers – this is common for all programming disciplines.
When you write a Query statement in Toad, Toad is shouting to make it better for you right there and then. Listen to the Toad!
Whether you are writing queries ad hoc or are using the Query Builder, tune that
query so it runs correctly and runs fast!
Not so lucky? Already facing poorly performing applications? What do I do?!?
It’s easy for me to say, “Hey, you should be developing really good SQL queries out of the box so you don’t have to worry about them later.” But in the real world, this is frequently impossible to achieve 100% of the time. As a DBA, you are a part-time fire fighter. When something bad comes up, it’s your job to fix it. So how can Toad help you find the problem?
Perhaps you have an application session that’s hogging application resources. Or you have a call in from an internal user that is having a problem with their program running. “It’s hung!!!” – How many times have you heard this?
Toad’s Session Browser can help. Open it, and view the queries by program. Then you can click through them and peruse the current SQL that is being executed, check the execution plans, and send to the SQL Optimizer right away for and immediate fix.
Program not running fast enough? Maybe it’s the SQL?
Maybe your system is being pegged and you’re not sure which application is causing the problem. Or maybe you don’t have 3 sessions in your system like I do. Maybe you have 3,000 sessions and you need a quick way to figure out which one is the culprit! Toad’s Top Session Finder will help you out!
Which program is causing the most network traffic? Find out, and then
navigate to the Session Browser for further investigation.
Have a Stored Procedure not running so fast lately?
Toad gives you two options for diagnosis. You can look at the theoretical or the actual. CodeXpert gives you the theoretical analysis. For a look at how your code is actually performing when executed, you can do an execution profile.
Step1: Turn on the ‘Stopwatch’. It’s on the Toad toolbar. 
Step2: Execute your stored procedure using the Editor or Schema Browser.
Step3: Open the Profiler Analysis window.
Step4: Look for the bottleneck. Which code line took the longest to run?
Takeaways
1. No one likes answering a beeper at 3AM.
2. Poorly written SQL can cripple your application’s performance.
3. Toad makes it easy to identify bad SQL in development.
4. Toad makes it easy to identify bad SQL in production.
5. Toad makes it easy to fix bad SQL not matter where it is.