Re: Statspack oracle 8.1.7.4.0

tpreto7_at_sapo.pt (Teresa) wrote in message news:<6dabc692.0404050853.37caf901_at_posting.google.com>...
> Hi
>
> I have ran statspack on a solaris 64, oracle 8i and I see that Soft
> Parse is low .... We use remote db links between 2 different servers,
> could this be the problem? since the issue is only in the target
> database
>
>
> Load Profile
> ~~~~~~~~~~~~ Per Second Per
> Transaction
> ---------------
> ---------------
> Redo size: 425,372.98
> 7,223,314.76
> Logical reads: 13,292.32
> 225,718.63
> Block changes: 1,986.09
> 33,725.99
> Physical reads: 2,152.20
> 36,546.79
> Physical writes: 444.96
> 7,555.92
> User calls: 26.32
> 446.98
> Parses: 12.97
> 220.18
> Hard parses: 11.97
> 203.31
> Sorts: 36.10
> 613.01
> Logons: 0.15
> 2.56
> Executes: 940.99
> 15,979.04
> Transactions: 0.06
>
> % Blocks changed per Read: 14.94 Recursive Call %: 98.56
> Rollback per transaction %: 0.00 Rows per Sort: 755.55
>
> Instance Efficiency Percentages (Target 100%)
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Buffer Nowait %: 100.00 Redo NoWait %: 100.00
> Buffer Hit %: 83.81 In-memory Sort %: 99.99
> Library Hit %: 70.15 Soft Parse %: 7.66
> Execute to Parse %: 98.62 Latch Hit %: 99.98
> Parse CPU to Parse Elapsd %: 97.42 % Non-Parse CPU: 96.64

If you have latch contention on the shared pool and or library cache latches, I'd say that you're on the right track, trying to reduce that bottleneck. What info is missing, is - what % of the wait event time is due to this "problem"?

Is it a problem, other than having a bad ratio?

I agree that it is likely, that if this is not a data warehouse, that re-usable SQL having fewer hard parses would be a good idea. Having such figures appear in a statspack report are quite scary, as it may be that this system will not scale due to non-reusable SQL. But then again, it might.

What time interval was this taken over?
What were the other wait events?
Were wait events on the network (for the data being sent across the db_link) and physical IO events?

What you have posted is likely a problem, but is it _the_ problem currently?

hth.

Pd Received on Mon Apr 05 2004 - 18:09:46 CDT