Performance - Please HELP !

[Admin.SIF]

Environment : N-Class machine, HP-UX 11.0, Oracle 8.1.7.1.
One of our procobol programs which calls other modules cobol and C, sets a lot of time to treat(handle) ten records. It is intended to treat(handle) 3000000 records! We look for something which can help to reduce the time of execution. The main part of the treatment consists in reading and writing in a BD Oracle.
Question : How to know which part take more time, and any suggestion to improve performance will be appreciated.
Thanks in advance,
Nora

[Paul R. Dittrich]

The best place to start is by using some of the basic built-in performance tools like "sar" and "top" to find out where the bottleneck is. Without more detailed information you can only guess at solutions.

[Admin.SIF]

With top and Glance, we can see that the program takes 97 % of a CPU and 24 % of memory, and it is first in the report of top. We have taken statistics with sar during the execution of the program. I would like to know how to benefit from these statistics to propose solutions.
Thanks again,
Nora

[A. Clay Stephenson]

I strongly suspect you have bad SQL. Since you have the source code I would enable profiling with the -p option. If you are running Micro_Focus -p works for it as well as the c routines. When you execute your code, you will produce a mon.out file and this can be examined with prof. It will show you how much time was spent in each function and system call. I strongly suspect you will see tons of time in read. You can also set SQL_TRACE to help analyze the SQL. The other technique that will probably prove useful is to run small portions of your code as SQLPlus scripts. Since your code does eventually finish, it does not appear to have infinite loop problems but rather in reading huge amounts of data probably in sequential mode.

Hope some of this helps, Clay

[MANOJ SRIVASTAVA]

Hi Admin

Here is what you need to do :

Study the TOP output most of the memeory CPU time must be getting utilized with these top proceesses , you need to further find pout that wether these are genuine jobs or not , in case there can be users running gzip , glance command to choke the machine . Another simple way is to run
UNIX95= ps -e -o ruser,vsz,pid,args | sort -rnk2 |more

to give you list of process eating the memory

, you can may be download sarcheck a free utility from www.sarcheck.com , which is free for one month trial version . Simple to installa and it gives good analysis.

I have attached a sample output from one of my systems so that you can see the way it gives the output.

Manoj Srivastava

[Wodisch]

Hello,
if you have only 24% of memory used, then you perhaps
have NOT tuned the "init*ora" parameters of your
Oracle instance?
Usually you have to setup at least the "magic" three
parameters "db_block_buffers", "shard_pool_size",
and "log_buffer_size" to fairly huge values (like more
than 500MB for buffers, 150MB for the shared pool,
and 1 to 4 MB for the log buffer).

Check your Oracle-setup first, then then operating
system and network. then the application and SQL;
it is much easier for us admins to correct problems
in "our" areas, than inside the application...

Last but not least: you did not tell us the amount of
RAM and CPUs you have in your box, nor how much
I/O is going on... - but on N-Classes you perhaps
have a couple of GB of RAM, and multiple CPUs,
so increase your Oracle-init*ora-parametes dramatically!

HTH,
Wodisch

[A. Clay Stephenson]

Hi,

I think I will suggest one more technique that has helped me in the past. You should plot
performance vs number of rows. I would also plot performance vs the log of the number of rows. If the slope of the log plot is 2 then you have an n-squared problem and is almost certainly a bad join (possibly a cartesian product). If the slope is near 4 then you basically have an n-squared problem within another n-squared problem. i.e. nested terrible joins. I usually use the technique when I have access to only a portion of the code but your best bet is still to profile the code.

I think since you are having problems at 10 rows and want to scale by over 4 orders of magnitude you can tune databases/kernels until the cows come home and still not make a dent in the problem. It's definitely in the code.

Regards, Clay