服务器 配置

[being21]

服务器 配置：4G内存，4G虚拟内存4个双核CPU raid5磁盘结构 UNIX操作系统


数据库版本 Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production


SGA 信息

名称 大小 (K)
---------------------------------------
Database Buffers 1638400
Fixed Size 723.8203125
Redo Buffers 8460
Variable Size 1310720

----------------------------------------
问题：现在有两个1500万记录级别的表，未分区（尝试分区后未发现有性能改进）。
用一个记录数为 1 万的表 关联这两个表，执行时间是2秒。
换成15万后就是500－700秒。
而且，如果有别的人用SQL loder 往数据库插数的时候，服务器完全不能用。速度无法理解。
看执行时 服务器 状态，I/O 不高，磁盘 busy 其中一个表 是两个磁盘都达到50%左右。
另外 一个 表 ，有些不均衡。一个磁盘 80％以上，一个20%。
sar -w 查看交换 比较 大
交换 在 机器 闲置，无人使用的情况下都 是 100 以上。
我的SQL 调用的时候，上升到300左右。
别的组在调用的时候在600左右。

查看 内存，4G 实际内存 后面括号里面的 数字要大于 实际内存数。虚拟内存还富余十多G。

－－－
顺便问一下，shared_pool_size这个值可以在管理界面直 接设置，但是我改小以后，缺实事释放不出来，我等了 三个小时。
最后放弃了，我的方法正确么？

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
问题：这台服务器配置有问题么？
问题：有什么改进的方法么?

[being21]

初始化参数

参数名 值 默认值 动态
----------------------------------------------------------------------------------------------------------------
O7_DICTIONARY_ACCESSIBILITY FALSE TRUE FALSE
active_instance_count TRUE FALSE
aq_tm_processes 1 FALSE IMMEDIATE
archive_lag_target 0 TRUE IMMEDIATE
audit_file_dest ?/rdbms/audit TRUE FALSE
audit_sys_operations FALSE TRUE FALSE
audit_trail NONE TRUE FALSE
background_core_dump partial TRUE FALSE
background_dump_dest /oracle/admin/crdsdw/bdump FALSE IMMEDIATE
backup_tape_io_slaves FALSE TRUE DEFERRED
bitmap_merge_area_size 1048576 TRUE FALSE
blank_trimming FALSE TRUE FALSE
buffer_pool_keep TRUE FALSE
buffer_pool_recycle TRUE FALSE
circuits 335 TRUE FALSE
cluster_database FALSE TRUE FALSE
cluster_database_instances 1 TRUE FALSE
cluster_interconnects TRUE FALSE
commit_point_strength 1 TRUE FALSE
compatible 9.2.0.0.0 FALSE FALSE
control_file_record_keep_time 7 TRUE IMMEDIATE
control_files /ora_data/crdsdw/control01.ctl FALSE FALSE
control_files /ora_data/crdsdw/control02.ctl FALSE FALSE
control_files /ora_data/crdsdw/control03.ctl FALSE FALSE
core_dump_dest /oracle/admin/crdsdw/cdump FALSE IMMEDIATE
cpu_count 4 TRUE FALSE
create_bitmap_area_size 8388608 TRUE FALSE
cursor_sharing EXACT TRUE IMMEDIATE
cursor_space_for_time FALSE TRUE FALSE
db_16k_cache_size 0 TRUE IMMEDIATE
db_2k_cache_size 0 TRUE IMMEDIATE
db_32k_cache_size 0 TRUE IMMEDIATE
db_4k_cache_size 0 TRUE IMMEDIATE
db_8k_cache_size 0 TRUE IMMEDIATE
db_block_buffers 0 TRUE FALSE
db_block_checking FALSE TRUE IMMEDIATE
db_block_checksum TRUE TRUE IMMEDIATE
db_block_size 8192 FALSE FALSE
db_cache_advice ON TRUE IMMEDIATE
db_cache_size 1677721600 FALSE IMMEDIATE
db_create_file_dest TRUE IMMEDIATE
db_create_online_log_dest_1 TRUE IMMEDIATE
db_create_online_log_dest_2 TRUE IMMEDIATE
db_create_online_log_dest_3 TRUE IMMEDIATE
db_create_online_log_dest_4 TRUE IMMEDIATE
db_create_online_log_dest_5 TRUE IMMEDIATE
db_domain FALSE FALSE
db_file_multiblock_read_count 32 FALSE IMMEDIATE
db_file_name_convert TRUE FALSE
db_files 200 TRUE FALSE
db_keep_cache_size 0 TRUE IMMEDIATE
db_name crdsdw FALSE FALSE
db_recycle_cache_size 0 TRUE IMMEDIATE
db_writer_processes 1 TRUE FALSE
dblink_encrypt_login FALSE TRUE FALSE
dbwr_io_slaves 4 FALSE FALSE
dg_broker_config_file1 ?/dbs/dr1@.dat TRUE IMMEDIATE
dg_broker_config_file2 ?/dbs/dr2@.dat TRUE IMMEDIATE
dg_broker_start FALSE TRUE IMMEDIATE
disk_asynch_io TRUE TRUE FALSE
dispatchers (PROTOCOL=TCP) (SERVICE=crdsdwXDB) FALSE IMMEDIATE
distributed_lock_timeout 60 TRUE FALSE
dml_locks 1472 TRUE FALSE
drs_start FALSE TRUE IMMEDIATE
enqueue_resources 1692 TRUE FALSE
event TRUE FALSE
fal_client TRUE IMMEDIATE
fal_server TRUE IMMEDIATE
fast_start_io_target 0 TRUE IMMEDIATE
fast_start_mttr_target 300 FALSE IMMEDIATE
fast_start_parallel_rollback LOW TRUE IMMEDIATE
file_mapping FALSE TRUE IMMEDIATE
filesystemio_options asynch TRUE IMMEDIATE
fixed_date TRUE IMMEDIATE
gc_files_to_locks TRUE FALSE
global_context_pool_size TRUE FALSE
global_names FALSE TRUE IMMEDIATE

[being21]

hash_area_size 1048576 FALSE FALSE
hash_join_enabled TRUE FALSE FALSE
hi_shared_memory_address 0 TRUE FALSE
hpux_sched_noage 0 TRUE FALSE
hs_autoregister TRUE TRUE IMMEDIATE
ifile TRUE FALSE
instance_groups TRUE FALSE
instance_name crdsdw FALSE FALSE
instance_number 0 TRUE FALSE
java_max_sessionspace_size 0 TRUE FALSE
java_pool_size 117440512 FALSE FALSE
java_soft_sessionspace_limit 0 TRUE FALSE
job_queue_processes 10 FALSE IMMEDIATE
large_pool_size 117440512 FALSE IMMEDIATE
license_max_sessions 0 TRUE IMMEDIATE
license_max_users 0 TRUE IMMEDIATE
license_sessions_warning 0 TRUE IMMEDIATE
local_listener TRUE IMMEDIATE
lock_name_space TRUE FALSE
lock_sga FALSE TRUE FALSE
log_archive_dest TRUE IMMEDIATE
log_archive_dest_1 LOCATION=/dwtext/FXQ/log MANDATORY REOPEN FALSE IMMEDIATE
log_archive_dest_10 TRUE IMMEDIATE
log_archive_dest_2 TRUE IMMEDIATE
log_archive_dest_3 TRUE IMMEDIATE
log_archive_dest_4 TRUE IMMEDIATE
log_archive_dest_5 TRUE IMMEDIATE
log_archive_dest_6 TRUE IMMEDIATE
log_archive_dest_7 TRUE IMMEDIATE
log_archive_dest_8 TRUE IMMEDIATE
log_archive_dest_9 TRUE IMMEDIATE
log_archive_dest_state_1 enable TRUE IMMEDIATE
log_archive_dest_state_10 enable TRUE IMMEDIATE
log_archive_dest_state_2 enable TRUE IMMEDIATE
log_archive_dest_state_3 enable TRUE IMMEDIATE
log_archive_dest_state_4 enable TRUE IMMEDIATE
log_archive_dest_state_5 enable TRUE IMMEDIATE
log_archive_dest_state_6 enable TRUE IMMEDIATE
log_archive_dest_state_7 enable TRUE IMMEDIATE
log_archive_dest_state_8 enable TRUE IMMEDIATE
log_archive_dest_state_9 enable TRUE IMMEDIATE
log_archive_duplex_dest TRUE IMMEDIATE
log_archive_format %t_%s.dbf TRUE FALSE
log_archive_max_processes 2 TRUE IMMEDIATE
log_archive_min_succeed_dest 1 TRUE IMMEDIATE
log_archive_start FALSE TRUE FALSE
log_archive_trace 0 TRUE IMMEDIATE
log_buffer 8388608 FALSE FALSE
log_checkpoint_interval 0 TRUE IMMEDIATE
log_checkpoint_timeout 1800 TRUE IMMEDIATE
log_checkpoints_to_alert FALSE TRUE IMMEDIATE
log_file_name_convert TRUE FALSE
log_parallelism 1 TRUE FALSE
logmnr_max_persistent_sessions 1 TRUE FALSE
max_commit_propagation_delay 700 TRUE FALSE
max_dispatchers 5 TRUE FALSE
max_dump_file_size UNLIMITED TRUE IMMEDIATE
max_enabled_roles 30 TRUE FALSE
max_rollback_segments 73 TRUE FALSE
max_shared_servers 20 TRUE FALSE
mts_circuits 335 TRUE FALSE
mts_dispatchers TRUE IMMEDIATE
mts_listener_address TRUE FALSE
mts_max_dispatchers 5 TRUE FALSE
mts_max_servers 20 TRUE FALSE
mts_multiple_listeners FALSE TRUE FALSE
mts_servers 1 TRUE IMMEDIATE
mts_service crdsdw TRUE FALSE
mts_sessions 330 TRUE FALSE

[being21]

nls_calendar TRUE FALSE
nls_comp TRUE FALSE
nls_currency TRUE FALSE
nls_date_format TRUE FALSE
nls_date_language TRUE FALSE
nls_dual_currency TRUE FALSE
nls_iso_currency TRUE FALSE
nls_language AMERICAN TRUE FALSE
nls_length_semantics BYTE TRUE IMMEDIATE
nls_nchar_conv_excp FALSE TRUE IMMEDIATE
nls_numeric_characters TRUE FALSE
nls_sort TRUE FALSE
nls_territory AMERICA TRUE FALSE
nls_time_format TRUE FALSE
nls_time_tz_format TRUE FALSE
nls_timestamp_format TRUE FALSE
nls_timestamp_tz_format TRUE FALSE
object_cache_max_size_percent 10 TRUE DEFERRED
object_cache_optimal_size 102400 TRUE DEFERRED
olap_page_pool_size 33554432 TRUE DEFERRED
open_cursors 1000 FALSE IMMEDIATE
open_links 4 TRUE FALSE
open_links_per_instance 4 TRUE FALSE
optimizer_dynamic_sampling 1 TRUE IMMEDIATE
optimizer_features_enable 9.2.0 TRUE FALSE
optimizer_index_caching 0 TRUE FALSE
optimizer_index_cost_adj 100 TRUE FALSE
optimizer_max_permutations 2000 TRUE FALSE
optimizer_mode CHOOSE TRUE FALSE
oracle_trace_collection_name TRUE FALSE
oracle_trace_collection_path ?/otrace/admin/cdf TRUE FALSE
oracle_trace_collection_size 5242880 TRUE FALSE
oracle_trace_enable FALSE TRUE IMMEDIATE
oracle_trace_facility_name oracled TRUE FALSE
oracle_trace_facility_path ?/otrace/admin/fdf TRUE FALSE
os_authent_prefix ops$ TRUE FALSE
os_roles FALSE TRUE FALSE
parallel_adaptive_multi_user FALSE TRUE IMMEDIATE
parallel_automatic_tuning FALSE TRUE FALSE
parallel_execution_message_size 2152 TRUE FALSE
parallel_instance_group TRUE IMMEDIATE
parallel_max_servers 5 TRUE FALSE
parallel_min_percent 0 TRUE FALSE
parallel_min_servers 0 TRUE FALSE
parallel_server FALSE TRUE FALSE
parallel_server_instances 1 TRUE FALSE
parallel_threads_per_cpu 2 TRUE IMMEDIATE
partition_view_enabled FALSE TRUE FALSE
pga_aggregate_target 524288000 FALSE IMMEDIATE
plsql_compiler_flags INTERPRETED TRUE IMMEDIATE
plsql_native_c_compiler TRUE IMMEDIATE
plsql_native_library_dir TRUE IMMEDIATE
plsql_native_library_subdir_count 0 TRUE IMMEDIATE
plsql_native_linker TRUE IMMEDIATE
plsql_native_make_file_name TRUE IMMEDIATE
plsql_native_make_utility TRUE IMMEDIATE
plsql_v2_compatibility FALSE TRUE IMMEDIATE
pre_page_sga FALSE TRUE FALSE
processes 300 FALSE FALSE
query_rewrite_enabled TRUE FALSE IMMEDIATE
query_rewrite_integrity enforced TRUE IMMEDIATE
rdbms_server_dn TRUE FALSE
read_only_open_delayed FALSE TRUE FALSE
recovery_parallelism 0 TRUE FALSE

[Linux_9]

remote_archive_enable true TRUE FALSE
remote_dependencies_mode TIMESTAMP TRUE IMMEDIATE
remote_listener TRUE IMMEDIATE
remote_login_passwordfile EXCLUSIVE FALSE FALSE
remote_os_authent FALSE TRUE FALSE
remote_os_roles FALSE TRUE FALSE
replication_dependency_tracking TRUE TRUE FALSE
resource_limit FALSE FALSE IMMEDIATE
resource_manager_plan TRUE IMMEDIATE
rollback_segments TRUE FALSE
row_locking always TRUE FALSE
serial_reuse DISABLE TRUE FALSE
serializable FALSE TRUE FALSE
service_names crdsdw TRUE IMMEDIATE
session_cached_cursors 0 TRUE FALSE
session_max_open_files 10 TRUE FALSE
sessions 335 TRUE FALSE
sga_max_size 3029303112 FALSE FALSE
shadow_core_dump partial TRUE FALSE
shared_memory_address 0 TRUE FALSE
shared_pool_reserved_size 53687091 TRUE FALSE
shared_pool_size 1073741824 FALSE IMMEDIATE
shared_server_sessions 330 TRUE FALSE
shared_servers 1 TRUE IMMEDIATE
sort_area_retained_size 0 TRUE DEFERRED
sort_area_size 1048576 FALSE DEFERRED
spfile ?/dbs/spfile@.ora TRUE FALSE
sql92_security FALSE TRUE FALSE
sql_trace FALSE TRUE FALSE
sql_version NATIVE TRUE FALSE
standby_archive_dest ?/dbs/arch TRUE IMMEDIATE
standby_file_management MANUAL TRUE IMMEDIATE
star_transformation_enabled TRUE FALSE FALSE
statistics_level TYPICAL TRUE IMMEDIATE
tape_asynch_io TRUE TRUE FALSE
thread 0 TRUE FALSE
timed_os_statistics 0 TRUE IMMEDIATE
timed_statistics TRUE FALSE IMMEDIATE
trace_enabled TRUE TRUE IMMEDIATE
tracefile_identifier TRUE FALSE
transaction_auditing TRUE TRUE DEFERRED
transactions 368 TRUE FALSE
transactions_per_rollback_segment 5 TRUE FALSE
undo_management AUTO FALSE FALSE
undo_retention 3600 FALSE IMMEDIATE
undo_suppress_errors FALSE TRUE IMMEDIATE
undo_tablespace UNDOTBS1 FALSE IMMEDIATE
use_indirect_data_buffers FALSE TRUE FALSE
user_dump_dest /oracle/admin/crdsdw/udump FALSE IMMEDIATE
utl_file_dir TRUE FALSE
workarea_size_policy AUTO TRUE IMMEDIATE

[Linux_9]

为啥又不能连三了呢？真是。
忘记说了，管理上面建议的
shared_pool_size 这个值 是 1G
db_cache_size 这个值 是 1.6G
PGA 是3.5G左右

缓存命中率：82.4%

[inthirties]

1500万的主表。4G的内存算是非常紧张的了，加大你的物理内存。减小swap。pga过大需要看看是不是有很多的排序。

[flush_520]

建索引 分区