【性能调整】系统视图（一）

最新推荐文章于 2022-10-18 22:26:26 发布

cixlv48826

最新推荐文章于 2022-10-18 22:26:26 发布

阅读量180

点赞数

文章标签：数据库

V$SESSION, V$SESSTAT, V$SESSION_EVENT, V$SESSION_LONGOPS,

V$SESSION_WAIT, V$SESS_IO, V$SESSION_CONNECT_INFO

select --会话信息

a.SADDR, --session's address

a.SID, --session id

a.SERIAL#, --when sesseion reuse,the serial + 1

a.TYPE, --the type of session

a.PADDR, --process address

a.PROCESS, --process id of client

a.SQL_ADDRESS, --the address of the session's sql

a.SQL_HASH_VALUE, --the hash_value of the session's sql

a.SQL_ID, --the id of the session's sql

a.EVENT#, --the event number

a.EVENT, --the describe of the event number

a.WAIT_CLASS#, --wait class number

a.WAIT_CLASS, --wait class describe

a.WAIT_TIME, --wait time

a.USER#, --the use id

a.USERNAME, --use name

a.OSUSER --the operation system user name

from v$session a where rownum = 1 ;

可以发现这些session相关的表中最核心的就是session表，这些表都有sid可以互相关联，分别存放着session的相关属性或者统计信息。例如：

SELECT a.PADDR,

a.SQL_ADDRESS,

a.SQL_HASH_VALUE,

a.SQL_ID,

b.BLOCK_GETS,

b.CONSISTENT_GETS,

b.PHYSICAL_READS,

c.AUTHENTICATION_TYPE,

c.OSUSER,

d.EVENT_ID,

d.EVENT,

d.TOTAL_WAITS,

d.TIME_WAITED,

d.WAIT_CLASS#,

d.WAIT_CLASS

from v$session a,

v$sess_io b,

v$session_connect_info c,

v$session_event d

where a.SID = (select sid from v$mystat where rownum = 1)

and a.SID = b.SID

and a.SID = c.sid

and d.SID = a.SID

;

系统状态相关视图：

v$statname和v$mystat, v$sysstat

select a.STATISTIC#,a.NAME,a.CLASS,a.STAT_ID from V$STATNAME a ;

其中的class代表意义：

1 代表事例活动

2 代表Redo buffer活动

4 代表锁

8 代表数据缓冲活动

16 代表OS活动

32 代表并行活动

64 代表表访问

128 代表调试信息

下面的语句可以查询当前session的状态

select * from

(select b.name,a.SID,a.VALUE from v$mystat a ,v$sysstat b

where a.STATISTIC# = b.STATISTIC# order by value desc )

where rownum < 30;

或者各session的各项值：

select * from

(select b.name,a.SID,a.VALUE from v$sesstat a ,v$sysstat b

where a.STATISTIC# = b.STATISTIC# order by value desc ) ;

列出常见的name值和解释：

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的列。

可以查看v$sysmetric中的awr信息，获得一些比率的计算方法：

SQL> select a.metric_name,a.METRIC_UNIT

2 from v$sysmetric a where a.METRIC_UNIT like '%!%%' escape '!';

METRIC_NAME METRIC_UNIT

---------------------------------------------------------------- ----------------------------------------------------------------

Buffer Cache Hit Ratio % (LogRead - PhyRead)/LogRead

Memory Sorts Ratio % MemSort/(MemSort + DiskSort)

Redo Allocation Hit Ratio % (#Redo - RedoSpaceReq)/#Redo

User Commits Percentage % (UserCommit/TotalUserTxn)

User Rollbacks Percentage % (UserRollback/TotalUserTxn)

Cursor Cache Hit Ratio % CursorCacheHit/SoftParse

Execute Without Parse Ratio % (ExecWOParse/TotalExec)

Soft Parse Ratio % SoftParses/TotalParses

User Calls Ratio % UserCalls/AllCalls

Host CPU Utilization (%) % Busy/(Idle+Busy)

User Limit % % Sessions/License_Limit

Database Wait Time Ratio % Wait/DB_Time

Database CPU Time Ratio % Cpu/DB_Time

Row Cache Hit Ratio % Hits/Gets

Row Cache Miss Ratio % Misses/Gets

Library Cache Hit Ratio % Hits/Pins

Library Cache Miss Ratio % Misses/Gets

Shared Pool Free % % Free/Total

PGA Cache Hit % % Bytes/TotalBytes

Process Limit % % Processes/Limit

Session Limit % % Sessions/Limit

Streams Pool Usage Percentage % Memory allocated / Size of Streams pool

Buffer Cache Hit Ratio % (LogRead - PhyRead)/LogRead

Memory Sorts Ratio % MemSort/(MemSort + DiskSort)

Execute Without Parse Ratio % (ExecWOParse/TotalExec)

Soft Parse Ratio % SoftParses/TotalParses

Host CPU Utilization (%) % Busy/(Idle+Busy)

Database CPU Time Ratio % Cpu/DB_Time

Library Cache Hit Ratio % Hits/Pins

Shared Pool Free % % Free/Total

30 rows selected.

SQL>

有了这些公式，就可以按照自己的定义来查看一些比率来帮助诊断性能问题（下列计算方法均为推测或参考网络上的一些方法）

Buffer Cache Hit Ratio % (LogRead - PhyRead)/LogRead

Buffer cache命中率=（所有的逻辑读-所有物理读）/所有的逻辑读

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';

Memory Sorts Ratio % MemSort/(MemSort + DiskSort)

内存中完成的排序所占比例。最理想状态下，在OLTP系统中，大部分排序不仅小并且能够完全在内存里完成排序。

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)';

Redo Allocation Hit Ratio % (#Redo - RedoSpaceReq)/#Redo

select (b.VALUE - a.VALUE) / b.VALUE

from v$sysstat a, v$sysstat b

where a.name = 'redo log space requests'

and b.NAME = 'redo size';

User Commits Percentage % (UserCommit/TotalUserTxn)

User Rollbacks Percentage % (UserRollback/TotalUserTxn)

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

select e.VALUE/(b.VALUE + a.VALUE + c.value + d.value)

from v$sysstat a, v$sysstat b,v$sysstat c,v$sysstat d,v$sysstat e

where a.name = 'user commits'

and b.NAME = 'user rollbacks'

and c.name = 'user calls'

and d.name = 'user I/O wait time'

and e.name = 'session logical reads'

;

或者当前session的事务逻辑读数：

select e.VALUE/(b.VALUE + a.VALUE + c.value + d.value)

from v$mystat a, v$mystat b,v$mystat c,v$mystat d,v$mystat e,

v$statname aa,v$statname bb,v$statname cc,v$statname dd,v$statname ee

where aa.name = 'user commits'

and bb.NAME = 'user rollbacks'

and cc.name = 'user calls'

and dd.name = 'user I/O wait time'

and ee.name = 'session logical reads'

and a.STATISTIC# = aa.STATISTIC#

and b.STATISTIC# = bb.STATISTIC#

and c.STATISTIC# = cc.STATISTIC#

and d.STATISTIC# = dd.STATISTIC#

and e.STATISTIC# = ee.STATISTIC#

;

而User commits和rollbacks的百分比可以由下面的sql显示

select a.VALUE/(b.VALUE + a.VALUE + c.value + d.value)

from v$sysstat a, v$sysstat b,v$sysstat c,v$sysstat d

where a.name = 'user commits'

and b.NAME = 'user rollbacks'

and c.name = 'user calls'

and d.name = 'user I/O wait time';

select b.VALUE/(b.VALUE + a.VALUE + c.value + d.value)

from v$sysstat a, v$sysstat b,v$sysstat c,v$sysstat d

where a.name = 'user commits'

and b.NAME = 'user rollbacks'

and c.name = 'user calls'

and d.name = 'user I/O wait time';

Cursor Cache Hit Ratio % CursorCacheHit/SoftParse

当前session下的cursor cache hit ratio计算

select cc.value / (aa.value - bb.value)

from (select b.STATISTIC#, a.SID, b.NAME, a.VALUE

from v$mystat a, v$statname b

where a.STATISTIC# = b.STATISTIC#

and b.NAME = 'parse count (total)') aa,

(select b.STATISTIC#, a.SID, b.NAME, a.VALUE

from v$mystat a, v$statname b

where a.STATISTIC# = b.STATISTIC#

and b.NAME = 'parse count (hard)') bb,

(select b.STATISTIC#, a.SID, b.NAME, a.VALUE

from v$mystat a, v$statname b

where a.STATISTIC# = b.STATISTIC#

and b.NAME like 'session cursor cache hits') cc;

当这个比率非常低的时候，可能意味着当前的session_cached_cursors设置的过低，需要稍稍调大这个值。

当sql解析的时候，如果当前session已经有缓存了的cursor，则可以直接使用对应的cursor，这个过程也叫做softer soft parse，显然适当的softer soft parse，使得session下的游标能够复用是可以提高一定的性能的。

SQL> show parameter session_cached_cursors

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

session_cached_cursors integer 20

SQL>

Execute Without Parse Ratio % (ExecWOParse/TotalExec)

未解析的执行的比率

select 1-b.VALUE / a.VALUE

from v$sysstat a, v$sysstat b

where a.name = 'execute count'

and b.name = 'parse count (total)';

Soft Parse Ratio % SoftParses/TotalParses

这项将显示系统是否有太多硬解析。该值将会与原始统计数据对比以确保精确。例如，软解析率仅为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)';

User Calls Ratio % UserCalls/AllCalls

select a.value / (a.value + b.value)

from v$sysstat a, v$sysstat b

where a.name = 'user calls'

and b.name = 'recursive calls';

Host CPU Utilization (%) % Busy/(Idle+Busy)

select b.VALUE / (a.value + b.value)

from V$OSSTAT a, V$OSSTAT b

where a.STAT_NAME = 'IDLE_TIME'

and b.STAT_NAME = 'BUSY_TIME';

Row Cache Hit Ratio % Hits/Gets

SELECT (SUM(GETS - GETMISSES - FIXED)) / SUM(GETS) "ROW CACHE HIT RATIO"

FROM V$ROWCACHE;

User Limit % % Sessions/License_Limit

Database Wait Time Ratio % Wait/DB_Time

DB time即为

Select * from from v$sysstat a where a.NAME = 'DB time';

（注：Wait待解析 oracle wait interface后再来看。）

Database CPU Time Ratio % Cpu/DB_Time

Library Cache Hit Ratio % Hits/Pins

Pins - The number of times a pin was requested for objects of this namespace.

Reloads - Any pin of an object that is not the first pin performed since the object handle was created, and which requires loading the object from disk.

A pin hits发生在当要执行SQL或者PL/SQL的时候，他已经在library cache中，并且可以被执行。

Library cache的命中率，往往意味着share pool的大小是否合适，或者sql解析是否存在问题（结合soft parse ratio来看，如果2者都较低，很可能就是sql解析有问题）。

select sum(PINS) Pins,

sum(RELOADS) Reloads,

round((sum(PINS) - sum(RELOADS)) / sum(PINS) * 100,2) Hit_Ratio

from v$librarycache;

Shared Pool Free % % Free/Total

select f.POOL,

name,

sgasize / 1024 / 1024　　allocated,

bytes / 1024　　　　　　 free,

round(bytes / sgasize * 100, 2) 　　　free_rate 　　　　

from (select sum(bytes) sgasize from sys.v_$sgastat) s,

sys.v_$sgastat f 　　　　

where f.name = 'free memory'

and f.name = 'shared pool'

;

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';

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

转载于:http://blog.itpub.net/16179598/viewspace-674355/

cixlv48826

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
【性能调整】系统视图（一）

V$SESSION, V$SESSTAT, V$SESSION_EVENT, V$SESSION_LONGOPS, V$SESSION_WAIT, V$SESS_IO, V$SESSION_CONNECT_INFOsel...
复制链接

扫一扫