Re: CRIT: RBLIVE statspack report

Thanks to both yourself and Nuno for your comments. This is exactly the kind of thing I find invaluable. And hopefully a few people who haven't commented could read the report and all comments to see how they apply. Anyone else fancying a go on this one - or posting their own performance report - should still please do so!

I should have added that since I upgraded from Oracle 734 to 816, users have been delighted in the response of the application - and they weren't complaining before so this (to my shame) is the reason I've done nothing regarding the rollback segments for fear of making anything worse... I simply created them at the size they were previously. However, when I go to LMTs for RBS/TEMP, they will definitely be 'better'.

Relevant SAR figures from that time period:

SunOS sunhc1 5.7 Generic_106541-08 sun4u 05/16/01

08:00:00    %usr    %sys    %wio   %idle
14:00:01      37       3       3      57
14:30:00      28       5       5      63
15:00:00      19       4       5      72
15:30:00      36       4       4      57
16:00:00      32       4       6      59

The 14-1600hrs period is the peak time for both databases. This is the lighter-used LIVE database on the machine so of that %usr column, I'd reckon less than half of that is down to this database. As you can see, we are a bit over-subscribed on CPU... :-)

I'll look over your other comments to try to see where the rollbacks/scans are coming from. Knowing the application provider, I wouldn't be surprised to find it's something fundamental buried deep within, but will do my best to find out...

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:990274993.6130.0.nnrp-12.9e984b29_at_news.demon.co.uk...
>
> As already appreciate, your rollback segments
> seem to be much too big for activity shown, and
> the optimal is too high - little known rule of thumb,
> rollback segments should be as small and as few
> as possible to reduce redundant I/O. In your case
> the size of the rollbacks doesn't seem to have
> resulted in any significant I/O cost.
>
> There seems to be little point in worrying (yet)
> about cursor_sharing - there seems to be virtually
> no latch contention - either as a measure of misses,
> or in the wait-time.
>
> I would be most interested in two points -
>
> Table scans - your rows fetched by scan
> exceed your rows fetched by rowid, and
> you are doing 2 'long' tablescans per minute.
> (long in your case could mean 240 blocks).
> I think this could be costing you a lot of CPU.
>
> Secondly the number of rollbacks you have
> is surprising - a rollback is usually an expensive
> operation. Why are so many happening.
>
> Quite possibly your 'hot object in kcadata' is
> the one being scanned all the time - but the
> low 'blocks per read' figure - despite the
> multiblock readcount being 32 suggests
> that you may be seeing 'skip' effects - check
> the 'only_sequential_access ' bit (19) in the
> FLAG - it may give you a clue.
>
> I'm not sure you will ever get a fantastic
> execute to parse' ratio from a forms front-end,
>
>
> Are you actually getting any complaints
> about performance on this one at present ?
>
>
> --
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
> Practical Oracle 8i: Building Efficient Databases
> Publishers: Addison-Wesley
>
> Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html
Received on Mon May 21 2001 - 04:33:19 CDT