On Dec 14, 10:24 am, emdproduct...@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 BakkerThanks. 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?

Logical reads in itself are used for any access, so not necessarily
related to index full scans. A high number of logical reads in itself
points to inefficient and untuned SQL.
Statspack on level 5 in conjunction with spreport will dump the top 5
offenders.
I've always been bad at interpreting the parse statistics. The
percentage of sql statements with more than 1 executions seems to be
low. However: your parse cpu to parse elapsed is almost 1. Higher than
1 is good.
It more looks like your database is being hit by random adhoc SQL.
Not using bindvariables will show once you run statspack on level 5.

--
Sybrand Bakker
Senior Oracle DBA