SELECT view_definition FROM v$fixed_view_definition WHERE view_name='GV$SQL';
SELECT view_definition FROM v$fixed_view_definition WHERE view_name='GV$SQLAREA';
SELECT view_definition FROM v$fixed_view_definition WHERE view_name='GV$SQLTEXT';
这里可以看到每个视图的定义,可以发现者三个视图分别基于下面三个表:
x$kglcursor_child
x$kglcursor_child_sqlid
x$kglna
--------v$sqltext 存储的是完整的SQL,SQL被分割
SQL> desc v$sqltext Name Null? Type ----------------------------------------- -------- ---------------------------- ADDRESS RAW(4) --------- HASH_VALUE NUMBER --------- 和 address 一起唯一标志一条sql COMMAND_TYPE NUMBER PIECE NUMBER ---------- 分片之后的顺序编号 SQL_TEXT VARCHAR2(64) -------------- 注意长度
-------v$sqlarea ----存储的SQL 和一些相关的信息,比如累计的执行次数,逻辑读,物理读等统计信息 SQL> desc v$sqlarea Name Null? Type ----------------------------------------- -------- ---------------------------- SQL_TEXT VARCHAR2(1000) 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 USERS_EXECUTING NUMBER LOADS NUMBER FIRST_LOAD_TIME VARCHAR2(38) INVALIDATIONS NUMBER PARSE_CALLS NUMBER DISK_READS NUMBER BUFFER_GETS NUMBER ROWS_PROCESSED NUMBER COMMAND_TYPE NUMBER OPTIMIZER_MODE VARCHAR2(25) PARSING_USER_ID NUMBER PARSING_SCHEMA_ID NUMBER KEPT_VERSIONS NUMBER ADDRESS RAW(4) HASH_VALUE NUMBER MODULE VARCHAR2(64) MODULE_HASH NUMBER ACTION VARCHAR2(64) ACTION_HASH NUMBER SERIALIZABLE_ABORTS NUMBER CPU_TIME NUMBER ELAPSED_TIME NUMBER IS_OBSOLETE VARCHAR2(1) CHILD_LATCH NUMBER
------v$sql --- 存储的是具体的SQL 和执行计划相关信息,实际上,v$sqlarea 可以看做 v$sql 根据 sqltext 等 做了 group by 之后的信息
Column
Datatype
Descrption
SQL_TEXT
VARCHAR2(1000)
当前SQL指针的前1000个字符(也就是说这里记录的SQL是不完整的)
EXECUTIONS
NUMBER
执行次数
DISK_READS
NUMBER
这个子指针Disk Read的次数
BUFFER_GETS
NUMBER
这个子指针的Buffer Gets数量
OPTIMIZER_MODE
VARCHAR2(10)
SQL执行的优化器模式
OPTIMIZER_COST
NUMBER
SQL执行成本
HASH_VALUE
NUMBER
在Library Cache中父指针的Hash Value值
SQL> desc v$sql Name Null? Type ----------------------------------------- -------- ---------------------------- SQL_TEXT VARCHAR2(1000) SHARABLE_MEM NUMBER PERSISTENT_MEM NUMBER RUNTIME_MEM NUMBER SORTS NUMBER LOADED_VERSIONS NUMBER OPEN_VERSIONS NUMBER USERS_OPENING NUMBER FETCHES NUMBER EXECUTIONS NUMBER USERS_EXECUTING NUMBER LOADS NUMBER FIRST_LOAD_TIME VARCHAR2(38) INVALIDATIONS NUMBER PARSE_CALLS NUMBER DISK_READS NUMBER BUFFER_GETS NUMBER ROWS_PROCESSED NUMBER COMMAND_TYPE NUMBER OPTIMIZER_MODE VARCHAR2(10) OPTIMIZER_COST NUMBER PARSING_USER_ID NUMBER PARSING_SCHEMA_ID NUMBER KEPT_VERSIONS NUMBER ADDRESS RAW(4) TYPE_CHK_HEAP RAW(4) HASH_VALUE NUMBER PLAN_HASH_VALUE NUMBER CHILD_NUMBER 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 NUMBER -------------- 注意这里跟 outline 有关 CHILD_ADDRESS RAW(4) SQLTYPE NUMBER REMOTE VARCHAR2(1) OBJECT_STATUS VARCHAR2(19) LITERAL_HASH_VALUE NUMBER LAST_LOAD_TIME VARCHAR2(38) IS_OBSOLETE VARCHAR2(1) CHILD_LATCH NUMBER
另外注意这个 QL> desc v$sql_plan Name Null? Type ----------------------------------------- -------- ---------------------------- ADDRESS RAW(4) HASH_VALUE NUMBER CHILD_NUMBER NUMBER ------------ 注意这个和 v$sql 里面的相同字段 一个SQL语句可能有多个子游标(Child Cursor);如果不指定值,会罗列出所有子游标的执行计划。 OPERATION VARCHAR2(60) OPTIONS VARCHAR2(60) OBJECT_NODE VARCHAR2(20) OBJECT# NUMBER OBJECT_OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(64) OPTIMIZER VARCHAR2(40) ID NUMBER PARENT_ID NUMBER DEPTH NUMBER POSITION NUMBER SEARCH_COLUMNS NUMBER COST NUMBER CARDINALITY NUMBER BYTES NUMBER OTHER_TAG VARCHAR2(70) PARTITION_START VARCHAR2(10) PARTITION_STOP VARCHAR2(10) PARTITION_ID NUMBER OTHER VARCHAR2(4000) DISTRIBUTION VARCHAR2(40) CPU_COST NUMBER IO_COST NUMBER TEMP_SPACE NUMBER ACCESS_PREDICATES VARCHAR2(4000) FILTER_PREDICATES VARCHAR2(4000)
1、V$SQL,V$SQLAREA 源都是一个。 SELECT view_definition FROM v$fixed_view_definition WHERE view_name='GV$SQL'; SELECT view_definition FROM v$fixed_view_definition WHERE view_name='GV$SQL_AREA';