缓存命中率sqlarea,gethit低的个人整理记录

一、抄袭

https://blog.youkuaiyun.com/cuiyan1982/article/details/79826894

讲:open_cursors、session_cached_cursors

https://www.modb.pro/db/25540

讲:session、session_cached_cursors cursor

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/refrn/V-LIBRARYCACHE.html#GUID-3BE0F434-A392-42C9-BD77-FD2A7B72D6DB

讲:v$libiarycache

逻辑推理:

https://blog.youkuaiyun.com/lqx0405/article/details/44777325

#———————————————————————————

二、首先监控侧告警:

sqlarea 缓冲区命中率低

我就去数据库查

select GETS,GETHITS,PINS,PINHITS,RELOADS ,INVALIDATIONS shixiao,(GETHITRATIO * 100) mzl from v$librarycache where namespace='SQL AREA';

GET: 请求GET的次数

GETHIT: 在内存中找到对象句柄的次数

PINS: 请求PIN的次数

PINHITS: 在内存中找到库对象的所有元数据块的次数

RELOADS: pin的内容重新加载次数(个人理解)

INVALIDATIONS: 空间中pin到内容失效的次数(个人理解)

GETHITRATIO: 命中率

理解+网络抄袭

在访问库缓存对象时,比如软解析时,要从库缓存中读取执行计划。

Oracle首先找到句柄,读取句柄中的信息,这就叫做一次库缓存Get。

如果库缓存中不包括对象的句柄信息,Oracle就要重新在库缓存中分配内存、构造句柄,

这就是库缓存句柄未命中(Get Miss)。

相反,如果在库缓存中找到了对象句柄,就是库缓存句柄命中(Get Hit)。

硬解析时,就会发生Get Miss。而软解析则是Get Hit。

在取出句柄中的其他内存块地址后,每访问一个内存块,都叫做一次库缓存Pin。

如果相应的内存块已经不在内存中了,这就是Pin Miss,Pin的未命中。相反就是Pin Hit。

当库缓存中对象发生改变后,会引起其他一些对象的无效(Invalidation)。

比如,你修改了一个表的结构,那么,选择了这个表的SQL语句的执行计划就会变的无效。

其实大部分对表的DDL操作,都会造成相关SQL语句执行计划的无效。

就连Grant(授权)、Revoke(撤消权限)这样看来跟执行计划耗无关系的操作,都会引起无效。

!!! 而Reload和Pin的次数的比值,应该小于1%。

如果超出了这些数值范围,就说明库缓存的使用有问题。

问题的原因主要有两个,没有使用绑定变量或是库缓存太小。

不过Reload和Pin的比例过高,也可能是在繁忙时执行了DDL所导致的。

欧智监控sql

select gethitratio*100 "get_pct" FROM v$librarycache where namespace ='BODY'

select gethitratio*100 "get_pct" FROM v$librarycache where namespace ='SQL AREA'

select gethitratio*100 "get_pct" FROM v$librarycache where namespace ='TRIGGER'

select gethitratio*100 "get_pct" FROM v$librarycache where namespace = 'TABLE/PROCEDURE'

#———————————————————————————

三、通过1得到是get hit少

感觉应该是需要增加绑定变量,

可能是sql大致句式一样,但是没有绑定变量,就没法gethit命中

#———————————————————————————

3.1 绑定变量1

select to_char(FORCE_MATCHING_SIGNATURE), count(1)

from gv$sql

where FORCE_MATCHING_SIGNATURE > 0

and FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATURE

group by FORCE_MATCHING_SIGNATURE

having count(1) > 1000

order by 2 desc;

--通过FORCE_MATCHING_SIGNATURE查看sql

select sql_text, FORCE_MATCHING_SIGNATURE, EXACT_MATCHING_SIGNATURE

FROM V$SQL

WHERE to_char(FORCE_MATCHING_SIGNATURE)='11167613790136244795';

#———————————————————————————

3.2 绑定变量2

select count(*) ,PERSISTENT_MEM from v$sqlarea group by PERSISTENT_MEM having count(*)>10 order by count(*) desc ;

count(*):sql数量

persistent_men:是占有稳定的内存数(单位:byte)

凡是我们因为没有绑定变量,sql语句一样只是where条件的值不一样,那么他的persistent_mem一定是一样的

这样我们就可以通过这个值的大小,和出现次数来判断有多少sql运行这个值是一样的,如果过多那么基本可以判断重复的sql过多,没有绑定变量。

我们可以

select sql_id,sql_text from v$sqlarea where PERSISTENT_MEM=xxx ;

来看是否有许多重复,没有绑定变量运行的sql语句。

#———————————————————————————

3.3 绑定变量3

查询代码parse 比较高的语句 :

set pagesize 600;

set linesize 120;

select substr(sql_text,1,100) "sql", count(*), sum(executions) "totexecs"

from v$sqlarea

where executions < 5

group by substr(sql_text,1,100)

having count(*) > 30

order by 2;

当reload的值过大,而且绑定变量做的不错了,

那么就需要增加share pool的大小(配合sga使用率去查)

#———————————————————————————

3.4 绑定变量4

查询硬解析和失败解析语句

select INSTANCE_NUMBER,

SNAP_ID,

to_char(END_INTERVAL_TIME, 'yyyy-mm-dd hh24:mi') end_time,

round(hard_parse / 3600, 1) hard_parse,

round(failures_parse / 3600, 1) failures_parse

from (select s.instance_number,

s.snap_id,

s.stat_name,

st.BEGIN_INTERVAL_TIME,

st.END_INTERVAL_TIME,

value - lag(value) over(partition by s.stat_name order by s.snap_id) value

from dba_hist_sysstat s, dba_hist_snapshot st

where stat_name in ('parse count (hard)', 'parse count (failures)')

and s.instance_number = st.instance_number

and s.instance_number = 1

and s.snap_id = st.snap_id) pivot(sum(value) for stat_name in ('parse count (hard)' as hard_parse, 'parse count (failures)' as failures_parse))

order by snap_id;

4 AWR分析

4.1取awr报告,观察

~①值:

Parses (SQL):

Hard parses (SQL):

Executes (SQL):

~②命中率:

Soft Parse %:

Execute to Parse %:

#———————————————————————————

4.2

Execute to Parse %:

1-(parse/execute)

表示sql语句解析后被重复执行的命中率,

如果该值偏小,说明分析(硬分析+软分析)的比例较大,快速分析较少。

Soft Parse %:

softs/(softs+hards),软解析的百分比,太低则需要调整应用使用绑定变量

#———————————————————————————

4.3

①如果两个命中率同时很低时,说明硬解析次数多,建议使用绑定变量。

②如果Soft Parse %高,而Execute to Parse %比低时(<40%),说明执行解析比率低

可以通过静态sql、动态绑定、调整session_cached_cursor参数、调整open_cursor等方法来减少软解析。

②.1 建议根据实际业务增大游标缓存:

改session_cached_cursor参数(例如40、100、200)默认50,

观察使用率是否是100%,在观察open_cursors使用率是否变高来判断

②.2 alter system set session_cached_cursors=400 scope=spfile; (重启)

②.3 alter system set open_cursors=500 scope=spfile;(重启)

5 session cursor

5.1

查看session_cached_cursors的使用率

SESSION_CACHED_CURSORS,就是说的是一个session可以缓存多少个cursor,

让后续相同的SQL语句不再打开游标,从而避免软解析的过程来提高性能。

(绑定变量是解决硬解析的问题),软解析同硬解析一样,比较消耗资源.所以这个参数非常重要

如果session_cached_cursors 100% 则建议增大这个值

Select 'session_cached_cursors' Parameter,

Lpad(Value, 5) Value,

Decode(Value, 0, ' n/a', To_Char(100 * Used / Value, '990') || '%') Usage

From (Select Max(s.Value) Used

From V$statname n, V$sesstat s

Where n.Name = 'session cursor cache count'

And s.Statistic# = n.Statistic#),

(Select Value From V$parameter Where Name = 'session_cached_cursors')

Union All

Select 'open_cursors',

Lpad(Value, 5),

To_Char(100 * Used / Value, '990') || '%'

From (Select Max(Sum(s.Value)) Used

From V$statname n, V$sesstat s

Where n.Name In

('opened cursors current', 'session cursor cache count')

And s.Statistic# = n.Statistic#

Group By s.Sid),

(Select Value From V$parameter Where Name = 'open_cursors');

#———————————————————————————

5.2

查看session cursor cache hits 和parse count(total)的比率

session cursor cache hits就是系统在高速缓存区中找到相应cursors的次数,

parse count(total)就是总的解析次数,

二者比值越高,性能越好。如果比例比较低,并且有较多剩余内存的话,可以考虑加大session_cached_cursors

select name,value from v$sysstat where name like '%cursor%';

select name,value from v$sysstat where name like '%parse%';

select a.value/b.value from v$sysstat a,v$sysstat b where a.name ='session cursor cache hits' and b.name='parse count (total)';

#———————————————————————————

5.3

查看是否合理

HIGHEST_OPEN_CUR :实际打开的cursors 的最大值

MAX_OPEN_CUR :参数Open_cursors的设定值

col HIGHEST_OPEN_CUR for 9999

col MAX_OPEN_CUR for a30

SELECT MAX(A.VALUE) AS HIGHEST_OPEN_CUR, P.VALUE AS MAX_OPEN_CUR

FROM V$SESSTAT A, V$STATNAME B, V$PARAMETER P

WHERE A.STATISTIC# = B.STATISTIC#

AND B.NAME = 'opened cursors current'

AND P.NAME = 'open_cursors'

GROUP BY P.VALUE;

二者太接近,甚至触发eRA一01000错误,那么你就应该调大参数Open_cursors的设定值。

如果问题依旧没有解决,盲目增大Open_cursors也是不对的,这个时候你得检查应用程序的代码是否合理,

比如说应用程序是否打开了游标,却没有在它完成工作后没有及时关闭。

以下语句可以帮助你确定导致游标漏出的会话:

SELECT A.VALUE, S.USERNAME, S.SID, S.SERIAL#

FROM V$SESSTAT A, V$STATNAME B, V$SESSION S

WHERE A.STATISTIC# = B.STATISTIC#

AND S.SID = A.SID

AND B.NAME = 'opened cursors curent';

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值