探索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 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

通过执行计划中的CONCATENATION分析sql问题(r4笔记第16天)

昨天开发的一个同事找到我,说写了一条sql语句,但是执行了半个小时还没有执行完,想让我帮忙看看是怎么回事。 他大体上给我讲了下逻辑,表bl1_rc_rates是...

3054
来自专栏SAP最佳业务实践

SAP S/4 HANA新变化-FI数据模型

With the installation of SAP Simple Finance, on-premise edition totals and appli...

4247
来自专栏乐沙弥的世界

SQL server 2005 PIVOT运算符的使用

        PIVOT,UNPIVOT运算符是SQL server 2005支持的新功能之一,主要用来实现行到列的转换。本文主要介绍PIVOT运算符的操作,...

682
来自专栏java达人

mysql left( right ) join使用on 与where 筛选的差异

有这样的一个问题mysql查询使用mysql中left(right)join筛选条件在on与where查询出的数据是否有差异。 可能只看着两个关键字看不出任...

2267
来自专栏数据和云

Oracle Hints - 先知的提示

在上周恩墨微信大讲堂的讨论中,几个有趣的视图跃入我们的视野,可以分享给大家。 在Oracle 11g中,新增的视图V$SQL_HINT记录了Oracle数据库中...

2876
来自专栏乐沙弥的世界

NULL 值与索引(一)

    NULL值是关系数据库系统布尔型(true,false,unknown)中比较特殊类型的一种值,通常称为UNKNOWN或空值,即是未知的,不确定的。由...

2522
来自专栏码生

webstorm 模板变量

${PROJECT_NAME} - the name of the current project.

1662
来自专栏杨建荣的学习笔记

通过pl/sql来格式化sql(r4笔记第63天)

在之前的一篇博文中分享了通过java来格式化sql,http://blog.itpub.net/23718752/viewspace-1444910/ 今天突然...

3374
来自专栏数据处理

CEdit只能输入16进制数

1325
来自专栏weixuqin 的专栏

SQL学习之查询

52610

扫码关注云+社区