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博客 ” ,链接:http://blog.itpub.net/22621861/viewspace-1283978/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22621861/viewspace-1283978/