Oracle Scratchpad

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…)

May 14, 2007

Statspack notes

Filed under: Statspack — Jonathan Lewis @ 8:38 pm BST May 14,2007

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.

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.

(more…)

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.

(more…)

February 9, 2007

Statspack 10g

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

[Further Reading on Statspack]

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.

(more…)

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

Scoping

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:
(more…)

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…)

January 5, 2007

Bind Variables

Filed under: Performance,Statspack,trace files,Troubleshooting — Jonathan Lewis @ 9:50 pm BST Jan 5,2007

I have made a few comments in previous articles about the use of bind variables and some of the peripheral details that can introduce surprises; and in the article on superfluous updates I made a throwaway comment about getting multiple child cursors for a single statement if you had columns of varchar2() or nvarchar2() defined to be longer than 32 bytes. It’s worth expanding on this point.

(more…)

December 27, 2006

Analysing Statspack (2)

Filed under: Statspack,Troubleshooting — Jonathan Lewis @ 11:16 pm BST Dec 27,2006

[Further Reading on Statspack]

One of the important things to know about the standard statspack report is where not to look. Here’s an example:

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:  100.00       Redo NoWait %:    100.00
            Buffer  Hit   %:  100.00    In-memory Sort %:    100.00
            Library Hit   %:   99.96        Soft Parse %:     99.00
         Execute to Parse %:   98.02         Latch Hit %:    100.00
Parse CPU to Parse Elapsd %:   92.74     % Non-Parse CPU:     98.56       

The Instance Efficiency summary (note especially the indication that 100% is the ideal in all cases) is essentially useless. At least, it is useless in isolation if you run off the occasional report trying to spot problems.

(more…)

December 3, 2006

Saving Statistics

Filed under: Infrastructure,Statistics,Statspack — Jonathan Lewis @ 10:46 pm BST Dec 3,2006

[Further Reading on Statspack]

I see that Doug Burns  has just published an example of “reason 2″ for using Statspack

The reason for mentioning this particular posting is not specifically its reference to Statspack, it’s for the throwaway comment that Doug uses to explain how he was rapidly able to address the problem highlighted by Statspack:
(more…)

November 30, 2006

Statspack Aid

Filed under: Statspack — Jonathan Lewis @ 8:40 pm BST Nov 30,2006

As an aside to comments I’m planning to make on statspack, I thought you might like to look at some of the material written by Tim Gorman about using a statspack repository.

For his collection on whitepapers, including a Powerpoint presentation and Word document on statspack.

And for his collection of scripts, including several that manipulate statspack data.

November 29, 2006

Analysing Statspack (1)

Filed under: Performance,Statspack,Troubleshooting — Jonathan Lewis @ 10:54 pm BST Nov 29,2006

[Further Reading on Statspack]

“How do you interpret statspack data ?”

If you go for a generic answer, it’s likely to be a long answer – but the first step is always the classic  “Tom Kyte turnaround”  … Why do you want to interpret Statspack data ?

As “a starter for ten” [Ed: a reference that may be familiar only to UK viewers of the quiz show Univeristy Challenge], I’m just going to float one thought: there are three reasons for looking at Statspack data and the reason dictates the approach, and the significance you attach to the data.

(more…)

« Previous PageNext Page »

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,983 other followers