Today’s post is about SQL tuning. During my more than 14 years of experience as a Database Administrator I haven’t found a better tool than Enterprise Manager (EM) to monitor Oracle’s DB performance. The “Top Activity” graph has been a constant during all these years.
Note: The EM’s Top Activity page has been deprecated in EM 13.3 PG.
So let’s start from the beginning. If I receive a call or ticket stating that the DB or a process in the DB is slow, the first thing I do is to jump to the “ASH Analytics” page in EM. This page is part of the “Diagnostics” DB pack.
Here I can spot right away SQL_ID 6kd5jj7kr8swv being the top CPU consumer followed by other 3 SQL_ID’s. I’m going to create a SQL Tuning Set (STS) containing all these SQL’s.
From the STS dashboard I’m going to to click on “Create” to launch the wizard.
Step 1 requires a name and owner for the STS.
Note: You can create an empty STS and populate it later.
The next step is to load the STS with SQL’s. I’m going to choose “Load SQL one time only” and select from “Cursor Cache“. This because I’m sure they are loaded in memory. Another possible option is to load them from the AWR repository.
Next is to filter the SQL’s we want top load to our STS. I’m using the SQL ID row along with the IN operator to achieve this.
The next screen will ask you if you want to run that immediately or you want to schedule this for a later time.
And finally the review page. If everything looks right, then click on “Submit“.
Once the STS is created you will be able to see those 4 SQL’s loaded into it. I’m going now to schedule a SQL Tuning Advisor (STA) task to find out if these SQL’s can be tuned for better performance. STA is part of the “Tuning” DB pack.
Let’s review the results of the STA task we just created couple of minutes back. STA examined 4 distinct SQL’s. It only found recommendations for 3 of them with an overall potential benefit of 55 seconds in DB time.
Let’s click on “Show all results” button to see detailed information about the recommendations. The table below shows all 4 SQL’s along with their recommendations. We can spot right away the benefits of tuning SQL ID 6kd5jj7kr8swv. A SQL Profile could be created with a benefit of 57% or an Index with a benefit of 72%.
What do you do here? Do you hit “Implement All SQL Profiles” and cross your fingers hoping the new execution plan are going to be faster?
You don’t know how many times I saw sub-optimal plans generated by SQL Profiles.
There’s actually a better way to handle this. Real Application Testing (RAT) DB pack has a tool named SQL Performance Analyzer (SPA). This tool allows you to play the SQL before and after the changes (SQL Profile) and gives you a detailed report with the results.
In order to do that you’ll have to click on the “Validate All Profiles with SPA” button.
What is going to happen is that SPA will gather data before and after implementing the SQL Profiles and will produce a report.
Note: SPA will actually implement the SQL Profiles. Keep this in mind in a running Production system. RAT has also the capabilities to replay this workload in a Non-Production system. You can implement the SQL Profiles there without harming the Production performance.
Ooops! The SQL Profiles actually have a regression impact of -824%. Look at the Net Impact for the 1st SQL. It has a -830% impact, this means that the SQL Profile produced a sub-optimal plan. After this I will go and disable the recently created SQL Profiles before they cause a bigger impact.
My next step will be to propose the Index creation. This may have different implications depending on the application you are running, the business needs and operating procedures. I decided to implement the index to measure the performance benefit.
Here you can see how the performance was before the SQL Profile (maroon), with the SQL Profile implemented (turquoise) and with the index created (pink).
In summary, SPA is a great tool that helps you validate that the changes made to the DB system are working as expected. SPA not only validates SQL Profiles, it also validates statistics, DB parameter changes, DB upgrades and also you can verify the performance of your DB system with an Exadata storage server simulation.
Thanks,
Alfredo