Oracle动态性能表.docx

Oracle动态性能表.docx

《Oracle动态性能表.docx》由会员分享，可在线阅读，更多相关《Oracle动态性能表.docx（33页珍藏版）》请在冰豆网上搜索。

Oracle动态性能表

Oracle动态性能表

（1）-V$SQLAREA

V$SQLAREA中的信息列

HASH_VALUE：

SQL语句的Hash值。

ADDRESS：

SQL语句在SGA中的地址。

这两列被用于鉴别SQL语句，有时，两条不同的语句可能hash值相同。

这时候，必须连同ADDRESS一同使用来确认SQL语句。

PARSING_USER_ID：

为语句解析第一条CURSOR的用户

VERSION_COUNT：

语句cursor的数量

KEPT_VERSIONS：

SHARABLE_MEMORY：

cursor使用的共享内存总数

PERSISTENT_MEMORY：

cursor使用的常驻内存总数

RUNTIME_MEMORY：

cursor使用的运行时内存总数。

SQL_TEXT：

SQL语句的文本（最大只能保存该语句的前1000个字符）。

MODULE,ACTION：

使用了DBMS_APPLICATION_INFO时session解析第一条cursor时的信息

V$SQLAREA中的其它常用列

SORTS:

语句的排序数

CPU_TIME:

语句被解析和执行的CPU时间

ELAPSED_TIME:

语句被解析和执行的共用时间

PARSE_CALLS:

语句的解析调用（软、硬）次数

EXECUTIONS:

语句的执行次数

INVALIDATIONS:

语句的cursor失效次数

LOADS:

语句载入（载出）数量

ROWS_PROCESSED:

语句返回的列总数

V$SQLAREA中的连接列

ColumnViewJoinedColumn（s）

HASH_VALUE,ADDRESSV$SESSIONSQL_HASH_VALUE,SQL_ADDRESS

HASH_VALUE,ADDRESSV$SQLTEXT,V$SQL,V$OPEN_CURSORHASH_VALUE,ADDRESS

SQL_TEXTV$DB_OBJECT_CACHENAME

示例：

1.查看消耗资源最多的SQL：

SELECThash_value,executions,buffer_gets,disk_reads,parse_calls

FROMV$SQLAREA

WHEREbuffer_gets>ORdisk_reads>1000000

ORDERBYbuffer_gets+100*disk_readsDESC;

2.查看某条SQL语句的资源消耗：

SELECThash_value,buffer_gets,disk_reads,executions,parse_calls

FROMV$SQLAREA

WHEREhash_Value=8ANDaddress=hextoraw（'CBD8E4B0'）;

Oracle动态性能表-

（2）-V$SQLTEXT

本视图包括Sharedpool中SQL语句的完整文本，一条SQL语句可能分成多个块被保存于多个记录内。

V$SQLTEXT中的常用列

HASH_VALUE：

SQL语句的Hash值

ADDRESS：

sql语句在SGA中的地址

SQL_TEXT：

SQL文本。

PIECE：

SQL语句块的序号

V$SQLTEXT中的连接列

ColumnViewJoinedColumn（s）

HASH_VALUE,ADDRESSV$SQL,V$SESSIONHASH_VALUE,ADDRESS

HASH_VALUE.ADDRESSV$SESSIONSQL_HASH_VALUE,SQL_ADDRESS

示例：

已知hash_value:

99，查询sql语句：

select*fromv$sqltext

wherehash_value='99'

orderbypiece

Oracle动态性能表-（3）-V$SYSSTAT

按照OracleDocument中的描述，v$sysstat存储自数据库实例运行那刻起就开始累计全实例（instance-wide）的资源使用情况。

类似于v$sesstat，该视图存储下列的统计信息：

1>.事件发生次数的统计（如：

usercommits）

2>.数据产生，存取或者操作的total列（如：

redosize）

3>.如果TIMED_STATISTICS值为true,则统计花费在执行操作上的总时间（如：

CPUusedbythissession）

v$sysstat视图常用列介绍：

lSTATISTIC#:

标识

lNAME:

统计项名称

lVALUE:

资源使用量

该视图还有一列class-统计类别但极少会被使用，各类信息如下：

1代表事例活动

2代表Redobuffer活动

4代表锁

8代表数据缓冲活动

16代表OS活动

32代表并行活动

64代表表访问

128代表调试信息

注意：

Statistic#的值在不同版本中各不相同，使用时要用Name做为查询条件而不要以statistic#的值做为条件。

使用v$sysstat中的数据

　　该视图中数据常被用于监控系统性能。

如buffercache命中率、软解析率等都可从该视图数据计算得出。

　　该视图中的数据也被用于监控系统资源使用情况，以及系统资源利用率的变化。

正因如此多的性能数据，检查某区间内系统资源使用情况可以这样做，在一个时间段开始时创建一个视图数据快照，结束时再创建一个，二者之间各统计项值的不同（endvalue-beginvalue）即是这一时间段内的资源消耗情况。

这是oracle工具的常用方法，诸如Statspack以及BSTAT/ESTAT都是如此。

　　为了对比某个区间段的数据，源数据可以被格式化（每次事务，每次执行，每秒钟或每次登陆），格式化后数据更容易从两者中鉴别出差异。

这类的对比在升级前，升级后或仅仅想看看一段时间内用户数量增长或数据增加如何影响资源使用方面更加实用。

　　你也可以使用v$sysstat数据通过查询v$system_event视图来检查资源消耗和资源回收。

V$SYSSTAT中的常用统计

　　V$SYSSTAT中包含多个统计项，这部分介绍了一些关键的v$sysstat统计项，在调优方面相当有用。

下列按字母先后排序：

数据库使用状态的一些关键指标：

lCPUusedbythissession：

所有session的cpu占用量，不包括后台进程。

这项统计的单位是百分之x秒.完全调用一次不超过10ms

ldbblockchanges：

那部分造成SGA中数据块变化的insert,update或delete操作数这项统计可以大概看出整体数据库状态。

在各项事务级别，这项统计指出脏缓存比率。

lexecutecount：

执行的sql语句数量（包括递归sql）

llogonscurrent：

当前连接到实例的Sessions。

如果当前有两个快照则取平均值。

llogonscumulative：

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

lparsecount（hard）：

在sharedpool中解析调用的未命中次数。

当sql语句执行并且该语句不在sharedpool或虽然在sharedpool但因为两者存在部分差异而不能被使用时产生硬解析。

如果一条sql语句原文与当前存在的相同，但查询表不同则认为它们是两条不同语句，则硬解析即会发生。

硬解析会带来cpu和资源使用的高昂开销，因为它需要oracle在sharedpool中重新分配内存，然后再确定执行计划，最终语句才会被执行。

lparsecount（total）：

解析调用总数，包括软解析和硬解析。

当session执行了一条sql语句，该语句已经存在于sharedpool并且可以被使用则产生软解析。

当语句被使用（即共享）所有数据相关的现有sql语句（如最优化的执行计划）必须同样适用于当前的声明。

这两项统计可被用于计算软解析命中率。

lparsetimecpu：

总cpu解析时间（单位：

10ms）。

包括硬解析和软解析。

lparsetimeelapsed：

完成解析调用的总时间花费。

lphysicalreads：

OSblocksread数。

包括插入到SGA缓存区的物理读以及PGA中的直读这项统计并非i/o请求数。

lphysicalwrites：

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

lredologspacerequests：

在redologs中服务进程的等待空间，表示需要更长时间的logswitch。

lredosize：

redo发生的总次数（以及因此写入logbuffer），以byte为单位。

这项统计显示出update活跃性。

lsessionlogicalreads：

逻辑读请求数。

lsorts（memory）andsorts（disk）：

sorts（memory）是适于在SORT_AREA_SIZE（因此不需要在磁盘进行排序）的排序操作的数量。

sorts（disk）则是由于排序所需空间太大，SORT_AREA_SIZE不能满足而不得不在磁盘进行排序操作的数量。

这两项统计通常用于计算in-memorysortratio。

lsorts（rows）:

列排序总数。

这项统计可被'sorts（total）'统计项除尽以确定每次排序的列。

该项可指出数据卷和应用特征。

ltablefetchbyrowid：

使用ROWID返回的总列数（由于索引访问或sql语句中使用了'whererowid=&rowid'而产生）

ltablescans（rowsgotten）：

全表扫描中读取的总列数

ltablescans（blocksgotten）：

全表扫描中读取的总块数，不包括那些split的列。

lusercommits+userrollbacks：

系统事务起用次数。

当需要计算其它统计中每项事务比率时该项可以被做为除数。

例如，计算事务中逻辑读，可以使用下列公式：

sessionlogicalreads/（usercommits+userrollbacks）。

注：

SQL语句的解析有软解析softparse与硬解析hardparse之说，以下是5个步骤：

1：

语法是否合法（sql写法）

2：

语义是否合法（权限，对象是否存在）

3：

检查该sql是否在公享池中存在

--如果存在,直接跳过4和5,运行sql.此时算softparse

4：

选择执行计划

5：

产生执行计划

--如果5个步骤全做,这就叫hardparse.

注意物理I/O

　　oracle报告物理读也许并未导致实际物理磁盘I/O操作。

这完全有可能因为多数操作系统都有缓存文件，可能是那些块在被读取。

块也可能存于磁盘或控制级缓存以再次避免实际I/O。

Oracle报告有物理读也许仅仅表示被请求的块并不在缓存中。

由V$SYSSTAT得出实例效率比（InstanceEfficiencyRatios）

下列是些典型的instanceefficiencyratios由v$sysstat数据计算得来，每项比率值应该尽可能接近1：

lBuffercachehitratio：

该项显示buffercache大小是否合适。

公式：

1-（（physicalreads-physicalreadsdirect-physicalreadsdirect（lob））/sessionlogicalreads）

执行：

select1-（

fromv$sysstata,v$sysstatb,v$sysstatc,v$sysstatd

where='physicalreads'and

='physicalreadsdirect'and

='physicalreadsdirect（lob）'and

='sessionlogicalreads';

lSoftparseratio：

这项将显示系统是否有太多硬解析。

该值将会与原始统计数据对比以确保精确。

例如，软解析率仅为则表示硬解析率太高。

不过，如果总解析量（parsecounttotal）偏低，这项值可以被忽略。

公式：

1-（parsecount（hard）/parsecount（total））

执行：

select1-

fromv$sysstata,v$sysstatb

='parsecount（hard）'and='parsecount（total）';

lIn-memorysortratio：

该项显示内存中完成的排序所占比例。

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

公式：

sorts（memory）/（sorts（memory）+sorts（disk））

执行：

select+

fromv$sysstata,v$sysstatb,v$sysstatc

='sorts（memory）'and

='sorts（memory）'='sorts（disk）';

lParsetoexecuteratio：

在生产环境，最理想状态是一条sql语句一次解析多数运行。

公式：

1-（parsecount/executecount）

执行：

select1-

fromv$sysstata,v$sysstatb

where='parsecount（total）'and='executecount';

lParseCPUtototalCPUratio：

该项显示总的CPU花费在执行及解析上的比率。

如果这项比率较低，说明系统执行了太多的解析。

公式：

1-（parsetimecpu/CPUusedbythissession）

执行：

select1-

fromv$sysstata,v$sysstatb

where='parsetimecpu'and

='CPUusedbythissession';

lParsetimeCPUtoparsetimeelapsed：

通常，该项显示锁竞争比率。

这项比率计算

是否时间花费在解析分配给CPU进行周期运算（即生产工作）。

解析时间花费不在CPU周期运算通常表示由于锁竞争导致了时间花费

公式：

parsetimecpu/parsetimeelapsed

执行：

select

fromv$sysstata,v$sysstatb

where='parsetimecpu'and='parsetimeelapsed';

从V$SYSSTAT获取负载间档（LoadProfile）数据

　　负载间档是监控系统吞吐量和负载变化的重要部分，该部分提供如下每秒和每个事务的统计信息：

logonscumulative,parsecount（total）,parsecount（hard）,executes,physicalreads,physicalwrites,blockchanges,andredosize.

　　被格式化的数据可检查'rates'是否过高，或用于对比其它基线数据设置为识别systemprofile在期间如何变化。

例如，计算每个事务中blockchanges可用如下公式：

dbblockchanges/（usercommits+userrollbacks）

执行：

select+

fromv$sysstata,v$sysstatb,v$sysstatc

where='dbblockchanges'and

='usercommits'='userrollbacks';

其它计算统计以衡量负载方式，如下：

lBlockschangedforeachread：

这项显示出blockchanges在blockreads中的比例。

它将指出是否系统主要用于只读访问或是主要进行诸多数据操作（如：

inserts/updates/deletes）

公式：

dbblockchanges/sessionlogicalreads

执行：

select

fromv$sysstata,v$sysstatb

where='dbblockchanges'and

='sessionlogicalreads';

lRowsforeachsort：

公式：

sorts（rows）/（sorts（memory）+sorts（disk））

执行：

select+

fromv$sysstata,v$sysstatb,v$sysstatc

where='sorts（rows）'and

='sorts（memory）'='sorts（disk）';

Oracle动态性能表-（4）-V$SESSTAT

按照OracleOnlineBook中的描述，v$sesstat存储session从login到logout的详细资源使用统计。

　　类似于v$sysstat,该视图存储下列类别的统计：

l事件发生次数的统计，如用户提交数。

l数据产生，存取或者操作的total列（如：

redosize）

l执行操作所花费的时间累积，例如sessionCPU占用（如果TIMED_STATISTICS值为true）

注意：

如果初始参数STATISTICS_LEVEL被设置为TYPICAL或ALL，时间统计被数据库自动收集如果STATISTICS_LEVEL被设置为BASIC，你必须设置TIMED_STATISTICS值为TRUE以打开收集功能。

如果你已设置了DB_CACHE_ADVICE,TIMED_STATISTICS或TIMED_OS_STATISTICS，或在初始参数文件或使用ALTER_SYSTEM或ALTERSESSION，那么你所设定的值的值将覆盖STATISTICS_LEVEL的值。

v$sysstat和v$sesstat差别如下：

nv$sesstat只保存session数据，而v$sysstat则保存所有sessions的累积值。

nv$sesstat只是暂存数据，session退出后数据即清空。

v$sysstat则是累积的，只有当实例被shutdown才会清空。

nv$sesstat不包括统计项名称，如果要获得统计项名称则必须与v$sysstat或v$statname连接查询获得。

v$sesstat可被用于找出如下类型session：

n高资源占用

n高平均资源占用比（登陆后资源使用率）

n默认资源占用比（两快照之间）

在V$SESSTAT中使用统计

　　多数v$sesstat中的统计参考是v$sysstat描述的子集，包括sessionlogicalreads,CPUusedbythissession,dbblockchanges,redosize,physicalwrites,parsecount（hard）,parsecount（total）,sorts（memory）,andsorts（disk）.

V$SESSTAT常用列说明

nSID：

session唯一ID

nSTATISTIC#：

资源唯一ID

nVALUE：

资源使用

示例1：

下列找出当前session中最高的logical和PhysicalI/O比率.

　　下列SQL语句显示了所有连接到数据库的session逻辑、物理读比率（每秒）。

logical和physicalI/O比率是通过自登陆后的时间消耗计算得出。

对于sessions连接到数据库这种长周期操作而言也许不够精确，不过做个示例却足够了。

先获得session逻辑读和物理读统计项的STATISTIC#值：

SELECTname,statistic#

FROMV$STATNAME

WHEREnameIN（'sessionlogicalreads','physicalreads'）;

NAMESTATISTIC#

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

sessionlogicalreads9

physicalreads40

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

SELECT

DECODE,NULL,'online','batch'）"User"

MAX（DECODE#,9,,0））

/greatest（3600*24*,1）"LogIO/s"

MAX（DECODE#,40,,0））

/greatest（3600*24*,1）"PhyIO/s"

60*24*"Minutes"

FROMV$SESSIONses

V$SESSTATsta

WHERE='ACTIVE'

AND=

AND#IN（9,40）

GROUPBY,,

ORDERBY

SUM（DECODE#,40,100*,）

/greatest（3600*24*,1）DESC;

SIDUserLogIO/sPhyIO/sMinutes

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

1951batch2911

470online6,1610

730batch7,568197

2153online1,48210

2386batch7,62035

1815batch7,50326

1965online4,87919

1668online4,3181

1142online95535

1855batch5738

1971online1,1381

1323online3,2635

1479batch2,8573

421online1,32215

2405online2588

示例2：

又例如通过v$sesstat和v$statname连接查询某个SID各项信息。

selecta.*,

fromv$sesstata,v$statnameb

where=10and#=#;

（2）-v$mystat

　　本视图是v$sesstat的一个子集，返回当前session的统计项。

当通过触发器审计session资源使用，可以使用v$mystat来捕获资源使用，这将比直接扫描v$sesstat的列要节省资源的多。

Oracle动态性能表-（5）-V$SQL,V$SQL_PLAN

（1）v$sql

　　一条语句可以映射多个cursor,因为对象所指的cursor可以有不同用户（如例1）。

如果有多个cursor（子游标）存在，在V$SQLAREA为所有cursor提供集合信息。

例1：

这里介绍以下childcursor

userA:

select*fromtbl