| No performance boost after upgrading database server RAM from 3GB to 16G [message #401507] |
Tue, 05 May 2009 05:51  |
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 #402050 is a reply to message #401824] |
Thu, 07 May 2009 07:32  |
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 |
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 |
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 #402275 is a reply to message #402079] |
Fri, 08 May 2009 06:31 |
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 #402292 is a reply to message #402278] |
Fri, 08 May 2009 08:44 |
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.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
. Moreover we have all datafiles (index, data, undo and tempfile) in the same directory.
