美创科技技术社区

注册

 

发新话题 回复该主题

DBA_HIST_ACTIVE_SESS_HISTORY视图 [复制链接]

1#

DBA_HIST_ACTIVE_SESS_HISTORY视图:

SQL> desc DBA_HIST_ACTIVE_SESS_HISTORY
Name                      Type         Nullable Default Comments
————————- ———— ——– ——- ——–
SNAP_ID                   NUMBER       Y
DBID                      NUMBER       Y
INSTANCE_NUMBER           NUMBER       Y
SAMPLE_ID                 NUMBER       Y
SAMPLE_TIME               TIMESTAMP(3) Y
SESSION_ID                NUMBER       Y
SESSION_SERIAL#           NUMBER       Y
USER_ID                   NUMBER       Y
SQL_ID                    VARCHAR2(13) Y
SQL_CHILD_NUMBER          NUMBER       Y
SQL_PLAN_HASH_VALUE       NUMBER       Y
FORCE_MATCHING_SIGNATURE  NUMBER       Y
SQL_OPCODE                NUMBER       Y
PLSQL_ENTRY_OBJECT_ID     NUMBER       Y
PLSQL_ENTRY_SUBPROGRAM_ID NUMBER       Y
PLSQL_OBJECT_ID           NUMBER       Y
PLSQL_SUBPROGRAM_ID       NUMBER       Y
SERVICE_HASH              NUMBER       Y
SESSION_TYPE              VARCHAR2(10) Y
SESSION_STATE             VARCHAR2(7)  Y
QC_SESSION_ID             NUMBER       Y
QC_INSTANCE_ID            NUMBER       Y
BLOCKING_SESSION          NUMBER       Y
BLOCKING_SESSION_STATUS   VARCHAR2(11) Y
BLOCKING_SESSION_SERIAL#  NUMBER       Y
EVENT                     VARCHAR2(64) Y
EVENT_ID                  NUMBER       Y
SEQ#                      NUMBER       Y
P1TEXT                    VARCHAR2(64) Y
P1                        NUMBER       Y
P2TEXT                    VARCHAR2(64) Y
P2                        NUMBER       Y
P3TEXT                    VARCHAR2(64) Y
P3                        NUMBER       Y
WAIT_CLASS                VARCHAR2(64) Y
WAIT_CLASS_ID             NUMBER       Y
WAIT_TIME                 NUMBER       Y
TIME_WAITED               NUMBER       Y
XID                       RAW(8)       Y
CURRENT_OBJ#              NUMBER       Y
CURRENT_FILE#             NUMBER       Y
CURRENT_BLOCK#            NUMBER       Y
PROGRAM                   VARCHAR2(64) Y
MODULE                    VARCHAR2(48) Y
ACTION                    VARCHAR2(32) Y
CLIENT_ID                 VARCHAR2(64) Y

视图定义:

select ash.snap_id, ash.dbid, ash.instance_number,
ash.sample_id, ash.sample_time,
ash.session_id, ash.session_serial#, ash.user_id,
ash.sql_id, ash.sql_child_number,
ash.sql_plan_hash_value, ash.force_matching_signature, ash.sql_opcode,
decode(ash.plsql_entry_object_id,0,to_number(NULL),
ash.plsql_entry_object_id),
decode(ash.plsql_entry_object_id,0,to_number(NULL),
ash.plsql_entry_subprogram_id),
decode(ash.plsql_object_id,0,to_number(NULL),
ash.plsql_object_id),
decode(ash.plsql_object_id,0,to_number(NULL),
ash.plsql_subprogram_id),
ash.service_hash,
decode(ash.session_type, 1, ‘FOREGROUND’, 2, ‘BACKGROUND’, ‘UNKNOWN’),
decode(ash.wait_time, 0, ‘WAITING’, ‘ON CPU’),
decode(ash.qc_session_id, 0, to_number(NULL), ash.qc_session_id),
decode(ash.qc_session_id, 0, to_number(NULL), ash.qc_instance_id),
(case when ash.blocking_session between 4294967291 and 4294967295
then to_number(NULL)
else ash.blocking_session
end),
(case when ash.blocking_session = 4294967295
then ‘UNKNOWN’
when ash.blocking_session = 4294967294
then ‘GLOBAL’
when ash.blocking_session = 4294967293
then ‘UNKNOWN’
when ash.blocking_session = 4294967292
then ‘NO HOLDER’
when ash.blocking_session = 4294967291
then ‘NOT IN WAIT’
else ‘VALID’
end),
(case when ash.blocking_session between 4294967291 and 4294967295
then to_number(NULL)
else ash.blocking_session_serial#
end),
decode(ash.wait_time, 0, evt.event_name, NULL),
decode(ash.wait_time, 0, evt.event_id,   NULL),
ash.seq#,
evt.parameter1, ash.p1,
evt.parameter2, ash.p2,
evt.parameter3, ash.p3,
decode(ash.wait_time, 0, evt.wait_class,    NULL),
decode(ash.wait_time, 0, evt.wait_class_id, NULL),
ash.wait_time, ash.time_waited,
ash.xid,
ash.current_obj#, ash.current_file#, ash.current_block#,
ash.program, ash.module, ash.action, ash.client_id
from WRM$_SNAPSHOT sn, WRH$_ACTIVE_SESSION_HISTORY ash, WRH$_EVENT_NAME evt
where      ash.snap_id          = sn.snap_id
and  ash.dbid             = sn.dbid
and  ash.instance_number  = sn.instance_number
and  sn.status            = 0
and  sn.bl_moved          = 0
and  ash.dbid             = evt.dbid
and  ash.event_id         = evt.event_id
union all
select ash.snap_id, ash.dbid, ash.instance_number,
ash.sample_id, ash.sample_time,
ash.session_id, ash.session_serial#, ash.user_id,
ash.sql_id, ash.sql_child_number,
ash.sql_plan_hash_value, ash.force_matching_signature, ash.sql_opcode,
decode(ash.plsql_entry_object_id,0,to_number(NULL),
ash.plsql_entry_object_id),
decode(ash.plsql_entry_object_id,0,to_number(NULL),
ash.plsql_entry_subprogram_id),
decode(ash.plsql_object_id,0,to_number(NULL),
ash.plsql_object_id),
decode(ash.plsql_object_id,0,to_number(NULL),
ash.plsql_subprogram_id),
ash.service_hash,
decode(ash.session_type, 1, ‘FOREGROUND’, 2, ‘BACKGROUND’, ‘UNKNOWN’),
decode(ash.wait_time, 0, ‘WAITING’, ‘ON CPU’),
decode(ash.qc_session_id, 0, to_number(NULL), ash.qc_session_id),
decode(ash.qc_session_id, 0, to_number(NULL), ash.qc_instance_id),
(case when ash.blocking_session between 4294967291 and 4294967295
then to_number(NULL)
else ash.blocking_session
end),
(case when ash.blocking_session = 4294967295
then ‘UNKNOWN’
when ash.blocking_session = 4294967294
then ‘GLOBAL’
when ash.blocking_session = 4294967293
then ‘UNKNOWN’
when ash.blocking_session = 4294967292
then ‘NO HOLDER’
when ash.blocking_session = 4294967291
then ‘NOT IN WAIT’
else ‘VALID’
end),
(case when ash.blocking_session between 4294967291 and 4294967295
then to_number(NULL)
else ash.blocking_session_serial#
end),
decode(ash.wait_time, 0, evt.event_name, NULL),
decode(ash.wait_time, 0, evt.event_id,   NULL),
ash.seq#,
evt.parameter1, ash.p1,
evt.parameter2, ash.p2,
evt.parameter3, ash.p3,
decode(ash.wait_time, 0, evt.wait_class,    NULL),
decode(ash.wait_time, 0, evt.wait_class_id, NULL),
ash.wait_time, ash.time_waited,
ash.xid,
ash.current_obj#, ash.current_file#, ash.current_block#,
ash.program, ash.module, ash.action, ash.client_id
from WRM$_SNAPSHOT sn, WRH$_ACTIVE_SESSION_HISTORY_BL ash, WRH$_EVENT_NAME evt
where      ash.snap_id          = sn.snap_id
and  ash.dbid             = sn.dbid
and  ash.instance_number  = sn.instance_number
and  sn.status            = 0
and  sn.bl_moved          = 1
and  ash.dbid             = evt.dbid
and  ash.event_id         = evt.event_id

分享 转发
TOP
发新话题 回复该主题