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

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`		✓

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

Columns

See also