监控sql语句的重载率
扫描二维码
随时随地手机看文章
Sql的重载率, 就是相同的语句, 由于无法使用共享池里已经保存的执行计划而不得不重新将代码载入后执行分析,建立查询树后再进行执行的一个过程.
极端糟糕的情况下, 重载率可能接近于1 , 就是说,每一个语句都需要载入后重新执行.
2.1 oracle库缓存Oracle的库缓存是内存的一个区域, 是共享池里的三个组成部分之一. 库缓存由共享SQL工作区, PL/SQL包和过程, 不同的锁和句柄组成. 每当有应用程序要执行sql或pl/sql语句时, 这些代码必须先暂存在oracle的库缓存中. 当应用程序运行代码时, oracle会先搜索库缓存看该代码是否已经存在于内存中. 如果代码已经写入内存中, oracle就可以重新使用该已存代码(也称为软解析). 如果内存里找不到该代码, oracle 必须将代码载入到内存中(也称为硬解析或库缓存不命中).
系统会给一个已配置的库缓存工作区分配了一定的内存量, 当内存耗尽时, 会自动从内存中删除一些不常用的代码, 以便腾出一定空间来装载应用程序所需的代码. 如果硬解析出现的次数太多, 我们可能需要增加分配给库缓存的内存容量.
动态性能视图V$librarycache中存储自最近一次启动oracle数据库之后到目前的库缓存的性能情况, 我们可以查看这个视图查看软解析和硬解析的命中率情况.
字段
数据类型
说明
NAMESPACE
VARCHAR2(15)
library cache的命名空间
GETS
NUMBER
请求GET该命名空间中对象的次数。
GETHITS
NUMBER
请求GET并在内存中找到了对象句柄的次数（锁定命中）。
GETHITRATIO
NUMBER
请求GET的命中率。
PINS
NUMBER
请求pin住该命名中对象的次数。
PINHITS
NUMBER
库对象的所有元数据在内存中被找到的次数（pin命中）。
PINHITRATIO
NUMBER
Pin命中率。
RELOADS
NUMBER
Pin请求需要从磁盘中载入对象的次数。
INVALIDATIONS
NUMBER
命名空间中的非法对象（由于依赖的对象被修改所导致）数。
DLM_LOCK_REQUESTS
NUMBER
GET请求导致的实例锁的数量。
DLM_PIN_REQUESTS
NUMBER
PIN请求导致的实例锁的数量.
DLM_PIN_RELEASES
NUMBER
请求释放PIN锁的次数。
DLM_INVALIDATION_REQUESTS
NUMBER
GET请求非法实例锁的次数。
DLM_INVALIDATIONS
NUMBER
从其他实例那的得到的非法pin数。
This view contains statistics about library cache performance and activity.
Column
Datatype
Description
NAMESPACE
VARCHAR2(15)
The library cache namespace
GETS
NUMBER
The number of times a lock was requested for objects of this namespace
GETHITS
NUMBER
The number of times an object's handle was found in memory
GETHITRATIO
NUMBER
The ratio of GETHITS to GETS
PINS
NUMBER
The number of times a PIN was requested for objects of this namespace
PINHITS
NUMBER
The number of times all of the metadata pieces of the library object were found in memory
PINHITRATIO
NUMBER
The ratio of PINHITS to PINS
RELOADS
NUMBER
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
INVALIDATIONS
NUMBER
The total number of times objects in this namespace were marked invalid because a dependent object was modified
DLM_LOCK_REQUESTS
NUMBER
The number of GET requests lock instance locks
DLM_PIN_REQUESTS
NUMBER
The number of PIN requests lock instance locks
DLM_PIN_RELEASES
NUMBER
The number of release requests PIN instance locks
DLM_INVALIDATION_REQUESTS
NUMBER
The number of GET requests for invalidation instance locks
DLM_INVALIDATIONS
NUMBER
The number of invalidation pings received from other instances
2.2 library cache的内存结构2.2.1 library cache中存储的信息
1, 按对象类型分类
共享游标(SQL and PL/SQL objects)
数据库对象(tables , indexes, procedures and so on)
2, 按存在时间分类
存储对象: 如table, index, view等(老化后,磁盘上还有, 所以叫永久存储对象)
瞬时对象: 如游标(老化后就不见了)
2.2.2 library cache中如何存储信息
图2 library cache的内存结构
共享池内存的结构, 是计算机常用的哈希表形式的扩展. 常用的哈希表形式, 总是先有一个哈希表, 保存对象地址(或句柄), 然后,根据对象地址(或句柄)访问对象.
表1 几个基本概念
名称
描述
地址
只有一个位置信息
句柄
处理位置,还包括一些其他相关信息
堆(heap)
程序开发者使用系统函数分配的内存
栈
开发者在程序中定义的变量就存在栈
如图2中, 每一组哈希值, 链表头叫做一个哈希桶. . 简单地说就是哈希桶(hash bucket)指向对象句柄(object handles) , 对象句柄存有对象所占的堆内存的地址.对象的堆往往不止一个, oracle习惯称这些堆为子堆 . 通常对象句柄中存有0号子堆的地址, 而0号子堆存有其他各个子堆的地址.
2.3 Library cache的pin与lock所有在library cache中的对象, 都由两部分组成, 一个句柄, 至少一个子堆.
句柄中记录的有对象的名字, 命名空间, lock的持有者和等待者, pin的持有者和等待者, 一些标志信息以及堆的地址.
在library cache中寻找对象时, 先计算hash值, 在hash表中找到句柄, 再经句柄, 找到对象实际的内存地址(子堆). 在这个过程中, 有两个重要数据项需要被锁保护起来. 一个是对象句柄, 另一个就是对象的内存堆. 在对象句柄上加的锁就是library cache lock, 在内存堆上加的锁就是library cache pin.
2.3.1 library cache lock
Locks除了阻止不相容的对句柄的访问, 以保护句柄中数据的完整性外, 获得locks也是在缓存中定位对象的唯一方式, 即: 进程在对句柄上加锁的同时, 完成在内存中定位堆的操作. 在句柄上获得lock, 饼子内存中定位到堆后, 对象可以pin自己的堆. 如果对象相关信息不在内存中, pinning一个对象将导致它和它的子堆被装载(此种情况下, 如果是多个对象pin一个对象, 将可能造成pin等待).
Lock有三种模式
l Share: 读对象锁
l Exclusive: 修改或创建对象
l Null: 专用于为会话持续.
注意, 永久存储对象可以被锁在以上任意一种方式, 瞬时对象只能被锁在null方式.
Null锁在执行sql声明的解析阶段被获得, 此后一直持有. 它不阻止任何DDL, 也用属于”易碎解析锁”称呼它.
以下两种情况下null锁被打碎:
l 当锁所在对象有一个独占pin时.
l 锁所在对象的任何依赖对象有一个独占pin时
Pin有两种模式:
l Share: 读一个对象堆
l Exclusive: 修改一个对象堆.
无论存储对象还是瞬时对象, 都能被pinned在share或exclusive模式. 当修改对象时, 进程会首先以share模式pin对象, 进行错误和安全检查, 然后再以exclusive模式pin对象. Pin的解除将会导致相关对象上的易碎锁break.
2.4 查看整个库缓存的运行情况 select sum(pins) pins,
sum(pinhits) pinhits,
sum(reloads) reloads,
sum(invalidations) invalidations,
100-(sum(pinhits)/sum(pins)) *100 reparsing
from v$librarycache;
上述代码可以得到库缓存的整体性能状况. 其中pin为对子堆也就是对象的实际访问或者叫执行次数, pinhits为执行成功数, reloads为尝试执行不在库缓存里的代码的次数. Invalidations是指那些由于某种原因(特别是通过DDL操作),使得要执行的代码已经失效从而需要重新载入解析代码的次数.
2.5 解决库缓存造成的问题库缓存偶尔会给用户带来的麻烦通常源于各种锁以及随之而来的由锁机制引发的以下等待事件:
库缓存加载锁: 当有其他用户端对同一对象使用了库缓存加载锁时, 新来的客户端必须等待先前的用户将锁释放出来.
库缓存锁: 比如两个用户端想要同时编译某段相同的代码时.
库缓存pin: 这时意味着其他会话以不兼容模式锁锁定了该子堆.
不管库缓存中出现了哪种类型的等待事件，想要确定哪些会话在等待以及在等待的是什么资源，可以通过V$SESSION_WAIT视图查询进行诊断。例如，如果想要找出那些在等待“库缓存pin”的会话，可以执行以下的查询语句。对于一个库缓存pin来说，该查询的关键部分是P1RAW字段，该字段给出了阻塞特定会话的对象的句柄地址。对于其他类型的等待事件，您可以参考Oracle数据库的说明文档，找出对应于等待中的某对象或资源的P值。
SELECT sid,event,p1raw
FROM sys.v_$session_wait
WHERE event = 'library cache pin'
AND state = 'WAITING';
然后我们可以执行以下的查询来找出正在等待哪些库缓存对象;
SELECT kglnaown AS owner, kglnaobj as Object
FROM sys.x$kglob
WHERE kglhdadr='&P1RAW';
要找出那些正在等待某个对象的用户，可以使用DBA_WAITERS视图并执行以下查询。这是一个非常简单的查询，却可以很巧妙的找出阻塞的会话，也就是查找与上面从V$SESSION_WAIT查询中找出的会话相匹配的等待会话，然后看看返回的holding_session结果。我们还可以看到在被阻塞的会话之后还有多少其他会话在等待中。如果有很多等待会话，那你就需要迅速采取行动了。
SELECT waiting_session, holding_session FROM dba_waiters;
现在我们已经确定了正在进行中的会话和被等待对象，以及引发问题的会话及其SQL。那么接下来要如何解决出现的问题呢?如果等待事件持续的时间过长，那么库缓存内部很可能发生了错误或故障。唯一的补救办法就是杀死持有该锁的所有进程。在Oracle数据库中要达到这个目的，可以使用alter system kill session命令。不过，这个命令是否有效还得看连接的类型。有时候需要用operating system kill命令或者关闭一系列应用程序来终止连接。我们需要检测库缓存中完全锁定状态下，哪一个方法对系统更行之有效。至少在不得不关闭系统和数据库之前，尝试一下强迫杀死进程的方法。
当我们使用库缓存时，只要记住它不过是在代码执行前，Oracle数据库必须将这些代码载入其中的内存区。将代码载入到库缓存的过程可能会受到限制，从而引起等待事件，使系统挂起。这时候我们要通过杀死会话、进程或修改代码的方法快速确定导致系统挂起的SQL进程，不过千万不要忘记了库缓存只是内存的事实，我们可能只是需要给引发问题的部分重新分配一些内存，使Oracle更有效地运行而已。
下载文档
