Home » RDBMS Server » Server Administration » SGA Sizing Question
SGA Sizing Question [message #244092] Mon, 11 June 2007 10:32 Go to next message
mivey4
Messages: 19
Registered: March 2007
Junior Member
Hi.

I have a development database setup on windows vista that has the following information for the SGA.

Total System Global Area 612368384 bytes
Fixed Size 1292060 bytes
Variable Size 226494692 bytes
Database Buffers 381681664 bytes
Redo Buffers 2899968 bytes

When I query v$parameters for any of the parameters corresponding to the SGA I get:

SGA_BLOCK TYPE VALUE DISPLAY_VALUE
shared_pool_size 6 0 0
large_pool_size 6 0 0
java_pool_size 6 0 0
db_cache_size 6 0 0
db_keep_cache_size 6 0 0
db_recycle_cache_size 6 0 0
log_buffer 3 28549 2854912

So my question is; where are the values for the SGA being calculated from??? And is there a value somewhere that needs to be switched if I want to use tell Oracle to use values assigned in the initialization parameter as opposed to autoconfiguring the SGA or will simply assiging the values in my spfile override the calculations that I am getting for the SGA right now?

I also have noted that a direct query of the v$parameter view for the redo log buffers says 28549 but SGA says the Redo buffers are 2899968. Whats up with that???

Sorry if I seem a bit confused; but I am. Embarassed
Re: SGA Sizing Question [message #244100 is a reply to message #244092] Mon, 11 June 2007 10:45 Go to previous messageGo to next message
sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata
Senior Member

I think you are looking on sga_max_size parameter.

query like:
select * from v$parameter where description like '%SGA%'

for geeting the sga_max_size value.

Sanka
Re: SGA Sizing Question [message #244103 is a reply to message #244092] Mon, 11 June 2007 10:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
where are the values for the SGA being calculated from???

From what Oracle needs.

Quote:
And is there a value somewhere that needs to be switched if I want to use tell Oracle to use values assigned in the initialization parameter as opposed to autoconfiguring the SGA

Don't use SGA_TARGET parameter and assign values to other parameters.

Quote:
I also have noted that a direct query of the v$parameter view for the redo log buffers says 28549 but SGA says the Redo buffers are 2899968. Whats up with that???

A minimum size is required, Oracle calculates it and don't care about your paramter if it is less.

All of this is of course in Concepts manual.

Regards
Michel
icon14.gif  Re: SGA Sizing Question [message #244116 is a reply to message #244103] Mon, 11 June 2007 11:19 Go to previous messageGo to next message
mivey4
Messages: 19
Registered: March 2007
Junior Member
Thanks Michel.

So if I understand you correctly; you're implying that by default Oracle will allocate its own memory space without having the need of manually setting any of the parameters but I can force manual allocation of them by having the SGA_TARGET parameter set to zero value?

I believe I understand this with the exception that I did follow up on your implication that this information in the concepts manual and it states that the default for SGA_TARGET is disabled and I didn't change the parameter after the initial install.

Can you direct me or advise me where I may find information for how I can acquire and view the actual SGA parameter allocations since the v$parameter view doesn't show it under these circumstances?

All I am able to determine via that view is the total amount of RAM allocated to SGA, not how much is allocated to what.

-------------------------------
P.S. I do read the manuals but the documentation has quite a amount of information that spawns seemingly endless links that can be quite cumbersome depending on what you're trying to find.

[Updated on: Mon, 11 June 2007 11:22]

Report message to a moderator

Re: SGA Sizing Question [message #244118 is a reply to message #244116] Mon, 11 June 2007 11:25 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member


Quote:
Can you direct me or advise me where I may find information for how I can acquire and view the actual SGA parameter allocations since


SQL>show parameter sga_max_size
SQL>show parameter shared_pool_size
SQL> show parameter log_buffer
SQL> show parameter db_cache_size
.
.
.
.
Re: SGA Sizing Question [message #244138 is a reply to message #244118] Mon, 11 June 2007 12:59 Go to previous messageGo to next message
mivey4
Messages: 19
Registered: March 2007
Junior Member
Thank you DreamzZ, I already knew that but what I actually meant was how can you see the actual values that are being being allocated to various SGA components when the SGA_TARGET value is set and I suppose the answer is that you can't because Oracle is allocating them dynamically and so your recommended show parameters statement display 0 for all values except the redo log buffer.

So the only way I apparently can view the values being used is to set the SGA_TARGET to 0. Then I get actual non-zero values for those parameters.

Its a learning curve, but I appreciate the reply.

Cool Cheers!
Re: SGA Sizing Question [message #244142 is a reply to message #244138] Mon, 11 June 2007 13:33 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Quote:
I apparently can view the values being used is to set the SGA_TARGET to 0. Then I get actual non-zero values for those parameters


when you set sga_target=some value its mean automatic tunning of Sga

but if i set the shared_pool_size=100m then its mean its the lower bound for this parameter sga will not reduce it from its lower bound during automatic tunning.
Re: SGA Sizing Question [message #244149 is a reply to message #244116] Mon, 11 June 2007 13:49 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database Concepts, Chapter 8 "Memory Architecture", Section "Overview of the System Global Area".
It explains in details how the parameters interfere.

Regards
Michel
Previous Topic: ora-00354
Next Topic: Oracle server very slow
Goto Forum:
  


Current Time: Sun Dec 04 00:26:40 CST 2016

Total time taken to generate the page: 0.07565 seconds