I’ve been promising to deliver a write-up on Toad’s new support for Statspack, and I’ve finally gotten around to doing it! Statspack has been around since the 8i release of Oracle. If you are unfamiliar with the technology, please read this. If you’re already a Statspack user and would like to see how Toad can help you take advantage of it, then this blog is for you.
Wait a sec, Statspack is sooooooo yesterday!
Many of you may be wondering why I am crowing about Toad’s support for Statspack when Oracle pretty much just replaced it with ADDM|AWR in the 10g Release. I have 2 pretty compelling reasons:
1. Not everyone is on Oracle 10g. Don’t laugh please. Many people will not leave 9i until Oracle quits selling extended support on it. This is a blog-worthy topic all by itself.
2. Oracle’s new automated monitoring and workload repository for 10g comes with a VERY big string. It is not included in the base licensing, whereas Statspack analysis always has been and will probably continue to be FREE.
So, you can continue to take advantage of the Statspack technology with no risk, or you can evaluate the benefit of licensing the newer AWR technology. The nice thing is that Toad’s DBA Module will empower you regardless of the direction you choose to follow.
How to start using Statspack with Toad
A few prerequisites:
1. You need Toad for Oracle version 9.5
2. You need the DBA module
3. The Statspack objects need to be installed
4. You need to create a job to collect the statistics on a regular basis so they can be queried.
If you have met objectives 1-2 above, you can see the Statspack feature here:
Once you go to open it, Toad will query for the Statspack objects. If it cannot find them, it will prompt you to run /rdms/admin/spcreate.sql as SYS while connected to the database server directly.
It will then offer to create this job for you:
Oracle’s AWR technology works in a similar way. It collects statistics by default every hour. These ‘snapshots’ can then be queried. The Statspack snapshots can be directly queried from the ‘PERFSTAT’ tables. Or, you can use Toad to generate the reports for you.
The Fun Stuff
Ok, enough about how to set it up, let’s walk through the screen and then a couple of more useful features the developer decided to throw in for your benefit.
You basically have a three step process for this screen:
1. Decide which period of time you want to report on
2. Decide which information you want included in the reports
3. View the results in the graphs
Choosing Your Time Period
When you open the screen for the first time, you may need to hit the ‘Refresh Snapshots” button. This will populate the list of snapshots you have available to query against. By selecting these, you are telling Toad which time period to include the results for. My favorite right-hand-mouse option is the ‘Check Last 24 Hours of Snapshots” which allows me to see what’s happened in the past day only. If you have JUST enabled Statspack in your database, you can use the ‘Find Collection Job in Schema Browser’ option to manually run the job a few times so you can see some snapshots to choose from.
You can also use the ‘options’ button to change the Statspack setup, including what thresholds and level of diagnostics to use. You can additionally mouse-right-click in the header (see the picture below to see what I am talking about) to add more detail information on the snapshot list. This allows you to know EXACTLY what snapshots you are looking at.
Choosing the Information to be Displayed
There are a few categories you can choose from:
1. Operating System
2. Database
3. IO
4. Session Activity
5. Ratios
6. Wait Events
These are all included as part of Statspack. You can also choose to create your own category and add any metric you want assigned to a custom chart to display that information.
As you select an item in this tree, you will see the appropriate graph show up on the right. If you already have a snapshot of time checked, it will query the data right away and populate the graph. If you check a snapshot afterwards, you can use the ‘Refresh RHS’ button to update the graphs to reflect what you have selected on the left.
View the Results
Depending on your monitor size and screen resolution, you may want to configure the graphs to show as few as 1 or as much as 3 graphs per row. You can tweak these settings so that the graphs are large enough to be readable, especially if they have a lot of data point lines.
Something you may not see right away – especially if you haven’t learned how right-mouse-click happy Toad is – is the ability to view a graph in a full-screen mode.
Learn to use the Mouse in Toad!
Once you have a full screen display of a graph, there are a few things you can do with the data contained.
If your graph contains a LOT of information and it is hard to see what is what, click on an item in the legend, and that line in the graph will be bolded, making it easier to distinguish!
You can also send the chart itself to Excel, or copy the data behind the graph to Excel so you can work the raw data on your own.
What’s Coming Up for Version 9.6
Toad’s DBA Module already has an interface that exposes the ADDM/ARW reports that Oracle provides. For version 9.6, we are rolling out an ‘AWR Browser’ which will work exactly like the Statspack feature documented above, but that will query the AWR snapshot repository. This way if you are happy with the Statspack information but want to take advantage of the AWR, you can have the best of both worlds.
If you want to see this feature before it’s commercially released, and/or have a say on what features are added or enhanced in the next version of Toad, just join the Beta Program!
The AWR Browser in version 9.6