Oracle Scratchpad

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.


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:

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.


November 23, 2006

Statspack level 6

Filed under: Execution plans,Infrastructure,Statspack,Troubleshooting — Jonathan Lewis @ 7:59 pm BST Nov 23,2006

[Further Reading on Statspack]

While teaching a session on explain plan and associated technology today, I mentioned the sprepsql.sql script that can be used to report execution plans. At the end of the session one of the attendees asked me if I was aware of a bug with v$sql_plan that could cause the statspack snapshot call to hang the database on a library cache lock until you killed the process.

In fact I was aware of it, but had completely forgotten about it. So at the end of the day I did a quick search on Metalink to see if I could find a reference. It looks like bug 3778541, opened on 20th July 2004, last updated 20th July 2006 (with predicted fix in version – so that looks like a long lifetime for 9.2 ;-)).  However this bug is, in fact, reported as fixed in the patch list for

[Further Reading on Statspack]

November 15, 2006

dbms_xplan pt.2

Filed under: dbms_xplan,Execution plans,Infrastructure,Statspack,Troubleshooting — Jonathan Lewis @ 8:22 am BST Nov 15,2006

[More on dbms_xplan.display_cursor()]

A comment on my previous posting about dbms_xplan pointed out that in 10g you also get the function dbms_xplan.display_awr that allows you to report historical execution plans from the AWR (automatic workload repository).

This is true, and there is yet another function dbms_xplan.display_sqlset which allows you to report execution plans from SQL Tuning Sets if you have been using the Automatic SQL Tuning Tools. But to use these functions you do need to purchase the Tuning Pack licence and the Diagnostic Pack licence.


« Previous Page

Powered by