使用V$檢視觀察Oracle執行狀況

hooca發表於2014-09-28
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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章