Home » RDBMS Server » Performance Tuning » show parameter session (merged)
show parameter session (merged) [message #391156] Wed, 11 March 2009 04:36 Go to next message
dirish
Messages: 205
Registered: November 2006
Senior Member
Hi,

I have a DB server with the following parameters:

SQL> sho sga;
Total System Global Area 2550136832 bytes
Fixed Size 1980808 bytes
Variable Size 268437112 bytes
Database Buffers 2264924160 bytes
Redo Buffers 14794752 bytes

SQL> sho parameter sessions;
NAME TYPE VALUE
------------------------------------ ----------- java_max_sessionspace_size integer 0
java_soft_sessionspace_limit integer 0
license_max_sessions integer 0
license_sessions_warning integer 0
logmnr_max_persistent_sessions integer 1
sessions integer 200shared_server_sessions integer


RAM Size = 8GB


Following the figures above, I want to know the maximum size I can set to the parameter sessions as I am expecting a rise in number of sessions/users.


Thank you,
Dirish
Re: show parameter session (merged) [message #391262 is a reply to message #391156] Wed, 11 March 2009 10:10 Go to previous messageGo to next message
alexzeng
Messages: 133
Registered: August 2005
Location: alexzeng.wordpress.com
Senior Member
hi,
You need to let us know the pga_aggregate_size. As there are still enough memory for pga if your system only run db. You can set the process/session to a higher value.
Regards,
Alex

[Updated on: Thu, 19 March 2009 09:52] by Moderator

Report message to a moderator

Re: show parameter session (merged) [message #391389 is a reply to message #391262] Thu, 12 March 2009 01:19 Go to previous messageGo to next message
dirish
Messages: 205
Registered: November 2006
Senior Member
SQL> sho parameter pga;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 809M
Re: show parameter session (merged) [message #391908 is a reply to message #391389] Sat, 14 March 2009 09:35 Go to previous messageGo to next message
alexzeng
Messages: 133
Registered: August 2005
Location: alexzeng.wordpress.com
Senior Member
dirish wrote on Thu, 12 March 2009 14:19
SQL> sho parameter pga;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 809M




Let's say, you can set the pga_aggregate_target to 1200M, then you can set the sessions to 300.
Regards,
Alex

[Updated on: Sat, 14 March 2009 10:03] by Moderator

Report message to a moderator

Re: show parameter session (merged) [message #392771 is a reply to message #391908] Thu, 19 March 2009 04:30 Go to previous messageGo to next message
dirish
Messages: 205
Registered: November 2006
Senior Member
Hello,

Kindly let me know if I should increase other parameters to cater for large number of users. Please find below my init.ora file.

processes=150
sga_target=2550136832
db_block_size=8192
compatible=10.2.0.1.0
archive_lag_target=0
db_file_multiblock_read_count=16
db_flashback_retention_target=1440
undo_management=AUTO
undo_tablespace=UNDOTBS1
job_queue_processes=10
open_cursors=300
pga_aggregate_target=848297984
control_files=/oracle/product/10.2.0/Db_1/dbs/DG_CONTROL_FILE
db_name=ROCLIVE
dg_broker_start=true
remote_login_passwordfile=exclusive
db_recovery_file_dest='/archive/arch'
db_recovery_file_dest_size=4096M
log_archive_dest_1='LOCATION=use_db_recovery_file_dest','valid_for=(ALL_ROLES,ONLINE_LOGFILE)'
log_archive_dest_2='LOCATION=use_db_recovery_file_dest','valid_for=(STANDBY_ROLE,STANDBY_LOGFILE)'
db_domain=roc.mti.gov.bw
_log_deletion_policy='all'
db_file_name_convert='/d01/','/d01/'
standby_file_management=auto
log_file_name_convert='/d01/redo','/d01/primredo','/archive/arch//onlinelog','/d01/primredo'
db_unique_name=LIVE
log_archive_format=%t_%s_%r.dbf
local_listener=LISTENER
control_files=/d01/primcontrolfiles/control01.ctl
Re: show parameter session (merged) [message #392876 is a reply to message #391156] Thu, 19 March 2009 09:34 Go to previous messageGo to next message
alexzeng
Messages: 133
Registered: August 2005
Location: alexzeng.wordpress.com
Senior Member
Hi,

My suggestion is
processes=300
open_cursors=500
pga_aggregate_target=1200M

Remember you need to have a test to see if it is all right.
Regards,
Alex

[Updated on: Thu, 19 March 2009 09:47] by Moderator

Report message to a moderator

Re: show parameter session (merged) [message #392880 is a reply to message #392876] Thu, 19 March 2009 09:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can suggest what you want it has no value.
The correct values depend on so many things that you can't say anything from what has been posted.

Regards
Michel
Re: show parameter session (merged) [message #392973 is a reply to message #391156] Thu, 19 March 2009 19:50 Go to previous message
oraxperts
Messages: 5
Registered: March 2009
Location: Melbourne
Junior Member
Dirish,

In Oracle 10g this parameter can have value from 1 to 2147483648 but will depend on you capacity requirement.

Cheers

[Updated on: Fri, 20 March 2009 00:06] by Moderator

Report message to a moderator

Previous Topic: which index is faster
Next Topic: Slowness Problem when logging in by tool Oracle sql developer
Goto Forum:
  


Current Time: Sat Dec 03 13:43:49 CST 2016

Total time taken to generate the page: 0.27391 seconds