OraFAQ Forum: Performance Tuning » Impact of RAM to oracle

Home » RDBMS Server » Performance Tuning » Impact of RAM to oracle (11gR2 on HP-UX IA64)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Impact of RAM to oracle [message #594262]

Tue, 27 August 2013 01:12

yipchunyu
Messages: 5
Registered: August 2013

Junior Member

We have two Oracle 11g R2 running on HP-UX IA64 (with 192 GB ram).
One for production and the other running for test (2 instances on it).
I want to have a concrete picture to see the impact of the RAM.
For example, will it runs faster enough on the UAT machines (as if we double the RAM).
Or whether it helps the production machines too.

It's because we already ask Oracle's support for advice and they suggest that adding more ram won't help.
But I saw some other people say that adding RAM will greatly help the performance.

Can I simply keep running below SQL to monitor the V$DB_CACHE_ADVICE? (in daily operation / running batch job) to check?
Or it has a better way to predict the performance gain with more RAM?

SELECT size_for_estimate, buffers_for_estimate, estd_physical_read_factor, estd_physical_reads
FROM V$DB_CACHE_ADVICE
WHERE name = 'DEFAULT'
AND block_size = (SELECT value FROM V$PARAMETER WHERE name = 'db_block_size')
AND advice_status = 'ON';

* I am not a DBA and know very little on these issues. I just want to know more on personal interest.

Report message to a moderator

Re: Impact of RAM to oracle [message #594263 is a reply to message #594262]

Tue, 27 August 2013 01:18

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Adding RAM will help if you have a lack of RAM otherwise it could even decrease the performances.

Quote:

Can I simply keep running below SQL to monitor the V$DB_CACHE_ADVICE? (in daily operation / running batch job) to check?

You can FIRST do that and see what it tells you.

The following is useless in your query:
Quote:

AND block_size = (SELECT value FROM V$PARAMETER WHERE name = 'db_block_size')

Regards
Michel

Report message to a moderator

Re: Impact of RAM to oracle [message #594295 is a reply to message #594263]

Tue, 27 August 2013 04:23

yipchunyu
Messages: 5
Registered: August 2013

Junior Member

Dear Michel,
Thx for your quick advice. I don't understand why adding more RAM will even decrease the performance. Would u pls share some information for my study? Many thx in advance.

Report message to a moderator

Re: Impact of RAM to oracle [message #594301 is a reply to message #594295]

Tue, 27 August 2013 04:28

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

The real question to my mind is why you trust google and forum posters (no disrespect Michel Wink

) more than the vendors actual support team...

Report message to a moderator

Re: Impact of RAM to oracle [message #594310 is a reply to message #594295]

Tue, 27 August 2013 05:05

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I didn't say it will, I said it could.
More memory -> less io -> more cpu -> more concurrency on latch -> more waits -> less performances
As I said, it is not mandatory it works like this but it could and this is not the only way.
It is not harmless to add more something (memory, cpu...).

Regards
Michel

Report message to a moderator

Re: Impact of RAM to oracle [message #594317 is a reply to message #594310]

Tue, 27 August 2013 06:54

roblco
Messages: 1
Registered: March 2012

Junior Member

I'm not saying that Michel's conjecture is true, but if it is. I can see that Oracle might just to an inappropriate nested loop since the whole table is in memory instead of a merge (from memory). Remember that humans makes these programs and they go in with certain assumptions.

Report message to a moderator

Re: Impact of RAM to oracle [message #594362 is a reply to message #594317]

Tue, 27 August 2013 21:25

yipchunyu
Messages: 5
Registered: August 2013

Junior Member

I did some checking on last night's situation and want to share with you (and seek for advice again).
* sorry I don't know how to format the table properly.

SGA Target = 90GB

Time Cache (MB) Buffers Estd Phys|Read Factor Estd Phys| Reads
Before Batch 1664 196586 3.1424 53064410
Before Batch 17536 2071714 1 16886540
Before Batch 33280 3931720 0.8495 14344901
Restarted Instance 7936 937657 1 90315
Restarted Instance 80384 9497558 1 90315
Restarted Instance 158720 18753140 1 90315
Just started Batch 7936 937657 1 1272264
Just started Batch 80384 9497558 1 1272264
Just started Batch 158720 18753140 1 1272264
Batch 7936 937657 1.6097 30662437
Batch 80384 9497558 1 19048083
Batch 158720 18753140 0.9913 18882797
Batch 7936 937657 1.9738 76564474
Batch 80384 9497558 1 38790310
Batch 158720 18753140 0.9238 35833626
After Batch 7936 937657 1.8039 113862332
After Batch 80384 9497558 1 63121122
After Batch 158720 18753140 0.8951 56502070

Does it mean adding RAM is no use to improve the batch? Or simply because we restart the instance (before batch) and so the adviser won't reflect it fast enough (which if we double the DB cahce, it should helps the batch jobs, just like the advisor suggest during after the batch?)

Report message to a moderator

Re: Impact of RAM to oracle [message #594363 is a reply to message #594362]

Tue, 27 August 2013 21:42

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

above contains directions how to maintain table format!

Report message to a moderator

Re: Impact of RAM to oracle [message #594429 is a reply to message #594362]

Wed, 28 August 2013 05:59

yipchunyu
Messages: 5
Registered: August 2013

Junior Member

Re-posted with proper format:

I did some checking on last night's situation and want to share with you (and seek for advice again).


SGA Target = 90GB

Time	           Cache (MB)	Buffers	Estd     Phys|Read Factor     Estd Phys| Reads
Before Batch	        1664    196586	         3.1424	              53064410
Before Batch	       17536	2071714	         1	              16886540
Before Batch	       33280	3931720	         0.8495	              14344901
Restarted Instance	7936	937657	         1	              90315
Restarted Instance     80384	9497558	         1	              90315
Restarted Instance    158720	18753140	 1	              90315
Just started Batch	7936	937657	         1	              1272264
Just started Batch     80384	9497558	         1	              1272264
Just started Batch    158720	18753140	 1	              1272264
Batch	                7936	937657	         1.6097	              30662437
Batch	               80384	9497558	         1	              19048083
Batch	              158720	18753140	 0.9913	              18882797
Batch	                7936	937657	         1.9738	              76564474
Batch	               80384	9497558	         1	              38790310
Batch	              158720	18753140	 0.9238	              35833626
After Batch	        7936	937657	         1.8039	              113862332
After Batch	       80384	9497558	         1	              63121122
After Batch	      158720	18753140	 0.8951	              56502070

Does it mean adding RAM is no use to improve the batch? Or simply because we restart the instance (before batch) and so the adviser won't reflect it fast enough (which if we double the DB cahce, it should helps the batch jobs, just like the advisor suggest after the batch?)

Report message to a moderator

Re: Impact of RAM to oracle [message #594453 is a reply to message #594301]

Wed, 28 August 2013 10:13

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

In my experience the vendor is amongst the last person I'd trust for advice. They generally seem come from the "throw more hardware at it" stable.

Report message to a moderator

Re: Impact of RAM to oracle [message #594496 is a reply to message #594453]

Thu, 29 August 2013 03:43

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

Well, yes. But in this case they're saying not to Wink

Report message to a moderator

Re: Impact of RAM to oracle [message #594531 is a reply to message #594496]

Thu, 29 August 2013 10:06

yipchunyu
Messages: 5
Registered: August 2013

Junior Member

Roachcoach wrote on Thu, 29 August 2013 16:43

Well, yes. But in this case they're saying not to

it's simply because the hardware is from HP (instead of Oracle itself). LOL

anyway, so, my assumption seems correct? Which it simply because the instance has been restarted and so the db cache advisor won't update fast enough?
How should I quantify the performance improvement? For example,
If I double the db cache (to 150GB), the physical IO will be lower. But how will it translate to actual performance gain? Need to run the batch with extra RAM for a real figure?

After Batch	        7936	937657	         1.8039	              113862332
After Batch	       80384	9497558	         1	              63121122
After Batch	      158720	18753140	 0.8951	              56502070

Report message to a moderator

Previous Topic:	NOWAIT lock requests could hang (like Parallel Queries may hang "enq: TS - contention")
Next Topic:	Please help on this

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Feb 02 18:18:55 CST 2025