Search notes:

Oracle: Difference and commonalities between V$SQL and V$SQLAREA

v$sql stores statistics about each child version of an SQL statement that is stored in the Shared Pool while v$sqlarea summarizes these statistics per sqlid (or address, respectively).
v$sql selects from the underling x$ table x$kglcursor_child while v$sqlarea selects from x$kglcursor_child_sqlid.
v$sql_shared_cursor stores the reason why a particular child cursor is not shared with another version of the «same» SQL statement.

Columns

The following query selects the column names that are available in either or both of v$sql and v$sqlarea views. 
with
   a as (select column_name from dba_tab_columns where table_name = 'V_$SQL'     and owner = 'SYS'),
   b as (select column_name from dba_tab_columns where table_name = 'V_$SQLAREA' and owner = 'SYS')
select
   coalesce(a.column_name, b.column_name)            col_name,
   case when a.column_name is not null then 'y' end  in_v$sql,
   case when b.column_name is not null then 'y' end  in_v$sqlarea
from
   a full outer join b on a.column_name = b.column_name
order by
   coalesce(a.column_name, b.column_name)
Github repository oracle-patterns, path: /Installed/dynamic-performance-views/sql/diff-sql_sqlarea.sql
The second column in the following table indicates whether the corresponding column name occurs in v$sql, the third column whether it occurs in v$sqlarea.
ACTION ✓ ✓
ACTION_HASH ✓ ✓
ADDRESS ✓ ✓
APPLICATION_WAIT_TIME ✓ ✓
AVOIDED_EXECUTIONS ✓ records the number of attempted statement executions that were prevented by the resource manager (see also the column SQL_QUARANTINE).
BIND_DATA ✓ ✓ See also the sys.sql_bind thpe and dbms_sqltune.extract_bind() function.
BUFFER_GETS ✓ ✓
CHILD_ADDRESS ✓
CHILD_LATCH ✓ ✓
CHILD_NUMBER ✓
CLUSTER_WAIT_TIME ✓ ✓
COMMAND_TYPE ✓ ✓
CONCURRENCY_WAIT_TIME ✓ ✓
CON_ID ✓ ✓
CPU_TIME ✓ ✓
DDL_NO_INVALIDATE ✓
DIRECT_READS ✓ ✓
DIRECT_WRITES ✓ ✓
DISK_READS ✓ ✓
ELAPSED_TIME ✓ ✓
END_OF_FETCH_COUNT ✓ ✓
EXACT_MATCHING_SIGNATURE ✓ ✓ See Identifying SQL statements. Compare with columns SQL_ID and FORCE_MATCHING_SIGNATURE.
EXECUTIONS ✓ ✓
FETCHES ✓ ✓
FIRST_LOAD_TIME ✓ ✓
FORCE_MATCHING_SIGNATURE ✓ ✓ See Identifying SQL statements. Compare with columns SQL_ID and EXACT_MATCHING_SIGNATURE.
FULL_PLAN_HASH_VALUE ✓ ✓
HASH_VALUE ✓ ✓ This value is equal to dbms_utility.sqlid_to_sqlhash(sqlid)
IM_SCANS ✓
IM_SCAN_BYTES_INMEMORY ✓
IM_SCAN_BYTES_UNCOMPRESSED ✓
INVALIDATIONS ✓ ✓
IO_CELL_OFFLOAD_ELIGIBLE_BYTES ✓ ✓
IO_CELL_OFFLOAD_RETURNED_BYTES ✓ ✓
IO_CELL_UNCOMPRESSED_BYTES ✓ ✓
IO_INTERCONNECT_BYTES ✓ ✓
IS_BIND_AWARE ✓ ✓ Y or N. A cursor can only be bind aware if it is also bind sensitive.
IS_BIND_SENSITIVE ✓ ✓ Y or N. A cursor that is not bind sensitive cannot be bind aware.
IS_OBSOLETE ✓ ✓
IS_REOPTIMIZABLE ✓ ✓ Y or N. Used in adaptive query optimization. See also v$sql_reoptimization_hints.
IS_RESOLVED_ADAPTIVE_PLAN ✓ ✓ Y or N displays if all adaptive plan alternatives are resolved. null indicates that the plan is not adaptive.
IS_ROLLING_INVALID ✓
IS_ROLLING_REFRESH_INVALID ✓
IS_SHAREABLE ✓
JAVA_EXEC_TIME ✓ ✓
KEPT_VERSIONS ✓ ✓
LAST_ACTIVE_CHILD_ADDRESS ✓
LAST_ACTIVE_TIME ✓ ✓
LAST_LOAD_TIME ✓ ✓
LITERAL_HASH_VALUE ✓ ✓
LOADED_VERSIONS ✓ ✓
LOADS ✓ ✓
LOCKED_TOTAL ✓ ✓
MODULE ✓ ✓
MODULE_HASH ✓ ✓
OBJECT_STATUS ✓ ✓ Cursor status: VALID, VALID_AUTH_ERROR, VALID_COMPILE_ERROR, VALID_UNAUTH, INVALID_UNAUTH or INVALID.
OLD_HASH_VALUE ✓ ✓ Maintained for backward compatibility: before 10g, Oracle calculated an SQL's hash value differently.
OPEN_VERSIONS ✓ ✓
OPTIMIZED_PHY_READ_REQUESTS ✓ ✓
OPTIMIZER_COST ✓ ✓
OPTIMIZER_ENV ✓ ✓
OPTIMIZER_ENV_HASH_VALUE ✓ ✓
OPTIMIZER_MODE ✓ ✓
OUTLINE_CATEGORY ✓ ✓
OUTLINE_SID ✓ ✓
PARSE_CALLS ✓ ✓
PARSING_SCHEMA_ID ✓ ✓
PARSING_SCHEMA_NAME ✓ ✓
PARSING_USER_ID ✓ ✓
PERSISTENT_MEM ✓ ✓ The (fixed amount of) bytes that is required to store the details of a child cursor across one more more executions. See persistent area. Compare with columns RUNTIME_MEM and SHARABLE_MEM.
PHYSICAL_READ_BYTES ✓ ✓ PHYSICAL_READ_BYTES + PHYSICAL_WRITE_BYTES = IO_INTERCONNECT_BYTES + IO_CELL_OFFLOAD_RETURNED_BYTES?
PHYSICAL_READ_REQUESTS ✓ ✓
PHYSICAL_WRITE_BYTES ✓ ✓
PHYSICAL_WRITE_REQUESTS ✓ ✓
PINNED_TOTAL ✓ ✓
PLAN_HASH_VALUE ✓ ✓
PLSQL_EXEC_TIME ✓ ✓
PROGRAM_ID ✓ ✓
PROGRAM_LINE# ✓ ✓
PX_SERVERS_EXECUTIONS ✓ ✓
REMOTE ✓ ✓
RESULT_CACHE ✓
ROWS_PROCESSED ✓ ✓
RUNTIME_MEM ✓ ✓ The (fixed amount of) bytes that is required to execute an SQL statement (child cursor). See run-time area. Compare with columns PERSISTENT_MEM and SHARABLE_MEM.
SERIALIZABLE_ABORTS ✓ ✓
SERVICE ✓
SERVICE_HASH ✓
SHARABLE_MEM ✓ ✓ Compare with columns PERSISTENT_MEM and RUNTIME_MEM.
SORTS ✓ ✓
SQLTYPE ✓
SQL_FULLTEXT ✓ ✓ The complete SQL statement text (clob). Compare with v$sqltext and v$sqltext_with_newlines and SQL_TEXT.
SQL_ID ✓ ✓ See Identifying SQL statements. Compare with columns SQL_ID and EXACT_MATCHING_SIGNATURE.
SQL_PATCH ✓ ✓ SQL patch that was used or NULL if no patch was used.
SQL_PLAN_BASELINE ✓ ✓
SQL_PROFILE ✓ ✓
SQL_QUARANTINE ✓ Indicates whether a statement has been terminated by the resource manager because the statement consumed too many resources. See also the column AVOIDED_EXECUTIONS
SQL_TEXT ✓ ✓ The first 1000 characters of the SQL statement text (varchar2(1000)). Compare with SQL_FULLTEXT.
TYPECHECK_MEM ✓ ✓
TYPE_CHK_HEAP ✓
USERS_EXECUTING ✓ ✓
USERS_OPENING ✓ ✓
USER_IO_WAIT_TIME ✓ ✓
VERSION_COUNT ✓

See also

A subset of the of the columns of v$sql and v$sqlarea are also found in v$sqlstats
dynamic performance views

Index