Oracle Scratchpad

November 1, 2007

AWR oddity

Filed under: Statspack,Troubleshooting — Jonathan Lewis @ 7:57 pm BST Nov 1,2007

I was on a site recently that had a standard strategy for installing Statspack on every single one of its databases – including the 10g databases. They also happened to have the licences that allowed them to run the AWR Report – which was rather nice because there are little bits of the Statspack report that don’t appear in the AWR report, and vice versa.


October 23, 2007

Analysing Statspack (7)

Filed under: Statspack,Troubleshooting — Jonathan Lewis @ 7:56 pm BST Oct 23,2007

[Further Reading on Statspack]

Here’s an interesting extract from an Oracle 9i statspack report taken from Oracle Database 10g Performance Tuning Tips and Techniques*** . The book doesn’t give the snapshot interval, number of CPUs, or any idea about the average number of sessions active during the period, so all the comments I make about the sample are highly speculative.

But, as a pure “thought experiment”, I want to give you a feasible explanation of the figures shown just to give you a flavour of how counter-intuitive a statspack report can be.

October 19, 2007


Filed under: Performance,Statspack,Troubleshooting — Jonathan Lewis @ 7:53 pm BST Oct 19,2007

Sometimes you think that SSD (solid state disk) is bound to speed up Oracle performance. But it’s surprising how wrong you can be – it’s not guaranteed to give you great performance.


September 20, 2007

Hit Ratios (3)

Filed under: Ratios,Statspack,Troubleshooting — Jonathan Lewis @ 11:56 am BST Sep 20,2007

 A little while ago I wrote some notes on why monitoring the buffer cache hit ratio (BCHR) was a pointless exercise. I’ve recently come across a lovely example that demonstrates very clearly the point that Connor McDonald was trying to make in his ‘pick a hit ratio’ script.


July 14, 2007

Analysing Statspack (6)

Filed under: Statspack,Troubleshooting — Jonathan Lewis @ 1:31 pm BST Jul 14,2007

[Further Reading on Statspack]

Here’s an extract of an AWR (automatic workload repository) snapshot published some time ago on the Internet, along with the text describing why it’s worth seeing. The extract comes from an article by Don Burleson:

Here is an an example of an Oracle 10g database with an undersized log buffer, in this example 512k:

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                   % Total
Event                            Waits    Time (s)   DB Time Wait Class
log file parallel write          9,670         291     55.67 System I/O
log file sync                    9,293         278     53.12 Commit
CPU time                                       225     43.12
db file parallel write           4,922         201     38.53 System I/O
control file parallel write      1,282          65     12.42 System I/O


July 7, 2007


Filed under: Hints,Performance,Statspack,Tuning — Jonathan Lewis @ 8:25 pm BST Jul 7,2007

The following question appeared on the Oracle Forums recently:

The use of functions – a function with other selects (eg. calculate availability of a part) – is slowing down our system when we do a select over our product file.
Is there some kind of rule when functions should be used or when we should try to create a more complex – combined – SQL that does not use the function.
Can functions be used in the where clause without loosing a lot of speed?

It’s a really good question, because it prompts such a lot of ideas that need to be tied together, so I thought I’d jot down a few thoughts.


July 3, 2007

Parse Calls

Filed under: Statspack,Troubleshooting — Jonathan Lewis @ 7:55 pm BST Jul 3,2007

Here’s an extract from a report of activity in an Oracle session that I’ve just been running. Spot the anomaly:

Name                           Value
----                           -----
session cursor cache hits          3
parse count (total)                5
parse count (hard)                31
execute count                     35

There are no tricks involved with this output, though the database activity is a little unusual, and I haven’t faked any numbers. So how come I’ve got more “hard parses” than “parses” ? (more…)

April 19, 2007

Analysing Statspack (5)

Filed under: Statspack,Troubleshooting — Jonathan Lewis @ 8:43 pm BST Apr 19,2007

[Further Reading on Statspack]

A few days ago someone emailed me a Statspack report (10g format) because one of their developers was complaining that “the system” was slow, and they thought this was a little odd because the Statspack report for the period didn’t seem to show any indications that “the system” was suffering from any type of overload.


March 7, 2007

Analysing Statspack (4)

Filed under: Statspack,Troubleshooting — Jonathan Lewis @ 4:38 am BST Mar 7,2007

[Further Reading on Statspack]

One of the ways to use statspack is to extract trending information from the data. I published some sample SQL on my website a couple of years ago to show how this could be done – but there are alternatives.


February 9, 2007

Statspack 10g

Filed under: Performance,Statspack,Troubleshooting — Jonathan Lewis @ 3:58 pm BST Feb 9,2007

I’ve always been a little nervous about advising people on the snapshot level and snapshot frequency for running statspack.snap(). In general level 0  every 15 minutes seems to be safe, with a slightly more cautious once per hour for levels 5 and above (which, in effect, is the default for the AWR). However, when taking snapshots, it would be sensible to monitor how much work goes into the snapshot so that you can adjust the frequency if you think that statspack itself could be causing some of your performance problems.


January 31, 2007

Event Histograms – 2

Filed under: Oracle,Statspack,Troubleshooting — Jonathan Lewis @ 8:38 pm BST Jan 31,2007

[Forward to part 3]

In an earlier blog [Part 1], I mentioned the 10g view v$event_histogram and described how useful it could be.

If you don’t have the licences that allow you to use the Automatic Workload Repository (AWR) in 10g, you will have noticed that the event histogram has made its way into Statspack for 10g. (more…)

January 14, 2007

Statspack Levels

Filed under: Statspack,Troubleshooting — Jonathan Lewis @ 9:24 pm BST Jan 14,2007

[Further Reading on Statspack]

There are currently five different levels of statspack snapshots, defined as follows in the table stats$level_description (9i version): (more…)

January 10, 2007

Event Histograms

Filed under: Oracle,Statspack,Troubleshooting — Jonathan Lewis @ 8:44 pm BST Jan 10,2007

[Forward to Part 2]

One of my favourite “little additions” in 10g is the v$event_histogram view. From a very short report it produces an extremely useful addition to the information about lost time. In v$system_event, we can get a report about the average wait time for an event – but when you condense a lot of data into a single number you always lose critical information. v$event_histogram addresses that problem. (more…)

January 8, 2007


Filed under: Statspack — Jonathan Lewis @ 8:51 pm BST Jan 8,2007

[Further Reading on Statspack]

Browsing the Internet recently, I came across the following question in response to a posting by Dan Fink:

Assuming I collect snapshots every 15 min, for example:

January 7, 2007

Analysing Statspack (3)

Filed under: Statspack — Jonathan Lewis @ 9:22 pm BST Jan 7,2007

[Further Reading on Statspack]

The output I want to look at in this example doesn’t come from statspack – but it does give you an important reminder about how statspack can deceive because it hides information (by averaging). (more…)

« Previous PageNext Page »

Powered by