Hello. My name's John Pocknell, Toad for Oracle product manager. And welcome to this multipart series on enabling agile database development using Toad for Oracle.
In Step 4 we're going to talk about how you can optimize your SQL and PL/SQL code using Optimize Current SQL feature in Toad. So why is this important? It's a well-known fact that addressing SQL-related issues in development costs a fraction of what it would cost to fix in production. So in other words, the earlier we fix the problem, then the cheaper it is for the business.
Poor SQL execution in development and test can cause major problems in production, which are exacerbated by scalability issues. More transactions, more users, more data. And this can lead to expensive rework and associated business costs.
Developers, often not sure what to do to fix poorly executed SQL, will often rely on a DBA to fix the problem for them. And that can be extremely disruptive.
So developers often try to rewrite the SQL manually. But this can be very time-consuming, and is often ineffective.
So how hard can it be to rewrite SQL? Well, it might surprise you to know that for a moderate SQL statement, as you can see on the right-hand side here, there are actually 30,491 possible ways that you can rewrite this query. Now granted, some of those rewrites will have the same execution plan. But if you look at the optimization details in the small print here, you can see that 985 of these rewrites generate a unique execution plan.
Now that would take a developer hours and hours and hours to be able to do manually. And that's the purpose of SQL Optimizer. SQL Optimizer basically automates this rewriting operation and generates multiple rewrites in seconds. So let's take a look at this in a demo.
So you recall in my previous video we used the PL/SQL profiler to identify a performance bottleneck. And by tuning the SQL statement, you can see the resulting effect there with the run on the left. So let's actually walk through the process of tuning that SQL statement.
So here is a stored procedure that we had before. And when we use the profiler, we'd identified that it was the second SQL statement that was the one that was causing the problem. So what we need to do is tune the SQL statement. So we can tune this SQL statement actually in situ. We don't have to copy and paste it, or reformat, or anything like that. Toad will do that itself.
So we come up to the toolbar here. And we select all to Optimize SQL at carat using rewrites. So again, no need to highlight anything. Just as long as the carat is in the SQL statement somewhere, then that's fine.
And what will happen is that it will automatically reform the SQL so that it will execute outside of the PL/SQL program. So you can see it's removing into. And it's replaced the locally declared variable of DPT_NAM with a bind variable-- DPT_NAM, so that when it executes, it will prompt for a value for DPT_NAM. So click OK.
So we have some basic options that we can put in here. We can limit the optimization to a number of minutes, if you've only got a short period of time to do this. If the SQL statements normally will take a longer time to run. And that's why you're trying to improve performance, you might want to limit the optimization process to simply generate rewrites without actually executing anything, and then execute them after the rewriting process has happened.
You can also refactor SQL before optimizing. So making sure that you're taking advantage of any efficiencies that you can build into the way the SQL statement has been written. For example, converting DECODE to CASE statements, removing subqueries, or changing subqueries into the right syntax ahead of time.
So I'm just going to go ahead with all the defaults and have it generate the rewrites for me. So that's going to, first of all, prompt me for a value of DPT_NAM, because it's now using a bind variable. And I've got a value in here, which I've entered. And I click OK. And that will initiate the process of rewriting.
So now it's going to take that original SQL statement, as you can see displayed in side window on the right hand. And it's now looking for alternative rewrites. It will execute each of those to collect the execution statistics. And then when that's done, I've got to compare them side-by-side.
So now you can see that it's generated a variety of different rewrites. In fact, only five in this particular case. The optimization process actually generated 133 rewrites in general. But only five were tested, because the remaining ones had the same execution plan. Therefore, there's no benefit.
Something else to notice too is that you can alter the goal of the optimization process by elapsed time or CPU I/O or any of these other metrics. It found a better alternative, which is alternative number one. If I click it, you can see it viewed here.
But let's actually get a sense of how it compares against the original SQL statement I had in my stored procedure. So that is the one on the left-hand side that was in the stored procedure. On the right here is the rewritten one. You can see quite a few changes in the syntax.
And down here at the bottom, you can see the execution plan displayed. So you can see any differences in here, as well as execution statistics. So it's basically execution in significantly less time. The original SQL statement took 31 seconds. The best alternative ran in 0.27 seconds. So a big improvement.
And so what we need to do at this point is to copy and paste this statement, and put that into our stored procedure. The other thing
06:56