Oracle Scratchpad

September 10, 2009

Philosophy – 5

Filed under: Philosophy,Statspack,Troubleshooting — Jonathan Lewis @ 6:31 pm GMT Sep 10,2009

Trouble-shooting with Statspack / AWR:

Something has to be top of the “Top 5 Timed Waits” … even when there are no performance problems.

[The Philosophy Series]
[Further Reading on Statspack]

July 10, 2009

Statspack Skills

Filed under: Performance,Statspack,Troubleshooting — Jonathan Lewis @ 7:28 pm GMT Jul 10,2009

I had a great time a couple of weeks ago at the  UKOUG meeting of the DBMS SIG (reported here by Coskan Gundogar). The range of presentations was good and I had a number of interesting conversations.

Of course, the exciting part for me was sitting down with a batch of Statspack and AWR reports that I had been supplied with in the previous couple of days and doing a “real-time” analysis of them.


May 26, 2009

CPU used

Filed under: Statspack,Troubleshooting — Jonathan Lewis @ 11:31 am GMT May 26,2009

[Further Reading on Statspack]

From time to time users of Statspack on the newer versions of Oracle are surprised to see the “CPU Time” in the “Top N Timed Events” section of the report looking very different from the “BUSY_TIME” that appears in the “OS Statistics” part of the report.

There are various reasons why the numbers can differ, but one of the reasons is simple and highly beneficial – prior to 10g Oracle usually updated time figures at the end of each database call; but from 10g there are some views where time is updated more regularly.


January 18, 2009

Analysing Statspack (11)

Filed under: Statspack — Jonathan Lewis @ 11:38 am GMT Jan 18,2009

[Further Reading on Statspack]

Here’s an interesting example of a Statspack report that appeared recently on the OTN Database General Forum. It’s from a user who wants to know why an update to seg$ appears in second place in the “SQL ordered by …” sections of the report. I’ve set the link to open in a new window so that you can read the report and my comments at the same time.


January 11, 2009


Filed under: Statspack,Troubleshooting — Jonathan Lewis @ 6:47 pm GMT Jan 11,2009

David Kurtz (an Oak Table member, and specialist in Peoplesoft) has justed posted a couple of interesting items on his blog.

The first is a note, with examples,  on how easily you can pull AWR into an Excel spreadsheet for graphing.

The second is an item about using this graphic approach to investigate a performance issue that started with spikes in waits on the “cache buffers chains” latches.

October 1, 2008

Analysing Statspack (10)

Filed under: Performance,Statspack,Troubleshooting — Jonathan Lewis @ 6:48 pm GMT Oct 1,2008

[Further Reading on Statspack]

The following question appeared some time ago on the Oracle Forum:

Event                       Waits   Time(s) Avg Wait(ms) % Total Call Time Wait Class
CPU time                              1,167                           86.9
db file sequential read   111,047       223            2              16.6 User I/O
log file sync              77,390       132            2               9.9 Commit
log file parallel write    78,060       124            2               9.2 System I/O
db file scattered read     79,233        10            0               0.7 User I/O

From AWR I am seeing a lot of CPU taking most call time %. Can anyone explain what this means. I have looked on metalink and there are a number of doc regarding top events in general.

So what could we guess from this minimal slice of an AWR report.


February 28, 2008


Filed under: Statspack,Troubleshooting — Jonathan Lewis @ 8:45 pm GMT Feb 28,2008

If you know of Douglas Adams you will know that the answer to life, the universe and everything is 42**.


February 18, 2008

Analysing Statspack

Filed under: Statspack,Troubleshooting — Jonathan Lewis @ 9:50 pm GMT Feb 18,2008

It’s been a few years since I last read this (pdf) article from Connie Dialeris Green of Oracle about how to use Statspack – and I’d forgotten how good it was.

December 18, 2007

Analysing Statspack (9)

Filed under: Statspack,Troubleshooting — Jonathan Lewis @ 7:00 pm GMT Dec 18,2007

[Further Reading on Statspack]

A recent item on the Oracle forums asked for help with a performance problem. It started with the suggestion that a recent increase in the size of the buffer cache may have made the performance seem worse to the end-users, and asked if this was likely to be true. Ultimately, the poster supplied a couple of Statspack reports, with a request for advice.


November 25, 2007


Filed under: Performance,Statspack,Troubleshooting — Jonathan Lewis @ 7:27 pm GMT Nov 25,2007

I wrote an article some time ago about how 10g gave you a very convenient way to capture run-time information about the work done by a query if you added the /*+ gather_plan_statistics */ hint to your SQL. At the time I pointed out that using this hint could make some queries use a lot more CPU, as they might spend more time collecting the information than they spent doing the actual work of the query.


November 10, 2007

Analysing Statspack(8)

Filed under: Statspack,Troubleshooting — Jonathan Lewis @ 2:31 am GMT Nov 10,2007

[Further Reading on Statspack]

In an earlier article on Statpack I quoted some figures taken 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          

I have since found on Burleson’s website the report from which this extract was taken.

November 1, 2007

AWR oddity

Filed under: Statspack,Troubleshooting — Jonathan Lewis @ 7:57 pm GMT 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 GMT 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 GMT 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 GMT 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.


« Previous PageNext Page »

The Rubric Theme. Blog at


Get every new post delivered to your Inbox.

Join 4,888 other followers