Configure SGA

[DBA_LUCAS]

Good afternoon !

I am enhancing my oracle database knowledge and I really need your help.
I'm having trouble setting up my SGA, I do not know which values ??I assign to the parameters, I know I can put Oracle to administer automatically dynamically, but I do not know which value assigns to the sga_max_size.
I should put the sga_target = 0 and the sga_max_size with how much?

I actually encounter the errors:

Select all

ORA-00604:error occurred at recursive SQL level 1 
ORA-04031:unable to allocate bytes of shared memory("Shared Pool","Select obj#,type#,ctime,mtim...","sql area","kglhim:temp")

Thanks and I hope the collaboration of all.

Thanks;

Att,

Lucas Mendes Nicácio

[dr_gori]

Topic Moved for DBA

[diegopedrao]

If your server is 32bits it will handle 2.5GB by processing / 3GB process in boot.ini, if it is 64bits it will handle up to the maximum memory of the machine.

cara You can do so for it Automatic Manage:

Select all

 
SHOW PARAMETER SGA_MAX_SIZE; 
SHOW PARAMETER SGA_TARGET; 
 
CREATE PFILE FROM SPFILE; 
ALTER SYSTEM SET SGA_MAX_SIZE=2G SCOPE=SPFILE; 
ALTER SYSTEM SET SGA_TARGET=2G SCOPE=SPFILE; 
SHUTDOWN IMMEDIATE; 
STATUP; 

Ready Your bank will be 2GB;

[DBA_LUCAS]

My server is 4G of RAM and the operating system is windowns 2008 64 bits.

In this case which setting is set? And explain me a little about this pfile created because I know the spfile that are the boot parameters, but this pfile I do not know.

Thanks;

[diegopedrao]

Pfile is a text-based file where Oracle's startup parameters are, the spfile and the file that can not be edited via text because it is a binary file, so what we do:

- We create a copy of Spfile, an initinstance will be generated.
- We change the SPFILE (SCOPE = SPFILE);
- We lowered the bank;
- We climbed the bank, case of error you can return the old spfile with the command:

Select all

 
CREATE SPFILE FROM PFILE='C:\...\INITINSTANCIA.ora'; 

Remembering that spfile you will only get to change it by the Oracle.

I recommend you to put 2G even for the bank ... to make room for the applications and for the OS.

only do the same steps I did in the previous post.

I hope I have helped.

ATT,

Diego Monteiro

[DBA_LUCAS]

Many thanks for the help, I will make the changes and put it if everything happened with exit !!!

[DBA_LUCAS]

Friends, everything happened ok, now I have another doubt, in fact is the same doubt, but in different operating systems,
I have a quadcore 2.4ghz Xeon server, 4GB memory, 250GB HD I do not know which distribution of the Linux is installed, but I believe to be Fedora or RedHat.
I configure my SGA in the same value already said above (2GB for MAX and for TARGET) ???

[adrianoturbo]

To find out the distribution that is using just go to the terminal and enter command more / etc / issue
this is smelling me to a tunning, friendly be very careful in these SGA or even PGA changes before making any changes make a diagnosis, because not always making change in this parameters will have expected performance.
The SGA works with Oracle's memory area and many times the memory area consumed by Oracle should not be the same as the operating system that is actually the PGA.
The slowness is related to several factors: from a badly made query, broken indices, Broadcast, Locks among others.

[adrianoturbo]

Oops correcting tuning.

[DBA_LUCAS]

I set up the server for 2GB of SGA and mesmo did not rose more, when I tried to give Startup it did not go up and accused a memory error, I believe it was very high 2GB, I could also see that the server was using memory in 94% ...

[diegopedrao]

Yes, you will have to reduce that it will work, otherwise it does not rise or otherwise it is slow doing swap.

ATT,

Diego Monteiro