在数据库层面分析系统性能(原创)

标签: 数据库 分析 系统 | 发表时间:2013-02-06 16:28 | 作者:
出处:http://www.iteye.com

系统级别信息

v$sysstat

按照OracleDocument中的描述,v$sysstat存储自数据库实例运行那刻起就开始累计全实例(instance-wide)的资源使用情况。
该视图存储下列的统计信息:

1>.事件发生次数的统计(如:user commits)
2>.数据产生,存取或者操作的total列(如:redo size)
3>.如果TIMED_STATISTICS值为true,则统计花费在执行操作上的总时间(如:CPU used by this session)
v$sysstat视图常用列介绍:

STATISTIC#: 标识
NAME: 统计项名称
VALUE: 资源使用量
该视图还有一列class-统计类别但极少会被使用,各类信息如下:
1 代表事例活动
2 代表Redo buffer活动
4 代表锁
8 代表数据缓冲活动
16 代表OS活动
32 代表并行活动
64 代表表访问
128 代表调试信息
注意:Statistic#的值在不同版本中各不相同,使用时要用Name做为查询条件而不要以statistic#的值做为条件。
V$SYSSTAT中包含多个统计项,这部分介绍了一些关键的v$sysstat统计项,在调优方面相当有用。下列按字母先后排序:
数据库使用状态的一些关键指标:
CPU used by this session:所有session的cpu占用量,不包括后台进程。这项统计的单位是百分之x秒.完全调用一次不超过10ms
db block changes:那部分造成SGA中数据块变化的insert,update或delete操作数 这项统计可以大概看出整体数据库状态。在各项事务级别,这项统计指出脏缓存比率。
execute count:执行的sql语句数量(包括递归sql)
logons current:当前连接到实例的Sessions。如果当前有两个快照则取平均值。
logons cumulative:自实例启动后的总登陆次数。
parse count (hard):在shared pool中解析调用的未命中次数。当sql语句执行并且该语句不在shared pool或虽然在shared pool但因为两者存在部分差异而不能被使用时产生硬解析。如果一条sql语句原文与当前存在的相同,但查询表不同则认为它们是两条不同语句,则硬解析即会发生。硬解析会带来cpu和资源使用的高昂开销,因为它需要oracle在shared pool中重新分配内存,然后再确定执行计划,最终语句才会被执行。
parse count (total):解析调用总数,包括软解析和硬解析。当session执行了一条sql语句,该语句已经存在于shared pool并且可以被使用则产生软解析。当语句被使用(即共享) 所有数据相关的现有sql语句(如最优化的执行计划)必须同样适用于当前的声明。这两项统计可被用于计算软解析命中率。
parse time cpu:总cpu解析时间(单位:10ms)。包括硬解析和软解析。
parse time elapsed:完成解析调用的总时间花费。
physical reads:OS blocks read数。包括插入到SGA缓存区的物理读以及PGA中的直读这项统计并非i/o请求数。
physical writes:从SGA缓存区被DBWR写到磁盘的数据块以及PGA进程直写的数据块数量。
redo log space requests:在redo logs中服务进程的等待空间,表示需要更长时间的log switch。
redo size:redo发生的总次数(以及因此写入log buffer),以byte为单位。这项统计显示出update活跃性。
session logical reads:逻辑读请求数。
sorts (memory) and sorts (disk):sorts(memory)是适于在SORT_AREA_SIZE(因此不需要在磁盘进行排序)的排序操作的数量。sorts(disk)则是由于排序所需空间太大,SORT_AREA_SIZE不能满足而不得不在磁盘进行排序操作的数量。这两项统计通常用于计算in-memory sort ratio。
sorts (rows): 列排序总数。这项统计可被'sorts (total)'统计项除尽以确定每次排序的列。该项可指出数据卷和应用特征。
table fetch by rowid:使用ROWID返回的总列数(由于索引访问或sql语句中使用了'where rowid=&rowid'而产生)
table scans (rows gotten):全表扫描中读取的总列数
table scans (blocks gotten):全表扫描中读取的总块数,不包括那些split的列。
user commits + user rollbacks:系统事务起用次数。当需要计算其它统计中每项事务比率时该项可以被做为除数。例如,计算事务中逻辑读,可以使用下列公式:session logical reads / (user commits + user rollbacks)。
注意:物理I/O,oracle报告物理读也许并未导致实际物理磁盘I/O操作。这完全有可能因为多数操作系统都有缓存文件,可能是那些块在被读取。块也可能存于磁盘或控制级缓存以再次避免实际I/O。Oracle报告有物理读也许仅仅表示被请求的块并不在缓存中。

v$sysstat中提供的系统信息是实例自启动以来的所有信息,并且没有想关的时间记录,在需要解决性能问题时,笔者一般将其中的信息作为baseline,如下列查询

SQL>select s.name,s.value/((sysdate-i.startup_time)*24*60*60) from v$instance i,v$sysstat s
where s.name='physical writes'

NAME                           S.VALUE/((SYSDATE-I.STARTUP_TIME)*24*60*60)
------------------------------ -------------------------------------------
physical writes                                                 .014322113

通过下列视图查询出所需信息,以判断性能瓶颈。

V$SYSMETRIC –记录了上15s和上1min钟的性能指标记录

SQL>Select  VALUE , METRIC_UNIT,INTSIZE_CSEC
from v$sysmetric
where metric_name='Physical Reads Per Sec';
VALUE METRIC_UNIT           INTSIZE_CSEC
---------- ----------------- ------------
654.6736 Reads Per Second          5959
134.9835 Reads Per Second          1515

V$SYSMETRIC_SUMMARY – 对上一小时的性能指标记录进行了统计,如 avg, max, min etc

SQL>select MAXVAL,MINVAL,AVERAGE,STANDARD_DEVIATION
from V$SYSMETRIC_SUMMARY
where metric_name='Physical Reads Per Sec';
MAXVAL     MINVAL    AVERAGE      STANDARD_DEVIATION
---------- ---------- ---------- ------------------
3.71784232          0 .076930034         .478529283
V$SYSMETRIC_HISTORY – 记录了上一小时里每1分钟的指标信息,上3分钟里每15s的指标信息

Select  metric_name,VALUE , METRIC_UNIT,INTSIZE_CSEC
from v$sysmetric_history
where metric_name='Physical Reads Per Sec'
and begin_time < sysdate - 5/(24*60)
and begin_time > sysdate - 15/(24*60);
DBA_HIST_SYSMETRIC_SUMMARY – hour summaries for last week.
系统级别的等待事件视图
V$SYSTEM_EVENT – 显示了系统的当前等待项,v$system_event则提供了自实例启动后各个等待事件的概括。常用于获取系统等待信息的历史影象。常用列如下

EVENT:等待事件名称
TOTAL_WAITS:此项事件总等待次数
TIME_WAITED:此项事件的总等待时间(单位:百分之一秒),由于该字段反应的是实例自启动以来等待事件的累加值,并不能真正反应性能瓶颈。
AVERAGE_WAIT:此项事件的平均等待用时(单位:百分之一秒)(time_waited/total_waits)
TOTAL_TIMEOUTS:此项事件的总等待超时次数
V$EVENTMETRIC – 上一分钟内等待事件的详细信息
DBA_HIST_SYSTEM_EVENT – 以一个snapshot为单位,包含了v$system_event的汇总信息
系统级别的等待事件类别视图
V$SYSTEM_WAIT_CLASS – cumulative since start up
V$WAITCLASSMETRIC – last 60 seconds deltas
V$WAITCLASSMETRIC_HISTORY – 60 seconds deltas for last hour
Remember DBA_HIST_WAITCLASSMETRIC_HISTORY is used for alerts and or baselines not everyday stats.
我们一般可以在出现IO延迟时使用上述视图。
The data in V$ACTIVE_SESSION_HISTORY (ASH) is probably better for seeing load because load is made up of both wait time and cpu time. Cpu information is not in the event/waitclass views but is in ASH along with the waits.
The second part, the  latencies, specifically I/O latencies,  are only available in the wait event and waitclass views (and the filestat views on a per file basis)
查看系统的平均延迟情况
SQL> select
      n.wait_class,10*m.time_waited/nullif(m.wait_count,0) avgms
from   v$waitclassmetric  m,
       v$system_wait_class n
where m.wait_class_id=n.wait_class_id order by avgms desc;

WAIT_CLASS                AVGMS
-------------------- ----------
Other
Application
Configuration
Commit
User I/O
Network
Idle                 3547.55358
Concurrency              22.827
System I/O                 .364
Another issue is that the documentation for 11gR2 says that the TIME_WAITED is microseconds but in my tests it’s actually centisecs
desc V$SYSTEM_WAIT_CLASS
Name                                        Type
-----------------------------------------  ----------------------------
WAIT_CLASS_ID                                NUMBER
WAIT_CLASS#                                  NUMBER
WAIT_CLASS                                   VARCHAR2(64)
TOTAL_WAITS                                  NUMBER
TIME_WAITED                                  NUMBER  - centi-seconds

系统等待事件类别描述如下

Administrative
Waits resulting from DBA commands that cause users to wait (for example, an index
rebuild)
Application
Waits resulting from user application code (for example, lock waits caused by row
level locking or explicit lock commands)
Cluster
Waits related to Real Application Cluster resources (for example, global cache
resources such as 'gc cr block busy'
Commit
This wait class only comprises one wait event - wait for redo log write confirmation
after a commit (that is, 'log file sync')
Concurrency
Waits for internal database resources (for example, latches)
Configuration
Waits caused by inadequate configuration of database or instance resources (for
example, undersized log file sizes, shared pool size)
Idle
Waits that signify the session is inactive, waiting for work (for example, 'SQL*Net
message from client')
Network
Waits related to network messaging (for example, 'SQL*Net more data to dblink')
Other
Waits which should not typically occur on a system (for example, 'wait for EMON to
spawn')
Scheduler
Resource Manager related waits (for example, 'resmgr: become active')
System I/O
Waits for background process IO (for example, DBWR wait for 'db file parallel write')
User I/O
Waits for user IO (for example 'db file sequential read')

确认IO延迟情况
For specific I/O latencies there are two choices – v$eventmetric and v$system_event. With v$system_event it requires  running multiple queries and taking the deltas but the deltas are are already calculated in v$eventmetric. Here is an example of getting I/O latencies from v$eventmetric
过去一分钟的延迟情况
col name for a25
select m.intsize_csec,
       n.name ,
       round(m.time_waited,3) time_waited,
       m.wait_count,
       round(10*m.time_waited/nullif(m.wait_count,0),3) avgms
from v$eventmetric m,
     v$event_name n
where m.event_id=n.event_id
  and n.name in (
                  'db file sequential read',
                  'db file scattered read',
                  'direct path read',
                  'direct path read temp',
                  'direct path write',
                  'direct path write temp',
                  'log file sync',
                  'log file parallel write'
);
INTSIZE_CSEC NAME                      TIME_WAITED WAIT_COUNT      AVGMS
------------ ------------------------- ----------- ---------- ----------
        6017 log file parallel write         2.538          4      6.345
        6017 log file sync                   2.329          1     23.287
        6017 db file sequential read             0          0
        6017 db file scattered read              0          0
        6017 direct path read                    0          0
        6017 direct path read temp               0          0
        6017 direct path write                   0          0
        6017 direct path write temp              0          0
以小时为单位查看延迟情况
select
       e.event_name,btime,
       round((time_ms_end-time_ms_beg)/nullif(count_end-count_beg,0),3) avg_ms
from (
select
       to_char(s.BEGIN_INTERVAL_TIME,'DD-MON-YY HH24:MI')  btime,
       total_waits count_end,
       time_waited_micro/1000 time_ms_end,
       Lag (e.time_waited_micro/1000)
              OVER( PARTITION BY e.event_name ORDER BY s.snap_id) time_ms_beg,
       Lag (e.total_waits)
              OVER( PARTITION BY e.event_name ORDER BY s.snap_id) count_beg
from
       DBA_HIST_SYSTEM_EVENT e,
       DBA_HIST_SNAPSHOT s
where
         s.snap_id=e.snap_id
   and e.event_name like '%&1%'
order by begin_interval_time
)
order by btime;
BTIME               AVG_MS
--------------- ----------
20-JUL-11 06:00      5.854
20-JUL-11 07:00      4.116
20-JUL-11 08:00     21.158
20-JUL-11 09:02      5.591
20-JUL-11 10:00      4.116
20-JUL-11 11:00      6.248
20-JUL-11 12:00     23.634
20-JUL-11 13:00     22.529
20-JUL-11 14:00      21.62
20-JUL-11 15:00     18.038
20-JUL-11 16:00     23.127
but the big drawback with wait event and/or wait class views is that they lack information on CPU load. CPU load can be found in the system statistics but it’s just easier to do it all in one query using v$active_session_history. Here is a query using ASH to calculate AAS load on the database over the last 60 seconds:

查看过去一分钟系统的平均等待时间,包括CPU等待
select
            round(count(*)/secs.var,3)     AVGS,
            decode(session_state,'ON CPU','CPU',wait_class)  wait_class
       from v$active_session_history ash,
            (select 60 var from dual)  secs
       where
            SAMPLE_TIME > sysdate - (secs.var/(24*60*60)) and
            SESSION_TYPE = 'FOREGROUND'
       group by decode(session_state,'ON CPU','CPU',wait_class) , secs.var
/
      AVGS WAIT_CLASS
---------- ---------------
      .016 Concurrency
      .001 Network
         0 Other
      .083 Configuration
      .001 Administrative
      .034 CPU
         0 System I/O
      .001 Commit
      .054 Application
         0 User I/O

参考至:http://dboptimizer.com/2011/07/07/mining-awr-statistics-metrics-verses-statistics/

               http://dboptimizer.com/2011/07/20/wait-event-and-wait-class-metrics-vs-vsystem_event/

               http://junsansi.itpub.net/post/29894/291051
               http://www.laoxiong.net/dbms_stats_and_analyze_and_global_statistics.html

               http://blog.163.com/wghbeyond@126/blog/static/3516618120106190204350/

本文原创,转载请注明出处、作者
如有错误,欢迎指正
邮箱:[email protected]



已有 0 人发表留言,猛击->> 这里<<-参与讨论


ITeye推荐



相关 [数据库 分析 系统] 推荐:

在数据库层面分析系统性能(原创)

- - ITeye博客
按照OracleDocument中的描述,v$sysstat存储自数据库实例运行那刻起就开始累计全实例(instance-wide)的资源使用情况. 1>.事件发生次数的统计(如:user commits). 2>.数据产生,存取或者操作的total列(如:redo size). 3>.如果TIMED_STATISTICS值为true,则统计花费在执行操作上的总时间(如:CPU used by this session).

8种Nosql数据库系统对比

- xcv58 - 伯乐在线 -博客
  导读:Kristóf Kovács 是一位软件架构师和咨询顾问,他最近发布了一片对比各种类型NoSQL数据库的文章. 文章由敏捷翻译 - 唐尤华编译.   虽然SQL数据库是非常有用的工具,但经历了15年的一支独秀之后垄断即将被打破. 这只是时间问题:被迫使用关系数据库,但最终发现不能适应需求的情况不胜枚举.

OceanBase 数据库的系统架构

- -
OceanBase 数据库采用 Shared-Nothing 架构,各个节点之间完全对等,每个节点都有自己的 SQL 引擎、存储引擎,运行在普通 PC 服务器组成的集群之上,具备可扩展、高可用、高性能、低成本、云原生等核心特性. OceanBase 数据库的整体架构如下图所示. OceanBase 数据库支持数据跨地域(Region)部署,每个地域可能位于不同的城市,距离通常比较远,所以 OceanBase 数据库可以支持多城市部署,也支持多城市级别的容灾.

Oracle数据库分析函数详解

- - MySQLOPS 数据库与运维自动化技术分享
Oracle数据库分析函数详解. 原创文章,转载请注明: 文章地址 Oracle数据库分析函数详解.

VoltDB内存数据库分析

- - 淘宝核心系统团队博客
VoltDB是一个宣称性能超过Mysql 100倍的新型数据库. 它源自Micheal Stonebraker一篇论文H-Store. 在这篇论文发表后,Stonebraker成立了VoltDB公司带着他的一些学生开始在OLTP数据库领域打拼. Stonebraker从上世纪70年代——数据库刚开始发展的时间——就开始在数据库领域活跃,这样的老古董提出的数据库的新想法,给了整个存储领域很大的想象空间.

Postgres 数据库分析工具

- - CSDN博客数据库推荐文章
Postgres号称是开源免费DBMS最强大的,并且支持二次开发. 本文就该DBMS的分析工具进行讨论. pg_class表记载表和几乎所有有 字段或者是那些类似表的东西. pg_index),序列,视图,复合类型和一些特殊关系类型. 也有pg_index这张表,包含关于索引的一部分信息.    relname(表,索引,视图等的名字); .

Oracle、Db2、SqlServer、MySQL 数据库插入当前系统时间

- - CSDN博客推荐文章
例如有表table,table 中有两个字段:name 、makedate. 插入系统时间应为sysdate:. insert into table (name,makedate) values('测试',sysdate);. 插入系统时间应为current timestamp并且makedate数据类型为timestamp.

ActiveMQ系统之——消息持久化到MySQL数据库中(二)

- - CSDN博客推荐文章
关于这一节的程序,需要使用到《 ActiveMQ系列之——安装、运行及事例代码(一)》中的示例代码. 本文主要介绍关于消息持久化的配置. ActiveMQ默认情况下是基于文件的存储,使用的是kahaDB,当然还有其它的持久化方式,例如LevelDB,这个是在5.8的版本中引入的,本文主要介绍数据库持久化,使用的数据库是MySQL,其它数据库类似.

数据库系统load飙高问题解决思路

- - BlogJava-qileilove
数据库服务器器load 飙高的报警,比如:.   如何处理load 异常飙高的报警呢. 本文尝试从原理,原因,解决方法来阐述这类问题的解决思路.   CPU作为服务器的关键资源经常成为性能瓶颈的根源,CPU使用率高并不总是意味着CPU工作繁忙,它有可能是正在等待其他子系统. 在进行性能分析时,将所有子系统当做一个整体来看是非常重要的,因为在子系统中可能会出现瀑布效应.

某物流系统数据库故障诊断

- - SQL - 编程语言 - ITeye博客
某物流公司新上一套软件系统,数据库CPU占用率基本维持在80%以上,业务高峰期数据库宕机. 重做日志缓冲区的已使用的空间达到三分之一时. 当dbwn进程向磁盘写入已修改的缓冲区的时候. 用户提交事务处理时的一条提交记录(经常commit会及时刷新重做日志缓冲区空间). 重做日志缓冲区的已使用的空间达到三分之一时.