OraFAQ Forum: Server Administration » Change SGA_TARGET and SGA_POOL

Home » RDBMS Server » Server Administration » Change SGA_TARGET and SGA_POOL_SIZE parameters (Oracle DB 11.1.0.7, 32-bit Windows Server)

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

Change SGA_TARGET and SGA_POOL_SIZE parameters [message #473781]

Tue, 31 August 2010 12:25

Ricky_1362002
Messages: 111
Registered: February 2009

Senior Member

Hi,

I just started at a new organization.

When I was going through the Enterprise Manager Grid Control, I found an error due to which I looked at the trace file and it said:

ORA-07445: exception encountered: core dump ACCESS_VIOLATION unable_to_trans_pc PC:0x7C81BD02 ADDR:0x49444E49 UNABLE_TO_READ]

I searched and found that it has something to do with the SGA parameters. I saw that the shared_pool_size and the sga_target paramters are set to 0

Also there are certain SQLs hanging at some point. I thought I should change the above mentioned parameters.

My question now is, can I use the Alter System statements from the SQL Plus to change these parameters, and do they change immediately or do I need to reboot the Oracle instance for those changes to take effect?

I would like to do:

alter system set sga_target=400m;

alter system set shared_pool_size=200m;

would these work and take effect immediately?

Please help.

Thanks,
Munna

Report message to a moderator

Re: Change SGA_TARGET and SGA_POOL_SIZE parameters [message #473792 is a reply to message #473781]

Tue, 31 August 2010 13:03

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

post FORMATTED results from following SQL

SELECT * FROM V$SGA_TARGET_ADVICE;

Report message to a moderator

Re: Change SGA_TARGET and SGA_POOL_SIZE parameters [message #473798 is a reply to message #473792]

Tue, 31 August 2010 13:14

Ricky_1362002
Messages: 111
Registered: February 2009

Senior Member

Thank you BlackSwan for your reply.

SQL> select * from v$sga_target_advice;

SGA_SIZE	SGA_SIZE_FACTOR	   ESTD_DB_TIME	   ESTD_DB_TIME_FACTOR	   ESTD_PHYSICAL_READS

1048	               1	      136101	           1	                27425426
524	               0.5	      159333	           1.1707	        29284870
786	               0.75	      142021	           1.0435	        27518672
1310	               1.25	      132331	           0.9723	        27049698
1572	               1.5	      128425	           0.9436	        26492962
1834	               1.75	      126370	           0.9285	        26492962
2096	               2	      126125	           0.9267	        26492962

Also, I have just gone through some other forum and it says that in 11g, "memory_max_target" and "memory_target" parameters manage SGA and PGA components automatically.

Is that true?

Report message to a moderator

Re: Change SGA_TARGET and SGA_POOL_SIZE parameters [message #473799 is a reply to message #473798]

Tue, 31 August 2010 13:17

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>alter system set sga_target=400m;
>alter system set shared_pool_size=200m;

By what formula or other means did you obtain the values above?
What evidence do you have that any change is required or would benefit the DB?

Report message to a moderator

Re: Change SGA_TARGET and SGA_POOL_SIZE parameters [message #473803 is a reply to message #473799]

Tue, 31 August 2010 13:25

Ricky_1362002
Messages: 111
Registered: February 2009

Senior Member

BlackSwan,

I haven't used any formula to obtain the above values, I recollected from a document I read which said that the recommended value for shared_pool_size would be 200M.

And regarding the evidence, I was getting the ORA-07445 error as below:
ORA-07445: exception encountered: core dump ACCESS_VIOLATION unable_to_trans_pc PC:0x7C81BD02 ADDR:0x49444E49 UNABLE_TO_READ]

When I searched for the above error, I found that it could be something related to the SGA/PGA parameters.

Please correct me if I am wrong.

Thank You.

[Updated on: Tue, 31 August 2010 13:25]

Report message to a moderator

Re: Change SGA_TARGET and SGA_POOL_SIZE parameters [message #473811 is a reply to message #473803]

Tue, 31 August 2010 14:55

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

ORA-00600/ORA-07445/ORA-03113 = Oracle bug => search on Metalink and/or call Oracle support

Report message to a moderator

Previous Topic:	login failed
Next Topic:	Multi-block disk reads for index range scans?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 29 08:37:13 CST 2024