使用V$视图观察Oracle运行状况

1）V$SGASTAT：             SGA内存分配状况
2）V$SYSSTAT：             数据块缓存区命中率
3）V$ROWCACHE            数据字典命中率
4）V$LIBRARYCACHE      共享SQL和PL/SQL命中率   

V$SGASTAT

点击(此处)折叠或打开

1. select * from v$sgastat

V$SYSSTAT

点击(此处)折叠或打开

1. 点击(此处)折叠或打开
   
2. select 1-(sum(decode(name, 'physical reads', value,0))/
   
3. (sum(decode(name, 'db block gets', value,0)) +
   
4. (sum(decode(name, 'consistent gets', value,0)))))
   
5. "Read Hit Ratio"
   
6. from v$sysstat;

"Read Hit Ratio"
0.9993513559939193853216038428998715967938

如果命中率低于95%，建议考虑增加DB_CACHE_SIZE值。

---

数据字典命中率

点击(此处)折叠或打开

1. select sum(gets), sum(getmisses),(1 - (sum(getmisses) / (sum(gets)
   
2. + sum(getmisses)))) * 100 HitRate
   
3. from v$rowcache;

"SUM(GETS)" "SUM(GETMISSES)" "HITRATE"
780511 14275 98.20391904235857199296414380726384209083

如果命中率低于95%，建议增加SHARED_POOL_SIZE值。

---

V$LIBRARYCACHE

点击(此处)折叠或打开

1. select sum(pins) "Executions", sum(pinhits) "Hits",
   
2. ((sum(pinhits) / sum(pins)) * 100) "PinHitRatio",
   
3. sum(reloads) "Misses", ((sum(pins) / (sum(pins)
   
4. + sum(reloads))) * 100) "RelHitRatio"
   
5. from v$librarycache;

Executions	Hits	PinHitRatio	Misses	RelHitRatio
4595289	4551871	99.0551628	21189	99.54101373

命中率应不低于99%，否则说明重载率过高。造成这一现象的原因可能是SQL绑定过高，可查看V$SQL_BIND_CAPTURE

点击(此处)折叠或打开

1. select sql_id, count(*) bind_count
   
2. from v$sql_bind_capture
   
3. where child_number = 0
   
4. group by sql_id
   
5. having count(*) > 20
   
6. order by count(*);

SQL_ID	BIND_COUNT
9qgtwh66xg6nz	21
c0agatqzq2jzr	25
g6r16rrup99u5	25

根据查出的SQL_ID，找出具体的SQL语句

点击(此处)折叠或打开

1. select sql_text, users_executing, executions, users_opening, buffer_gets
   
2. from v$sqlarea
   
3. where sql_id = 'g6r16rrup99u5'
   
4. order by buffer_gets;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22621861/viewspace-1283978/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22621861/viewspace-1283978/