OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » SGA Sizing Question

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

SGA Sizing Question [message #244092]

Mon, 11 June 2007 10:32

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

Report message to a moderator

Re: SGA Sizing Question [message #244100 is a reply to message #244092]

Mon, 11 June 2007 10:45

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

Report message to a moderator

Re: SGA Sizing Question [message #244103 is a reply to message #244092]

Mon, 11 June 2007 10:48

Michel Cadot
Messages: 68686
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

Report message to a moderator

Re: SGA Sizing Question [message #244116 is a reply to message #244103]

Mon, 11 June 2007 11:19

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

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
.
.
.
.

Report message to a moderator

Re: SGA Sizing Question [message #244138 is a reply to message #244118]

Mon, 11 June 2007 12:59

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!

Report message to a moderator

Re: SGA Sizing Question [message #244142 is a reply to message #244138]

Mon, 11 June 2007 13:33

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.

Report message to a moderator

Re: SGA Sizing Question [message #244149 is a reply to message #244116]

Mon, 11 June 2007 13:49

Michel Cadot
Messages: 68686
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

Report message to a moderator

Previous Topic:	ora-00354
Next Topic:	Oracle server very slow

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 02:02:08 CDT 2024