[20210112]ashtop查询特定表的SQL语句.txt

--//ashtop.sql是Tanel Poder大师写的查看  Display top ASH time 的脚本,它非常灵活.
--//例如有时候优化我要集中精力优化特定表的相关语句,这样可以统筹考虑索引的建立,执行如下:

@ ashtop username,sql_id,module,machine "sql_id in (select sql_id from v$sqlarea where lower(sql_text) like '%ms_cf01%')" trunc(sysdate) sysdate

--//这样有一个缺点就是sql_text 定义为VARCHAR2(1000),应该换成sql_fulltext字段.
--//toad下查询有一个缺点仅仅查询sql_text字段.

--//我自己在ashtop基础上写一个脚本如下:

$ cat ashtable.sql
column module format a30
prompt
prompt @ tpt/&&1.ashtop username,sql_id,module "&&3 and sql_id in (select sql_id from v$sqlarea where lower(sql_fulltext) like lower('%&&2%'))"  &&4 &&5
prompt
@ tpt/&&1.ashtop username,sql_id,module "&&3 and sql_id in (select sql_id from v$sqlarea where lower(sql_fulltext) like lower('%&&2%'))"  &&4 &&5

--//简单说明:
--//参数1 '' d ,''表示执行ashtop,d 表示执行dashtop.
--//参数2 查询相关的表
--//参数3 补充查询条件
--//参数4,5 查询时间范围.我一般选择查询trunc(sysdate-1) sysdate,这样简单一些.
--//如果打入具体时间最好不要有空格 ,选择yyyymmddhh24miss格式.
--//写成 "to_date('20210112080000','yyyymmddhh24miss')" "to_date('20210112090000','yyyymmddhh24miss')"
--//我自己定义3个alias:
alias zdate='date +"%Y/%m/%d %T"'
alias zzdate="date +'trunc(sysdate)+%H/24+%M/1440+%S/86400 == %Y/%m/%d %T'"
alias zzzdate="date +'%Y%m%d%H%M%S'"

$ zdate ;zzdate ;zzzdate
2021/01/12 16:03:53
trunc(sysdate)+16/24+03/1440+53/86400 == 2021/01/12 16:03:53
20210112160353
--//可以选择第2种格式,这样不容易错.
SCOTT@book> select trunc(sysdate)+16/24+03/1440+53/86400 aa from dual ;
AA
-------------------
2021-01-12 16:03:53

--//测试如下:
SYS@192.168.31.8:1521/hrp430> @ ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SYS> @ ashtable d ms_cf01 "1=1" trunc(sysdate-1) sysdate
@ tpt/dashtop username,sql_id,module "1=1 and sql_id in (select sql_id from v$sqlarea where lower(sql_fulltext) like '%ms_cf01%')"  trunc(sysdate-1) sysdate
%This  USERNAME             SQL_ID        MODULE                         TotalSeconds FIRST_SEEN          LAST_SEEN
------ -------------------- ------------- ------------------------------ ------------ ------------------- -------------------
  75%  XXXYYY_HIS           fcqbzpqstq4ns XXXYYY.EXE                              660 2021-01-11 08:46:17 2021-01-12 10:49:46
  13%  XXXYYY_HIS           abwrcfvwk3g18 XXXYYY.EXE                              110 2021-01-11 11:05:52 2021-01-12 13:18:30
   2%  XXXYYY_HIS           40dgpux1au2dx httpd.exe                                20 2021-01-11 09:27:52 2021-01-11 09:28:22
   2%  XXXYYY_HIS           7nsf4avvugcd2 XXXYYY.EXE                               20 2021-01-11 17:02:36 2021-01-12 11:30:00
   1%  XXXYYY_HIS           0189dt8bnpc5h XXXYYY.EXE                               10 2021-01-11 16:51:25 2021-01-11 16:51:25
   1%  XXXYYY_HIS           20abadqmfqytr XXXYYY.EXE                               10 2021-01-11 19:42:31 2021-01-11 19:42:31
   1%  XXXYYY_HIS           7taw1y68rgb3j XXXYYY.EXE                               10 2021-01-12 10:42:55 2021-01-12 10:42:55
   1%  XXXYYY_HIS           9s3xvyzrgf8hr XXXYYY.EXE                               10 2021-01-11 08:53:28 2021-01-11 08:53:28
   1%  XXXYYY_HIS           bwwx64v9by0c8 XXXYYY.EXE                               10 2021-01-12 10:41:35 2021-01-12 10:41:35
   1%  SYS                  0r91tqwyxd3gm sqlplus@zzzzzzz4 (TNS V1-V3)             10 2021-01-12 10:59:57 2021-01-12 10:59:57
   1%  SYS                  f5w07uwyh1t78 sqlplus@zzzzzzz4 (TNS V1-V3)             10 2021-01-12 09:13:36 2021-01-12 09:13:36

11 rows selected.

SYS> @ ashtable d ms_cf01 "module<>'sqlplus@zzzzzzz4 (TNS V1-V3)'" trunc(sysdate-1) sysdate
@ tpt/dashtop username,sql_id,module "module<>'sqlplus@zzzzzzz4 (TNS V1-V3)' and sql_id in (select sql_id from v$sqlarea where lower(sql_fulltext) like '%ms_cf01%')"  trunc(sysdate-1) sysdate
%This  USERNAME             SQL_ID        MODULE                         TotalSeconds FIRST_SEEN          LAST_SEEN
------ -------------------- ------------- ------------------------------ ------------ ------------------- -------------------
  77%  XXXYYY_HIS           fcqbzpqstq4ns XXXYYY.EXE                              660 2021-01-11 08:46:17 2021-01-12 10:49:46
  13%  XXXYYY_HIS           abwrcfvwk3g18 XXXYYY.EXE                              110 2021-01-11 11:05:52 2021-01-12 13:18:30
   2%  XXXYYY_HIS           40dgpux1au2dx httpd.exe                                20 2021-01-11 09:27:52 2021-01-11 09:28:22
   2%  XXXYYY_HIS           7nsf4avvugcd2 XXXYYY.EXE                               20 2021-01-11 17:02:36 2021-01-12 11:30:00
   1%  XXXYYY_HIS           0189dt8bnpc5h XXXYYY.EXE                               10 2021-01-11 16:51:25 2021-01-11 16:51:25
   1%  XXXYYY_HIS           20abadqmfqytr XXXYYY.EXE                               10 2021-01-11 19:42:31 2021-01-11 19:42:31
   1%  XXXYYY_HIS           7taw1y68rgb3j XXXYYY.EXE                               10 2021-01-12 10:42:55 2021-01-12 10:42:55
   1%  XXXYYY_HIS           9s3xvyzrgf8hr XXXYYY.EXE                               10 2021-01-11 08:53:28 2021-01-11 08:53:28
   1%  XXXYYY_HIS           bwwx64v9by0c8 XXXYYY.EXE                               10 2021-01-12 10:41:35 2021-01-12 10:41:35
9 rows selected.


SYS> @ ashtable '' ms_cf01 "module<>'sqlplus@zzzzzzz4 (TNS V1-V3)'" trunc(sysdate-1) sysdate

@ tpt/ashtop username,sql_id,module "module<>'sqlplus@zzzzzzz4 (TNS V1-V3)' and sql_id in (select sql_id from v$sqlarea where lower(sql_fulltext) like '%ms_cf01%')"  trunc(sysdate-1) sysdate

    Total
  Seconds     AAS %This   USERNAME             SQL_ID        MODULE                         FIRST_SEEN          LAST_SEEN
--------- ------- ------- -------------------- ------------- ------------------------------ ------------------- -------------------
      606      .0   74% | XXXYYY_HIS           fcqbzpqstq4ns XXXYYY.EXE                     2021-01-11 08:10:21 2021-01-12 15:40:36
      140      .0   17% | XXXYYY_HIS           abwrcfvwk3g18 XXXYYY.EXE                     2021-01-11 08:22:29 2021-01-12 15:36:29
       11      .0    1% | XXXYYY_HIS           0189dt8bnpc5h XXXYYY.EXE                     2021-01-11 11:44:31 2021-01-12 15:31:13
       11      .0    1% | XXXYYY_HIS           7nsf4avvugcd2 XXXYYY.EXE                     2021-01-11 09:49:55 2021-01-12 15:39:56
       10      .0    1% | XXXYYY_HIS           40dgpux1au2dx httpd.exe                      2021-01-11 09:27:52 2021-01-12 10:57:53
        5      .0    1% | XXXYYY_HIS           fagcu20tqqc7x XXXYYY.EXE                     2021-01-11 11:05:37 2021-01-12 15:43:33
        4      .0    0% | XXXYYY_HIS           7taw1y68rgb3j XXXYYY.EXE                     2021-01-12 10:42:55 2021-01-12 15:23:50
        3      .0    0% | XXXYYY_HIS           7nsf4avvugcd2 XXXYYY.exe                     2021-01-11 10:52:41 2021-01-11 17:24:29
        2      .0    0% | XXXYYY_HIS           4z4v4s5cwg509 XXXYYY.EXE                     2021-01-11 08:20:16 2021-01-12 10:42:36
        2      .0    0% | XXXYYY_HIS           bwwx64v9by0c8 XXXYYY.EXE                     2021-01-11 11:21:39 2021-01-12 10:41:35
        2      .0    0% | XXXYYY_HIS           cb5gcp4z78dcd XXXYYY.EXE                     2021-01-12 08:26:22 2021-01-12 09:39:06
        1      .0    0% | XXXYYY_HIS           0189dt8bnpc5h XXXYYY.exe                     2021-01-11 11:21:18 2021-01-11 11:21:18
        1      .0    0% | XXXYYY_HIS           0cf378ddjdpg7 XXXYYY.EXE                     2021-01-12 08:14:02 2021-01-12 08:14:02
        1      .0    0% | XXXYYY_HIS           20abadqmfqytr XXXYYY.EXE                     2021-01-11 19:42:31 2021-01-11 19:42:31
        1      .0    0% | XXXYYY_HIS           4a9g8wpx7vztz XXXYYY.EXE                     2021-01-11 17:31:00 2021-01-11 17:31:00
        1      .0    0% | XXXYYY_HIS           7ddzrw0m02pbv XXXYYY.EXE                     2021-01-11 21:25:16 2021-01-11 21:25:16
        1      .0    0% | XXXYYY_HIS           7q8fbm8yasd6a XXXYYY.exe                     2021-01-11 10:37:58 2021-01-11 10:37:58
        1      .0    0% | XXXYYY_HIS           9s3xvyzrgf8hr XXXYYY.EXE                     2021-01-11 08:53:28 2021-01-11 08:53:28
        1      .0    0% | XXXYYY_HIS           adqhw8anay6gn XXXYYY.EXE                     2021-01-11 17:34:53 2021-01-11 17:34:53
        1      .0    0% | XXXYYY_HIS           amdf8hd8bjmcs XXXYYY.EXE                     2021-01-12 08:17:21 2021-01-12 08:17:21
        1      .0    0% | XXXYYY_HIS           amdf8hd8bjmcs XXXYYY.exe                     2021-01-11 09:36:27 2021-01-11 09:36:27
        1      .0    0% | XXXYYY_HIS           cstxs81kx0bpn XXXYYY.EXE                     2021-01-12 10:04:05 2021-01-12 10:04:05
        1      .0    0% | XXXYYY_HIS           dbp7133hcw1gz XXXYYY.EXE                     2021-01-12 08:12:24 2021-01-12 08:12:24
        1      .0    0% | XXXYYY_HIS           drv519wx0q8nv XXXYYY.EXE                     2021-01-11 10:56:18 2021-01-11 10:56:18
        1      .0    0% | XXXYYY_HIS           gpp3c8k5zm917 XXXYYY.EXE                     2021-01-11 09:42:28 2021-01-11 09:42:28
29 rows selected.