Re: Help me read my statspack report

emdproduction_at_hotmail.com wrote:
> > You are starving the CPU (see top 5 events, always start there), by
> > issuing way too much sorts (see 'sort (rows)'), too many chained rows
> > (see 'table fetch continued row') and way way too much full table scans
> > (see 'tablescans (short tables)' and 'tablescans (long tables)'
> > You must have inefficient and untuned sql. You can track down the SQL
> > starving your system, by running statspack(5).
> >
> > --
> > Sybrand Bakker
>
> Thanks. But I think we had too much index scan as well, did you see we
> have a high logical reads?
>
> Also, could you comment on
> ======
> Execute to Parse %: 76.61 Latch Hit %: 99.49
> Parse CPU to Parse Elapsd %: 97.32 % Non-Parse CPU: 92.36
>
> Shared Pool Statistics Begin End
> ------ ------
> Memory Usage %: 94.04 94.02
> % SQL with executions>1: 28.31 26.99
> % Memory for SQL w/exec>1: 26.11 25.70
> ==========
> Does it mean bind variable is not used here?

If you have excessive table scans only from 10 to 11, it could be something as simple as not having a needed index for something that runs then. You should follow Jonathan and Sybrand's advice and find out exactly which SQL are problematic, rather than try to second guess from the summarized statistics.

As to too much index scanning, that also depends on the specific code that is running, so that is where you need to get runtime plans for the code, and figure out what is happening with the optimizer that it isn't making the best decision.

If it turns out that the full scans are a correct optimization for that particular code, consider putting those tables or indices in a recycle pool. I've seen magical results from that (mostly due to reducing buffer thrashing of all the other tables that don't need to go into another pool). See v$bh in the performance tuning manual for figuring out which objects might be appropriate (and Jonathan's site and blog for more precise details).

More generally, sometimes badly tuned code makes everything else go downhill fast. That is one reason why most modern tuning methodologies show that most performance problems are due to badly tuned sql. If you start making overgeneralizations about the problem being a system problem, you could make it worse by incorrect system tuning.

The bind variable issue makes a difference when people issue substantially the same code using literals to make oracle think the code is different. Again, you would need to know the specific code and what users do to know if this is an issue. There is a lot about this on asktom and in Tom Kyte's books.

See the advisors (select view_name from user_views where view_name like '%ADV%'; or OEM pga memory usage details) about too many sorts.

jg

-- 
@home.com is bogus.
http://lkml.org/lkml/2006/12/13/370