LoadRunner监控Oracle计数器要点
监控方法：
LoadRunner监控Oracle
http://www.docin.com/p-92077154.html
计数器介绍：
1、sorts(disk)(V$SYSSTAT)
If the number of disk writes is non-zero for a given sort operation, then this statistic is incremented. Sorts that require I/O to disk are quite resource intensive. Try increasing the size of the initialization parameter SORT_AREA_SIZE. For more information, see "SORT_AREA_SIZE".
sorts (memory) and sorts (disk)：sorts(memory)是在SORT_AREA_SIZE(因此不需要在磁盘进行排序)的排序操作的数量。sorts(disk)则是由于排序所需空间太大，SORT_AREA_SIZE不能满足而不得不在磁盘进行排序操作的数量。这两项统计通常用于计算in-memory sort ratio。
2、sort(memory)(V$SYSSTAT)
If the number of disk writes is zero, then the sort was performed completely in memory and this statistic is incremented. This is more an indication of sorting activity in the application work load. You cannot do much better than memory sorts, except maybe no sorts at all. Sorting is usually caused by selection criteria specifications within table join SQL operations.
In-memory sort ratio：该项显示内存中完成的排序所占比例。最理想状态下，在OLTP系统中，大部分排序不仅小并且能够完全在内存里完成排序。
公式：sorts (memory) / ( sorts (memory) + sorts (disk) )
执行：
selecta.value/(b.value+c.value)
fromv$sysstat a,v$sysstat b,v$sysstat c
wherea.name='sorts (memory)'and
b.name='sorts (memory)'andc.name='sorts (disk)';
对于要做大量排序操作的SQL语句的执行（例如select * from tt order by 1,2,3,4;），可监控到sort(disk)和sort(memory)都会有所上升。性能好的话，应该是大部分排序在内存中进行。
查询PGA统计信息：
SELECT * FROM v$pgastat;
查看bytes processed、extra bytes read/written的增量值和cache hit percentage的值
如果cache hit percentage偏低，则要考虑调整PGA
PGA
（Program Global Area程序全局区）是一块包含一个服务进程的数据和控制信息的内存区域。它是Oracle在一个服务进程启动时创建的，是非共享的。一个Oracle进程拥有一个PGA内存区。一个PGA也只能被拥有它的那个服务进程所访问，只有这个进程中的Oracle代码才能读写它。因此，PGA中的结构是不需要Latch保护的。
3、db block gets (V$SYSSTAT)
Number of blocks accessed in buffer cache for INSERT, UPDATE, DELETE, and SELECT FOR UPDATE. Represent block logical reads (from cache).The logical reads ALWAYS include the physical reads. Low number of physical reads is preferable.
在Oracle的文档中有这样一段解释：
db block gets：Number of times a CURRENT block was requested.
consistent gets：Number of times a consistent read was requested for a block.
physical reads：Total number of data blocks read from disk. This number equals the value of "physical reads direct" plus all reads into buffer cache.
---------------------------------------------------------------
针对以上3个概念进行的说明解释及关系如下：
1、DB Block Gets（当前请求的块数目）
当前模式块意思就是在操作中正好提取的块数目，而不是在一致性读的情况下而产生的块数。正常的情况下，一个查询提取的块是在查询开始的那个时间点上存在的数据块，当前块是在这个时刻存在的数据块，而不是在这个时间点之前或者之后的数据块数目。
2、Consistent Gets（数据请求总数在回滚段Buffer中的数据一致性读所需要的数据块）
这里的概念是在处理你这个操作的时候需要在一致性读状态上处理多少个块，这些块产生的主要原因是因为由于在你查询的过程中，由于其他会话对数据块进行操 作，而对所要查询的块有了修改，但是由于我们的查询是在这些修改之前调用的，所以需要对回滚段中的数据块的前映像进行查询，以保证数据的一致性。这样就产 生了一致性读。
3、Physical reads（物理读）
就是从磁盘上读取数据块的数量，其产生的主要原因是：
1)、 在数据库高速缓存中不存在这些块
2)、 全表扫描
3)、 磁盘排序
它们三者之间的关系大致可概括为：
逻辑读指的是Oracle从内存读到的数据块数量。一般来说是'consistent gets' + 'db block gets'。当在内存中找不到所需的数据块的话就需要从磁盘中获取，于是就产生了'physical reads'。
Buffer Cache Hit Ratio
查看oracle缓存的命中率（大于90％）
select 1 - ((physical.value - direct.value - lobs.value) / logical.value) "Buffer Cache Hit Ratio"
from v$sysstat physical,v$sysstat direct,v$sysstat lobs,v$sysstat logical
where physical.name = 'physical reads'
and direct.name='physical reads direct'
and lobs.name='physical reads direct (lob)'
and logical.name='session logical reads';
该项显示buffer cache大小是否合适
4、parse count (hard)(V$SYSSTAT)
Total number of parse calls (real parses). A hard parse is a very expensive operation in terms of memory use, because it requires Oracle to allocate a
workheap and other memory structures and then build a parse tree.Should be minimized. The ratio of Hard Parse to Total should be less than 20%.
parse count (hard)：在shared pool中解析调用的未命中次数。当sql语句执行并且该语句不在shared pool或虽然在shared pool但因为两者存在部分差异而不能被使用时产生硬解析。如果一条sql语句原文与当前存在的相同，但查询表不同则认为它们是两条不同语句，则硬解析即会发生。硬解析会带来cpu和资源使用的高昂开销，因为它需要oracle在shared pool中重新分配内存，然后再确定执行计划，最终语句才会被执行。
parse count (total)：解析调用总数，包括软解析和硬解析。当session执行了一条sql语句，该语句已经存在于shared pool并且可以被使用则产生软解析。当语句被使用(即共享)所有数据相关的现有sql语句(如最优化的执行计划)必须同样适用于当前的声明。这两项统计可被用于计算软解析命中率。
Soft parse ratio：这项将显示系统是否有太多硬解析。该值将会与原始统计数据对比以确保精确。例如，软解析率仅为0.2则表示硬解析率太高。不过，如果总解析量(parse count total)偏低，这项值可以被忽略。
公式：1 - ( parse count (hard) / parse count (total) )
执行：
select1-(a.value/b.value)
fromv$sysstat a,v$sysstat b
Wherea.name='parse count (hard)'andb.name='parse count (total)';
The parse process includes the following phases:
Checking that the SQL statement is syntactically valid (that is, that the SQL conforms to the rules of the SQL language, and that all keywords and operators are valid and correctly used).
Checking that the SQL is semantically valid. This means that all references to database objects (such as tables and columns) are valid.
Checking security (that is, that the user has permission to perform the specified SQL operations on the objects involved).
Determining an execution plan for the SQL statement. The execution plan describes the series of steps that Oracle performs in order to access and update the data involved.
Parsing can be an expensive operation. Its overhead is often masked by the greater overhead of high I/O requirements. However, eliminating unnecessary parsing is always desirable.
The parse/execute ratio reflects the ratio of parse calls to execute calls. Because parsing is an expensive operation, it is better to parse statements once and then execute them many times. High parse ratios (greater than 20%) can result from the following circumstances:
If literals, rather than bind variables, are used as query parameters, the SQL must be re-parsed on every execution. You should use bind variables whenever possible, unless there is a pressing reason for using column histograms.
Some development tools or techniques result in SQL cursors being discarded after execution. If a cursor is discarded, then the parse is required before the statement can be re-executed.
诊断：
查找不能被充分共享利用的SQL语句（查询LibraryCache中执行次数偏低的SQL语句）：
SELECT sql_text FROM v$sqlarea WHERE executions < 5 ORDER BY UPPER(sql_text);
查找SQL执行次数和SQL解释次数（hard parse），对比两个值的差：
SELECT sql_text , parse_calls , executions FROM v$sqlarea ORDER BY parse_calls;
查询v$librarycache视图的Reloads值（reparsing）的值，值应该接近0，否则应该考虑调整shared pool size,通过调整Shared Pool来调整Library Cache
invalidations的值也应该接近0
SQL> select namespace,gethitratio,pinhitratio,reloads,invalidations from v$librarycache;
SELECT gethitratio FROM v$librarycache WHERE namespace = 'SQL AREA';
SELECT sql_text , users_executing , executions , loads FROM v$sqlarea;
进一步查询该SQL的完整信息：
SELECT * FOM v$sqltext WHERE sql_text LIKE 'SELECT * FROM hr.employees WHERE %'
5、CPU used by this session(V$SYSSTAT)
Amount of CPU time (in tens of milliseconds) used by a session from the time a user call starts until it ends. If a user call completes within 10 milliseconds, the start- and end-user call times are the same for purposes of this statistic, and 0 milliseconds are added
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';
V$SYSSTAT shows Oracle CPU usage for all sessions. The statistic "CPU used by this session" shows the aggregate CPU used by all sessions.
V$SESSTAT shows Oracle CPU usage per session. You can use this view to determine which particular session is using the most CPU.
If you can, determine why the processes use so much CPU time and attempt to tune them. Possible areas to research include, but are not limited to, the following:
Reparsing SQL Statements
Read Consistency
Scalability Limitations Within the Application
Wait Detection
Latch Contention
---------------------------------------------------------------------------------------------
修改sample rate：
To change the length of each monitoring sample (in seconds), edit the dat\monitors\vmon.cfg file in the LoadRunner root folder. The default rate is 10 seconds.
The minimum sampling rate for the Oracle Monitor is 10 seconds. If you set the sampling rate at less than 10 seconds, the Oracle Monitor will continue to monitor at 10 second intervals.
添加自定义计数器：
在LoadRunner安装路径的\dat\monitors找到vmon.cfg文件并修改：
[V$ Monitor]
Counters=150
CustomCounters=9
;How many seconds for each data sample?
SamplingRate=10
[Custom0]
;Name must be unique
Name=库快存命中率
Description=该计数器返回当前库快存命中率
Query=SELECT 100*((sum(pins-reloads))/sum(pins)) from v$librarycache
IsRate=0
[Custom1]
;Name must be unique
Name=高速缓存区命中率
Description=oracle database shoot straight
Query=SELECT round(1-SUM(PHYSICAL_READS)/(SUM(DB_BLOCK_GETS) + SUM(CONSISTENT_GETS)), 4) * 100 FROM (SELECT CASE WHEN NAME='physical reads' THEN VALUE END PHYSICAL_READS,CASE WHEN NAME = 'db block gets' THEN VALUE END DB_BLOCK_GETS,CASE WHEN NAME = 'consistent gets' THEN VALUE END CONSISTENT_GETS FROM V$SYSSTAT WHERE Name IN ('physical reads','db block gets','consistent gets'))
IsRate=0
[Custom2]
;Name must be unique
Name=共享区库缓存区命中率
Description=命中率应大于0.99
Query=Select round(sum(pins-reloads)/sum(pins) * 100, 2) from v$librarycache
IsRate=0
[Custom3]
;Name must be unique
Name=共享区字典缓存区命中率
Description=命中率应大于0.85
Query=Select round(sum(gets-getmisses-usage-fixed)/sum(gets) * 100, 2) from v$rowcache
IsRate=0
[Custom4]
;Name must be unique
Name=检测回滚段的争用
Description=应该小于1%
Query=select round(sum(waits)/sum(gets) * 100, 2) from v$rollstat
IsRate=0
[Custom5]
;Name must be unique
Name=检测回滚段收缩次数
Description=应该小于1%
Query=select sum(shrinks) from v$rollstat, v$rollname where v$rollstat.usn = v$rollname.usn
IsRate=0
[Custom6]
;Name must be unique
Name=监控表空间的I/O读总数
Description=监控表空间的I/O
Query=select sum(f.phyrds) pyr from v$filestat f, dba_data_files df where f.file# = df.file_id
IsRate=0
[Custom7]
;Name must be unique
Name=监控表空间的I/O块读总数
Description=监控表空间的I/O
Query=select sum(f.phyblkrd) pbr from v$filestat f, dba_data_files df where f.file# = df.file_id
IsRate=0
[Custom8]
;Name must be unique
Name=监控表空间的I/O写总数
Description=监控表空间的I/O
Query=select sum(f.phywrts) pyw from v$filestat f, dba_data_files df where f.file# = df.file_id
IsRate=0
