各个平台上调整最大SGA区的方法，欢迎大家补充！

halt2000

我的操作系统是HP-UX 11i(64),Oracle 9i(64),内存2G,交换分区4G,近期出了这样的问题，请问cooly怎么去一步一步的解决这个问题。谢谢！
SQL> show sga

Total System Global Area 320301720 bytes
Fixed Size 735896 bytes
Variable Size 285212672 bytes
Database Buffers 33554432 bytes
Redo Buffers 798720 bytes

以下是错误日志
客户端报错:
ORA-27102:OUT OF MEMORY
HP-UX error 12: out of space
......
服务器系统日志没有报错,数据库日志:
skgpspawn failed:category = 27142, depinfo = 12, op = fork, loc = skgpspawn3
skgpspawn failed:category = 27142, depinfo = 12, op = fork, loc = skgpspawn3
skgpspawn failed:category = 27142, depinfo = 12, op = fork, loc = skgpspawn3
Thu Jul 10 09:20:03 2003
skgpspawn failed:category = 27142, depinfo = 12, op = fork, loc = skgpspawn3
skgpspawn failed:category = 27142, depinfo = 12, op = fork, loc = skgpspawn3
skgpspawn failed:category = 27142, depinfo = 12, op = fork, loc = skgpspawn3
Thu Jul 10 10:01:05 2003
skgpspawn failed:category = 27142, depinfo = 22, op = fork, loc = skgpspawn5
skgpspawn failed:category = 27142, depinfo = 22, op = fork, loc = skgpspawn5
skgpspawn failed:category = 27142, depinfo = 22, op = fork, loc = skgpspawn5

fals

嘿嘿，各种平台，口气很大的。先佩服一把吧。
可是，我怎么没有看见在windows 2000 advance server平台上的大SGA使用呢？

还请继续总结，谢谢版主辛苦，关注并等待中。

coolyl

最初由 halt2000 发布
[B]我的操作系统是HP-UX 11i(64),Oracle 9i(64),内存2G,交换分区4G,近期出了这样的问题，请问cooly怎么去一步一步的解决这个问题。谢谢！
SQL> show sga

Total System Global Area 320301720 bytes
Fixed Size 735896 bytes
Variable Size 285212672 bytes
Database Buffers 33554432 bytes
Redo Buffers 798720 bytes

以下是错误日志
客户端报错:
ORA-27102:OUT OF MEMORY
HP-UX error 12: out of space
......
服务器系统日志没有报错,数据库日志:
skgpspawn failed:category = 27142, depinfo = 12, op = fork, loc = skgpspawn3
skgpspawn failed:category = 27142, depinfo = 12, op = fork, loc = skgpspawn3
skgpspawn failed:category = 27142, depinfo = 12, op = fork, loc = skgpspawn3
Thu Jul 10 09:20:03 2003
skgpspawn failed:category = 27142, depinfo = 12, op = fork, loc = skgpspawn3
skgpspawn failed:category = 27142, depinfo = 12, op = fork, loc = skgpspawn3
skgpspawn failed:category = 27142, depinfo = 12, op = fork, loc = skgpspawn3
Thu Jul 10 10:01:05 2003
skgpspawn failed:category = 27142, depinfo = 22, op = fork, loc = skgpspawn5
skgpspawn failed:category = 27142, depinfo = 22, op = fork, loc = skgpspawn5
skgpspawn failed:category = 27142, depinfo = 22, op = fork, loc = skgpspawn5 [/B]

HP-UX 11i(64),Oracle 9i(64)上对SGA是没有限制的，你贴出你的os的核心参数的修改来看看

coolyl

最初由 fals 发布
[B]嘿嘿，各种平台，口气很大的。先佩服一把吧。
可是，我怎么没有看见在windows 2000 advance server平台上的大SGA使用呢？

还请继续总结，谢谢版主辛苦，关注并等待中。 [/B]

最近因为工作调动的问题一直很忙，我会尽快整理出来windows上的调整方法的

halt2000

最初由 coolyl 发布
[B]


HP-UX 11i(64),Oracle 9i(64)上对SGA是没有限制的，你贴出你的os的核心参数的修改来看看 [/B]

* Tunable parameters

STRMSGSZ        65535
maxdsiz         0X040000000
maxdsiz_64bit   0X0000000080000000
maxfiles        1024
maxssiz         401604608
maxssiz_64bit   0X040000000
maxswapchunks   2048
maxtsiz         0X0000000040000000
maxuprc         1458
maxusers        200
nstrpty         60
num_tachyon_adapters 5
semmni          512
semmns          2048
shmmax          2147483648
shmmni          512
vps_ceiling     64


SQL> show parameters;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
O7_DICTIONARY_ACCESSIBILITY boolean FALSE
active_instance_count integer
aq_tm_processes integer 0
archive_lag_target integer 0
audit_file_dest string ?/rdbms/audit
audit_sys_operations boolean FALSE
audit_trail string NONE
background_core_dump string partial
background_dump_dest string /oracle/oracle/admin/zs1000/bd
ump
backup_tape_io_slaves boolean FALSE

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
bitmap_merge_area_size integer 1048576
blank_trimming boolean FALSE
buffer_pool_keep string
buffer_pool_recycle string
circuits integer 170
cluster_database boolean FALSE
cluster_database_instances integer 1
cluster_interconnects string
commit_point_strength integer 1
compatible string 9.2.0.0.0
control_file_record_keep_time integer 7

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /data/control01.ctl, /data/con
trol02.ctl, /data/control03.ct
l
core_dump_dest string /oracle/oracle/admin/zs1000/cd
ump
cpu_count integer 2
create_bitmap_area_size integer 8388608
cursor_sharing string EXACT
cursor_space_for_time boolean FALSE
db_16k_cache_size big integer 0
db_2k_cache_size big integer 0

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_block_buffers integer 0
db_block_checking boolean FALSE
db_block_checksum boolean TRUE
db_block_size integer 8192
db_cache_advice string ON
db_cache_size big integer 33554432
db_create_file_dest string
db_create_online_log_dest_1 string

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
db_domain string
db_file_multiblock_read_count integer 16
db_file_name_convert string
db_files integer 200
db_keep_cache_size big integer 0
db_name string zs1000
db_recycle_cache_size big integer 0

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_writer_processes integer 1
dblink_encrypt_login boolean FALSE
dbwr_io_slaves integer 0
dg_broker_config_file1 string ?/dbs/dr1@.dat
dg_broker_config_file2 string ?/dbs/dr2@.dat
dg_broker_start boolean FALSE
disk_asynch_io boolean TRUE
dispatchers string (PROTOCOL=TCP) (SERVICE=zs1000
XDB)
distributed_lock_timeout integer 60
dml_locks integer 748

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
drs_start boolean FALSE
enqueue_resources integer 968
event string
fal_client string
fal_server string
fast_start_io_target integer 0
fast_start_mttr_target integer 300
fast_start_parallel_rollback string LOW
file_mapping boolean FALSE
filesystemio_options string asynch
fixed_date string

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
gc_files_to_locks string
global_context_pool_size string
global_names boolean FALSE
hash_area_size integer 1048576
hash_join_enabled boolean TRUE
hi_shared_memory_address integer 0
hpux_sched_noage integer 0
hs_autoregister boolean TRUE
ifile file
instance_groups string
instance_name string zs1000

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_number integer 0
java_max_sessionspace_size integer 0
java_pool_size big integer 117440512
java_soft_sessionspace_limit integer 0
job_queue_processes integer 300
large_pool_size big integer 16777216
license_max_sessions integer 0
license_max_users integer 0
license_sessions_warning integer 0
local_listener string
lock_name_space string

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
log_archive_dest string
log_archive_dest_1 string
log_archive_dest_10 string
log_archive_dest_2 string
log_archive_dest_3 string
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_9 string
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable
log_archive_dest_state_2 string enable
log_archive_dest_state_3 string enable
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_duplex_dest string
log_archive_format string %t_%s.dbf
log_archive_max_processes integer 2
log_archive_min_succeed_dest integer 1
log_archive_start boolean FALSE
log_archive_trace integer 0
log_buffer integer 524288
log_checkpoint_interval integer 0
log_checkpoint_timeout integer 1800
log_checkpoints_to_alert boolean FALSE
log_file_name_convert string

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_parallelism integer 1
logmnr_max_persistent_sessions integer 1
max_commit_propagation_delay integer 700
max_dispatchers integer 5
max_dump_file_size string UNLIMITED
max_enabled_roles integer 30
max_rollback_segments integer 37
max_shared_servers integer 20
mts_circuits integer 170
mts_dispatchers string (PROTOCOL=TCP) (SERVICE=zs1000
XDB)

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
mts_listener_address string
mts_max_dispatchers integer 5
mts_max_servers integer 20
mts_multiple_listeners boolean FALSE
mts_servers integer 1
mts_service string zs1000
mts_sessions integer 165
nls_calendar string
nls_comp string
nls_currency string
nls_date_format string

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_date_language string
nls_dual_currency string
nls_iso_currency string
nls_language string AMERICAN
nls_length_semantics string BYTE
nls_nchar_conv_excp string FALSE
nls_numeric_characters string
nls_sort string
nls_territory string AMERICA
nls_time_format string
nls_time_tz_format string

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_timestamp_format string
nls_timestamp_tz_format string
object_cache_max_size_percent integer 10
object_cache_optimal_size integer 102400
olap_page_pool_size integer 33554432
open_cursors integer 300
open_links integer 4
open_links_per_instance integer 4
optimizer_dynamic_sampling integer 1
optimizer_features_enable string 9.2.0
optimizer_index_caching integer 0

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_index_cost_adj integer 100
optimizer_max_permutations integer 2000
optimizer_mode string CHOOSE
oracle_trace_collection_name string
oracle_trace_collection_path string ?/otrace/admin/cdf
oracle_trace_collection_size integer 5242880
oracle_trace_enable boolean FALSE
oracle_trace_facility_name string oracled
oracle_trace_facility_path string ?/otrace/admin/fdf
os_authent_prefix string ops$
os_roles boolean FALSE

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_adaptive_multi_user boolean FALSE
parallel_automatic_tuning boolean FALSE
parallel_execution_message_size integer 2152
parallel_instance_group string
parallel_max_servers integer 5
parallel_min_percent integer 0
parallel_min_servers integer 0
parallel_server boolean FALSE
parallel_server_instances integer 1
parallel_threads_per_cpu integer 2
partition_view_enabled boolean FALSE

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 25165824
plsql_compiler_flags string INTERPRETED
plsql_native_c_compiler string
plsql_native_library_dir string
plsql_native_library_subdir_count integer 0
plsql_native_linker string
plsql_native_make_file_name string
plsql_native_make_utility string
plsql_v2_compatibility boolean FALSE
pre_page_sga boolean FALSE
processes integer 150

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
query_rewrite_enabled string FALSE
query_rewrite_integrity string enforced
rdbms_server_dn string
read_only_open_delayed boolean FALSE
recovery_parallelism integer 0
remote_archive_enable string true
remote_dependencies_mode string TIMESTAMP
remote_listener string
remote_login_passwordfile string EXCLUSIVE
remote_os_authent boolean FALSE
remote_os_roles boolean FALSE

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
replication_dependency_tracking boolean TRUE
resource_limit boolean FALSE
resource_manager_plan string
rollback_segments string
row_locking string always
serial_reuse string DISABLE
serializable boolean FALSE
service_names string zs1000
session_cached_cursors integer 0
session_max_open_files integer 10
sessions integer 170

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 320301720
shadow_core_dump string partial
shared_memory_address integer 0
shared_pool_reserved_size big integer 5872025
shared_pool_size big integer 117440512
shared_server_sessions integer 165
shared_servers integer 1
sort_area_retained_size integer 0
sort_area_size integer 524288
spfile string ?/dbs/spfile@.ora
sql92_security boolean FALSE

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sql_trace boolean FALSE
sql_version string NATIVE
standby_archive_dest string ?/dbs/arch
standby_file_management string MANUAL
star_transformation_enabled string FALSE
statistics_level string TYPICAL
tape_asynch_io boolean TRUE
thread integer 0
timed_os_statistics integer 0
timed_statistics boolean TRUE
trace_enabled boolean TRUE

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
tracefile_identifier string
transaction_auditing boolean TRUE
transactions integer 187
transactions_per_rollback_segment integer 5
undo_management string AUTO
undo_retention integer 10800
undo_suppress_errors boolean FALSE
undo_tablespace string UNDOTBS1
use_indirect_data_buffers boolean FALSE
user_dump_dest string /oracle/oracle/admin/zs1000/ud
ump

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir string
workarea_size_policy string AUTO

ssssss24

多谢！收藏！！

coolyl

WINDOWS平台上扩展SGA
针对32bit的Windows。Windows2000上不能利用超过4G的内存，一般是2G的内存保留给进程，2G内存保留给核心的。在Windows2000 Advanced Server上可以分配3G给进程，1G留给核心的。
1．        如果机器的内存在4G以下，可以使用Physical Address Extensions（PAE）或者是Address Windowing Extensions（AWE）进行扩展，如果机器内存大于4GB就只能使用AWE进行扩展了。
AWE支持以下的Windows操作系统：
Windows 2000 Datacenter Server
Windows 2000 Advanced Server
Windows 2003 Datacenter Edition（32bit）
Windows 2003 Enterprise Edition（32bit）
AWE不支持以下的Windows操作系统：
Windows 2000 Server（Standard）
Windows2000 Professional
Windows XP Home Edition
Windows XP Professional
Windows 2003 Standard Edition
Windows 2003 Web Edition
AWE支持的Oracle数据库的版本：
Oracle 8.1.6.X
Oracle 8.1.7.X
Oracle 9.2.X
AWE不支持Oracle9.0.1.X
在标准版的Oracle9.2.0.1上，如果你设置了use_indirect_data_buffers=true,启动就会报错：
ORA-439-feature not enabled:very large memory.
这个是标准版的Oracle的9.2.0.1的一个bug（＃2520796），在Oracle9.2.0.2中解决了。
2．        在操作系统上启动AWE
AWE在操作系统上可以通过在boot.ini文件中加/PAE切换启用。
例如：
multi(0)disk(0)rdisk(0)partition(1)\WINNT="Microsoft Windows 2000 Advanced Server" /PAE
也可以同时使用/PAE和/3G在同一台机器上，例如：
multi(0)disk(0)rdisk(0)partition(1)\WINNT="Microsoft Windows 2000 Advanced Server" /3GB /PAE
但是同时使用内存最到能够支持到16G，如果用使用16G以上的内存，则必须选择其中的一种使用了。
3．        在Oracle上启用AWE
首先要在启动的参数文件中设置参数
USE_INDIRECT_DATA_BUFFERS=TRUE
如果设置了这个参数，对于Oracle9.2.0的版本则不能再使用DB_CACHE_SIZE参数了，只能使用DB_BLOCK_BUFFERS参数。
扩展的内存只能增加到BUFFER CACHE中去使用，因此只能增大DB_BLOCK_BUFFERS这个参数去扩展SGA区。
4．        AWE_WINDOW_MEMORY实现故障解决
在Oracle8.1.7版本以下启动数据库的时候不用设置AWE_WINDOW_MEMORY的最小值，而在Oracle9.2.0的版本中则强制要设置AWE_WINDOW_MEMORY的最小值，这个最小值在Oracle8.1.7中通过DB_BLOCK_LRU_LATCHES参数设定，在Oracle9.2.0中则通过_DB_BLOCK_LRU_LATCHES隐含参数设定，Oracle9.2.0的AWE_WINDOW_MEMORY的最小值由以下的公式计算：
MIN(AWE_WINDOW_MEMORY)=(4096 * DB_BLOCK_SIZE * _DB_BLOCK_LRU_LATCHES)/8
_DB_BLOCK_LRU_LATCHES = (Max buffer pools * SETS_PER_POOL)
Max Buffer Pools是个常量，等于8，SETS_PER_POOL是个变量，它的大小由是否启用VLM（即设定USE_INDIRECT_DATA_BUFFERS=TRUE参数）决定：
SETS_PER_POOL = 2* CPU_COUNT (启用 VLM)
SETS_PER_POOL= CPU Count /2  (不启用VLM)
例如：
CPU's = 16
             DB_BLOCK_SIZE = 8192
             Total RAM = 16 GB  
             SETS_PER_POOL = 2 * CPU_COUNT = 32
             _DB_BLOCK_LRU_LATCHES = (max buffer Pools * sets_per_pool) = 8*32 = 256              MIN(AWE_WINDOW_MEMORY)=(4096*DB_BLOCK_SIZE*_DB_BLOCK_LRU_LATCHES)/8 =( 4096 * 8192 * 256) / 8 = 1073741824 bytes = 1024 MB
            这样在Windows的注册表中的HKLM\Software\Oracle\Homex下的AWE_WINDOW_MEMORY值至少是1024M，否则就会提示错误：
            ORA-27102 out of memory  
            OSD-00034 Message 34 not found;  Product=RDBMS;facility =SOSD  
            O/S Error: (OS 8) Not enough storage is available to process this command

ITjane

,read and read,thinking and thinking

jerry

To : coolyl

Nice work...
^^
Thanks...

pineapplecui

up