ORACLE I/O性能分析步骤:
1,性能分析的几个原则:
a, 检查消息的成功率。(成功率必须>99%,否则测试无意义)
b, 监测日志,不应出现异常。
2,分析CPU负荷。(mpstat可看多CPU情况,top->1可查看cpu情况)
命令:mpstat -P ALL 2 10
3,再查找哪个磁盘I/O吃紧 (iostat -x 1)
命令:iostat -x 1
注意:r/s+w/s,await, svctm, %util 四者之间的关系。(见后面描述)
4,然后查找 ORACLE 系统表 v$filestat 得知表空间的访问频率。
命令:Select phyrds,phywrts,d.name from v$datafile d,v$filestat f where f.file#=d.file# order by d.name
============
下面是实验室的测试结果:
1,看下面输出,其中iowait非常高,说明 oracle 可能存在 I/O 瓶颈。
[oracle@BJ-WAP-SA-1 ~]$ mpstat -P ALL 2 100
Linux 2.6.9-78.ELsmp (BJ-WAP-SA-1) 06/25/2010
09:02:59 AM CPU %user %nice %sys %iowait %irq %soft %steal %idle intr/s
09:03:01 AM all 1.25 0.00 0.94 13.56 0.00 0.00 0.00 84.25 5902.50
09:03:01 AM 0 0.00 0.00 0.50 0.50 0.00 0.00 0.00 99.00 0.00
09:03:01 AM 1 1.00 0.00 1.50 1.00 0.00 0.00 0.00 97.00 4.50
09:03:01 AM 2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00 0.50
09:03:01 AM 3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00 0.00
09:03:01 AM 4 8.50 0.00 4.00 5.00 0.00 0.00 0.00 82.00 5460.00
09:03:01 AM 5 0.50 0.00 1.50 98.00 0.00 0.00 0.00 0.50 428.00
09:03:01 AM 6 0.00 0.00 0.00 3.50 0.00 0.00 0.00 96.00 0.00
09:03:01 AM 7 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00 9.00
09:03:01 AM CPU %user %nice %sys %iowait %irq %soft %steal %idle intr/s
09:03:03 AM all 0.06 0.00 0.12 12.41 0.06 0.00 0.00 87.34 1333.50
09:03:03 AM 0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00 0.00
09:03:03 AM 1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00 4.50
09:03:03 AM 2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00 0.50
09:03:03 AM 3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00 0.00
09:03:03 AM 4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00 1014.50
09:03:03 AM 5 0.00 0.00 0.50 99.50 0.00 0.00 0.00 0.00 305.00
09:03:03 AM 6 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00 0.00
09:03:03 AM 7 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00 9.00
2,看下面输出,可以得到如下结论:
a, cciss/c0d0p3 的 %util 为90.40%,说明CPU时间均用于排队了。
b, 继续分析:(w/s+r/s)*svctm 表明在1秒内总的服务时间,如果它略等于1秒,说明消息过来比较均速。否则,说明消息非常不均速。
c, 看其 await = 排队时间+svctm,相差较大,说明排队时间较长。
iostat -x 1
avg-cpu: %user %nice %system %iowait %steal %idle
0.75 0.00 0.25 12.50 0.00 86.75
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util
cciss/c0d0 0.00 194.06 0.00 182.18 0.00 3009.90 0.00 1504.95 16.52 1.25 6.89 5.41 98.61
cciss/c0d0p1
0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
cciss/c0d0p2
0.00 27.72 0.00 29.70 0.00 459.41 0.00 229.70 15.47 0.33 11.23 10.30 30.59
cciss/c0d0p3
0.00 150.50 0.00 150.50 0.00 2407.92 0.00 1203.96 16.00 0.90 6.02 6.01 90.40
cciss/c0d0p5
0.00 15.84 0.00 1.98 0.00 142.57 0.00 71.29 72.00 0.02 8.00 8.00 1.58
cciss/c0d0p6
0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
cciss/c0d0p7
0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
avg-cpu: %user %nice %system %iowait %steal %idle
0.00 0.00 0.12 12.50 0.00 87.38
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util
cciss/c0d0 0.00 158.00 0.00 157.00 0.00 2528.00 0.00 1264.00 16.10 1.67 10.64 6.37 100.00
cciss/c0d0p1
0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
cciss/c0d0p2
0.00 41.00 0.00 44.00 0.00 688.00 0.00 344.00 15.64 0.57 12.77 12.73 56.00
cciss/c0d0p3
0.00 117.00 0.00 113.00 0.00 1840.00 0.00 920.00 16.28 1.11 9.81 8.09 91.40
cciss/c0d0p5
0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
cciss/c0d0p6
0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
cciss/c0d0p7
0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
3, 看下面输出,在三个表空间 undotbs01.dbf,system01.dbf 和 wapgw_data_tablespace.dbf 访问非常频繁。尤以undotbs01.dbf为最繁忙。
SQL> select phyrds,phywrts,d.name from v$datafile d,v$filestat f where f.file#=d.file# order by d.name;
PHYRDS PHYWRTS NAME
------ ------- --------------------------------------------------------------------------------
37583 35088 /bases/oradata/wapgw_data_tablespace.dbf
64 22 /opt/oracle/oradata/CMCCWAP/120100624_21.dbf
64 22 /opt/oracle/oradata/CMCCWAP/120100624_22.dbf
74 22 /opt/oracle/oradata/CMCCWAP/120100624_23.dbf
44 22 /opt/oracle/oradata/CMCCWAP/120100625_00.dbf
44 22 /opt/oracle/oradata/CMCCWAP/120100625_01.dbf
44 22 /opt/oracle/oradata/CMCCWAP/120100625_02.dbf
44 22 /opt/oracle/oradata/CMCCWAP/120100625_03.dbf
44 22 /opt/oracle/oradata/CMCCWAP/120100625_04.dbf
44 22 /opt/oracle/oradata/CMCCWAP/120100625_05.dbf
44 22 /opt/oracle/oradata/CMCCWAP/120100625_06.dbf
44 22 /opt/oracle/oradata/CMCCWAP/120100625_07.dbf
44 22 /opt/oracle/oradata/CMCCWAP/120100625_08.dbf
44 22 /opt/oracle/oradata/CMCCWAP/120100625_09.dbf
44 22 /opt/oracle/oradata/CMCCWAP/120100625_10.dbf
44 22 /opt/oracle/oradata/CMCCWAP/120100625_11.dbf
44 22 /opt/oracle/oradata/CMCCWAP/120100625_12.dbf
64 22 /opt/oracle/oradata/CMCCWAP/120100625_13.dbf
64 22 /opt/oracle/oradata/CMCCWAP/120100625_14.dbf
64 22 /opt/oracle/oradata/CMCCWAP/120100625_15.dbf
PHYRDS PHYWRTS NAME
------ ------- --------------------------------------------------------------------------------
64 22 /opt/oracle/oradata/CMCCWAP/120100625_16.dbf
64 22 /opt/oracle/oradata/CMCCWAP/120100625_17.dbf
64 22 /opt/oracle/oradata/CMCCWAP/120100625_18.dbf
64 22 /opt/oracle/oradata/CMCCWAP/120100625_19.dbf
64 22 /opt/oracle/oradata/CMCCWAP/120100625_20.dbf
64 22 /opt/oracle/oradata/CMCCWAP/120100625_21.dbf
64 22 /opt/oracle/oradata/CMCCWAP/120100625_22.dbf
64 22 /opt/oracle/oradata/CMCCWAP/120100625_23.dbf
84 22 /opt/oracle/oradata/CMCCWAP/RADIUS120100624_04.dbf
84 22 /opt/oracle/oradata/CMCCWAP/RADIUS120100624_05.dbf
94 22 /opt/oracle/oradata/CMCCWAP/RADIUS120100624_06.dbf
94 22 /opt/oracle/oradata/CMCCWAP/RADIUS120100624_07.dbf
104 22 /opt/oracle/oradata/CMCCWAP/RADIUS120100624_08.dbf
104 22 /opt/oracle/oradata/CMCCWAP/RADIUS120100624_09.dbf
104 22 /opt/oracle/oradata/CMCCWAP/RADIUS120100624_10.dbf
104 22 /opt/oracle/oradata/CMCCWAP/RADIUS120100624_11.dbf
104 22 /opt/oracle/oradata/CMCCWAP/RADIUS120100624_12.dbf
104 12 /opt/oracle/oradata/CMCCWAP/RADIUS120100624_13.dbf
24 22 /opt/oracle/oradata/CMCCWAP/RADIUS120100624_14.dbf
24 22 /opt/oracle/oradata/CMCCWAP/RADIUS120100624_15.dbf
24 22 /opt/oracle/oradata/CMCCWAP/RADIUS120100624_16.dbf
PHYRDS PHYWRTS NAME
------ ------- --------------------------------------------------------------------------------
24 22 /opt/oracle/oradata/CMCCWAP/RADIUS120100624_17.dbf
24 22 /opt/oracle/oradata/CMCCWAP/RADIUS120100624_18.dbf
24 22 /opt/oracle/oradata/CMCCWAP/RADIUS120100624_19.dbf
24 22 /opt/oracle/oradata/CMCCWAP/RADIUS120100624_20.dbf
24 22 /opt/oracle/oradata/CMCCWAP/RADIUS120100624_21.dbf
24 22 /opt/oracle/oradata/CMCCWAP/RADIUS120100624_22.dbf
24 22 /opt/oracle/oradata/CMCCWAP/RADIUS120100624_23.dbf
64 22 /opt/oracle/oradata/CMCCWAP/RADIUS120100625_00.dbf
64 22 /opt/oracle/oradata/CMCCWAP/RADIUS120100625_01.dbf
64 22 /opt/oracle/oradata/CMCCWAP/RADIUS120100625_02.dbf
64 22 /opt/oracle/oradata/CMCCWAP/RADIUS120100625_03.dbf
64 22 /opt/oracle/oradata/CMCCWAP/RADIUS120100625_04.dbf
64 22 /opt/oracle/oradata/CMCCWAP/RADIUS120100625_05.dbf
84 22 /opt/oracle/oradata/CMCCWAP/RADIUS120100625_06.dbf
84 22 /opt/oracle/oradata/CMCCWAP/RADIUS120100625_07.dbf
84 22 /opt/oracle/oradata/CMCCWAP/RADIUS120100625_08.dbf
84 22 /opt/oracle/oradata/CMCCWAP/RADIUS120100625_09.dbf
84 22 /opt/oracle/oradata/CMCCWAP/RADIUS120100625_10.dbf
24 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100624_04.dbf
24 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100624_05.dbf
44 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100624_06.dbf
PHYRDS PHYWRTS NAME
------ ------- --------------------------------------------------------------------------------
104 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100624_07.dbf
24 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100624_08.dbf
24 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100624_09.dbf
24 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100624_10.dbf
24 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100624_11.dbf
24 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100624_12.dbf
24 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100624_13.dbf
24 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100624_14.dbf
34 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100624_15.dbf
44 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100624_16.dbf
44 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100624_17.dbf
44 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100624_18.dbf
44 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100624_19.dbf
44 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100624_20.dbf
44 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100624_21.dbf
44 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100624_22.dbf
44 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100624_23.dbf
84 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100625_00.dbf
44 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100625_01.dbf
84 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100625_02.dbf
84 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100625_03.dbf
PHYRDS PHYWRTS NAME
------ ------- --------------------------------------------------------------------------------
84 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100625_04.dbf
84 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100625_05.dbf
84 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100625_06.dbf
84 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100625_07.dbf
84 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100625_08.dbf
84 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100625_09.dbf
84 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100625_10.dbf
104 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100625_11.dbf
104 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100625_12.dbf
104 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100625_13.dbf
104 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100625_14.dbf
104 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100625_15.dbf
104 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100625_16.dbf
104 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100625_17.dbf
104 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100625_18.dbf
104 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100625_19.dbf
104 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100625_20.dbf
104 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100625_21.dbf
104 22 /opt/oracle/oradata/CMCCWAP/SOCKET120100625_22.dbf
104 12 /opt/oracle/oradata/CMCCWAP/SOCKET120100625_23.dbf
44 22 /opt/oracle/oradata/CMCCWAP/cmcc_user_data_tablespace_1.dbf
PHYRDS PHYWRTS NAME
------ ------- --------------------------------------------------------------------------------
44 22 /opt/oracle/oradata/CMCCWAP/cmcc_user_data_tablespace_2.dbf
44 22 /opt/oracle/oradata/CMCCWAP/cmcc_user_data_tablespace_3.dbf
44 22 /opt/oracle/oradata/CMCCWAP/cmcc_user_data_tablespace_4.dbf
44 22 /opt/oracle/oradata/CMCCWAP/cmcc_user_data_tablespace_5.dbf
24 22 /opt/oracle/oradata/CMCCWAP/cwmlite01.dbf
24 22 /opt/oracle/oradata/CMCCWAP/drsys01.dbf
173 22 /opt/oracle/oradata/CMCCWAP/example01.dbf
24 22 /opt/oracle/oradata/CMCCWAP/indx01.dbf
34 22 /opt/oracle/oradata/CMCCWAP/odm01.dbf
10566 451 /opt/oracle/oradata/CMCCWAP/system01.dbf
24 22 /opt/oracle/oradata/CMCCWAP/tools01.dbf
5152 115890 /opt/oracle/oradata/CMCCWAP/undotbs01.dbf
24 22 /opt/oracle/oradata/CMCCWAP/users01.dbf
24 22 /opt/oracle/oradata/CMCCWAP/wap_archiving_data_1.dbf
24 22 /opt/oracle/oradata/CMCCWAP/wap_archiving_data_2.dbf
24 22 /opt/oracle/oradata/CMCCWAP/wap_archiving_data_3.dbf
24 22 /opt/oracle/oradata/CMCCWAP/wap_archiving_data_4.dbf
24 22 /opt/oracle/oradata/CMCCWAP/wap_archiving_data_5.dbf
32 22 /opt/oracle/oradata/CMCCWAP/wap_user_data_tablespace_1.dbf
24 22 /opt/oracle/oradata/CMCCWAP/wap_user_data_tablespace_2.dbf
24 22 /opt/oracle/oradata/CMCCWAP/wap_user_data_tablespace_3.dbf
PHYRDS PHYWRTS NAME
------ ------- --------------------------------------------------------------------------------
35 35 /opt/oracle/oradata/CMCCWAP/wap_user_data_tablespace_4.dbf
24 22 /opt/oracle/oradata/CMCCWAP/wap_user_data_tablespace_5.dbf
28 22 /opt/oracle/oradata/CMCCWAP/xdb01.dbf
相关推荐
Oracle性能分析是数据库管理员在日常工作中至关重要的一环,它涉及到CPU使用率、内存管理、I/O性能等多个关键领域。本文将深入探讨这些方面,并提供一些基础的分析工具和方法。 首先,数据库配置报告是进行性能分析...
### Oracle性能分析工具详解 #### 一、性能规划器(Capacity Planner)概述 性能规划器(Capacity Planner)是一款强大的工具,集成于Oracle企业治理包(Oracle Enterprise Management Packs)之中,主要用于收集和...
### Oracle数据库性能分析 #### 一、Oracle体系结构 Oracle数据库是一个复杂且功能强大的关系型数据库管理系统(RDBMS),其核心组件包括实例和数据库文件。理解Oracle的体系结构对于优化性能至关重要。 - **实例**...
Oracle性能分析工具是数据库管理员用来监控和优化Oracle数据库性能的关键工具。本文主要介绍了Oracle性能规划器的使用方法,这是Oracle企业治理包的一部分,用于收集、存储和分析系统性能参数。 性能规划器的主要...
Oracle 性能分析——使用 set_autotrace_on 和 set_timing_on 来分析 select 语句的性能 Oracle 数据库性能分析是数据库优化的重要步骤之一,通过对 SQL 语句的执行计划和运行时间的分析,可以了解数据库的性能瓶颈...
OWI通过等待事件来收集数据库的运行情况,等待事件是Oracle性能分析的基础,如SQL等待、I/O等待、锁等待等。 2. **性能调整基础**:性能优化的目标是最大化数据库的吞吐量,减少响应时间,并确保系统的稳定性和可...
详细版,内容主要为spotlight on oracle的介绍与日常使用分析。
本手册是以一个优化数据案例,一步一步生成AWR性能分析报告,对数据库性能优化有大帮助哦。
Oracle的Active Session History(ASH)报告是性能分析的重要工具,尤其在Oracle 11g数据库环境中。ASH报告提供了对数据库实例中活动会话的详细历史记录,帮助DBA(数据库管理员)快速定位性能问题,优化数据库操作...
本主题聚焦于"Oracle.10g性能分析与优化思路",旨在深入探讨如何通过一系列技术手段和策略让Oracle数据库运行得更加流畅、快速。 一、SQL优化 1. SQL执行计划分析:了解SQL语句的执行过程,利用 Explain Plan 工具...
士大夫士大夫为让我而为让位让位热污染玩儿玩儿玩儿玩儿玩儿
1. **性能监控与分析**:书中详细介绍了Oracle的性能监控工具,如SQL Trace、TKPROF、AWR(自动工作负载存储库)和ASH(活动会话历史)等,这些都是DBA日常工作中不可或缺的性能分析手段。通过这些工具,可以追踪SQL语句...
本文将详细介绍如何通过Oracle Enterprise Manager (OEM)来收集Oracle数据库的性能日志,并生成相关的性能分析报告。 #### 一、Oracle性能日志概述 Oracle性能日志主要包括自动工作负载记录(Automatic Workload ...
Oracle 性能分析 优化思路
Oracle 性能调优的基本方案 Oracle 性能调优是数据库管理员和开发者非常关心的问题。为了确保数据库的高效运行,需要对 Oracle 性能进行调优。本文将对 Oracle 性能调优的基本方案进行概述,并介绍了该方案的八个...