Detect performance issues using PL/SQL Profiler

Hello. My name is John Pocknell, the Toad for Oracle product manager, and welcome to this multi-part series on enabling agile database development using Toad for Oracle. In step three, we're going to talk about how you can detect performance issues in your PL/SQL code by using the Toad PL/SQL profiler. So why is this important? Well, it's often problematic for developers to try to find out why our PL/SQL program is running slow, particularly if it contains multiple loops and SQL statements. And this problem may have come originally from a production DBA who's complaining about a performance problem in production. So it's very important that developers can profile the data using Toad. So Toad's profiling tool uses the Oracle profiling package. But if you used to use that package directly, its text-based and difficult to interpret. So what Toad does, it takes that data and it shows you graphically how long each line of code to execute, as well as providing code coverage data to expose any unused PL/SQL blocks or code that doesn't run. It can also be used to profile code dependencies as well. And it can be used to compare performance before and after resolution. So now I'm going to give you a demo of how you can use the PL/SQL profiler to detect a performance bottleneck in your PL/SQL code. So here I am inside of Toad, and I have a stored procedure open, which contains two SQL statements. I've been told that this stored procedure is taking too long to run. It's actually causing a problem in production and I need a fast way to identify exactly where the problem is and then fix the SQL statement. So the first thing with profile you need to know is how to turn it on. So there is a toggle button actually on the main toolbar. There's a similar toggle button in the set parameters window when you actually go ahead and execute the code. But if you've never used profile before-- and it is a feature from the base edition-- so if you're using Toad, you already have this. You may not be aware of the fact you haven't switched it on. And so you can switch it on in here. If it hasn't been installed yet, you can install it. That will say Add Profiler. And it will install a table into a schema of your choice. I'd recommend the Toad schema to install that into. And all we're doing is when you're profiling code, is we store that data in a table and then we give you a grepl representation of that data. So there are some Toad options as well. So if you go View Toad Options or just press the Toad Options button right here, and go down to Execute Compile, there is a setting here Save Profiler Settings Between Sessions, so when you close Toad down, it will still preserve whatever profiler settings you had for the next session. The other one is if you prefer to use the Hierarchical Profile, which was available from Oracle 11G, you can use that one turning it on right there. So to profile my code, I simply execute the code in a normal way. So I use this button here to execute the code. I don't want to compile any reference objects. and I Open the Set Parameters dialog. So here's the toggle button again. So I'll toggle that on just to make sure I'm collecting the profiler data. There's a profiler tab here, where I can configure the hierarchical profile if that's what I set up in the Options that I showed you earlier. And then just go ahead and execute the code. OK. So execution is complete. Let's close that window and then go to the profiler tab at the bottom. If this tab isn't open, just simply right click the tab region and turn it on. So from here, I'll go straight to the Details tab so I can view all the graphic information, just to refresh and just sort by run date so I have the latest run date showing at the top. There it is. And then expand out from the top level down to the actual program name, and they can see that I'm showing you the lines of code took the predominant amount of time to run. Line 36 took about 100% of the time to run, and in fact, took about just under 25 seconds to run. And if we look at the line 36, you see all of it is the SQL statement. So it's clearly shown this SQL statement, not the other one, is the one that's causing the performance bottleneck. And what I need to do now is to optimize the SQL statement. I will cover SQL optimization in the next video. But once the SQL statement has been tuned, the other way to use profiler is to compare performance before and after. So if I close this down for a second and just go ahead over to this one here, where this SQL statement has been tuned, and just run this one, with the profile turned on and gather the execution data. It's exactly the same, it's just that the SQL statement that was identified earlier has now been tuned. You can see that run a lot faster. Go to my profiler data, go to my details button again, and then sort by run date. So now I have two runs. And I compare these side by side, which I can do by multi-selecting them, you can see that run 63 compared to run 62, which is one I had earlier, took a lot less time. And so this is an example of comparing performance before and after tuning the SQL statement. So that's how you profile PL/SQL code. In the next video, we'll cover optimizing the SQL statement in a few easy short steps. Thank you very much for