Graphics

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.

Analysing Statspack (10)

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

42

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

Analysing Statspack

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.

Analysing Statspack (9)

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

gather_plan_statistics

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.

Analysing Statspack(8)

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

AWR oddity

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.

Analysing Statspack (7)

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

SSD

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.

Hit Ratios (3)

 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.

Analysing Statspack (6)

[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

Functions

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.

Parse Calls

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

Statspack notes

If you’re a user of statspack, have a little browse of Dan Fink’s blog, you’ll find some useful comments there. For future readers of this pointer, check particularly around the March 2007 timeframe.