PLSQL_ENTRY_SUBPROGRAM_ID NUMBER
PLSQL_OBJECT_ID NUMBER
PLSQL_SUBPROGRAM_ID NUMBER
FIRST_REFRESH_TIME DATE
LAST_REFRESH_TIME DATE
REFRESH_COUNT NUMBER
SID NUMBER
PROCESS_NAME VARCHAR2(5)
SQL_ID VARCHAR2(13)
SQL_TEXT VARCHAR2(2000)
IS_FULL_SQLTEXT VARCHAR2(1)
SQL_EXEC_START DATE
SQL_EXEC_ID NUMBER
SQL_PLAN_HASH_VALUE NUMBER
EXACT_MATCHING_SIGNATURE NUMBER
FORCE_MATCHING_SIGNATURE NUMBER
SQL_CHILD_ADDRESS RAW(8)
SESSION_SERIAL# NUMBER
PX_IS_CROSS_INSTANCE VARCHAR2(1)
PX_MAXDOP NUMBER
PX_MAXDOP_INSTANCES NUMBER
PX_SERVERS_REQUESTED NUMBER
PX_SERVERS_ALLOCATED NUMBER
PX_SERVER# NUMBER
PX_SERVER_GROUP NUMBER
PX_SERVER_SET NUMBER
PX_QCINST_ID NUMBER
PX_QCSID NUMBER
ERROR_NUMBER VARCHAR2(40)
ERROR_FACILITY VARCHAR2(4)
ERROR_MESSAGE VARCHAR2(256)
BINDS_XML CLOB
OTHER_XML CLOB
ELAPSED_TIME NUMBER
QUEUING_TIME NUMBER
CPU_TIME NUMBER
FETCHES NUMBER
BUFFER_GETS NUMBER
DISK_READS NUMBER
DIRECT_WRITES NUMBER
IO_INTERCONNECT_BYTES NUMBER
PHYSICAL_READ_REQUESTS NUMBER
PHYSICAL_READ_BYTES NUMBER
PHYSICAL_WRITE_REQUESTS NUMBER
PHYSICAL_WRITE_BYTES NUMBER
APPLICATION_WAIT_TIME NUMBER
CONCURRENCY_WAIT_TIME NUMBER
CLUSTER_WAIT_TIME NUMBER
USER_IO_WAIT_TIME NUMBER
PLSQL_EXEC_TIME NUMBER
JAVA_EXEC_TIME NUMBER
RM_LAST_ACTION VARCHAR2(48)
RM_LAST_ACTION_REASON VARCHAR2(30)
RM_LAST_ACTION_TIME DATE
RM_CONSUMER_GROUP VARCHAR2(30)
V$SQL_MONITOR 收集的信息每秒刷新一次，接近实时，当 SQL 执行完毕，信息并不会
立即从 v$sql_monitor 中删除，至少会保留 1 分钟，v$sql_plan_monitor 视图中的执行计划信息
也是每秒更新一次，当 SQL 执行完结，它们同样至少被保留 1 分钟。
实时 SQL 监控需要 statistics_level 初始化参数设置为 TYPICAL 或 ALL：
idle> show parameter statistic
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_pending_statistics boolean FALSE
statistics_level string TYPICAL
timed_os_statistics integer 0
timed_statistics boolean TRUE
同 CONTROL_MANAGEMENT_PACK_ACCESS 参数必须是 DIAGNOSTIC+TUNING（这是缺省设置）：
idle> show parameter control_managemen
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_management_pack_access string DIAGNOSTIC+TUNING
idle> SELECT statistics_name,session_status,system_status,activation_level,session_settable FROM v$statistics_level WHERE statistics_name = 'SQL Monitoring';
STATISTICS_NAME SESSION_ SYSTEM_S ACTIVAT SES
---------------------------------------------------------------- -------- -------- ------- ---
SQL Monitoring ENABLED ENABLED TYPICAL YES
，数据库会启动自动的实时 SQL 监控，Oracle 还提供 Hints 可以强制制定对
SQL 执行监控或者不允许执行监控，这两个 Hints 是 monitor 与 no_monitor。
select /*+ monitor */ count(*) from scott.emp where sal > 5000;
select /*+ no_monitor */ count(*) from scott.emp where sal >5000;
查看监控信息
idle> set long 10000000
idle> set longchunksize 10000000
idle> set linesize 200
idle> select /*+ monitor */ count(*) from scott.emp where sal > 5000;
COUNT(*)
----------
0
idle> select dbms_sqltune.report_sql_monitor from dual;
REPORT_SQL_MONITOR
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL Monitoring Report
SQL Text
------------------------------
select /*+ monitor */ count(*) from scott.emp where sal > 5000
Global Information
------------------------------
Status : DONE (ALL