Home » RDBMS Server » Performance Tuning » No performance boost after upgrading database server RAM from 3GB to 16G (win 3000 Server Enterprise Edition, Oracle 9i (9.2.0.7.0) Enterprise Edition)
No performance boost after upgrading database server RAM from 3GB to 16G [message #401507] Tue, 05 May 2009 05:51 Go to next message
dbanukesh
Messages: 96
Registered: November 2008
Location: London
Member

Hi All,

We've upgraded Siebel database server with 16G memory from 3G to improve the performance of the database but users are still complaining no boost in the performance. I have changed few parameters recommended by oracle and Microsoft but that doesn't seem help. I have followed 225349.1 metalink doc for this. Post memory configuration changes are as follows

Switches in boot.ini file - /3G, /PAE and /Userva=3030

Registry Parameter - AWE_WINDOW_SIZE=512MB

Database Parameters - USE_INDIRECT_DATA_BUFFERS=TRUE

Removed DB_CACHE_SIZE and Added DB_BLOCK_BUFFERS parameters in oracle parameter file.

Though application guys saying they can not see any improvements in performance. They are more interested to see database using most of the available memory. Currently database is constantly using around 3G memory.

Currently sga_max_size is set to 1470702836

Thanks in Advance

Nukesh
Re: No performance boost after upgrading database server RAM from 3GB to 16G [message #401522 is a reply to message #401507] Tue, 05 May 2009 06:14 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Just adding more resources would ***not always*** help performance.

>>application guys saying they can not see any improvements in performance.

First you have define "performance" in your terms and measure it (time/IO whatever).
There are many tools within database that could help you to identify the
most offending sql or modules. Identify and fix them.

>>They are more interested to see database using most of the available memory.
It may or may not help with "performance".
Database may still be doing unnecessary work (bad sql, bad statistics, bad design) and
still be not performing.

To start with,
Are your database statistics updated?
If so when and how exactly?



Re: No performance boost after upgrading database server RAM from 3GB to 16G [message #401529 is a reply to message #401522] Tue, 05 May 2009 06:39 Go to previous messageGo to next message
dbanukesh
Messages: 96
Registered: November 2008
Location: London
Member

Hi Mahesh,

Thanks for the reply.This is Sibel application database and siebel generates its own queries from application to database. so there is no manual interaction of the users. Yes, Database stats daily updated in the morning before application starts using by the users. I have scheduled stats gather job (DBMS_STATS.GATHER_SCHEMA_STATS('SIEBEL_SCHEMA')Wink. Moreover we have all datafiles (index, data, undo and tempfile) in the same directory.

I am also attaching statspack report taken today.

Thanks
Nukesh
Re: No performance boost after upgrading database server RAM from 3GB to 16G [message #401551 is a reply to message #401507] Tue, 05 May 2009 09:05 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
You aren't going to be able to tell anything usefull from a 12 second window.
Try 15 minutes.
Re: No performance boost after upgrading database server RAM from 3GB to 16G [message #401760 is a reply to message #401551] Wed, 06 May 2009 06:31 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
12s is definitely too short a window.

However, during that 12 seconds, the Db looks to be running pretty smoothly.

What was it that led you to believe that shortage of memory was causing a problem with the database?
Re: No performance boost after upgrading database server RAM from 3GB to 16G [message #401819 is a reply to message #401760] Wed, 06 May 2009 09:47 Go to previous messageGo to next message
dbanukesh
Messages: 96
Registered: November 2008
Location: London
Member

Hi,
Thanks for reply. Please find attached statspack report generated for 15 min interval.
Earlier we had 3G memory on the server and it was fully utilizing by the database and there were performacne compalaints so management has decided to upgrade it because of growing business. Though from my end looking at statspack and other test scripts it looks good. even user login time is excellent. Management is more intrested in seeing we are fully utilizing 16G memory on the server so that users can feel maximum throughput .

Thanks
Nukesh
Re: No performance boost after upgrading database server RAM from 3GB to 16G [message #401824 is a reply to message #401507] Wed, 06 May 2009 10:08 Go to previous messageGo to next message
dbanukesh
Messages: 96
Registered: November 2008
Location: London
Member

I am planning to do the follwoing.

1. Increase the Buffer Cache to 1.5 GB
2. Increase the Shared Pool to 1 GB

What do you think about this?

Thanks
Nukesh
Re: No performance boost after upgrading database server RAM from 3GB to 16G [message #402050 is a reply to message #401824] Thu, 07 May 2009 07:32 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Increasing the buffer cache may make some difference.
You're not using all of the PGA that you've got at the moment, and the stats from the shared pool advisory show that you could double the SGA and make no noticable difference to the system.

The problems your system has are mostly IO related- 2/3 of your waits are for Scattered and Sequential reads.

This usually means that you have SQL that needs tuning - are you in a position to be able to tune the SQL at all?

One way of reducing IO contention is to increase the number of disks in your Raid array - what sort of configuration have you got at the moment?
Re: No performance boost after upgrading database server RAM from 3GB to 16G [message #402066 is a reply to message #402050] Thu, 07 May 2009 08:24 Go to previous messageGo to next message
dbanukesh
Messages: 96
Registered: November 2008
Location: London
Member

Hi, i am bit confused with your commect 'the stats from the shared pool advisory show that you could double the SGA and make no noticable difference to the system.'

Is that 'no noticable' or noticiable'? I think surely it can make a difference.

Currently we have got all datafiles on the same disk drive and seems it has got huge BUFFER WAIT due to heavy loaded datafiles on the same drive. I have asked downtime for moving them on another drives to avoid the
sequential waits on same Disk..I am looking at the vulnerable queries and tuning them.


[Updated on: Thu, 07 May 2009 08:27]

Report message to a moderator

Re: No performance boost after upgrading database server RAM from 3GB to 16G [message #402071 is a reply to message #402066] Thu, 07 May 2009 08:39 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That was 'No Noticable Difference' - Have a look at the Shared Pool Advisory section of the statspack report.

Quote:
Currently we have got all datafiles on the same disk drive

You have got to be joking.

What muppet advised you that that was a good configuration?

Tell me that you've at least got your redo logs and control files on a different drive?

Adding extra memory is going to be mostly a waste of money until you get your IO problems fixed.
If I were you, I'd get a bunch of discs and turn them into a stripped and mirrored Raid array (Raid 10 works) - that will improve both the resiliance and performance significantly.

Tuning the SQL is going to make a big difference too.

If you fix the IO problem and the poorly performing SQL, then you may improve your systems performance to the point where available memory becomes a problem - alternatively, fixing those two may just fix the performance.

This is a wonderful example of why it's important to determine what the performace problem actually is before you try to fix it.
Re: No performance boost after upgrading database server RAM from 3GB to 16G [message #402079 is a reply to message #402071] Thu, 07 May 2009 09:06 Go to previous messageGo to next message
dbanukesh
Messages: 96
Registered: November 2008
Location: London
Member

Thanks for the valuable feedbacks. I completely agree with you. I joined couple of months back here and couldnt believe at this configuration. I have asked for addtional drive or existing drive to be expanded but they are not much intrested intrested giving me the space rather they are only intrested in memory cost they put and want to see meomory utilization around 12-14G. Must say tough life here Smile

Yes we have redo and control files on other drives. Apparantly i was speaking with oracle support and they advised me to increase buffer cache t0 1.5G and shared pool to 1G. What do you think of this?
Re: No performance boost after upgrading database server RAM from 3GB to 16G [message #402082 is a reply to message #401507] Thu, 07 May 2009 09:09 Go to previous messageGo to next message
BlackSwan
Messages: 25038
Registered: January 2009
Location: SoCal
Senior Member
>What do you think of this?
It is like giving chicken soup to a dead man.
It won't hurt & a miracle might happen so it actually helps him.
Re: No performance boost after upgrading database server RAM from 3GB to 16G [message #402083 is a reply to message #402066] Thu, 07 May 2009 09:09 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
JRowBottom wrote:
>>This is a wonderful example of why it's important to determine what the performance problem actually is before you try to fix it.

Exactly!.
Just to add,

OP wrote:
>> Management is more intrested in seeing we are fully utilizing 16G memory on the server so that users can feel maximum throughput

As said already,
memory utilization/allocation is ***NOT ALWAYS*** proportional to performance.
There is no point in blindly adding more and more hardware resources.
Re: No performance boost after upgrading database server RAM from 3GB to 16G [message #402088 is a reply to message #401507] Thu, 07 May 2009 09:22 Go to previous messageGo to next message
dbanukesh
Messages: 96
Registered: November 2008
Location: London
Member

Thanks guys! I have to rack my head to get additional space on the server now to shuffle datafiles. Additioanlly i am looking to tune the sqls. We have got RIAD (1+0) on the server so it is okay.
Re: No performance boost after upgrading database server RAM from 3GB to 16G [message #402090 is a reply to message #401507] Thu, 07 May 2009 09:30 Go to previous messageGo to next message
BlackSwan
Messages: 25038
Registered: January 2009
Location: SoCal
Senior Member
adding more gasoline to a race car won't make it go faster.
In fact the added weight may make it go slower.

If memory was not the bottleneck, then adding more memory will not make application run faster.

Ready, Fire, Aim!
Re: No performance boost after upgrading database server RAM from 3GB to 16G [message #402275 is a reply to message #402079] Fri, 08 May 2009 06:31 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
Yes we have redo and control files on other drives. Apparantly i was speaking with oracle support and they advised me to increase buffer cache t0 1.5G and shared pool to 1G. What do you think of this?


You should see an increase in the buffer ache hit ratio if you increase your Buffer cache size, but there's no guarantee you'll see much of a performance improvement from that.

Increasing the SGA might help, but as I said, based on the SGA advisory rows from the Statspack report, I wouldn't anticipate a noticable improvement from that.

So your F drive is already a Raid array - that's ok then. When you said it was a single disk I assumed that you meant a single physical drive.
You should be able to lower the IO contention on your F drive by increasing the number of disks in the array, if that's an option.

The best way to lower the IO contention and improve performane is to see if you can tune any of the SQL - it may not be possible as you're running a 3rd party application, but you may be allowed to create indexes and create stored outlines for problem queries.

You can give them increased memory utilisation by increasing the size of the SGA massively, but it won't change the performance,
Re: No performance boost after upgrading database server RAM from 3GB to 16G [message #402278 is a reply to message #402275] Fri, 08 May 2009 07:03 Go to previous messageGo to next message
dbanukesh
Messages: 96
Registered: November 2008
Location: London
Member

Thanks for continuous feedback. About disks -Yes we have all datafiles on a single physical drive (F) and all physical drives are RAID configured. I planning to move few of the index and datafiles to E drive. Currently sga_max_size is set to 1470702836. What values should i think of setting for this environment. one more questions-how can i see full queries (caught in statspack report)so that i can plan to tune (wharever necessary creating indexes )?
Re: No performance boost after upgrading database server RAM from 3GB to 16G [message #402292 is a reply to message #402278] Fri, 08 May 2009 08:44 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Terminology Point: A Raid aray is a Logical drive, composed of several seperate physical drives.

If you look in v$sql just after you run the statspack report, the Hash Value displayed in the Statspack report is the same as the Hash_Value column on v$sql or v$sql_text.

You can see the actual plan used by looking in v$sql_plan for that Hash_value.

From a performance point of view, the best thing to do would be to consolidate all the disks used in your seperate Raid arrays (out of curiosity, how many disks do you have for each array) into one big raid array. This will do a better job of distributing IO across the disks than you ever can by moving datafiles around.

If that's not possible then you can use the Statspack report to guide you in splitting the datafile io down as evenly as possible.
Ideally you'd have a longer snapshot than 15 minutes for this, to even out any IO spikes, but based on the one you've provided, I'd say you want SIEBELDATA01.DBF on one disk, and SIEBELDATA02.DBF, SIEBELDATA03.DBF, SIEBELDATA04.DBF, SIEBELINDEX01.DBF, SIEBELINDEX02.DBF, SIEBELINDEX03.DBF, SIEBELINDEX04.DBF on another.
That should even things out across the two arrays.
Re: No performance boost after upgrading database server RAM from 3GB to 16G [message #402298 is a reply to message #402292] Fri, 08 May 2009 09:47 Go to previous messageGo to next message
dbanukesh
Messages: 96
Registered: November 2008
Location: London
Member

Thanks ! I have noted all your feedback. Any thoughts on how much i should increase SGA?
Re: No performance boost after upgrading database server RAM from 3GB to 16G [message #402786 is a reply to message #402298] Tue, 12 May 2009 08:19 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If Oracle have advised going to 1Gb, then I'd do that as a starter, and then run some more statspack and see if the SGA advisory thinks it's made any difference.

I really think that sorting out your problem SQL and balancing your IO across your disk heads will make much more difference.
Re: No performance boost after upgrading database server RAM from 3GB to 16G [message #402829 is a reply to message #402786] Tue, 12 May 2009 12:01 Go to previous messageGo to next message
dbanukesh
Messages: 96
Registered: November 2008
Location: London
Member

Thanks, Apart from this i have identified some tables which are affected by row chaining as well as row migration as data block size is 4k. So one more action plan is to create a new tablespace with 8K block size and move these tables to new tablespace. This will also lower down I/O. Thanks for your continous support with this issue. this is really learning curve for me. i highly appreciate this.

Re: No performance boost after upgrading database server RAM from 3GB to 16G [message #402933 is a reply to message #402829] Wed, 13 May 2009 04:17 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
One more thought - You're using Win2K as an OS.

Is that the 32-bit or the 64-bit version?

Re: No performance boost after upgrading database server RAM from 3GB to 16G [message #402936 is a reply to message #402933] Wed, 13 May 2009 04:20 Go to previous message
dbanukesh
Messages: 96
Registered: November 2008
Location: London
Member

It is Windows 3000 Server Enterprise Edition R2 32 Bit.
Previous Topic: Regarding AWR report
Next Topic: Query Optimization (merged)
Goto Forum:
  


Current Time: Mon Dec 05 12:53:13 CST 2016

Total time taken to generate the page: 0.14768 seconds