并发用户量 应用类型(OLTP or DSS?)
270-300 OLTP 数据库参数
End Parameter Name value Begin value (if different) archive_lag_target 900 audit_file_dest audit_trail /u01/app/oracle/admin/center/adump DB cluster_database TRUE compatible control_files 11.2.0.0.0 +VOTE/center/controlfile/control02.ctl, +DATA/center/controlfile/control03.ctl, +DATA/center/controlfile/current.265.797342933 db_block_size 8192 db_create_file_de+DATA st db_domain db_file_name_con/datadg/center/datafile, +DATA/center/datafile vert db_name diagnostic_dest dispatchers fal_client fal_server center /u01/app/oracle (PROTOCOL=TCP) (SERVICE=centerXDB) CENTER CENTERDG instance_number 1 local_listener (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.90.175)(PORT=1521)))) log_archive_config log_archive_dest_LOCATION=+LOG valid_for=(all_logfiles, all_roles) 1 db_unique_name=center dg_config=(center, centerdg) log_archive_dest_service=centerdg LGWR ASYNC valid_for=(online_logfiles, primary_role) 2 db_unique_name=centerdg log_archive_dest_enable state_1 log_archive_dest_enable state_2 log_archive_forma%t_%s_%r.dbf t log_file_name_co/redodg/center/onlinelog, +DATA/center/onlinelog nvert memory_target open_cursors processes remote_listener 54223962112 300 1000 erp-scan:1521 remote_login_pasEXCLUSIVE swordfile service_names sessions spfile center 1600 +DATA/center/spfilecenter.ora standby_file_manAUTO agement thread undo_retention 1 1800 undo_tablespace UNDOTBS1
End Parameter Name value Begin value (if different) archive_lag_target 900 audit_file_dest audit_trail /u01/app/oracle/admin/center/adump DB cluster_database TRUE compatible control_files 11.2.0.0.0 +VOTE/center/controlfile/control02.ctl, +DATA/center/controlfile/control03.ctl, +DATA/center/controlfile/current.265.797342933 db_block_size 8192 db_create_file_de+DATA st db_domain db_file_name_con/datadg/center/datafile, +DATA/center/datafile vert db_name diagnostic_dest dispatchers fal_client fal_server center /u01/app/oracle (PROTOCOL=TCP) (SERVICE=centerXDB) CENTER CENTERDG instance_number 2 local_listener (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.90.176)(PORT=1521)))) log_archive_config log_archive_dest_LOCATION=+LOG valid_for=(all_logfiles, all_roles) 1 db_unique_name=center dg_config=(center, centerdg) log_archive_dest_service=centerdg LGWR ASYNC valid_for=(online_logfiles, primary_role) 2 db_unique_name=centerdg log_archive_dest_enable state_1 log_archive_dest_enable state_2 log_archive_forma%t_%s_%r.dbf t log_file_name_co/redodg/center/onlinelog, +DATA/center/onlinelog nvert memory_target open_cursors processes remote_listener 54223962112 300 1000 erp-scan:1521 remote_login_pasEXCLUSIVE swordfile service_names sessions center 1600 spfile +DATA/center/spfilecenter.ora standby_file_manAUTO agement thread undo_retention 2 1800 undo_tablespace UNDOTBS2
三 性能诊断与AWR解读
OS性能
[oracle@erp1oel ~]$ vmstat 5 10 procs -----------memory---------- - --swap-- -----io---- -- system-- -----cpu----- r b swpd free buff cache si so bi bo in cs us sy id wa s 6 1 1103908 85562104 239956 33484984 0 0 460 30 0 0 2 1 97 0 0 4 0 1103908 85552904 239956 33485204 0 0 118143 650 21238 22619 5 1 93 2 0 4 1 1103908 85554160 239956 33485288 0 0 120927 1270 21289 22161 5 1 93 1 0 3 1 1103908 85556448 239956 33485448 0 0 118226 911 20504 20713 5 0 93 2 0 3 1 1103908 85558720 239956 33485488 0 0 327649 382 20455 34177 4 1 93 2 0 6 2 1103908 85510416 239956 33485552 0 0 329859 1442 22407 24455 6 1 91 2 0 2 2 1103908 85514992 239956 33485868 0 0 358962 382 25087 31262 4 1 92 2 0 5 1 1103908 85568840 239956 33485880 0 0 254108 328 24961 25621 5 1 93 1 0 1 1 1103908 85600568 239956 33485952 0 0 181188 580 26194 28164 4 1 93 2 0 3 1 1103908 85615952 239956 33485992 0 0 336441 351 24309 24701 3 1 95 1 0
CPU负载不高
avg-cpu: %user %nice %system %iowait %steal %idle 4.65 0.00 0.56 2.49 0.00 92.31 Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 0.00 0.00 0.00 0 0 sdc 0.00 0.00 0.00 0 0 sdb 0.00 0.00 0.00 0 0 sdd 0.00 0.00 0.00 0 0 sde 0.00 0.00 0.00 0 0 sdf 0.00 0.00 0.00 0 0 sdg 0.00 0.00 0.00 0 0 sdh 522.60 193447.60 464.00 967238 2320 sdi 546.20 196338.40 658.00 981692 3290 sdj 0.00 0.00 0.00 0 0 sdk 49.80 0.00 513.60 0 2568 sdl 0.00 0.00 0.00 0 0 sdm 6.80 153.60 33.00 768 165 sdn 0.00 0.00 0.00 0 0 sdo 0.00 0.00 0.00 0 0 sdp 582.40 200710.00 514.00 1003550 2570 sdq 0.00 0.00 0.00 0 0 sdr 0.00 0.00 0.00 0 0 sds 0.00 0.00 0.00 0 0 sdt 0.00 0.00 0.00 0 0 sdu 0.00 0.00 0.00 0 0 dm-0 68.20 0.00 513.60 0 2568 dm-1 0.00 0.00 0.00 0 0 dm-2 0.00 0.00 0.00 0 0
以上信息显示部分磁盘非常忙碌，各磁盘负载并不均衡。 请核对sdb sdi sdp磁盘用途。
AWR报告解读
四 SQL分析
TOP SQL部分
逻辑读较多
五 主要问题总结
1
2
3
