DBConsole 11g (part 2) – Using the SQL Performance Analyzer

Now straight into the more interesting Oracle 11g DBConsole features. Let’s start using the SQL Performance Analyzer and doing so make use of Replay. The Performance section of the DBConsole utility has a link to Advisor Central. The Advisor Central has again two sections: The Checkers section and the Advisors section.

From the Advisor section you are able to click on the SQL Performance Analyzer link.

Here you can choose from three options. I will demonstrate the “Guided Workflow”, but before that I show you the screens of the “Optimizer Upgrade Simulation” and “Parameter Change” sections. The descriptions in the screenshots are self-explainable.

SQL Performance Optimizer – The Guided Workflow

The guided workflow section will give you the opportunity to see the performance impact between two defined situations. Choosing for the Guided Workflow option we are presented with the following screen.

Guided Workflow

Clicking on “execute” for step 1, the following screen is presented.

but STS first…

I don’t have a SQL Tuning Set yet, so I clicked the “Link to STS Creation Wizard” URL. That forwarded me to another page.

Because it is my first attempt, I created a simple tuning set based on one of the APEX schemas and clicked next.

Again I kept it simple and picked out a load model based on the AWR snapshots taken the last 24 hours and clicked next.

Because I hadn’t collected much data yet via AWR, I didn’t want to filter out anything and clicked on next again.

On the Schedule page I executed the setup via the immediate option and clicked next.

The review page shows a summery and the PL/SQL block to be executed. Here I submitted the created tuning set. The following screenshot show the succesfull creation of my first STS 😉

Back on track

OK, I created now a tuning set based on an APEX schema; let’s see what we can do regarding performance testing via the rest of the Guided Workflow tour. I got back to the Guided Workflow and executed the first step again.

After filling in a name and description I chose my SQL tuning set and clicked the “Create” button. This action led me back to the Guided Workflow page.

Here I clicked on execute again (for step 2).

The Replay Trial Name was auto-generated and I left it at that. Filled in a description and set the Per-SQL Time Limit on “Customize” (other options are EXPLAIN ONLY and UNLIMITED). Because it is just a test, I set the time to 1 minute and clicked “Submit” again.

After a short while executing the task, the status for step 2 was set to OK and the execute option for step 3 was activated. Now we should change the environment, before executing step 3, for instance, remove statistics or load more or less table data. I am just testing the procedure and because I wanted a sort of baseline, I didn’t alter anything.

Clicking on the execute button took me to the following screen.

The screen is identical as the one in step 2. This time, becoming brave, I set the Per-SQL Time Limit on UNLIMITED and clicked again on “Submit”.

After finishing processing, I could again click on “execute” for the next step (4)

The following screen can be used to set the different trials you want to compare against a comparison metric (with options as show in the screenshot). I had only defined two trials so I went ahead, kept it default on “execute elapsed time” and clicked on “Submit”.

Despite this “limitless” setting in one of the trails defined, it went well and took not much time (~ 1 minute).

So now onwards to report page via clicking for the last time on step 5, execute…

So here you see the results. Remember, I did twice, exactly the same (al least almost; I used only different workflow settings) and I didn’t change something on environment level. Global statistics and SQL Statement Count (“improved” contra “unchanged”) reflect this. If there are fluctuations, than they are probably caused by my heavy strained laptop (which I used for this small test) and the Oracle database environment build on it.

You got the possibility here to zoom a little further into details. The following shows the “SQL statement with errors” link.

Clicking on the main report page on “Improvement Impact: 92%” shows:

To see the following page I clicked on the SQL ID with the 48.010% Net Impact on Workload (%):

The second half of the screen shows a side to side comparison between the statements of the two trails.

Going back to the beginning (the report main page) and then clicking on the link “plan changed / plan unchanged” shows the last comparison page.

Conclusion

The SQL Performance Analyzer is a very powerful toolset that can help you with new deployments, analyze the impact of a database change or for see the results dealing with database upgrades. The whole SQL Performance Analyzer Guided Workflow process becomes more reliable via the error overview control page. Combined with the Database Replay, SQL Replay features of the Oracle 11g release, these tools will certainly decrease risks while, for example, implementing changes. The Guided Workflow section of the SQL Performance Analyzer also makes it very simple to implement a strategy and oversee the consequences.

Disclaimer

The information demonstrated and shared here is based on Oracle beta software. The following is intended to outline Oracle’s general product direction.  It is intended for information purposes only, and may not be incorporated into any contract.  It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions.  The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.