SQL> select * from v$resource_limit;
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU
------------------------------ ------------------- --------------- ---------- ----------
processes 122 150 150 150
sessions 130 170 170 170
enqueue_locks 272 324 2230 2230
enqueue_resources 186 199 968 UNLIMITED
ges_procs 0 0 0 0
ges_ress 0 0 0 UNLIMITED
ges_locks 0 0 0 UNLIMITED
ges_cache_ress 0 0 0 UNLIMITED
ges_reg_msgs 0 0 0 UNLIMITED
ges_big_msgs 0 0 0 UNLIMITED
ges_rsv_msgs 0 0 0 0
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU
------------------------------ ------------------- --------------- ---------- ----------
gcs_resources 0 0 279488 279488
gcs_shadows 0 0 279488 279488
dml_locks 1 140 748 UNLIMITED
temporary_table_locks 0 9 UNLIMITED UNLIMITED
transactions 32508 44739 187 UNLIMITED
branches 0 15 187 UNLIMITED
cmtcallbk 0 5 187 UNLIMITED
sort_segment_locks 274 400 UNLIMITED UNLIMITED
max_rollback_segments 19 30 38 38
max_shared_servers 1 1 20 20
parallel_max_servers 0 5 6 6
22 rows selected.
SQL> show parameter session
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
mts_sessions integer 165
session_cached_cursors integer 0
session_max_open_files integer 10
sessions integer 170
shared_server_sessions integer 165
SQL> show parameter process
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 1
db_writer_processes integer 1
job_queue_processes integer 10
log_archive_max_processes integer 2
processes integer 150
SQL> show parameter transactions
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
transactions integer 187
transactions_per_rollback_segment integer 5
Formula for setting sessions
Sessions = (1.1 *
PROCESSES) + 5For Prime Database
Processes = 220
Sessions =247
Changing these paramaters requires downtime of database
Now change the parameters in pfile and
Startup pfile=’ /appOracle/product/9.2.0/dbs/initprimepro.ora’;
SQL> select * from v$resource_limit;
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU
------------------------------ ------------------- --------------- ---------- ----------
processes 40 41 220 220
sessions 43 46 247 247
enqueue_locks 175 178 3140 3140
enqueue_resources 172 172 1304 UNLIMITED
ges_procs 0 0 0 0
ges_ress 0 0 0 UNLIMITED
ges_locks 0 0 0 UNLIMITED
ges_cache_ress 0 0 0 UNLIMITED
ges_reg_msgs 0 0 0 UNLIMITED
ges_big_msgs 0 0 0 UNLIMITED
ges_rsv_msgs 0 0 0 0
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU
------------------------------ ------------------- --------------- ---------- ----------
gcs_resources 0 0 279488 279488
gcs_shadows 0 0 279488 279488
dml_locks 0 10 1084 UNLIMITED
temporary_table_locks 0 0 UNLIMITED UNLIMITED
transactions 2 8 271 UNLIMITED
branches 0 0 271 UNLIMITED
cmtcallbk 0 0 271 UNLIMITED
sort_segment_locks 12 12 UNLIMITED UNLIMITED
max_rollback_segments 11 11 55 55
max_shared_servers 1 1 20 20
parallel_max_servers 0 0 6 6
No comments:
Post a Comment