常用v_视图

1&＃xff0c;找出锁定问题

SELECT /*&＃43; ordered */
 B.USERNAME, B.SERIAL#, D.ID1, A.SQL_TEXT
  FROM V$LOCK D
  JOIN V$SESSION B ON B.LOCKWAIT &＃61; D.KADDR
     JOIN V$SQLTEXT A ON A.ADDRESS &＃61; B.SQL_ADDRESS
 AND A.HASH_VALUE &＃61; B.SQL_HASH_VALUE;

2&＃xff0c;检查用户的当前操作及其使用的资源
SELECT A.SID, A.USERNAME, S.SQL_TEXT
  FROM V$SESSION A, V$SQLTEXT S
 WHERE A.SQL_ADDRESS &＃61; S.ADDRESS
   AND A.SQL_HASH_VALUE &＃61; S.HASH_VALUE
 ORDER BY A.USERNAME, A.SID, S.PIECE;

3&＃xff0c;查看v$sql_bind_capture,看看average binds是否大于15
SELECT sql_id,COUNT(*) bind_count FROM v$sql_bind_capture
WHERE child_number&＃61;0 GROUP BY sql_id HAVING COUNT(*) >20 ORDER BY COUNT(*) DESC;

4&＃xff0c;查看有问题的SQL并修复&＃xff1a;
SELECT sql_text,users_executing,executions,users_opening,buffer_gets
FROM v$sqlarea WHERE sql_id&＃61;&＃39;cprfzqn5y3w1h&＃39; ORDER BY buffer_gets;

SELECT SIZE_FOR_ESTIMATE,
       BUFFERS_FOR_ESTIMATE,
       ESTD_PHYSICAL_READ_FACTOR,
       ESTD_PHYSICAL_READS
  FROM V$DB_CACHE_ADVICE
 WHERE NAME &＃61; &＃39;DEFAULT&＃39;
   AND BLOCK_SIZE &＃61;
       (SELECT VALUE FROM V$PARAMETER WHERE NAME &＃61; &＃39;db_block_size&＃39;)
   AND ADVICE_STATUS &＃61; &＃39;ON&＃39;;

SELECT sum(gets),SUM(getmisses),(1-SUM(getmisses)/(SUM(gets)&＃43;SUM(getmisses))) * 100 HitRate FROM v$rowcache;