Oracle动态性能表.docx

1、Oracle动态性能表Oracle动态性能表(1)-V$SQLAREAV$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_

2、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_P

3、ROCESSED: 语句返回的列总数V$SQLAREA中的连接列Column View Joined Column(s)HASH_VALUE, ADDRESS V$SESSION SQL_HASH_VALUE, SQL_ADDRESSHASH_VALUE, ADDRESS V$SQLTEXT, V$SQL, V$OPEN_CURSOR HASH_VALUE, ADDRESSSQL_TEXT V$DB_OBJECT_CACHE NAME示例：1.查看消耗资源最多的SQL：SELECT hash_value, executions, buffer_gets, disk_reads, parse_c

4、allsFROM V$SQLAREAWHERE buffer_gets OR disk_reads 1000000ORDER BY buffer_gets + 100 * disk_reads DESC;2.查看某条SQL语句的资源消耗：SELECT hash_value, buffer_gets, disk_reads, executions, parse_callsFROM V$SQLAREAWHERE hash_Value = 8 AND address = hextoraw(CBD8E4B0);Oracle动态性能表-(2)-V$SQLTEXT本视图包括Shared pool中SQL语

5、句的完整文本，一条SQL语句可能分成多个块被保存于多个记录内。V$SQLTEXT中的常用列HASH_VALUE：SQL语句的Hash值ADDRESS：sql语句在SGA中的地址SQL_TEXT：SQL文本。PIECE：SQL语句块的序号V$SQLTEXT中的连接列Column View Joined Column(s)HASH_VALUE, ADDRESS V$SQL, V$SESSION HASH_VALUE, ADDRESSHASH_VALUE. ADDRESS V$SESSION SQL_HASH_VALUE, SQL_ADDRESS示例：已知hash_value:99，查询sql语句：

6、select * from v$sqltextwhere hash_value=99order by pieceOracle动态性能表-(3)-V$SYSSTAT按照OracleDocument中的描述，v$sysstat存储自数据库实例运行那刻起就开始累计全实例(instance-wide)的资源使用情况。类似于v$sesstat，该视图存储下列的统计信息：1.事件发生次数的统计(如：user commits)2.数据产生，存取或者操作的total列(如：redo size)3.如果TIMED_STATISTICS值为true,则统计花费在执行操作上的总时间(如：CPU used by th

7、is session)v$sysstat视图常用列介绍：l STATISTIC#: 标识l NAME: 统计项名称l VALUE: 资源使用量该视图还有一列class-统计类别但极少会被使用，各类信息如下：1 代表事例活动2 代表Redo buffer活动4 代表锁8 代表数据缓冲活动16 代表OS活动32 代表并行活动64 代表表访问128 代表调试信息注意：Statistic#的值在不同版本中各不相同，使用时要用Name做为查询条件而不要以statistic#的值做为条件。使用v$sysstat中的数据该视图中数据常被用于监控系统性能。如buffer cache命中率、软解析率等都可从该视

8、图数据计算得出。该视图中的数据也被用于监控系统资源使用情况，以及系统资源利用率的变化。正因如此多的性能数据，检查某区间内系统资源使用情况可以这样做，在一个时间段开始时创建一个视图数据快照，结束时再创建一个，二者之间各统计项值的不同(end value - begin value)即是这一时间段内的资源消耗情况。这是oracle工具的常用方法，诸如Statspack以及BSTAT/ESTAT都是如此。为了对比某个区间段的数据，源数据可以被格式化（每次事务，每次执行，每秒钟或每次登陆），格式化后数据更容易从两者中鉴别出差异。这类的对比在升级前，升级后或仅仅想看看一段时间内用户数量增长或数据增加如何

9、影响资源使用方面更加实用。你也可以使用v$sysstat数据通过查询v$system_event视图来检查资源消耗和资源回收。V$SYSSTAT中的常用统计V$SYSSTAT中包含多个统计项，这部分介绍了一些关键的v$sysstat统计项，在调优方面相当有用。下列按字母先后排序：数据库使用状态的一些关键指标：l CPU used by this session：所有session的cpu占用量，不包括后台进程。这项统计的单位是百分之x秒.完全调用一次不超过10msl db block changes：那部分造成SGA中数据块变化的insert,update或delete操作数 这项统计可以大概

10、看出整体数据库状态。在各项事务级别，这项统计指出脏缓存比率。l execute count：执行的sql语句数量(包括递归sql)l logons current：当前连接到实例的Sessions。如果当前有两个快照则取平均值。l logons cumulative：自实例启动后的总登陆次数。l parse count (hard)：在shared pool中解析调用的未命中次数。当sql语句执行并且该语句不在shared pool或虽然在shared pool但因为两者存在部分差异而不能被使用时产生硬解析。如果一条sql语句原文与当前存在的相同，但查询表不同则认为它们是两条不同语句，则硬解析

11、即会发生。硬解析会带来cpu和资源使用的高昂开销，因为它需要oracle在shared pool中重新分配内存，然后再确定执行计划，最终语句才会被执行。l parse count (total)：解析调用总数，包括软解析和硬解析。当session执行了一条sql语句，该语句已经存在于shared pool并且可以被使用则产生软解析。当语句被使用(即共享) 所有数据相关的现有sql语句(如最优化的执行计划)必须同样适用于当前的声明。这两项统计可被用于计算软解析命中率。l parse time cpu：总cpu解析时间(单位：10ms)。包括硬解析和软解析。l parse time elapsed

12、：完成解析调用的总时间花费。l physical reads：OS blocks read数。包括插入到SGA缓存区的物理读以及PGA中的直读这项统计并非i/o请求数。l physical writes：从SGA缓存区被DBWR写到磁盘的数据块以及PGA进程直写的数据块数量。l redo log space requests：在redo logs中服务进程的等待空间，表示需要更长时间的log switch。l redo size：redo发生的总次数(以及因此写入log buffer)，以byte为单位。这项统计显示出update活跃性。l session logical reads：逻辑读请

13、求数。l sorts (memory) and sorts (disk)：sorts(memory)是适于在SORT_AREA_SIZE(因此不需要在磁盘进行排序)的排序操作的数量。sorts(disk)则是由于排序所需空间太大，SORT_AREA_SIZE不能满足而不得不在磁盘进行排序操作的数量。这两项统计通常用于计算in-memory sort ratio。l sorts (rows): 列排序总数。这项统计可被sorts (total)统计项除尽以确定每次排序的列。该项可指出数据卷和应用特征。l table fetch by rowid：使用ROWID返回的总列数(由于索引访问或sql语

14、句中使用了where rowid=&rowid而产生)l table scans (rows gotten)：全表扫描中读取的总列数l table scans (blocks gotten)：全表扫描中读取的总块数，不包括那些split的列。l user commits + user rollbacks：系统事务起用次数。当需要计算其它统计中每项事务比率时该项可以被做为除数。例如，计算事务中逻辑读，可以使用下列公式：session logical reads / (user commits + user rollbacks)。注：SQL语句的解析有软解析soft parse与硬解析hard p

15、arse之说，以下是5个步骤：1：语法是否合法(sql写法)2：语义是否合法(权限，对象是否存在)3：检查该sql是否在公享池中存在- 如果存在,直接跳过4和5,运行sql. 此时算soft parse4：选择执行计划5：产生执行计划- 如果5个步骤全做,这就叫hard parse.注意物理I/Ooracle报告物理读也许并未导致实际物理磁盘I/O操作。这完全有可能因为多数操作系统都有缓存文件，可能是那些块在被读取。块也可能存于磁盘或控制级缓存以再次避免实际I/O。Oracle报告有物理读也许仅仅表示被请求的块并不在缓存中。由V$SYSSTAT得出实例效率比(Instance Efficien

16、cy Ratios)下列是些典型的instance efficiency ratios 由v$sysstat数据计算得来，每项比率值应该尽可能接近1：l Buffer cache hit ratio：该项显示buffer cache大小是否合适。公式：1-(physical reads-physical reads direct-physical reads direct (lob) / session logical reads)执行：select1-( from v$sysstat a,v$sysstat b,v$sysstat c,v$sysstat d where =physical

17、readsand =physical reads directand =physical reads direct (lob)and =session logical reads;l Soft parse ratio：这项将显示系统是否有太多硬解析。该值将会与原始统计数据对比以确保精确。例如，软解析率仅为则表示硬解析率太高。不过，如果总解析量(parse count total)偏低，这项值可以被忽略。公式：1 - ( parse count (hard) / parse count (total) )执行：select1- from v$sysstat a,v$sysstat b =pars

18、e count (hard)and =parse count (total);l In-memory sort ratio：该项显示内存中完成的排序所占比例。最理想状态下，在OLTP系统中，大部分排序不仅小并且能够完全在内存里完成排序。公式：sorts (memory) / ( sorts (memory) + sorts (disk) )执行：select +from v$sysstat a,v$sysstat b,v$sysstat c=sorts (memory)and =sorts (memory)=sorts (disk);l Parse to execute ratio：在生产环境

19、，最理想状态是一条sql语句一次解析多数运行。公式：1 - (parse count/execute count)执行：select1-from v$sysstat a,v$sysstat bwhere =parse count (total)and =execute count;l Parse CPU to total CPU ratio：该项显示总的CPU花费在执行及解析上的比率。如果这项比率较低，说明系统执行了太多的解析。公式：1 - (parse time cpu / CPU used by this session)执行：select1-from v$sysstat a,v$syss

20、tat bwhere =parse time cpuand =CPU used by this session;l Parse time CPU to parse time elapsed：通常，该项显示锁竞争比率。这项比率计算是否时间花费在解析分配给CPU进行周期运算(即生产工作)。解析时间花费不在CPU周期运算通常表示由于锁竞争导致了时间花费公式：parse time cpu / parse time elapsed执行：select from v$sysstat a,v$sysstat bwhere =parse time cpuand =parse time elapsed;从V$SY

21、SSTAT获取负载间档(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 c

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

23、hanges / session logical reads执行：select from v$sysstat a,v$sysstat bwhere =db block changesand =session logical reads ;l Rows for each sort：公式：sorts (rows) / ( sorts (memory) + sorts (disk) )执行：select +from v$sysstat a,v$sysstat b,v$sysstat cwhere =sorts (rows)and =sorts (memory)=sorts (disk);Oracle

24、动态性能表-(4)-V$SESSTAT按照OracleOnlineBook中的描述，v$sesstat存储session从login到logout的详细资源使用统计。类似于v$sysstat,该视图存储下列类别的统计：l 事件发生次数的统计，如用户提交数。l 数据产生，存取或者操作的total列(如：redo size)l 执行操作所花费的时间累积，例如session CPU占用(如果TIMED_STATISTICS值为true)注意：如果初始参数STATISTICS_LEVEL被设置为TYPICAL或ALL，时间统计被数据库自动收集如果STATISTICS_LEVEL被设置为BASIC，你必

25、须设置TIMED_STATISTICS值为TRUE以打开收集功能。如果你已设置了DB_CACHE_ADVICE,TIMED_STATISTICS或TIMED_OS_STATISTICS，或在初始参数文件或使用ALTER_SYSTEM或ALTER SESSION，那么你所设定的值的值将覆盖STATISTICS_LEVEL的值。v$sysstat和v$sesstat差别如下：n v$sesstat只保存session数据，而v$sysstat则保存所有sessions的累积值。n v$sesstat只是暂存数据，session退出后数据即清空。v$sysstat则是累积的，只有当实例被shutdo

26、wn才会清空。n v$sesstat不包括统计项名称，如果要获得统计项名称则必须与v$sysstat或v$statname连接查询获得。v$sesstat可被用于找出如下类型session：n 高资源占用n 高平均资源占用比(登陆后资源使用率)n 默认资源占用比(两快照之间)在V$SESSTAT中使用统计多数v$sesstat中的统计参考是v$sysstat描述的子集，包括session logical reads, CPU used by this session, db block changes, redo size, physical writes, parse count (hard

27、), parse count (total), sorts (memory), and sorts (disk).V$SESSTAT常用列说明n SID：session唯一IDn STATISTIC#：资源唯一IDn VALUE：资源使用示例1：下列找出当前session中最高的logical和Physical I/O比率.下列SQL语句显示了所有连接到数据库的session逻辑、物理读比率(每秒)。logical和physical I/O比率是通过自登陆后的时间消耗计算得出。对于sessions连接到数据库这种长周期操作而言也许不够精确，不过做个示例却足够了。先获得session逻辑读和物理

28、读统计项的STATISTIC#值：SELECTname, statistic# FROM V$STATNAME WHEREnameIN (session logical reads,physical reads) ;NAME STATISTIC#- -session logical reads 9physical reads 40通过上面获得的STATISTIC#值执行下列语句：SELECT , DECODE,NULL,online,batch) User , MAX(DECODE#,9,0) /greatest(3600*24*,1) Log IO/s , MAX(DECODE#,40,0)

29、 /greatest(3600*24*,1) Phy IO/s , 60*24* MinutesFROM V$SESSION ses , V$SESSTAT staWHERE = ACTIVEAND = AND # IN (9,40)GROUP BY , , ORDER BY SUM( DECODE#,40,100*, ) / greatest(3600*24*,1) DESC;SID User Log IO/s Phy IO/s Minutes- - - - -1951 batch 291 1470 online 6,161 0730 batch 7,568 1972153 online 1

30、,482 102386 batch 7,620 351815 batch 7,503 261965 online 4,879 191668 online 4,318 11142 online 955 351855 batch 573 81971 online 1,138 11323 online 3,263 51479 batch 2,857 3421 online 1,322 152405 online 258 8示例2：又例如通过v$sesstat和v$statname连接查询某个SID各项信息。select a.*,from v$sesstat a,v$statname b where

31、=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：这里介绍以下child cursoruser A: select * from tbl