DBConsole 11g (part 2) - Using the SQL Performance Analyzer 02 oracle11g advisor central 021

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.

DBConsole 11g (part 2) - Using the SQL Performance Analyzer 02 oracle11g advisor central 02

DBConsole 11g (part 2) - Using the SQL Performance Analyzer 01 oracle11g advisor central 01

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

DBConsole 11g (part 2) - Using the SQL Performance Analyzer 03 oracle11g dbconsole performance analyzer

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.

DBConsole 11g (part 2) - Using the SQL Performance Analyzer 04 oracle11g dbconsole upgrade simulation

DBConsole 11g (part 2) - Using the SQL Performance Analyzer 05 oracle11g dbconsole parameter change

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

DBConsole 11g (part 2) - Using the SQL Performance Analyzer 06 ora11g perfana 01

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

DBConsole 11g (part 2) - Using the SQL Performance Analyzer 07 ora11g tuning set 00

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.

DBConsole 11g (part 2) - Using the SQL Performance Analyzer 08 ora11g tuning set 01

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

DBConsole 11g (part 2) - Using the SQL Performance Analyzer 09 ora11g tuning set 02

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

DBConsole 11g (part 2) - Using the SQL Performance Analyzer 10 ora11g tuning set 03

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

DBConsole 11g (part 2) - Using the SQL Performance Analyzer 11 ora11g tuning set 04

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

DBConsole 11g (part 2) - Using the SQL Performance Analyzer 12 ora11g tuning set 05

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 😉

DBConsole 11g (part 2) - Using the SQL Performance Analyzer 13 ora11g tuning set 06

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.

DBConsole 11g (part 2) - Using the SQL Performance Analyzer 14 ora11g perfana 02

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.

DBConsole 11g (part 2) - Using the SQL Performance Analyzer 15 ora11g perfana 03

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

DBConsole 11g (part 2) - Using the SQL Performance Analyzer 16 ora11g perfana 04

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.

DBConsole 11g (part 2) - Using the SQL Performance Analyzer 17 ora11g perfana 05

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.

DBConsole 11g (part 2) - Using the SQL Performance Analyzer 18 ora11g perfana 06

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”.

DBConsole 11g (part 2) - Using the SQL Performance Analyzer 19 ora11g perfana 07

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

DBConsole 11g (part 2) - Using the SQL Performance Analyzer 20 ora11g perfana 08

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”.

DBConsole 11g (part 2) - Using the SQL Performance Analyzer 21 ora11g perfana 09

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

DBConsole 11g (part 2) - Using the SQL Performance Analyzer 22 ora11g perfana 10

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

DBConsole 11g (part 2) - Using the SQL Performance Analyzer 23 ora11g perfana 11

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.

DBConsole 11g (part 2) - Using the SQL Performance Analyzer 24 ora11g perfana 12

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

DBConsole 11g (part 2) - Using the SQL Performance Analyzer 25 ora11g perfana 13

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

DBConsole 11g (part 2) - Using the SQL Performance Analyzer 26 ora11g perfana 14 a

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

DBConsole 11g (part 2) - Using the SQL Performance Analyzer 27 ora11g perfana 14 b

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

DBConsole 11g (part 2) - Using the SQL Performance Analyzer 28 ora11g perfana 15

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.

3 Comments

  1. Jeffrey Han July 22, 2007
  2. Karl July 11, 2007
  3. Ann June 28, 2007