服务器 配置

Question

服务器 配置：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这个值可以在管理界面直 接设置，但是我改小以后，缺实事释放不出来，我等了 三个小时。
最后放弃了，我的方法正确么？－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
问题：这台服务器配置有问题么？
问题：有什么改进的方法么?

Answer 1

初始化参数参数名                                    值                                              默认值        动态             
----------------------------------------------------------------------------------------------------------------
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/[email protected]                                 TRUE       IMMEDIATE      
dg_broker_config_file2                 ?/dbs/[email protected]                                 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               

Answer 2

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              

Answer 3

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          

Answer 4

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/[email protected]                              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  

Answer 5

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

Answer 6

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