老是在用ASH,昨天心血来潮,想看看ash视图里面是怎么样的,过程也算曲折,不过也算抛砖引玉。
先看看v$active_session_history的情况。
-->
是个同义词
SQL> select owner,object_name,object_type from dba_objects where object_name='V$ACTIVE_SESSION_HISTORY';
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------ -------------------
PUBLIC V$ACTIVE_SESSION_HISTORY
SYNONYM
-->到同义词视图里去看,是从哪来的。找到了V_$ACTIVE_SESSION_HISTORY
SQL> select *from Dba_synonyms where synonym_name='V$ACTIVE_SESSION_HISTORY';
OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK
------------------------------ ------------------------------ ------------------------------ ------------------------------ --------------------
PUBLIC V$ACTIVE_SESSION_HISTORY SYS V_$ACTIVE_SESSION_HISTORY
-->查看
V_$ACTIVE_SESSION_HISTORY,感觉要找到了,是个视图。
select owner,object_name,object_type from dba_objects where object_name='V_$ACTIVE_SESSION_HISTORY';
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------ -------------------
SYS V_$ACTIVE_SESSION_HISTORY VIEW
-->然后查看视图代码,感觉应该找到了。但是一看基表,怎么还是v$active_session_history,又回到原点了。
1* select owner,view_name,text from dba_views where view_name='V_$ACTIVE_SESSION_HISTORY'
SQL> /
SYS V_$ACTIVE_SESSION_HISTORY select "SAMPLE_ID","SAMPLE_TIME","IS_AWR_SAMPLE","SESSION_ID","SESSION_SERIAL#",
"SESSION_TYPE","FLAGS","USER_ID","SQL_ID","IS_SQLID_CURRENT","SQL_CHILD_NUMBER",
"SQL_OPCODE","SQL_OPNAME","FORCE_MATCHING_SIGNATURE","TOP_LEVEL_SQL_ID","TOP_LEV
EL_SQL_OPCODE","SQL_PLAN_HASH_VALUE","SQL_PLAN_LINE_ID","SQL_PLAN_OPERATION","SQ
L_PLAN_OPTIONS","SQL_EXEC_ID","SQL_EXEC_START","PLSQL_ENTRY_OBJECT_ID","PLSQL_EN
TRY_SUBPROGRAM_ID","PLSQL_OBJECT_ID","PLSQL_SUBPROGRAM_ID","QC_INSTANCE_ID","QC_
SESSION_ID","QC_SESSION_SERIAL#","PX_FLAGS","EVENT","EVENT_ID","EVENT#","SEQ#","
P1TEXT","P1","P2TEXT","P2","P3TEXT","P3","WAIT_CLASS","WAIT_CLASS_ID","WAIT_TIME
","SESSION_STATE","TIME_WAITED","BLOCKING_SESSION_STATUS","BLOCKING_SESSION","BL
OCKING_SESSION_SERIAL#","BLOCKING_INST_ID","BLOCKING_HANGCHAIN_INFO","CURRENT_OB
J#","CURRENT_FILE#","CURRENT_BLOCK#","CURRENT_ROW#","TOP_LEVEL_CALL#","TOP_LEVEL
_CALL_NAME","CONSUMER_GROUP_ID","XID","REMOTE_INSTANCE#","TIME_MODEL","IN_CONNEC
TION_MGMT","IN_PARSE","IN_HARD_PARSE","IN_SQL_EXECUTION","IN_PLSQL_EXECUTION","I
N_PLSQL_RPC","IN_PLSQL_COMPILATION","IN_JAVA_EXECUTION","IN_BIND","IN_CURSOR_CLO
SE","IN_SEQUENCE_LOAD","CAPTURE_OVERHEAD","REPLAY_OVERHEAD","IS_CAPTURED","IS_RE
PLAYED","SERVICE_HASH","PROGRAM","MODULE","ACTION","CLIENT_ID","MACHINE","PORT",
"ECID","DBREPLAY_FILE_ID","DBREPLAY_CALL_COUNTER","TM_DELTA_TIME","TM_DELTA_CPU_
TIME","TM_DELTA_DB_TIME","DELTA_TIME","DELTA_READ_IO_REQUESTS","DELTA_WRITE_IO_R
EQUESTS","DELTA_READ_IO_BYTES","DELTA_WRITE_IO_BYTES","DELTA_INTERCONNECT_IO_BYT
ES","PGA_ALLOCATED","TEMP_SPACE_ALLOCATED" from
v$active_session_history
-->这条路貌似不通,因为动态性能视图都是基于内存的。查查
v$fixed_view_definition
原来基于
GV$ACTIVE_SESSION_HISTORY ,这种视图在rac环境中比较常用。继续查找。
select *from v$fixed_view_definition where view_name='V$ACTIVE_SESSION_HISTORY';
VIEW_NAME
------------------------------
VIEW_DEFINITION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
V$ACTIVE_SESSION_HISTORY
SELECT sample_id, sample_time, is_awr_sample, session_id, session_serial#, session_type, flags, user_id, sql_id, is_sqlid_current, sql_child_number, sql_opcode, sql_opname, force_matching_signature, t
op_level_sql_id, top_level_sql_opcode, sql_plan_hash_value, sql_plan_line_id, sql_plan_operation, sql_plan_options, sql_exec_id, sql_exec_start, plsql_entry_object_id, plsql_entry_subprogram_id, plsql
_object_id, plsql_subprogram_id, qc_instance_id, qc_session_id, qc_session_serial#, px_flags, event, event_id, event#, seq#, p1text, p1, p2text, p2, p3text, p3, wait_class, wait_class_id, wait_time, s
ession_state, time_waited, blocking_session_status, blocking_session, blocking_session_serial#, blocking_inst_id, blocking_hangchain_info, current_obj#, current_file#, current_block#, current_row#, to
p_level_call#, top_level_call_name, consumer_group_id, xid, remote_instance#, time_model, in_connection_mgmt, in_parse, in_hard_parse, in_sql_execution, in_plsql_execution, in_plsql_rpc, in_plsql_comp
ilation, in_java_execution, in_bind, in_cursor_close, in_sequence_load, capture_overhead, replay_overhead, is_captured, is_replayed, service_hash, program, module, action, client_id, machine, port, ec
id, dbreplay_file_id, dbreplay_call_counter, tm_delta_time, tm_delta_cpu_time, tm_delta_db_time, delta_time, delta_read_io_requests, delta_write_io_requests, delta_read_io_bytes, delta_write_io_bytes,
delta_interconnect_io_bytes, pga_allocated, temp_space_allocated FROM
GV$ACTIVE_SESSION_HISTORY WHERE inst_id = USERENV('INSTANCE')
-->查找,是个同义词,继续查
select owner,object_name,object_type from dba_objects where object_name='GV$ACTIVE_SESSION_HISTORY';
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------ -------------------
PUBLIC GV$ACTIVE_SESSION_HISTORY SYNONYM
-->找到如下的内容,set long设置到最大了还是看不到“幕后”的基表
select VIEW_DEFINITION from v$fixed_view_definition where view_name='GV$ACTIVE_SESSION_HISTORY';
SELECT /*+ no_merge ordered use_nl(s,a) */ a.inst_id, s.sample_id, s.sample_time, s.is_awr_sample, a.session_id, a.session_serial#, decode(a.session_type, 1, 'FOREGROUND', 'BACKGROUND'), a.flags, a.u
ser_id, a.sql_id, decode(bitand(a.flags, power(2, 4)), NULL, 'N', 0, 'N', 'Y'), a.sql_child_number, a.sql_opcode, a.force_matching_signature, decode(a.top_level_sql_id, NULL, a.sql_id, a.top_level_sql
_id), decode(a.top_level_sql_id, NULL, a.sql_opcode, a.top_level_sql_opcode), a.sql_opname,a.sql_plan_hash_value, decode(a.sql_plan_operation, NULL, to_number(NULL), a.sql_plan_line_id),
a.sql_plan_operation, a.sql_plan_options, decode(a.sql_exec_id, 0, to_number(NULL), a.sql_exec_id), a.sql_exec_start, decode(a.plsql_entry_object_id,0,to_number(NULL), a.plsql_entry_object_id),
decode(a.plsql_entry_object_id,0,to_number(NULL), a.plsql_entry_subprogram_id), decode(a.plsql_object_id,0,to_number(NULL),a.plsql_object_id), decode(a.plsql_object_id,0,to_number(NULL),a.plsql_
subprogram_id), decode(a.qc_session_id, 0, to_number(NULL), a.qc_instance_id), decode(a.qc_session_id, 0, to_number(NULL), a.qc_session_id), decode(a.qc_session_id, 0, to_number(NULL), a.qc_session_se
rial#), decode(a.px_flags, 0, to_number(NULL), a.px_flags), decode(a.wait_time, 0, a.event, NULL), decode(a.wait_time, 0, a.event_id, NULL), decode(a.wait_time, 0, a.event#, NULL), a.seq#, a.p1te
xt, a.p1, a.p2text, a.p2, a.p3text, a.p3, decode(a.wait_time, 0, a.wait_class, NULL), decode(a.wait_time, 0, a.wait_class_id, NULL), a.wait_time, decode(a.wait_time, 0, 'WAITING', 'ON CPU'), a.time
_waited, (case when a.blocking_session = 4294967295 then 'UNKNOWN' when a.blocking_session = 4294967294 then 'GLOBAL' when a.blocking_session = 4294967293 then 'UNK
NOWN' when a.blocking_session = 4294967292 then 'NO HOLDER' when a.blocking_session = 4294967291 then 'NOT IN WAIT' else 'VALID' end), (case when a.blocking_session
between 4294967291 and 4294967295 then to_number(NULL) else a.blocking_session end), (case when a.blocking_session between 4294967291 and 4294967295 then to_number(NULL) e
lse a.blocking_session_serial# end), (case when a.blocking_session between 4294967291 and 4294967295 then to_number(NULL) else a.blocking_inst_id end), (case when a.blocking_session be
tween 4294967291 and 4294967295 then NULL else decode(bitand(a.flags, power(2, 3)),NULL, 'N', 0,'N','Y') end), a.current_obj#, a.current_file#, a.current_block#, a.current_row#,a.top_lev
el_call#, a.top_level_call_name, decode(a.consumer_group_id, 0, to_number(NULL), a.consumer_group_id), a.xid, decode(a.remote_instance#, 0, to_number(NULL), a.remote_instance#), a.time_model, a.in_con
nection_mgmt, a.in_parse, a.in_hard_parse, a.in_sql_execution, a.in_plsql_execution, a.in_plsql_rpc, a.in_plsql_compilation, a.in_java_execution, a.in_bind, a.in_cursor_close, a.in_sequence_load, deco
de(bitand(a.flags, power(2, 5)), NULL, 'N', 0, 'N', 'Y'), decode(bitand(a.flags, power(2, 6)), NULL, 'N', 0, 'N', 'Y'), decode(bitand(a.flags, power(2, 0)), NULL, 'N', 0, 'N', 'Y'), decode(bitand(a.fl
ags, power(2, 2)), NULL, 'N', 0, 'N', 'Y'), a.service_hash, a.program, a.module, a.action, a.client_id, a.machine, a.port, a.ecid, a.dbreplay_file_id, a.dbreplay_call_counter, decode(a.tm_delta_time,
0, to_number(null), a.tm_delta_time), decode(a.tm_delta_time, 0, to_number(null), a.tm_delta_cpu_time), decode(a.tm_delta_time, 0, to_number(null), a.tm_delta_db_time), decode(a.d
elta_time, 0, to_number(null), a.delta_time), decode(a.delta_time, 0, to_number(null), a.delta_read_io_requests), decode(a.delta_time, 0, to_number(null), a.delta_write_io_requ
ests), decode(a.delta_time, 0, to_number(null), a.delta_read_io_bytes), decode(a.delta_time, 0, to_number(null), a.delta_write_io_bytes), decode(a.delta_time, 0, to_number(null),
-->
查看
v$fixed_view_definition,可是字段的最大长度是4000,就以为这视图的定义超过了4000我就看不到完整的内容了。
SQL> desc v$fixed_view_definition
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
VIEW_NAME VARCHAR2(30)
VIEW_DEFINITION VARCHAR2(4000)
-->看来还是找不基表,可以试着找找,有没有和sql视图类似的情况,在基表里有一个clob字段存着完整信息
从v$fixed_view_definition下手
SQL> select VIEW_DEFINITION from v$fixed_view_definition where view_name=upper('v$fixed_view_definition');
VIEW_DEFINITION
--------------------------------------------------------------------------------
select VIEW_NAME , VIEW_DEFINITION from GV$FIXED_VIEW_DEFINITION where inst_id
= USERENV('Instance')
-->找到定义和基表,查看列定义
SQL> select VIEW_DEFINITION from v$fixed_view_definition where view_name=upper('gv$fixed_view_definition');
VIEW_DEFINITION
--------------------------------------------------------------------------------
select i.inst_id,kqfvinam,kqftpsel from x$kqfvi i, x$kqfvt t where i.indx = t.indx
SQL> desc x$kqfvt
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
ADDR RAW(8)
INDX NUMBER
INST_ID NUMBER
KQFTPSEL VARCHAR2(4000)
-->看来还是不行啊。办法总比困难多,我从执行计划里拿,
我直接看gv$active_session_history,这下终于显形了。
SQL> explain plan for select *from GV$ACTIVE_SESSION_HISTORY;
SQL> select *from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2905781256
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 127K| 0 (0)| 00:00:01 |
| 1 | VIEW | GV$ACTIVE_SESSION_HISTORY | 100 | 127K| 0 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 100 | 131K| 0 (0)| 00:00:01 |
| 3 | FIXED TABLE FULL | X$KEWASH | 100 | 5200 | 0 (0)| 00:00:01 |
|* 4 | FIXED TABLE FIXED INDEX| X$ASH (ind:1) | 1 | 1299 | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("S"."SAMPLE_ADDR"="A"."SAMPLE_ADDR" AND "S"."SAMPLE_ID"="A"."SAMPLE_ID" AND
"S"."SAMPLE_TIME"="A"."SAMPLE_TIME" AND "S"."NEED_AWR_SAMPLE"="A"."NEED_AWR_SAMPLE")
-->要那到完整的定义,可以试着trace一下,也是一种方法。
此外,还有一个数据字典视图 DBA_HIST_ACTIVE_SESS_HISTORY,这个是写入disk的内容。不过不是实时的数据信息。也是比较实用的视图。
1* select owner,object_name,object_type from dba_objects where object_name like 'DBA_%SESS%HI%'
SQL>
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------ -------------------
SYS DBA_HIST_SESSMETRIC_HISTORY VIEW
SYS DBA_HIST_ACTIVE_SESS_HISTORY VIEW
PUBLIC DBA_HIST_SESSMETRIC_HISTORY SYNONYM
PUBLIC DBA_HIST_ACTIVE_SESS_HISTORY SYNONYM
-->
查找,就轻松找到了视图的定义。
select owner,view_name,text from dba_views where view_name='DBA_HIST_ACTIVE_SESS_HISTORY'
SYS DBA_HIST_ACTIVE_SESS_HISTORY select /* ASH/AWR meta attributes */
ash.snap_id, ash.dbid, ash.instance_number,
ash.sample_id, ash.sample_time,
/* Session/User attributes */
ash.session_id, ash.session_serial#,
decode(ash.session_type, 1,'FOREGROUND', 'BACKGROUND'),
ash.flags,
ash.user_id,
/* SQL attributes */
ash.sql_id,
decode(bitand(ash.flags, power(2, 4)), NULL, 'N', 0, 'N', 'Y'),
ash.sql_child_number, ash.sql_opcode,
(select command_name from DBA_HIST_SQLCOMMAND_NAME
where command_type = ash.sql_opcode
and dbid = ash.dbid) as sql_opname,
ash.force_matching_signature,
decode(ash.top_level_sql_id, NULL, ash.sql_id, ash.top_level_sql_id),
decode(ash.top_level_sql_id, NULL, ash.sql_opcode,
ash.top_level_sql_opcode),
/* SQL Plan/Execution attributes */
ash.sql_plan_hash_value,
decode(ash.sql_plan_line_id, 0, to_number(NULL), ash.sql_plan_line_id),
(select operation_name from DBA_HIST_PLAN_OPERATION_NAME
where operation_id = ash.sql_plan_operation#
and dbid = ash.dbid) as sql_plan_operation,
(select option_name from DBA_HIST_PLAN_OPTION_NAME
where option_id = ash.sql_plan_options#
and dbid = ash.dbid) as sql_plan_options,
decode(ash.sql_exec_id, 0, to_number(NULL), ash.sql_exec_id),
ash.sql_exec_start,
/* PL/SQL attributes */
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),
/* PQ attributes */
decode(ash.qc_session_id, 0, to_number(NULL), ash.qc_instance_id),
decode(ash.qc_session_id, 0, to_number(NULL), ash.qc_session_id),
decode(ash.qc_session_id, 0, to_number(NULL), ash.qc_session_serial#),
decode(ash.px_flags, 0, to_number(NULL), ash.px_flags),
/* Wait event attributes */
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,
decode(ash.wait_time, 0, 'WAITING', 'ON CPU'),
ash.time_waited,
(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
end),
(case when ash.blocking_session between 4294967291 and 4294967295
then to_number(NULL)
else ash.blocking_session_serial#
end),
(case when ash.blocking_session between 4294967291 and 4294967295
then to_number(NULL)
else ash.blocking_inst_id
end),
(case when ash.blocking_session between 4294967291 and 4294967295
then NULL
else decode(bitand(ash.flags, power(2, 3)), NULL, 'N',
0, 'N', 'Y')
end),
/* Session's working context */
ash.current_obj#, ash.current_file#, ash.current_block#,
ash.current_row#, ash.top_level_call#,
(select top_level_call_name from DBA_HIST_TOPLEVELCALL_NAME
where top_level_call# = ash.top_level_call#
and dbid = ash.dbid) as top_level_call_name,
decode(ash.consumer_group_id, 0, to_number(NULL),
ash.consumer_group_id),
ash.xid,
decode(ash.remote_instance#, 0, to_number(NULL), ash.remote_instance#),
ash.time_model,
decode(bitand(ash.time_model,power(2, 3)),0,'N','Y')
as in_connection_mgmt,
decode(bitand(ash.time_model,power(2, 4)),0,'N','Y')as in_parse,
decode(bitand(ash.time_model,power(2, 7)),0,'N','Y')as in_hard_parse,
decode(bitand(ash.time_model,power(2,10)),0,'N','Y')as in_sql_execution,
decode(bitand(ash.time_model,power(2,11)),0,'N','Y')
as in_plsql_execution,
decode(bitand(ash.time_model,power(2,12)),0,'N','Y')as in_plsql_rpc,
decode(bitand(ash.time_model,power(2,13)),0,'N','Y')
as in_plsql_compilation,
decode(bitand(ash.time_model,power(2,14)),0,'N','Y')
as in_java_execution,
decode(bitand(ash.time_model,power(2,15)),0,'N','Y')as in_bind,
decode(bitand(ash.time_model,power(2,16)),0,'N','Y')as in_cursor_close,
decode(bitand(ash.time_model,power(2,17)),0,'N','Y')as in_sequence_load,
decode(bitand(ash.flags,power(2,5)),NULL,'N',0,'N','Y')
as capture_overhead,
decode(bitand(ash.flags,power(2,6)), NULL,'N',0,'N','Y' )
as replay_overhead,
decode(bitand(ash.flags,power(2,0)),NULL,'N',0,'N','Y') as is_captured,
decode(bitand(ash.flags,power(2,2)), NULL,'N',0,'N','Y' )as is_replayed,
/* Application attributes */
ash.service_hash, ash.program,
substrb(ash.module,1,(select ksumodlen from x$modact_length)) module,
substrb(ash.action,1,(select ksuactlen from x$modact_length)) action,
ash.client_id,
ash.machine, ash.port, ash.ecid,
/* DB Replay info */
ash.dbreplay_file_id, ash.dbreplay_call_counter,
/* stash columns */
ash.tm_delta_time,
ash.tm_delta_cpu_time,
ash.tm_delta_db_time,
ash.delta_time,
ash.delta_read_io_requests,
ash.delta_write_io_requests,
ash.delta_read_io_bytes,
ash.delta_write_io_bytes,
ash.delta_interconnect_io_bytes,
ash.pga_allocated,
ash.temp_space_allocated
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 ash.dbid = evt.dbid
and ash.event_id = evt.event_id
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23718752/viewspace-1120378/,如需转载,请注明出处,否则将追究法律责任。
发表评论 取消回复