I have been not clear at all about what are the differences between v$sql and v$sqlarea view until I read a chapter in Christian Antognini's book "Troubleshooting Oracle Performance"
The points I have learned are:
- The result of a parse operation is a parent cursor and a child cursor stored in the library cache.
- Parent cursor: text of sql statement
- Child cursor : execution plan and excution environment
- v$sql gives information about child cursor
- v$sqlarea gives information about parenet cursor
Below I tried to compare the columns of these two views line by line in a 10g database, which shows the differenece quite obviously:
v$sql v$sqlarea
Name Type Name
----------------------------- --------------------- ---------------------------
CHILD_ADDRESS RAW(4)
CHILD_NUMBER NUMBER
SQLTYPE NUMBER
SERVICE VARCHAR2(64)
SERVICE_HASH NUMBER
TYPE_CHK_HEAP RAW(4)
-----------------------------------------------------------------------------------
VERSION_COUNT
LAST_ACTIVE_CHILD_ADDRESS
-----------------------------------------------------------------------------------
[ below is same ... ]
SQL_TEXT VARCHAR2(1000) SQL_TEXT
SQL_FULLTEXT CLOB SQL_FULLTEXT
SQL_ID VARCHAR2(13) SQL_ID
SHARABLE_MEM NUMBER SHARABLE_MEM
PERSISTENT_MEM NUMBER PERSISTENT_MEM
RUNTIME_MEM NUMBER RUNTIME_MEM
SORTS NUMBER SORTS
LOADED_VERSIONS NUMBER LOADED_VERSIONS
OPEN_VERSIONS NUMBER OPEN_VERSIONS
USERS_OPENING NUMBER USERS_OPENING
FETCHES NUMBER FETCHES
EXECUTIONS NUMBER EXECUTIONS
PX_SERVERS_EXECUTIONS NUMBER PX_SERVERS_EXECUTIONS
END_OF_FETCH_COUNT NUMBER END_OF_FETCH_COUNT
USERS_EXECUTING NUMBER USERS_EXECUTING
LOADS NUMBER LOADS
FIRST_LOAD_TIME VARCHAR2(19) FIRST_LOAD_TIME
INVALIDATIONS NUMBER INVALIDATIONS
PARSE_CALLS NUMBER PARSE_CALLS
DISK_READS NUMBER DISK_READS
DIRECT_WRITES NUMBER DIRECT_WRITES
BUFFER_GETS NUMBER BUFFER_GETS
APPLICATION_WAIT_TIME NUMBER APPLICATION_WAIT_TIME
CONCURRENCY_WAIT_TIME NUMBER CONCURRENCY_WAIT_TIME
CLUSTER_WAIT_TIME NUMBER CLUSTER_WAIT_TIME
USER_IO_WAIT_TIME NUMBER USER_IO_WAIT_TIME
PLSQL_EXEC_TIME NUMBER PLSQL_EXEC_TIME
JAVA_EXEC_TIME NUMBER JAVA_EXEC_TIME
ROWS_PROCESSED NUMBER ROWS_PROCESSED
COMMAND_TYPE NUMBER COMMAND_TYPE
OPTIMIZER_MODE VARCHAR2(10) OPTIMIZER_MODE
OPTIMIZER_COST NUMBER OPTIMIZER_COST
OPTIMIZER_ENV RAW(797) OPTIMIZER_ENV
OPTIMIZER_ENV_HASH_VALUE NUMBER OPTIMIZER_ENV_HASH_VALUE
PARSING_USER_ID NUMBER PARSING_USER_ID
PARSING_SCHEMA_ID NUMBER PARSING_SCHEMA_ID
PARSING_SCHEMA_NAME VARCHAR2(30) PARSING_SCHEMA_NAME
KEPT_VERSIONS NUMBER KEPT_VERSIONS
ADDRESS RAW(4) ADDRESS
HASH_VALUE NUMBER HASH_VALUE
OLD_HASH_VALUE NUMBER OLD_HASH_VALUE
PLAN_HASH_VALUE NUMBER PLAN_HASH_VALUE
MODULE VARCHAR2(64) MODULE
MODULE_HASH NUMBER MODULE_HASH
ACTION VARCHAR2(64) ACTION
ACTION_HASH NUMBER ACTION_HASH
SERIALIZABLE_ABORTS NUMBER SERIALIZABLE_ABORTS
OUTLINE_CATEGORY VARCHAR2(64) OUTLINE_CATEGORY
CPU_TIME NUMBER CPU_TIME
ELAPSED_TIME NUMBER ELAPSED_TIME
OUTLINE_SID NUMBER OUTLINE_SID
REMOTE VARCHAR2(1) REMOTE
OBJECT_STATUS VARCHAR2(19) OBJECT_STATUS
LITERAL_HASH_VALUE NUMBER LITERAL_HASH_VALUE
LAST_LOAD_TIME VARCHAR2(19) LAST_LOAD_TIME
IS_OBSOLETE VARCHAR2(1) IS_OBSOLETE
CHILD_LATCH NUMBER CHILD_LATCH
SQL_PROFILE VARCHAR2(64) SQL_PROFILE
PROGRAM_ID NUMBER PROGRAM_ID
PROGRAM_LINE# NUMBER PROGRAM_LINE#
EXACT_MATCHING_SIGNATURE NUMBER EXACT_MATCHING_SIGNATURE
FORCE_MATCHING_SIGNATURE NUMBER FORCE_MATCHING_SIGNATURE
LAST_ACTIVE_TIME DATE LAST_ACTIVE_TIME
BIND_DATA RAW(2000) BIND_DATA
1 comment:
thanks for the info.
It helped me do a join between
v$active_session_history and v$sqlarea instead of v$sql.
-- V$sql had many child_address for the same sql_id
Post a Comment