oracle v$sql视图的使用

比​​v$sqlarea​​​视图详细，group by sql_id 之后就是​​v$sqlarea​​ 。

主要字段

主要字段有：
sql_text sql的前1000个字符
sql_id 关联字段
parsing_schema_name 相当于用户名
command_type 命令类型(int值)
last_active_time 最后活动时间

session视图关联sql视图

方案一

session.sql_address = sql.address，如下sql：

select
s2.*
from
v$session s  left join v$sql s2
on s.sql_address = s2.address
order by s2.last_active_time

方案二

都用sql_id关联，例如：

select
s2.*
from
v$session s  left join v$sql s2
on s.sql_id= s2.sql_id
order by s2.last_active_time

字段列表

COLUMN_NAME	DATA_TYPE
IO_CELL_OFFLOAD_RETURNED_BYTES	NUMBER
IO_CELL_UNCOMPRESSED_BYTES	NUMBER
PINNED_TOTAL	NUMBER
LOCKED_TOTAL	NUMBER
OPTIMIZED_PHY_READ_REQUESTS	NUMBER
PHYSICAL_WRITE_BYTES	NUMBER
PHYSICAL_WRITE_REQUESTS	NUMBER
PHYSICAL_READ_BYTES	NUMBER
PHYSICAL_READ_REQUESTS	NUMBER
IO_INTERCONNECT_BYTES	NUMBER
IO_CELL_OFFLOAD_ELIGIBLE_BYTES	NUMBER
TYPECHECK_MEM	NUMBER
BIND_DATA	RAW
LAST_ACTIVE_TIME	DATE
FORCE_MATCHING_SIGNATURE	NUMBER
EXACT_MATCHING_SIGNATURE	NUMBER
PROGRAM_LINE#	NUMBER
PROGRAM_ID	NUMBER
SQL_PLAN_BASELINE	VARCHAR2
SQL_PATCH	VARCHAR2
SQL_PROFILE	VARCHAR2
CHILD_LATCH	NUMBER
IS_SHAREABLE	VARCHAR2
IS_BIND_AWARE	VARCHAR2
IS_BIND_SENSITIVE	VARCHAR2
IS_OBSOLETE	VARCHAR2
LAST_LOAD_TIME	VARCHAR2
LITERAL_HASH_VALUE	NUMBER
OBJECT_STATUS	VARCHAR2
REMOTE	VARCHAR2
SQLTYPE	NUMBER
CHILD_ADDRESS	RAW
OUTLINE_SID	NUMBER
ELAPSED_TIME	NUMBER
CPU_TIME	NUMBER
OUTLINE_CATEGORY	VARCHAR2
SERIALIZABLE_ABORTS	NUMBER
ACTION_HASH	NUMBER
ACTION	VARCHAR2
MODULE_HASH	NUMBER
MODULE	VARCHAR2
SERVICE_HASH	NUMBER
SERVICE	VARCHAR2
CHILD_NUMBER	NUMBER
PLAN_HASH_VALUE	NUMBER
OLD_HASH_VALUE	NUMBER
HASH_VALUE	NUMBER
TYPE_CHK_HEAP	RAW
ADDRESS	RAW
KEPT_VERSIONS	NUMBER
PARSING_SCHEMA_NAME	VARCHAR2
PARSING_SCHEMA_ID	NUMBER
PARSING_USER_ID	NUMBER
OPTIMIZER_ENV_HASH_VALUE	NUMBER
OPTIMIZER_ENV	RAW
OPTIMIZER_COST	NUMBER
OPTIMIZER_MODE	VARCHAR2
COMMAND_TYPE	NUMBER
ROWS_PROCESSED	NUMBER
JAVA_EXEC_TIME	NUMBER
PLSQL_EXEC_TIME	NUMBER
USER_IO_WAIT_TIME	NUMBER
CLUSTER_WAIT_TIME	NUMBER
CONCURRENCY_WAIT_TIME	NUMBER
APPLICATION_WAIT_TIME	NUMBER
BUFFER_GETS	NUMBER
DIRECT_WRITES	NUMBER
DISK_READS	NUMBER
PARSE_CALLS	NUMBER
INVALIDATIONS	NUMBER
FIRST_LOAD_TIME	VARCHAR2
LOADS	NUMBER
USERS_EXECUTING	NUMBER
END_OF_FETCH_COUNT	NUMBER
PX_SERVERS_EXECUTIONS	NUMBER
EXECUTIONS	NUMBER
FETCHES	NUMBER
USERS_OPENING	NUMBER
OPEN_VERSIONS	NUMBER
LOADED_VERSIONS	NUMBER
SORTS	NUMBER
RUNTIME_MEM	NUMBER
PERSISTENT_MEM	NUMBER
SHARABLE_MEM	NUMBER
SQL_ID	VARCHAR2
SQL_FULLTEXT	CLOB
SQL_TEXT	VARCHAR2