探索ASH 第一篇
公众号：杨建荣的学习笔记 · 作者：杨建荣 · 发布：2014-03-15 10:39:27 · 原文链接
老是在用ASH，对它的依赖感觉已经大于AWR,昨天心血来潮，想看看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,…..
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#,…..
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一下，也是一种方法。