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

相关文章

来自专栏java达人

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

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

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

执行计划中的COLLECTION ITERATOR PICKLER FETCH导致的性能问题 (r5笔记第49天)

今天开发的同事找到我,让我评估一个sql语句。因为这条语句被应用监控组给抓取出来了,需要尽快进行性能调优。 sql语句比较长,是由几个Union连接起来的子查询...

3465
来自专栏乐沙弥的世界

SQL server 2005 PIVOT运算符的使用

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

592
来自专栏乐沙弥的世界

SQL,PL/SQL 数据类型一览表

The following is a list of datatypes available in Oracle.

1053
来自专栏乐沙弥的世界

当心外部连接中的ON子句

       在SQL tuning中,不良写法导致SQL执行效率比比皆是。最近的SQL tuning中一个外部连接写法不当导致过SQL执行时间超过15分钟左右...

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

一条简单的sql在11g和12c中的不同(r5笔记第2天)

今天在查看awr报告的时候,有一句很简单的sql语句引起了我的注意,因为它排在SQL Order by Reads的第2位。 Physical ReadsExe...

3384
来自专栏数据和云

案例分析:倾斜值传入导致 SQL 资源消耗升高

作者 | 邓秋爽:云和恩墨技术工程师,有超过七年超大型数据库专业服务经验,擅长 Oracle 数据库优化、SQL 优化和 Troubleshooting。

1184
来自专栏码生

webstorm 模板变量

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

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

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

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

3294
来自专栏weixuqin 的专栏

SQL学习之查询

49410

扫码关注云+社区