Performance Issues with SAP 4.7 on RHEL 4.0 using HP Integrity

[Former Member]

Folks,

We have just migrated to a new Production environment that is -- Oracle 10.2.0.2, RHEL 4.0 on two (2) HP Integrity servers 4640 each with 4 CPUs and 16 GB RAM in a clustered environment with CI and DB separated. We are at SAP 4.7 kernel 640

We also have two(2) additional application servers with 16GB to support two hundred and fifty concurrent users.

A one(1) hour job now takes six(6) hours.

What are the Oracle or other parameters(SAP or O/S) that I must change to increase the performance.

The database server is consuming a lot of cpu cycles when the top command is issued on the server

[Former Member]

Hi Folks,

The problem was solved by inserting more Oracle parameters as per SAP Note 830576..

Thanks for all the help..

Regards,

Vinood.

[Former Member]

>

>

> What are the Oracle or other parameters(SAP or O/S) that I must change to increase the performance.

>

Hi Vinood,

About O/S changes that can increase your performance in RHEL4.0 or any linux to use more than CPUs you need kernel that support more than one CPUs.

To check about your current kernel using:

- uname -a (in terminal, and check if there is smp somewhere in name of the kernel)

- cat /proc/cpuinfo (in terminal, and check about how many cpu families your RHEL4.0 see - they have to be 4 numbered ID from 0 to 3)

In case that you are using single CPU kernel you have 2 ways:

- download latest kernel and configure it (manually) and compile it - which I didn't recommend

- find already compiled kernel, in your RHEL4.0 repository with packages, which have name kernel-smp and install it

After installing the kernel with smp, you have to set up your favorite boot loader (lilo - manual, or grub - automatic) to use that kernel. And reboot trying to use your new kernel image. Now check about your current kernel.

Another thing is memory.

Check your memory usage by linux using:

- free (in terminal)

- cat /proc/meminfo

if the memory is less than expected, you need kernel supporting PCs with big memory. This kernel is usually packet and can be found in name kernel-bigmem.

Best Regards

[Former Member]

Hi Ivan,

The CPU infor is fine it has 4 CPU's and it shows correctly 0-3 and the kernel is 2.6.9-42.EL #1 SMP.

However the memory is max out

This is the current state and values

MemTotal: 16631232 kB

MemFree: 20992 kB

Buffers: 84208 kB

Cached: 14767680 kB

SwapCached: 2720 kB

Active: 13242096 kB

Inactive: 2746192 kB

HighTotal: 0 kB

HighFree: 0 kB

LowTotal: 16631232 kB

LowFree: 20992 kB

SwapTotal: 54460336 kB

SwapFree: 54216896 kB

Dirty: 1808 kB

Writeback: 0 kB

Mapped: 9138176 kB

Slab: 106080 kB

CommitLimit: 62775952 kB

Committed_AS: 27267152 kB

PageTables: 371296 kB

VmallocTotal: 137429633024 kB

VmallocUsed: 16512 kB

VmallocChunk: 137429616016 kB

HugePages_Total: 0

HugePages_Free: 0

Hugepagesize: 262144 kB

Will appreciate any recommendations suggestions. There are only 30 persons on the system, but the database is consuming almost all 16GB RAM.

[markus_doehr2]

Linux uses "unused" memory for filesystem cache, this is normal and not reason to be worried about. You can see that if you start "top -s 1".

If a single job is running very long you would need to trace that one.

- is this a standard program or a self written one?

- if it´s a standard program, what program is it and what is the selection criteria?

Run the program with an SQL-Trace (transaction ST05) and check, which statements are running slow. Run an explain on them and check if an additional index might be of help.

Please check the recommended parameters in the already mentioned note.

Do you update your database statistics regularly?

It´s very difficult to give advises here - too less information is given and I seriously doubt, that the platform is the reason for the "slowlyness".

Markus

[Former Member]

On behalf of Vinood:

We have noticed most of the UPD processes taking very long to update a SAP table - FMIFIIT. We believe that this is causing all the other DIA processes to wait and is playing a major part in our problem of poor system response.

When reviewing expensive SQL statements, we notice that several of them involve select on this table and one very expensive sql statement refers to an update statement on this table. I have exported the SQL statements list into an Excel file and it is available.

I have pasted the SQL statement and explain plan below.

We have checked the table and it appears to have all its indexes. The table indicates that the statistics have been updated. We ran the most recent statistics update this morning under load.

======

SQL Statement

UPDATE

"FMIFIIT"

SET

"ZHLDT" = :A0 , "GNJHR" = :A1 , "PERIO" = :A2 , "CFSTAT" = :A3 , "CFSTATSV" = :A4 , "CFCNT" = :A5 , "OBJNRZ" = :A6 , "TRBTR" = :A7 , "FKBTR" = :A8 , "FISTL" = :A9 , "FONDS" = :A10 , "FIPEX" = :A11 , "FAREA" = :A12 , "MEASURE" = :A13 , "GRANT_NBR" = :A14 , "BUS_AREA" = :A15 , "PRCTR" = :A16 , "WRTTP" = :A17 , "VRGNG" = :A18 , "BUKRS" = :A19 , "STATS" = :A20 , "TWAER" = :A21 , "CFLEV" = :A22 , "SGTXT" = :A23 , "TRANR" = :A24 , "CTRNR" = :A25 , "USERDIM" = :A26 , "HKONT" =

:A27 , "VOBUKRS" = :A28 , "VOGJAHR" = :A29 , "VOBELNR" = :A30 , "VOBUZEI" = :A31 , "KNGJAHR" =

:A32 , "KNBELNR" = :A33 , "KNBUZEI" = :A34 , "SKNTO" = :A35 , "RDIFF" = :A36 , "PAYFLG" = :A37 ,

"PSOBT" = :A38 , "MENGE" = :A39 , "MEINS" = :A40 , "VBUND" = :A41 , "XREF3" = :A42 , "VREFBT" =

:A43 , "VREFBN" = :A44 , "VRFORG" = :A45 , "VRFPOS" = :A46 , "VRFKNT" = :A47 , "VRFTYP" = :A48 ,

"VRFSYS" = :A49 , "FMVOR" = :A50

WHERE

"MANDT" = :A51 AND "FMBELNR" = :A52 AND "FIKRS" = :A53 AND "FMBUZEI" = :A54 AND "BTART" = :A55

AND "RLDNR" = :A56 AND "GJAHR" = :A57 AND "STUNR" = :A58#

Execution Plan

Explain from v$sql_plan: Address: 00000401D05592A0 Hash_value: 3021312262 Child_number: 0 Sql_id: 1xp8g2yu1b486

Parse Timestamp: 20080306 13:04:55

UPDATE STATEMENT ( Estimated Costs = 1 , Estimated #Rows = 0 )

3 UPDATE FMIFIIT

2 TABLE ACCESS BY INDEX ROWID FMIFIIT

( Estim. Costs = 1 , Estim. #Rows = 1 )

Estim. CPU-Costs = 6,009 Estim. IO-Costs = 1

Filter Predicates

1 INDEX RANGE SCAN FMIFIIT~1

( Estim. Costs = 1 , Estim. #Rows = 1 )

Search Columns: 1

Estim. CPU-Costs = 4,313 Estim. IO-Costs = 1

Access Predicates

[hannes_kuehnemund]

Dear Vinood,

please update asap from RHEL4.0 to the latest quarterly update from Red Hat! RHEL4.0 is more then three years old!

Another note, current x86_64 machines are far more powerful then any ia64 servers...

Best Regards,

Hannes

[Former Member]

Hi Vinood,

> What are the Oracle or other parameters(SAP or O/S) that I must change to increase the performance.

The Oracle parameter recommendations are described in note 830576. Also make sure you installed the Oracle patches on top of 10.2.0.2, see note 871096.

The Oracle CBO statistics are up to date?

Best regards,

Elmar.