Download PLATOThe free tool for auditing and tuning your database Version 52 now available  Jul 12, 2010
The DBA-Village forum
as RSS feed
Site Statistics| Ever registered | 41903 | | Total active users | 26384 | | Act. users last 24h | 318 | | Act. users last hour | 15 | | Hits last week | 5251 | | Hits last month | 21266 |
|
Go up
how setting db_cache_size
| Message |
Score |
Author |
Date |
   I have a machine with 40Gb of Ram.
On this machin...... |
|
pier pier |
Apr 15, 2005 |
| Hi,
You can resiza the db_cache_size by editing...... |
|
Demetrios Gogos |
Apr 15, 2005 |
| It also depends on what version of Oracle you are ...... |
|
Edward Grimm |
Apr 15, 2005 |
| The question was not : how can I setting db_cache_...... |
|
pier pier |
Apr 17, 2005 |
| Dear friend,
Can you found any problem to get h...... |
|
Gitesh Trivedi |
Apr 18, 2005 |
| Dear Pier,
You can set the db_cache_size safely...... |
 |
neeraj bhatia |
Apr 18, 2005 |
| Incidentally, does anybody remember Jonathan Lewis...... |
|
Yong Huang |
Apr 19, 2005 |
| Dear Yong,
It is common that large shared pool...... |
|
neeraj bhatia |
Apr 21, 2005 |
| Neeraj,
I emailed Jonathan and he told me this ...... |
|
Yong Huang |
Apr 25, 2005 |
| With Jonathan's permission, here's his email to me...... |
|
Yong Huang |
Apr 25, 2005 |
| Subject: |
how setting db_cache_size |
| Author: |
pier pier, Italy |
| Date: |
Apr 15, 2005, 1971 days ago |
| Message: |
I have a machine with 40Gb of Ram.
On this machine there is a tipical dss applicatation. Only one Oracle Instance
the pga is : (40 G * 80%)* 50% = 16 G.
shared_pool_size = 1G
db_cache_size = 1G
The memory of the machine is always 50% free.
I assume that 20% (40*0,2 = 8) of the memory will be used by the OS
The question is: Could I set db_cache_size = 15Gb? (40-16-1-8)
thanks |
| Goto: |
Reply - Top of page |
If you think this item violates copyrights, please click here
| Subject: |
Re: how setting db_cache_size |
| Author: |
Demetrios Gogos, Greece |
| Date: |
Apr 15, 2005, 1971 days ago |
| Message: |
Hi,
You can resiza the db_cache_size by editing the Pfile while the database is closed and then create the spfile with this script
Create spfile [='spfile-name']
FROM pfile [='pfile-name']
|
| Your rating?: |
This reply is Good Excellent |
| Goto: |
Reply - Top of page |
If you think this item violates copyrights, please click here
| Subject: |
Re: how setting db_cache_size |
| Author: |
Edward Grimm, United States |
| Date: |
Apr 15, 2005, 1971 days ago |
| Message: |
It also depends on what version of Oracle you are currently using. If you are using Oracle 9ir2, then I would ask what is your sga_max_size set to. Hopefully you set it and did not allow it to default.
You can create your spfile when the oracle instance is open.
sqlplus > create spfile from pfile;
This assumes you have started the instance from a pfile init_yourdbname.ora file.
You can also set the parameter sga_max_size in the init*.ora file to however you would like your instance to grow. If you are a single instance on a 40gb machine, you can safely set it to 16gb. Then shutdown your instance.
Startup nomount.
sqlplus > create spfile from pfile;
this loads your new versions into the spfile.
sqlplus > shutdown immediate;
sqlplus > startup;
no parameters on the startup it will default to your spfile.
Then once the instance is up you can dynamically adjust your db_cache_size because you have already pre-allocated the ram to the oracle server.
Hope that helps |
| Your rating?: |
This reply is Good Excellent |
| Goto: |
Reply - Top of page |
If you think this item violates copyrights, please click here
| Subject: |
Re: how setting db_cache_size |
| Author: |
pier pier, Italy |
| Date: |
Apr 17, 2005, 1969 days ago |
| Message: |
The question was not : how can I setting db_cache_size.
But: in your opinion, in the situation described above, Is it right setting db_cache_size = 16Gb or it is too high?
Thanks |
| Your rating?: |
This reply is Good Excellent |
| Goto: |
Reply - Top of page |
If you think this item violates copyrights, please click here
| Subject: |
Re: how setting db_cache_size |
| Author: |
Gitesh Trivedi, India |
| Date: |
Apr 18, 2005, 1969 days ago |
| Message: |
Dear friend,
Can you found any problem to get hit ratio of buffer cache.I think you first check your database buffer cache hit ratio.If it is below then 85 as per oracle guide line then you can increase it because you have good extra memory.
I think you check all ratio like sorting,library cache,dictionary cache.And use this extra memory as per your database needed.
If you get good buffer cache hit ratio in existing configuration why you want to increase it? There is no need to increase it..
All the best.. |
| Your rating?: |
This reply is Good Excellent |
| Goto: |
Reply - Top of page |
If you think this item violates copyrights, please click here
| Subject: |
Re: how setting db_cache_size |
| Author: |
neeraj bhatia, India |
| Date: |
Apr 18, 2005, 1969 days ago |
| Score: |
|
| Message: |
Dear Pier,
You can set the db_cache_size safely.
OS= total RAM*20%
PGA= (RAM-OS)50%
Rest for sga_max_size.
Now check the stats on shared Pool,buffer cache,log buffer.
For example if there are waits on library cache,increase its value.
In DSS ,try to configure recycle pool.
Neeraj
|
| Your rating?: |
This reply is Good Excellent |
| Goto: |
Reply - Top of page |
If you think this item violates copyrights, please click here
| Subject: |
Re: how setting db_cache_size |
| Author: |
Yong Huang, United States |
| Date: |
Apr 19, 2005, 1968 days ago |
| Message: |
Incidentally, does anybody remember Jonathan Lewis's article or message about why a too big buffer cache is not a good idea either? I remember he said that but can't find the reference. It probably has to do with excessive cache buffer chains latch contention, but I may remember that wrong.
Yong Huang |
| Your rating?: |
This reply is Good Excellent |
| Goto: |
Reply - Top of page |
If you think this item violates copyrights, please click here
| Subject: |
Re: how setting db_cache_size |
| Author: |
neeraj bhatia, India |
| Date: |
Apr 21, 2005, 1965 days ago |
| Message: |
Dear Yong,
It is common that large shared pool size degrade performance,But I am surprised to read that large buffer cache size may degrade as well.
If you have any document,please send me link.
Neeraj. |
| Your rating?: |
This reply is Good Excellent |
| Goto: |
Reply - Top of page |
If you think this item violates copyrights, please click here
| Subject: |
Re: how setting db_cache_size |
| Author: |
Yong Huang, United States |
| Date: |
Apr 25, 2005, 1961 days ago |
| Message: |
Neeraj,
I emailed Jonathan and he told me this may be the message:
http://groups-beta.google.com/group/comp.databases.oracle.server/browse_frm/thread/db633d8febc12662
I just re-read it and think there must be another message he posted. But this one is kind of relevant. I'm replying to his email asking for permission to post his email to me on dba-village.
Yong Huang |
| Your rating?: |
This reply is Good Excellent |
| Goto: |
Reply - Top of page |
If you think this item violates copyrights, please click here
| Subject: |
Re: how setting db_cache_size |
| Author: |
Yong Huang, United States |
| Date: |
Apr 25, 2005, 1961 days ago |
| Message: |
With Jonathan's permission, here's his email to me:
Another thought that came to mind as I read it [the Google thread - Yong's note] was the issue
of truncating or dropping objects. Oracle can find any dirty
blocks for the object very easily - as they're on the checkpoint
queue. But finding blocks which are clean in the buffer is
expensive, and gets more expensive the larger the buffer
and the bigger the object. (There were some plans to
improve the code for this, but I haven't tested recently
to see if they have been implemented yet).
I remember the days when I used to think that a
buffer that was between 0.5% and 2% of the
data size was probably about right. With growing
databases, I think I had to move that to a percentage
of the 'active data set' - i.e. the popular bit of the
data set. Now, I look at each case separately.
The problem is exaggerated now by the fact that
(a) large memory sizes are common - though I
haven't see many over 32GB yet and (b) many
systems use direct I/O, and therefore don't have
a large fraction of the memory taken over by
the file system buffer.
As you will find in the post above, the comment
I made to the OP was that if the resource is sitting
there, you may as well let Oracle know about it.
BUT if 100GB seems to be an extreme amount of
memory, and if you have some evidence that
putting loads of it into the buffer is causing a
problem, it is perfectly reasonable to consider
putting some of your database back into the file
system buffer. There may be cases where some
oddity of Oracle's behaviour means you get
Oracle contention if you put memory into the
Oracle buffer, but avoid it if Oracle thinks it
has to do physicals disk reads.
And sometimes, you just have to "waste" a resource
because you simply can't use it safely. |
| Your rating?: |
This reply is Good Excellent |
| Goto: |
Reply - Top of page |
If you think this item violates copyrights, please click here
|