Home » RDBMS Server » Server Administration » Changes in the Oracle parameters (Oracle 10g)
Changes in the Oracle parameters [message #424698] Mon, 05 October 2009 07:08 Go to next message
nsurendiran
Messages: 16
Registered: March 2007
Location: India
Junior Member

Dear All,
We have encountered a server outage in our live systems. So we were doing some analysis and found that Oracle resources are getting used up in a large amount. Because of this, DB got hanged and it required a forced shutdown and start.

Our Oracle Expertise had a look into the live server configuration settings and advised that
1) Change the value of parameter pga_aggregate_target to 209715200(bytes) from 72142028(bytes)
2) Change the value of parameter sga_target to 364904448 (bytes)from 0
3) Change the value of parameter open_cursors to 300 from 200
4) Change the value of parameter session_cached_cursors to 40 from 20

Anyone please advise how to change these parameters? I find somewhere using alter command. But I would like to get some experienced people over here.

Also kindly suggest what are all the checks to be done before changing these parameters

Kindly suggest what kind of impact wil be there if something in this goes wrong

Any help would be appreciated

Thanks
Surendran
Re: Changes in the Oracle parameters [message #424700 is a reply to message #424698] Mon, 05 October 2009 07:23 Go to previous messageGo to next message
balakrishnay
Messages: 54
Registered: September 2009
Location: Pune
Member

Hi,

When you DB got hanged ..what were the session waits .. you can never judge your performance until and unless you see something is problematic ..

Benchmark your performance by setting diffrent parameters values and compare awr reports how its performing after increasing parameters.

Regards

Bala
Re: Changes in the Oracle parameters [message #424788 is a reply to message #424698] Tue, 06 October 2009 00:58 Go to previous messageGo to next message
nsurendiran
Messages: 16
Registered: March 2007
Location: India
Junior Member

Can anyone give me the alter commands to set the above mentioned values?

Thanks
Suren
Re: Changes in the Oracle parameters [message #424802 is a reply to message #424788] Tue, 06 October 2009 01:43 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What about ALTER SYSTEM?

Regards
Michel
Re: Changes in the Oracle parameters [message #424856 is a reply to message #424698] Tue, 06 October 2009 04:33 Go to previous messageGo to next message
trantuananh24hg
Messages: 664
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
nsurendiran wrote on Mon, 05 October 2009 19:08

Our Oracle Expertise had a look into the live server configuration settings and advised that
1) Change the value of parameter pga_aggregate_target to 209715200(bytes) from 72142028(bytes)


How? Why did you(he/she) change the value of PGA? What's log message cause this action?

2) Change the value of parameter sga_target to 364904448 (bytes)from 0

10g used Automatic Memory Management, why didn't you (she/he) used before? So that, SGA_MAX_SIZE with some parameters must be initialized instead of SGA_TARGET. Did the DB work well before?

Quote:

3) Change the value of parameter open_cursors to 300 from 200
4) Change the value of parameter session_cached_cursors to 40 from 20


How many cursors was opened concurrently when this open_cursors parameter was 200?
How many cached cursor per session where this session_cached_cursors was 20?

At the end, log file message did not show to you some thing useful information, did it?


Re: Changes in the Oracle parameters [message #424859 is a reply to message #424856] Tue, 06 October 2009 04:53 Go to previous messageGo to next message
balakrishnay
Messages: 54
Registered: September 2009
Location: Pune
Member

Hi,

You can monitor using this script

/* open cursors */

select max(a.value) as highest_open_cur, p.value as max_open_cur
   from v$sesstat a, v$statname b, v$parameter p
   where a.statistic# = b.statistic# 
   and b.name = 'opened cursors current'
   and p.name= 'open_cursors'
   group by p.value;

/* for session cashed cursors */

select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic#  and s.sid=a.sid
and b.name = 'session cursor cache count' ;



Regards

Bala
Re: Changes in the Oracle parameters [message #424940 is a reply to message #424698] Tue, 06 October 2009 11:24 Go to previous message
ursusca
Messages: 40
Registered: September 2009
Location: Toronto, ON
Member

Hi,

Tuning at the instance level is often limited by design and application choices. To be sure that the problem is in the database instance, check the OS statistics and general machine health before tuning the instance. You have to start tuning with the design, then the application, and then the instance. What is your OS?

[Updated on: Tue, 06 October 2009 11:25]

Report message to a moderator

Previous Topic: error for create temp table space in oracle 9
Next Topic: Applying PSU
Goto Forum:
  


Current Time: Wed Sep 28 09:14:46 CDT 2016

Total time taken to generate the page: 0.15709 seconds