psoft8 performance problems on D270

[Eileen Millen]

We have a D270 with one CPU that is used for Peoplesoft development. When it was 10.2 and running psoft7, the performance and export times were much better.
The system was installed with 11.0, had an OSS disk array added and now has psoft7 and psoft8 on it. The DBA says that the Oracle export that used to take 2 hours, is now taking 2 days. The users say that the performance is much worse now.
In Perfview, I discovered that the GLB_RUN_QUE goes up to 10 or even 20 and stays there until the next reboot.
The GLB_PRI_QUEUE is less than 1.
The GLOBAL_CPU_TOTAL_UTIL does get up to over 90%, but not very often.
GLB_MEM_UTIL is mostly 70%.

Are there any known problems with psoft8?
Is there a patch for the unrealistic high load
at 11.0? I know there is one for 11i.
Any help would be appreciated.
Thanks,
Eileen

[Patrick Wallek]

How much memory do you have? Is there any swapping going on? What does 'swapinfo -tam' look like?

My GUESS is that with 2 instances of peoplesoft, you are running short of memory, thus causing the machine to start swapping, causing everything to slow down.

[S.K. Chan]

The GLB_RUN_QUE represent the average number of runnable processes in that interval. One of our Oracle DB server runs between 5->10 and it seems fine. You might want to cross check these values with sar instead just to be sure it is reporting accurately.

[Eileen Millen]

Patrick,
The swapinfo output
swapinfo -tam
Mb Mb Mb PCT START/ Mb
TYPE AVAIL USED FREE USED LIMIT RESERVE PRI NAME
dev 1024 23 1001 2% 0 - 1 /dev/vg00/lvol2
reserve - 1001 -1001
memory 1579 563 1016 36%
total 2603 1587 1016 61% - 0 -

S.K Chan,

The load average is 11.6 right now and this is the sar output.
cu86 # sar -u 1 10
HP-UX cu86 B.11.00 A 9000/861 02/27/02

11:04:37 %usr %sys %wio %idle
11:04:38 1 3 0 96
11:04:39 0 0 0 100
11:04:40 0 0 0 100
11:04:41 0 0 0 100
11:04:42 0 2 0 98
11:04:43 0 3 0 97
11:04:44 0 0 0 100
11:04:45 0 0 0 100
11:04:46 0 0 0 100
11:04:47 0 0 0 100

Average 0 1 0 99

cu86 # sar -q 1 10
HP-UX cu86 B.11.00 A 9000/861 02/27/02

11:04:52 runq-sz %runocc swpq-sz %swpocc
11:04:53 13.0 99 0.0 0
11:04:54 1.0 100 0.0 0
11:04:55 0.0 0 0.0 0
11:04:56 0.0 0 0.0 0
11:04:57 0.0 0 0.0 0
11:04:58 12.0 100 0.0 0
11:04:59 0.0 0 0.0 0
11:05:00 0.0 0 0.0 0
11:05:01 0.0 0 0.0 0
11:05:02 1.0 100 0.0 0

Average 6.8 40 0.0 0


Thanks,
Eileen

[A. Clay Stephenson]

Hi Eileen:

Can you post a kmtune output? I would check for a timeslice setting of 1 rather than 10 first thing.

[Volker Borowski]

Hello Eileen,

let's take a look at Oracle.

Did you upgrade Oracle ?
What release&patchlevel are you on, where did you come from ?
Which changes have been done to the init.ora.

Is the export driven by a script ? Pls. attach it if possible.
Any concurrent activities when this script/export is running ?

Did you change from rule to cost based optimizer in Oracle ? If yes, are the DB-statistics up to date ?

Something to start with
Volker

[Eileen Millen]

Volker,
Oracle was upgraded from 7.3.4.X.X to
8.1.7.1.0.
The export is sone manually with these options:
direct=y, buffer=10,000, compress=no
It is a full db export.
We have 4 Oracle instances and 2 versions of Tuxedo. We did change from rule to cost.
The DBA is having trouble with HRUAT.

I am seeing major and minor page faults in Perfview. Process are getting killed.
Is it normal for restarsrv to keep starting about every minute.

Thanks Eileen

[Volker Borowski]

Good morning Eileen,

several things to check in this case:

1) 8.1.7.1 has has a memory leak. If you face ORA-4031 errors, go for higher patchset or bugfix (HP_UX 64) 1755574 or 1755574 (32 Bit)

2) 8.1.x requires more memory that 7.3.4. You should at least increase db_block_buffers and shared_pool_size in init.ora.

3) When you use the cost based optimizer, you need to calculate statistics for your tables to give the optimizer at better base for calculation. Use
ANALYZE TABLE name COMPUTE STATISTICS;
ANALYZE INDEX name COMPUTE STATISTICS;
for this if possible.
If you face lock problems try to go with ESTIMATE.
But this will not affect the time of you database export! Since you do a full export, you will always do sequential scans.

Only thing I can think of for this export problem is a typo with db_block_buffers, so that it is much too small.

When you watch the logfile/messages during this export, is there a special table, where it takes extremly long ? How big is it ? DO you have better export times, if you just export the USER and not the entire database ?

Please check, if it is possible to attach the entire init.ora file.

Keep on hunting
Volker

[Eileen Millen]

thanks Volker
I am at HP today and tomorrow and will pass the information on to our DBAs.
Eileen

[Eileen Millen]

Thanks Volker.
The DBAs are using a workaround for the memory leak. The export time has improved.

The psoft8 performance is bad when there are many people using it.
Peoplesoft recommends 2CPUs. One for Oracle and one for Peoplesoft.
On Friday, the CPU usage did get up to 100%.
2 second queries were taking 15 minutes.
The GLB_RUN_QUEUE is close to 20 now and won't go down until we reboot. It went up after I used STM to run a disk and memory test.
There is no GLB_PRI_QUEUE even when the CPU was at 100%.

Eileen