学习动态视图(一) v$sysstat + v$sesstat

动态视图学习计划列表

视图定义

v$sysstat  shows  cumulative instance wide statistics  since the instance was
started.
v$sesstat  shows  cumulative session wide statistics  since the beginning of each session. It must be joined with v$statname to see the names.

SQL> desc v$sysstat
 名称                          
 --------------------------
 STATISTIC#                    
 NAME   #v$sesstat没有name,要和v$statname联合查旬                           
 CLASS                         
 VALUE                         

SQL> desc v$sesstat
 名称                          
 --------------------------
 SID                           
 STATISTIC#                    
 VALUE                         

SQL> desc v$statname
 名称                          
 --------------------------
 STATISTIC#                    
 NAME                          
 CLASS    
 
其中class列含义如下：
1 代表事例活动
2 代表Redo buffer活动
4 代表锁
8 代表数据缓冲活动
16 代表OS活动
32 代表并行活动
64 代表表访问
128 代表调试信息
                    
 
select name,class,value from v$sysstat;

NAME                                                    CLASS      VALUE
-------------------------------------------------- ---------- ----------
global cache defers                                        40         22
global cache convert timeouts                              40          0
global cache blocks lost                                   40          0
global cache claim blocks lost                             40          0
global cache blocks corrupt                                40          0
global cache prepare failures                              40          0
global cache skip prepare failures                         40      17722
total number of slots                                       8          0
instance recovery database freeze count                    32          0
background checkpoints started                              8        107
background checkpoints completed                            8        107

The following query will return session id of the current session (it doesn't work in all envronments especially in two-task and SQL*Net configurations because terminal is null).

select s.sid,s.schemaname,s.machine from v$process p, v$session s
where p.addr = s.paddr and  s.terminal = userenv('terminal');

       SID SCHEMANAME                                                   MACHINE
---------- ------------------------------------------------------------ ----------------------------
        24 SYSTEM                                                       PEOA\AO

select n.name,n.class,t.value from v$sesstat t,v$statname n
where t.statistic#=n.statistic# and t.sid = &sid;

NAME                                                    CLASS      VALUE
-------------------------------------------------- ---------- ----------
execute count                                              64        125
bytes sent via SQL*Net to client                            1      31505
bytes received via SQL*Net from client                      1       3459
SQL*Net roundtrips to/from client                           1        102
bytes sent via SQL*Net to dblink                            1          0
bytes received via SQL*Net from dblink                      1          0
SQL*Net roundtrips to/from dblink                           1          0
sorts (memory)                                             64         61
sorts (disk)                                               64          0
sorts (rows)                                               64       1217
OTC commit optimization attempts                          128          0

Very often it is useful to take a snapshot from v$sysstat/v$sesstat before and
after a test run. The difference of these two snapshots shows the activity
between the snapshots (don't shutdown the instance if using v$sysstat or
disconnect the session from Oracle if using v$sesstat between the snapshots!)
This how the unsupported bstat.sql/estat.sql scripts work (using v$sysstat).

如果初始参数STATISTICS_LEVEL被设置为TYPICAL或ALL，时间统计被数据库自动收集;如果STATISTICS_LEVEL被设置为BASIC，你必须设置TIMED_STATISTICS值为TRUE以打开收集功能。
如果你已设置了DB_CACHE_ADVICE,TIMED_STATISTICS或TIMED_OS_STATISTICS，或在初始参数文件或使用ALTER_SYSTEM或ALTER SESSION，那么你所设定的值的值将覆盖STATISTICS_LEVEL的值。

 

Description of relevant statistics
----------------------------------

SORTS (MEMORY)          Number of sorts in completed in sort areas in memory.

SORTS (DISK)                  Number of sorts that needed temporary segments.

The sum of these two statistics is the number of all sorts performed. If too many sorts are performed then modify applications/SQL statements to reduce sorting. Increasing SORT_AREA_SIZE will help more sorts to fit in memory, but also more memory is needed.

SORTS (ROWS)            Total number of rows sorted in all sorts performed.这项统计可被'sorts (total)'统计项除尽以确定每次排序的列。该项可指出数据卷和应用特征。

Rows for each sort：
公式：sorts (rows) / ( sorts (memory) + sorts (disk) )
执行：
select a.value/(b.value+c.value)
from v$sysstat a,v$sysstat b,v$sysstat c
where a.name='sorts (rows)' and
b.name='sorts (memory)' and c.name='sorts (disk)';

In-memory sort ratio：
该项显示内存中完成的排序所占比例。最理想状态下，在OLTP系统中，大部分排序不仅小并且能够完全在内存里完成排序。
公式：sorts (memory) / ( sorts (memory) + sorts (disk) )
执行：
select a.value/(b.value+c.value)
from v$sysstat a,v$sysstat b,v$sysstat c
where a.name='sorts (memory)' and
b.name='sorts (memory)' and c.name='sorts (disk)';

TABLE SCANS (SHORT TABLES)
                                          Number of full table scans against tables with 4 or less db blocks. It is more efficient to access short tables using full table scans (at most 1 IO) than using indexes.

TABLE SCANS (LONG TABLES)
                                          Number of full table scans against tables with 5 or more db blocks. It would be more efficient to access long tables using indexes if less than 10-20% rows are returned. If it is necessary to access long tables using full table scans don't expect good buffer cache hit ratio. Increasing DB_BLOCK_BUFFERS doesn't help long table scans  because these block will stay at the bottom of LRU chain, but increasing DB_FILE_MULTIBLOCK_READ_COUNT can help to reduce disk IOs (not CPU usage).

The number of these two statistics gives the number of all full table scans.

TABLE SCAN BLOCKS GOTTEN
                                          Number of blocks accessed by all full table scans.
Since short table scans access at most 4 block it is possible to estimate the average number of blocks access by one long table scan.

TABLE SCAN ROWS GOTTEN
                                          Number of rows  accessed  by all full table scans. This is not the number of rows  returned  because only qualifying rows are returned.

TABLE FETCH BY ROWID
                                          Number of rows accessed by rowid. This counts all rows accessed using indexes (which point to rows via rowids) and all rows accessed using rowids directly (where rowid = :rowid). Use of non-selective indexes will increase this statistic a lot.

TABLE FETCH CONTINUED ROW
                                          Number of times when second row piece of a chained row is fetched. High number indicates that rows are chained. NOTE: rows with long columns are often chained because they don't fit in one db block.

CLUSTER KEY SCANS
                                          Number of cluster key scans.

CLUSTER KEY SCAN BLOCK GETS
                                          Number of blocks accessed by cluster key scans. The ratio of this statistic to 'cluster key scans' gives the average number of blocks accessed per cluster key scan. Ideally this ratio should be 1 ie. all data for each cluster key should fit on one db block.

CONSISTENT GETS
                                          Number of blocks accessed in buffer cache for normal queries (SELECTs without for update clause).

DB BLOCK GETS
                                          Number of blocks accessed in buffer cache for INSERT, UPDATE, DELETE and SELECT FOR UPDATE.

The sum of these two statistics is the number of  logical reads.

db block changes        那部分造成SGA中数据块变化的insert,update或delete操作数。这项统计可以大概看出整体数据库状态。在各项事务级别，这项统计指出脏缓存比率。

PHYSICAL READS
                                         Number of physical reads. This number must be compared to logical reads to calculate cache hit ratio.

physical writes              从SGA缓存区被DBWR写到磁盘的数据块以及PGA进程直写的数据块数量。

Buffer cache hit ratio：该项显示buffer cache大小是否合适。
公式：1-((physical reads-physical reads direct-physical reads direct (lob)) / session logical reads)
select 1-((a.value-b.value-c.value)/d.value)
from v$sysstat a,v$sysstat b,v$sysstat c,v$sysstat d
where a.name='physical reads' and
b.name='physical reads direct' and
c.name='physical reads direct (lob)' and
d.name='session logical reads';
或者
select 1-((a.value-b.value-c.value)/(d.value+e.value))
from v$sysstat a,v$sysstat b,v$sysstat c,v$sysstat d ,v$sysstat e
where a.name='physical reads' and
b.name='physical reads direct' and
c.name='physical reads direct (lob)' and
d.name='consistent gets' and
e.name='db block gets';
consistent gets+db block gets=~ session logical reads

BUFFER BUSY WAITS
                                         Number of times block access failed because another process held the block in incompatible mode. If this statistic is over 10% of logical reads then use V$WAITSTAT to check contention.

       
logons current              当前连接到实例的Sessions。如果当前有两个快照则取平均值。

logons cumulative        自实例启动后的总登陆次数。

execute count              执行的sql语句数量(包括递归sql)

CPU used by this session
                                        所有session的cpu占用量，不包括后台进程。这项统计的单位是百分之x秒.完全调用一次不超过10ms

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      完成解析调用的总时间花费。

*注：SQL语句的解析有软解析soft parse与硬解析hard parse之说，以下是5个步骤：
1：语法是否合法(sql写法)
2：语义是否合法(权限，对象是否存在)
3：检查该sql是否在公享池中存在
-- 如果存在,直接跳过4和5,运行sql. 此时算soft parse
4：选择执行计划
5：产生执行计划
-- 如果5个步骤全做,这就叫hard parse.

Soft parse ratio：

这项将显示系统是否有太多硬解析。该值将会与原始统计数据对比以确保精确。例如，软解析率仅为0.2则表示硬解析率太高。不过，如果总解析量(parse count total)偏低，这项值可以被忽略。
公式：1 - ( parse count (hard) / parse count (total) )
执行：
select 1-(a.value/b.value)
from v$sysstat a,v$sysstat b
Where a.name='parse count (hard)' and b.name='parse count (total)';

Parse to execute ratio：在生产环境，最理想状态是一条sql语句一次解析多数运行。
公式：1 - (parse count/execute count)
执行：
select 1-(a.value/b.value)
from v$sysstat a,v$sysstat b
where a.name='parse count (total)' and b.name='execute count';

Parse CPU to total CPU ratio：该项显示总的CPU花费在执行及解析上的比率。如果这项比率较低，说明系统执行了太多的解析。
公式：1 - (parse time cpu / CPU used by this session)
执行：
select 1-(a.value/b.value)
from v$sysstat a,v$sysstat b
where a.name='parse time cpu' and
b.name='CPU used by this session';

Parse time CPU to parse time elapsed：通常，该项显示锁竞争比率。这项比率计算
是否时间花费在解析分配给CPU进行周期运算(即生产工作)。解析时间花费不在CPU周期运算通常表示由于锁竞争导致了时间花费
公式：parse time cpu / parse time elapsed
执行：
select a.value/b.value
from v$sysstat a,v$sysstat b
where a.name='parse time cpu' and b.name='parse time elapsed';

redo log space requests
                                       在redo logs中服务进程的等待空间，表示需要更长时间的log switch。
            
redo size                      redo发生的总次数(以及因此写入log buffer)，以byte为单位。这项统计显示出update活跃性。

session logical reads  逻辑读请求数。

user commits + user rollbacks
                                          系统事务起用次数。当需要计算其它统计中每项事务比率时该项可以被做为除数。例如，计算事务中逻辑读，可以使用下列公式：session logical reads / (user commits + user rollbacks)。

 

从V$SYSSTAT获取负载间档(Load Profile)数据

　　负载间档是监控系统吞吐量和负载变化的重要部分，该部分提供如下每秒和每个事务的统计信息：logons cumulative, parse count (total), parse count (hard), executes, physical reads, physical writes, block changes, and redo size.

　　被格式化的数据可检查'rates'是否过高，或用于对比其它基线数据设置为识别system profile在期间如何变化。例如，计算每个事务中block changes可用如下公式：
db block changes / ( user commits + user rollbacks )
执行：
select a.value/(b.value+c.value)
from v$sysstat a,v$sysstat b,v$sysstat c
where a.name='db block changes' and
b.name='user commits' and c.name='user rollbacks';

其它计算统计以衡量负载方式，如下：
Blocks changed for each read：这项显示出block changes在block reads中的比例。它将指出是否系统主要用于只读访问或是主要进行诸多数据操作(如：inserts/updates/deletes)
公式：db block changes / session logical reads
执行：
select a.value/b.value
from v$sysstat a,v$sysstat b
where a.name='db block changes' and
b.name='session logical reads' ;

v$sesstat使用示例1：下列找出当前session中最高的logical和Physical I/O比率.

下列SQL语句显示了所有连接到数据库的session逻辑、物理读比率(每秒)。logical和physical I/O比率是通过自登陆后的时间消耗计算得出。对于sessions连接到数据库这种长周期操作而言也许不够精确，不过做个示例却足够了。

先获得session逻辑读和物理读统计项的STATISTIC#值：
SELECT name, statistic#
FROM V$STATNAME
WHERE name IN ('session logical reads','physical reads') ;
NAME STATISTIC#
------------------------------ ----------
session logical reads 9
physical reads 40

通过上面获得的STATISTIC#值执行下列语句：

SELECT ses.sid
, DECODE(ses.action,NULL,'online','batch') "User"
, MAX(DECODE(sta.statistic#,9,sta.value,0))/greatest(3600*24*(sysdate-ses.logon_time),1) "Log IO/s"
, MAX(DECODE(sta.statistic#,40,sta.value,0))/greatest(3600*24*(sysdate-ses.logon_time),1) "Phy IO/s"
, 60*24*(sysdate-ses.logon_time) "Minutes"
FROM V$SESSION ses , V$SESSTAT sta
WHERE ses.status = 'ACTIVE'
AND sta.sid = ses.sid
AND sta.statistic# IN (9,40)
GROUP BY ses.sid, ses.action, ses.logon_time
ORDER BY
SUM( DECODE(sta.statistic#,40,100*sta.value,sta.value) )/greatest(3600*24*(sysdate-ses.logon_time),1) DESC;

SID User Log IO/s Phy IO/s Minutes
----- ------ -------- -------- -------
1951 batch 291 257.3 1
470 online 6,161 62.9 0
730 batch 7,568 43.2 197
2153 online 1,482 98.9 10
2386 batch 7,620 35.6 35
1815 batch 7,503 35.5 26
1965 online 4,879 42.9 19
1668 online 4,318 44.5 1
1142 online 955 69.2 35
1855 batch 573 70.5 8
1971 online 1,138 56.6 1
1323 online 3,263 32.4 5
1479 batch 2,857 35.1 3
421 online 1,322 46.8 15
2405 online 258 50.4 8

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/55472/viewspace-321809/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/55472/viewspace-321809/