Oracle Scratchpad

February 23, 2011

AWR Reports

Filed under: AWR,Oracle,Statspack,Troubleshooting — Jonathan Lewis @ 6:51 pm GMT Feb 23,2011

A little thought for those who use OEM to generate AWR reports – if you generate the standard HTML form and then try posting it into (say) the OTN database forum when you want some help interpreting it, it won’t look very readable (even if you do remember to put the right tags before and after to reproduce it in fixed font).

January 14, 2011

Statspack on RAC

Filed under: Statspack — Jonathan Lewis @ 6:46 pm GMT Jan 14,2011

Some time ago I was on a client site which was busy setting up a RAC cluster using Oracle 10g. Although I wasn’t involved with that particular system there were a couple of coffee-breaks where I ended up chatting with the consultant that the client had hired to install the cluster. In one of our breaks he surprised me by making the casual comment: “Of course, you can’t run Statspack on RAC because it locks up the system.”

April 14, 2010

Analysing Statspack 12

Filed under: AWR,Oracle,Statspack,Troubleshooting — Jonathan Lewis @ 6:54 pm GMT Apr 14,2010

[Further Reading on Statspack]

Part 12 is about a 2-node RAC system.  Someone recently posted links to a matched pair of AWR reports in this thread on the OTN Database General forum and, after I had made a couple of quick comments on them, gave me permission to use them on my blog.

March 21, 2010

Nutshell – 2

Filed under: Infrastructure,Statspack — Jonathan Lewis @ 8:16 pm GMT Mar 21,2010

There is a lot of confusion around about the significance of the statistic “parse calls”. The important thing to remember is that it is simply counting a certain type of call from the OCI library – the amount of work done by a parse call may vary enormously depending upon circumstances, and sometimes the amount of work is so tiny that it’s not worth worrying about.

“parse call” may:

a) Have to optimise the statement because it failed to find it after searching the library cache

b) Find the statement after searching the library cache, and still have to optimise it for various reasons, e.g. the previous plan has been flushed from memory or the same text applies to different objects depending on who is executing it.

c) Find the statement after searching the library cache and not have to optimise it because the plan is still available and the user has the appropriate privileges.

d) Operate through the session cursor cache or pl/sql cursor cache allowing it to use a short cut to the statement’s location in the library cache without having to search the cache.

When the Oracle increments the counter for “parse calls”  you still have to work out whether that call turned into (a), (b), (c) or (d).

Just to confuse the issue, Oracle may also record a “parse count (hard)” without recording a “parse call”.

[Back to Nutshell -1 (Redo and Undo)]

Update Jan 2011

Randolf Geist has been looking at adaptive cursor sharing, and has noted that the parse call – including parse calls that go through the session cursor cache – seems to be the point in the code where adaptive cursor sharing can take place: in other words, it’s not an event that gets triggered or flagged by executions.

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: AWR,Oracle,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.

« Previous PageNext Page »

Powered by