One of the best tips on SQL Tuning I can offer you is this:
DO NOT DO OVER-THE-FENCE TUNING.
It’s taken me years to finally figure this out.
What do I mean by over-the-fence SQL Tuning? It goes like this: Someone sends you an email, or puts in a ticket:
Hey John! Can you have a look at this query? It’s running pretty slowly
select …
from …
where …
The first temptation is to immediately start digging in to the execution plan and try to tune the query, but I’m telling you…DON’T.
The very first thing you want to do is this: Start a conversation with the person that’s brought the slow-running query to your attention.
Why on earth would you want to do that? Why not just delve straight into the query??
Because more often than not, you probably don’t have all the pertinent information you need. In fact, many times, developers will find a slow running query, and then they’ll unintentionally modify the query in some way.
How Developers Accidentally Modify Queries
One of the big things I’ve seen developers do is replace bind variables with literal values. This can have a huge effect on your plan–especially if the literals and bind variables aren’t of the same type.
So if you’re tuning the query they gave you, but the data types of some of the values in the query are different from the query that’s actually running in production code, then you’re trying to tune an entirely different query than what’s running. In fact, the plan you come up with at the “ideal execution plan” may not even be a viable plan for your query!!
Another example of how a developer might unintentionally change a query: You might have a “dynamic” query that changes based on user inputs. For example, lets say we have a form that allows the end user to search for a person. They can either search by first name, last name, or phone number, or any combination of the three.
The code might look something like this:
sql = "select id, first_name, last_name, phone_number, email
from customers
where 1 = 1";
if (firstName != "") {
sql += " and first_name = :firstName";
}
if (lastName != "") {
sql += " and last_name = :lastName";
}
if (phoneNumber != "") {
sql += " and phone_number = :phoneNumber";
}Many developers would say that this is just one query with different “tweaks” to it…but in reality, it’s 8 different queries! And any one of those queries would likely be optimized differently than any of the others. Things like this probably won’t become fully apparent in the email or ticket you receive that concerns the tuning issue.
Getting a Full Understanding of The Situation
Another example of why you want to avoid over-the-fence tuning and start a conversation with the developer is you may be able to optimize by gathering a full understanding of the query and how it’s used within the application.
Let me give you an example: I had a developer contact me a while back, asking me to tune a query. I immediately started a conversation with the developer, and I discovered what the developer was doing was this:
- Run a query that takes a long period of time (this is the query that they highlighted as needing tuned)
- Import the result set from the query into an Excel spreadsheet
- Rung a second query that also took a long period of time
- Import those results into the same Excel workbook
- Use Excel’s VLOOKUP() function to “join” the two result sets (which also took a long period of time, since each result set was pretty sizable)
If I had just done over-the-fence tuning, I would have first tuned the first query…then the developer would have asked me to tune the second query…and then the overall process still would have been slow, because of the VLOOKUP() join in Excel at the end.
But because I didn’t go down that path…Because I stopped and asked questions, I was able to tune their entire process! Instead of two slow-running queries, I was able to have the developer issue only one query which ran significantly faster than any of the other steps.
So instead of trying to do over-the-fence tuning…instead of trying to work on a query that was “chucked over the fence” to you , go start up a conversation with the person that’s asking you to tune the query.
Starting a Conversation With Your Stakeholder
You might be asking–What questions should you be asking your stakeholder?
Basically, your objective is to be able to
- Understand why they’re running the query in the first place (i.e. what is the business need behind their query)
- Understand the exact conditions that they’re running the query under (what does their database session look like when it runs the query?)
- And Understand things from an application perspective (in other words, is this really a SQL Tuning problem after all? Or is this an application problem?)
I usually ask the following questions:
- What happens before the query? (Is this an application problem?)
- What happens after the query? (Is this an application problem?)
- What is the query, and what are the exact conditions that the query is run under? (What does the database session look like?)
- What’s the overall objective? (What are the business needs behind the query?)
So a more visual representation is this:
What Happens Before The Query?
You want to know what triggers this query. Is it part of a regularly scheduled job? Does the end-user click a button in the application that causes this query to fire? Does a trigger fire this query?
Figuring out how and when the database is running the query will give you more insight into what the application is doing. Thus, it may clue you in on whether you have a problem that needs to be addressed at the application layer.
For example, you might ask “Is the query run as some kind of polling operation that’s meant to detect changes? If so, how frequently are we running query? Could we use something else to detect changes that might not be as CPU-intensive, such as Advanced Queues?”
Dig into the details of how frequently the query is run, don’t be afraid to ask questions like “is there any way this query can run less frequently?” or “can we bulk-process things?”
What Happens After The Query?
What happens to the result set once we get it back from the database? Do we dump it into an Excel spreadsheet as part of a report? Is a GUI displaying the result set to the user? Or is it part of a flat file extraction?
I’ve seen some silly, silly things where people dump millions of rows to an Excel spreadsheet…only to aggregate them down using Excel.
Nobody has time to read a report that is millions of rows long. Anyone who says that they do is lying.
Asking questions like “what happens to the result set once we’ve got it” can help you dig into some of the ridiculous situations you can find and help eliminate waste.
What Is The Query?
“But Kaley!” I hear you say, “I already know what the query is! They sent it to us in the email/ticket!!”
Really? Do you know everything about the query??
- Did the developer replace bind variables with literals when he or she sent the query to you?
- Do you know what the data types of the bind variables are?
- Do you know if the text of the query can change based on input?
- If you’re using Oracle Virtual Private Database, are we running the query with a VPD context set? Is someone with EXEMPT ACCESS privileges running the query?
- Are we using FIRST_ROWS mode, or ALL_ROWS mode?
- What NLS Settings are we using when running this query?
Your objective is to be able to figure out the exact set of circumstances that they’re running the query under, so that you can replicate their exact results.
Once you’re able to replicate their results, it ensures that you have a great starting point.
What Is The Overall Objective?
Why are we running this query in the first place? Is it part of a report? Maybe this is a supporting query to show information in a GUI? Or is this part of an ETL job?
The more information you have, the better you’ll understand the business objectives that you’re aiming for. You might even find more efficient ways to achieve the same results!
Summary: Why You Should Avoid Over-The-Fence Tuning
Whew! This post has been a long one!!
The most important thing to take away is that you should should avoid over-the-fence tuning. Start a conversation with anyone who gives you a tuning mission. You need to fully understand what happens before the query, what happens after we run the query (what do we do with the result set), what query are we running, and what are the exact set of circumstances we’re running it under, and what business objective are we trying to reach.
If you ask these questions, you’ll align with your business objectives, and you won’t waste your precious time. You’ll be able to figure out when you need to rewrite the query, when you need to add parallelism, when you need to add an index, and when the problem isn’t a SQL Tuning problem at all, but rather an application design problem.
Free Oracle SQL Tuning Guide
Checkout my FREE guide, 7 SQL Tuning Secrets You Can Use Immediately, Even If You’ve Never Tuned a Query In Your Life!
Get it here: tuningsql.com/secrets
