1）V$SGASTAT： SGA内存分配状况
2）V$SYSSTAT： 数据块缓存区命中率
3）V$ROWCACHE 数据字典命中率
4）V$LIBRARYCACHE 共享SQL和PL/SQL命中率
V$SGASTAT
V$SYSSTAT
"Read Hit Ratio"
0.9993513559939193853216038428998715967938
如果命中率低于95%，建议考虑增加DB_CACHE_SIZE值。
数据字典命中率
"SUM(GETS)" "SUM(GETMISSES)" "HITRATE"
780511 14275 98.20391904235857199296414380726384209083
如果命中率低于95%，建议增加SHARED_POOL_SIZE值。
V$LIBRARYCACHE
命中率应不低于99%，否则说明重载率过高。造成这一现象的原因可能是SQL绑定过高，可查看V$SQL_BIND_CAPTURE
根据查出的SQL_ID，找出具体的SQL语句
2）V$SYSSTAT： 数据块缓存区命中率
3）V$ROWCACHE 数据字典命中率
4）V$LIBRARYCACHE 共享SQL和PL/SQL命中率
V$SGASTAT
点击(此处)折叠或打开
- select * from v$sgastat
点击(此处)折叠或打开
-
点击(此处)折叠或打开
-
select 1-(sum(decode(name, 'physical reads', value,0))/
-
(sum(decode(name, 'db block gets', value,0)) +
-
(sum(decode(name, 'consistent gets', value,0)))))
-
"Read Hit Ratio"
- from v$sysstat;
"Read Hit Ratio"
0.9993513559939193853216038428998715967938
数据字典命中率
点击(此处)折叠或打开
-
select sum(gets), sum(getmisses),(1 - (sum(getmisses) / (sum(gets)
-
+ sum(getmisses)))) * 100 HitRate
- from v$rowcache;
"SUM(GETS)" "SUM(GETMISSES)" "HITRATE"
780511 14275 98.20391904235857199296414380726384209083
V$LIBRARYCACHE
点击(此处)折叠或打开
-
select sum(pins) "Executions", sum(pinhits) "Hits",
-
((sum(pinhits) / sum(pins)) * 100) "PinHitRatio",
-
sum(reloads) "Misses", ((sum(pins) / (sum(pins)
-
+ sum(reloads))) * 100) "RelHitRatio"
- from v$librarycache;
| Executions | Hits | PinHitRatio | Misses | RelHitRatio |
| 4595289 | 4551871 | 99.0551628 | 21189 | 99.54101373 |
命中率应不低于99%，否则说明重载率过高。造成这一现象的原因可能是SQL绑定过高，可查看V$SQL_BIND_CAPTURE
点击(此处)折叠或打开
-
select sql_id, count(*) bind_count
-
from v$sql_bind_capture
-
where child_number = 0
-
group by sql_id
-
having count(*) > 20
- order by count(*);
| SQL_ID | BIND_COUNT |
| 9qgtwh66xg6nz | 21 |
| c0agatqzq2jzr | 25 |
| g6r16rrup99u5 | 25 |
点击(此处)折叠或打开
-
select sql_text, users_executing, executions, users_opening, buffer_gets
-
from v$sqlarea
-
where sql_id = 'g6r16rrup99u5'
- order by buffer_gets;
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/22621861/viewspace-1283978/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
调整PL/SQL代码加速执行（2例）
下一篇：
检查Oracle的锁状态并清除问题会话
请登录后发表评论
登录
全部评论
