very poor performance

[Former Member]

Hi all!

Hope somebody can give me advise waht to do...

We have a ECC6.0-System running on Windows 2003 64bit with Oracle 10.2.0.2. Until a month ago (before plant-holidays) performance was not the best but ok.

During Plant holidays we changed the hardware of our DB-Server to a much more powerful hardware.

New Hardware: 4 Quadcore, 70GB RAM, 15rpm disks,... should be fine...

We have'T changed any parameters duing HW-Change and all seemd to be OK.

This week a lot of users came back from holiday and the server-load nearly reaches full. (Not all users are here - but most of them.)

The problem now is, that the performance now isn't worth to call it performance. Users have to wait up to 20 minutes for small trasnactions like opening an invoice or something like that (under normal conditions under 2 seconds!)

The first thing I found now is the waitevent "db file sequential read-value" which is about 90% of total response time!

That couldn't be ok, or?

According to that I'm tracing Diskreads/sec. and with wokload there we have a value of about 2500 reads/s. During night "only" about 500 to 800 reads/s. Is that ok or do I have some problems with my disks?!!?

Would be graet to hear fro some gurus in here,

regards,

Scheidleder Alexander

[Former Member]

Hi ,

If such a huge DB read is there then you may need to check your buffer size & other parameters related to oracle as well as your SAP.

Performance of SAP system depends on many application like SAP,Database,OS ,network as well as hardware.As in your case hardware is more than enough.You may need to consider other applications.Please follow these notes to have bigger picture where exaclty your issue is & then decide the best one to suit your issue::

Note 88416 - Zero administration memory management as of 4.0A/ Windows

Note 103747 - Performance: Parameter recommendations as of Release 4.0

Note 1289199 - Information about Oracle parameters

Note 1171650 - Automated Oracle DB parameter check

Hope this will give you little idea to troubleshoot your issue in correct way.

Thanks..

Mohit

[markus_doehr2]

> During Plant holidays we changed the hardware of our DB-Server to a much more powerful hardware.

> New Hardware: 4 Quadcore, 70GB RAM, 15rpm disks,... should be fine...

> We have'T changed any parameters duing HW-Change and all seemd to be OK.

So how much of that big memory have you assigned to the database? Is this 64bit?

Markus

[Former Member]

The system now works with the old-values:

Total System Global Area 4294967296 bytes

Fixed Size 2206544 bytes

Variable Size 3216856240 bytes

Database Buffers 1073741824 bytes

Redo Buffers 2162688 bytes

[markus_doehr2]

> Total System Global Area 4294967296 bytes

Any reason why this is "only" 4 GB if you have 70 GB on your machine? Just curious.

The more memory you offer the database the less reading must be done (since the data is in the database cache) - and hence the system will be much faster. Accessing memory is MUCH faster than doing an I/O to the disk.

Markus

[Former Member]

Yeah - of course we planned to give a lot more than 4G to the database.

I'm just insecure converning the values. I never found any article in which there were higher values than 4 G mentioned (and implemented)

Therefore I don't know how much SGA_MAX_SIZE I should implement..

I think I will start with about 50G? Can that work?

Just to make sure: I will increase SGA_MAX_SIZE to 50 GB - then I will have to restart SAP and Oracle and afterwards I can tune all other buffer-values online, or?

Ther should be no problem by just increasing the SGA_MAX _SIZE to 50G and tneh restarting the system?

I'm asking that because it''s possible for me to restart the whole system in about 50min (during lunch-time )

Thanks,

regards

Alex

[markus_doehr2]

> Yeah - of course we planned to give a lot more than 4G to the database.

Ok

> I'm just insecure converning the values. I never found any article in which there were higher values than 4 G mentioned (and implemented)

> Therefore I don't know how much SGA_MAX_SIZE I should implement..

> I think I will start with about 50G? Can that work?

It depends how much memory the SAP applications (and the respective buffers) need. If you look now in the system in ST02, what is the amount of memory used?

> Just to make sure: I will increase SGA_MAX_SIZE to 50 GB - then I will have to restart SAP and Oracle and afterwards I can tune all other buffer-values online, or?

> Ther should be no problem by just increasing the SGA_MAX _SIZE to 50G and tneh restarting the system?

If you don't need more than 16 - 17 GB for your SAP application - no. Make sure the machine doesn't start swapping!

Also check

Note 789011 - FAQ: Oracle memory areas

(I know - not really easy)

You may also check if your statistic values are up-to-date resp. reflecting what is in the tables.

And also note, that there may be a performance issue because of Windows 2003:

Note 1316558 - System hang situations on Windows Server 2003

Note 1320013 - CRM 2007/CRM 7.0 on Windows or other memory-intensive Appl. (if you have big PXA buffers)

Also install the Microsoft Hotfix for the high paging rates (if you haven't done so):

Note 1009297 - Windows Server 2003 Family: High Paging Rates

Markus

[Former Member]

Thanks for that answer - helps me much!

I think I will start setting the SGA_MAX_SIZE to 40G - that should be ok and therfore the DB-Server is not used as an Appl.-server (there are 4 others..) I'm quite sure I have enough spare memory to prvent system from swaping.

Further tips wellome! g

g

Alex

[markus_doehr2]

> I think I will start setting the SGA_MAX_SIZE to 40G - that should be ok and therfore the DB-Server is not used as an Appl.-server (there are 4 others..) I'm quite sure I have enough spare memory to prvent system from swaping.

So there's just the CI on the database server? If yes, then you could go even higher since those processes don't use that much CPU. Just for curiosity: Any reason why you don't use Windows 2008?

Markus

[Former Member]

First of all thanks for your help!

I was really impressed by that speed I got what I needed!!

I have to say the system runs a fast as I have never seen it before.

The main changes I made: SGA_MAX_SIZE resized to 40G and the DB_Cache_Size to 10GB...

I think there are a lot of things I can tune during the next weeks, but for now I love the performance and so do the users

Windows 2003 64bit was the fitting OS during the ReleaseChange in spring 2007. Therefor the system is still W2k3 and not Windows 2008 (We didn't install a new system at the new hardware, we decided to migrate the system using Univeral Restore...)

THX,

Alex

[markus_doehr2]

> First of all thanks for your help!

you're welcome

> I was really impressed by that speed I got what I needed!!

> I have to say the system runs a fast as I have never seen it before.

I can imagine.

A "rule of thumb" (I heard long ago) said, that 2 - 3 % of the database size should fit in the memory to keep the speed. If you have more you get of course MUCH more speed.

Markus