Re: Interpreting statspack report

"Pascal" <pascal.oegerli_at_postmail.ch> wrote in message news:1132781980.108742.280080_at_g44g2000cwa.googlegroups.com...
>
>> > Buffer Gets Executions Gets per Exec %Total Time (s) Time (s)
>> > Hash
>> > Value
>> > --------------- ------------ -------------- ------ -------- --------- ----------
>> > 5,936,120 48,747 121.8 10.5 1114.58 1570.03
>> > 3004224063
>> > Module: w3wp.exe
>> > SELECT A.CAPTION, A.LANG_ID FROM X_MLTEXT A WHERE ML_ID=:B1 ORDE
>> > R BY LANG_ID DESC
>> >
>> > 2,794,360 19,353 144.4 4.9 524.16 847.55
>> > 1434437237
>> > Module: w3wp.exe
>> > SELECT A.CAPTION, A.LANG_ID FROM X_MLTEXT A, X_ML B WHERE B.ML_I
>> > D_CODE = LOWER(:B1 ) AND A.ML_ID=B.ML_ID ORDER BY LANG_ID DESC
>> >
>
> Thanks for your reply. The machine is a server that runs about 50
> databases. The customer is a large telecomunications company in
> Switzerland. Unfortunately, I have no acces to the server itself, I
> only have some statspack reports.
>
> The particularly expensive statment gets the labels for the fields of a
> screen of the application. It is executed once when a user navigates to
> any screen. I will ask the developers if it is possible to load all
> text at the beginning of the application and put it in the application
> server cache. I think this would be a big gain.
>
> Regards
>
> Pascal
>

You seem to be getting a lot of CPU and not many waits if you have 50 databases running on the same machine. Whatever else is happening, your users may find the application slow because of everything else that's happening on the machine.

The statistics for this statement (121 buffer gets per execution) combined with your comment about getting "the labels for the fields of a screen" don't quite match. Do you really have an average of about 121 fields per screen ?

This suggests two things:

    the table is quite small and being tablescanned every single     time (which can use up a lot of CPU), or

    the fields are being fetched one at a time, which could mean     about 4 buffer gets per field, which brings the average down     to 30 fields per screen. (Or maybe there are several different     languages, and the front-end is deciding which language to     display after fetching all of them). This would use up excessive     CPU, increase the latching, and waste time in round-trips, which     would be hidden by statspack because it doesn't report time     lost on sql*net message from client.

You might be able to do something about improving efficiency without going to the extreme of engineering code to fill and use a front-end cache.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle: Fundamentals

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 4th Nov 2005