【Oracle九大效能檢視】之2.v$sqlarea_查效能SQL
1、v$sqlarea表的結構 原文出自飛鷹工作室
SQL> desc v$sqlarea
名稱 是否為空? 型別
----------------------------------------- -------- ------------------------
SQL_TEXT VARCHAR2(1000)
SQL_FULLTEXT CLOB
SQL_ID VARCHAR2(13)
SHARABLE_MEM NUMBER
PERSISTENT_MEM NUMBER
RUNTIME_MEM NUMBER
SORTS NUMBER
VERSION_COUNT NUMBER
LOADED_VERSIONS NUMBER
OPEN_VERSIONS NUMBER
USERS_OPENING NUMBER
FETCHES NUMBER
EXECUTIONS NUMBER
PX_SERVERS_EXECUTIONS NUMBER
END_OF_FETCH_COUNT NUMBER
USERS_EXECUTING NUMBER
LOADS NUMBER
FIRST_LOAD_TIME VARCHAR2(38)
INVALIDATIONS NUMBER
PARSE_CALLS NUMBER
DISK_READS NUMBER
DIRECT_WRITES NUMBER
BUFFER_GETS NUMBER
APPLICATION_WAIT_TIME NUMBER
CONCURRENCY_WAIT_TIME NUMBER
CLUSTER_WAIT_TIME NUMBER
USER_IO_WAIT_TIME NUMBER
PLSQL_EXEC_TIME NUMBER
JAVA_EXEC_TIME NUMBER
ROWS_PROCESSED NUMBER
COMMAND_TYPE NUMBER
OPTIMIZER_MODE VARCHAR2(10)
OPTIMIZER_COST NUMBER
OPTIMIZER_ENV RAW(882)
OPTIMIZER_ENV_HASH_VALUE NUMBER
PARSING_USER_ID NUMBER
PARSING_SCHEMA_ID NUMBER
PARSING_SCHEMA_NAME VARCHAR2(30)
KEPT_VERSIONS NUMBER
ADDRESS RAW(8)
HASH_VALUE NUMBER
OLD_HASH_VALUE NUMBER
PLAN_HASH_VALUE NUMBER
MODULE VARCHAR2(64)
MODULE_HASH NUMBER
ACTION VARCHAR2(64)
ACTION_HASH NUMBER
SERIALIZABLE_ABORTS NUMBER
OUTLINE_CATEGORY VARCHAR2(64)
CPU_TIME NUMBER
ELAPSED_TIME NUMBER
OUTLINE_SID VARCHAR2(40)
LAST_ACTIVE_CHILD_ADDRESS RAW(8)
REMOTE VARCHAR2(1)
OBJECT_STATUS VARCHAR2(19)
LITERAL_HASH_VALUE NUMBER
LAST_LOAD_TIME DATE
IS_OBSOLETE VARCHAR2(1)
CHILD_LATCH NUMBER
SQL_PROFILE VARCHAR2(64)
PROGRAM_ID NUMBER
PROGRAM_LINE# NUMBER
EXACT_MATCHING_SIGNATURE NUMBER
FORCE_MATCHING_SIGNATURE NUMBER
LAST_ACTIVE_TIME DATE
BIND_DATA RAW(2000)
TYPECHECK_MEM NUMBER
2、查V$SQLAREA表相關資訊語句
select sql_id,address,hash_value,
executions as 累計的執行次數executions,
buffer_gets as 邏輯讀buffer_gets,
disk_reads as 物理讀disk_reads,
sql_text
from v$sqlarea
order by buffer_gets desc;
備註:sql_text欄位存的這個sql的前1000個字元。查詢整個的sql還需去v$sqltext或者v$sqltext_with_newlines。
如:
select * from v$sqltext where sql_id='685jucmq3q7nd';
select * from v$sqltext_with_newlines where sql_id='685jucmq3q7nd';
SQL> desc v$sqlarea
名稱 是否為空? 型別
----------------------------------------- -------- ------------------------
SQL_TEXT VARCHAR2(1000)
SQL_FULLTEXT CLOB
SQL_ID VARCHAR2(13)
SHARABLE_MEM NUMBER
PERSISTENT_MEM NUMBER
RUNTIME_MEM NUMBER
SORTS NUMBER
VERSION_COUNT NUMBER
LOADED_VERSIONS NUMBER
OPEN_VERSIONS NUMBER
USERS_OPENING NUMBER
FETCHES NUMBER
EXECUTIONS NUMBER
PX_SERVERS_EXECUTIONS NUMBER
END_OF_FETCH_COUNT NUMBER
USERS_EXECUTING NUMBER
LOADS NUMBER
FIRST_LOAD_TIME VARCHAR2(38)
INVALIDATIONS NUMBER
PARSE_CALLS NUMBER
DISK_READS NUMBER
DIRECT_WRITES NUMBER
BUFFER_GETS NUMBER
APPLICATION_WAIT_TIME NUMBER
CONCURRENCY_WAIT_TIME NUMBER
CLUSTER_WAIT_TIME NUMBER
USER_IO_WAIT_TIME NUMBER
PLSQL_EXEC_TIME NUMBER
JAVA_EXEC_TIME NUMBER
ROWS_PROCESSED NUMBER
COMMAND_TYPE NUMBER
OPTIMIZER_MODE VARCHAR2(10)
OPTIMIZER_COST NUMBER
OPTIMIZER_ENV RAW(882)
OPTIMIZER_ENV_HASH_VALUE NUMBER
PARSING_USER_ID NUMBER
PARSING_SCHEMA_ID NUMBER
PARSING_SCHEMA_NAME VARCHAR2(30)
KEPT_VERSIONS NUMBER
ADDRESS RAW(8)
HASH_VALUE NUMBER
OLD_HASH_VALUE NUMBER
PLAN_HASH_VALUE NUMBER
MODULE VARCHAR2(64)
MODULE_HASH NUMBER
ACTION VARCHAR2(64)
ACTION_HASH NUMBER
SERIALIZABLE_ABORTS NUMBER
OUTLINE_CATEGORY VARCHAR2(64)
CPU_TIME NUMBER
ELAPSED_TIME NUMBER
OUTLINE_SID VARCHAR2(40)
LAST_ACTIVE_CHILD_ADDRESS RAW(8)
REMOTE VARCHAR2(1)
OBJECT_STATUS VARCHAR2(19)
LITERAL_HASH_VALUE NUMBER
LAST_LOAD_TIME DATE
IS_OBSOLETE VARCHAR2(1)
CHILD_LATCH NUMBER
SQL_PROFILE VARCHAR2(64)
PROGRAM_ID NUMBER
PROGRAM_LINE# NUMBER
EXACT_MATCHING_SIGNATURE NUMBER
FORCE_MATCHING_SIGNATURE NUMBER
LAST_ACTIVE_TIME DATE
BIND_DATA RAW(2000)
TYPECHECK_MEM NUMBER
2、查V$SQLAREA表相關資訊語句
select sql_id,address,hash_value,
executions as 累計的執行次數executions,
buffer_gets as 邏輯讀buffer_gets,
disk_reads as 物理讀disk_reads,
sql_text
from v$sqlarea
order by buffer_gets desc;
備註:sql_text欄位存的這個sql的前1000個字元。查詢整個的sql還需去v$sqltext或者v$sqltext_with_newlines。
如:
select * from v$sqltext where sql_id='685jucmq3q7nd';
select * from v$sqltext_with_newlines where sql_id='685jucmq3q7nd';
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/23577591/viewspace-682547/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 【Oracle九大效能檢視】之3.v$session_關於與其它效能檢視表聯合查問題SQLOracleSessionSQL
- oracle效能檢視Oracle
- 查詢oracle效能SQLOracleSQL
- Oracle日常效能檢視Oracle
- 【Oracle九大效能檢視】之6.v$process檢視Oracle
- Oracle檢視:常用動態效能檢視Oracle
- oracle v$sysstat效能檢視Oracle
- 【TUNE_ORACLE】Oracle檢查點(二)檢查點效能Oracle
- 檢視SQL Server 效能優化工具SQLServer優化
- awr 中單個SQL 效能檢視SQL
- Oracle中效能檢視V$SYSSTATOracle
- (轉)Oracle 動態效能檢視Oracle
- 【SQL】Oracle查詢轉換之檢視合併SQLOracle
- (轉)Oracle動態效能檢視學習之v$processOracle
- Oracle動態效能檢視學習之 V$ROLLSTAT -- 轉Oracle
- Mongo效能檢查Go
- Oracle日常效能檢視常用語句Oracle
- Oracle九大效能檢視 之一Oracle
- Oracle效能診斷檢視總結Oracle
- 【SQL】Oracle查詢轉換之物化檢視查詢重寫SQLOracle
- Oracle動態效能檢視學習之v$sqltext & v$sqlareaOracleSQL
- Oracle動態效能檢視之v$session_longops ztOracleSessionGo
- 通過 v$sqlarea 查詢disk read嚴重(I/O)的SQL-- Oracle效能檢視SQLOracle
- oracle-一些檢視效能相關的檢視Oracle
- 通過Oracle動態效能檢視採集查詢調優數Oracle
- 提高SQL查詢效能SQL
- SQL查詢效能分析SQL
- oracle 資料庫效能健康檢查指令碼Oracle資料庫指令碼
- 動態效能檢視
- Oracle 記憶體使用建議效能檢視Oracle記憶體
- oracle一些效能檢視的解釋Oracle
- Oracle效能問題檢查 - 常用查詢指令碼(final)Oracle指令碼
- 檢查 os 效能(linux)Linux
- Oracle動態效能檢視學習之v$lock & v$locked_objectOracleObject
- (轉)Oracle動態效能檢視學習之V$DB_OBJECT_CACHEOracleObject
- (轉)oracle 資料庫效能健康檢查指令碼Oracle資料庫指令碼
- ASM動態效能檢視ASM
- Oracle SQL效能優化OracleSQL優化