探索ASH 第一篇

老是在用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一下,也是一种方法。

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2014-03-15

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏数据结构与算法

洛谷P2421 [NOI2002]荒岛野人(扩展欧几里得)

克里特岛以野人群居而著称。岛上有排列成环行的M个山洞。这些山洞顺时针编号为1,2,…,M。岛上住着N个野人,一开始依次住在山洞C1,C2,…,CN中,以后每年,...

413
来自专栏HansBug's Lab

4052: [Cerc2013]Magical GCD

4052: [Cerc2013]Magical GCD Time Limit: 10 Sec  Memory Limit: 128 MB Submit: 148...

2008
来自专栏算法修养

PAT 1017 Queueing at Bank (模拟)

1017. Queueing at Bank (25) 时间限制 400 ms 内存限制 65536 kB 代码长度限制 16000 B ...

2707
来自专栏性能与架构

将 Redis 作为图数据库

1. 简介 Redis 在 4.0 中正式支持了Module模块系统,使其可以进行丰富的扩展 图数据库的应用越来越广泛,RedisGraph 就是一个 Redi...

3456
来自专栏PPV课数据科学社区

【学习】七天搞定SAS(三):基本模块调用

搞定基本的函数之后,开始鼓捣SAS里面的模型。也就是说,要开始写PROC了。说实话,越学SAS,越觉得SAS像Stata...无论是从输出的样式,还是语法。好不...

2855
来自专栏数据分析

C# 6.0 功能预览 (二)

在Language Feature Status上面看到,其实更新的并不是特别多,为了不会误导看了C# 6.0 功能预览 (一)的园友,现在把官方的更新列表拿了...

2885
来自专栏jeremy的技术点滴

python开发小技巧

2594
来自专栏闵开慧

曾经做过的40道程序设计课后习题总结(二)

曾经做过的40道程序设计课后习题总结(二) 课后习题目录 1 斐波那契数列 2 判断素数 3 水仙花数 4 分解质因数 5 杨辉三角 6 学习成绩查询 7 求最...

3447
来自专栏一个爱瞎折腾的程序猿

个人项目框架搭建 -- Autofac简单使用记录

572
来自专栏数据和云

空与非空 EMPTY_LOB和NULL的区别

编辑手记: EMPTY_LOB与NULL在字面意思上看起来差不多,但实际上,它们却有天壤之别。 前不久写过一篇文章,描述如果表包含了触发器,在通过IMP导入数据...

2984

扫码关注云+社区