[20231128]完善ashtable.sql.txt

[20231128]完善ashtable.sql.txt

--//以前寫的指令碼,我本來的意思透過利用ashtop.sql指令碼查詢v$sqlarea特定字串找sql語句,主要用來查詢特定表的情況.
--//當然這樣會存在一些遺漏,因為又可能使用檢視.也可以使用ashttz.sql指令碼.
--//我當時是順便寫的,變數順序沒有考慮好,通用性很差,重新設計.

--//原來指令碼如下.
$ 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

--//而tpt ashtop.sql指令碼接收的引數如下:
--//   @ashtop <grouping_cols> <filters> <fromtime> <totime>
--//我修改如下
--//原來的引數保持一致.
--//引數5設定為查詢v$sqlarea檢視中的特定字串,我主要原來查詢表.
--//引數6設定為d或者''選擇執行ashtop.sql還是dashtop.sql指令碼.

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

--//測試如下：
> @ ashtable sql_id 1=1 &day dbms_lob.substr ''

@ tpt/ashtop sql_id "1=1 and sql_id in (select sql_id from v$sqlarea where lower(sql_fulltext) like lower('%dbms_lob.substr%'))"  sysdate-1 sysdate

    Total                                                                         Distinct Distinct
  Seconds     AAS %This   SQL_ID        FIRST_SEEN          LAST_SEEN           Execs Seen  Tstamps
--------- ------- ------- ------------- ------------------- ------------------- ---------- --------
     1136      .0   49% | df54kmrg2yz8t 2023-11-28 15:50:37 2023-11-29 09:09:31       1136     1113
     1107      .0   48% | 18vdbxnv6kt3f 2023-11-28 15:51:20 2023-11-29 09:07:49       1107     1058
       41      .0    2% | 0nh1kyy93h1u9 2023-11-29 09:07:54 2023-11-29 09:09:00          3       40
       15      .0    1% | 8md9hh0pkfyrj 2023-11-29 09:09:16 2023-11-29 09:09:30          1       14

> @ ashtable sql_id 1=1 &day dbms_lob.substr d

@ tpt/dashtop sql_id "1=1 and sql_id in (select sql_id from v$sqlarea where lower(sql_fulltext) like lower('%dbms_lob.substr%'))"  sysdate-1 sysdate

    Total
  Seconds     AAS %This   SQL_ID        FIRST_SEEN          LAST_SEEN
--------- ------- ------- ------------- ------------------- -------------------
     1590      .0   56%   df54kmrg2yz8t 2023-11-28 09:11:51 2023-11-29 08:53:34
     1200      .0   43%   18vdbxnv6kt3f 2023-11-28 09:11:30 2023-11-29 07:43:55
       20      .0    1%   9rx5hy4fughh6 2023-11-28 09:51:59 2023-11-28 09:53:33
       10      .0    0%   afgd7ncmxq77d 2023-11-28 11:47:51 2023-11-28 11:47:51


SYS@192.168.100.141:1621/dbcn/dbcn1> @ ashtable sql_id 1=1 &day YB_GJYB_JSXX d

@ tpt/dashtop sql_id "1=1 and sql_id in (select sql_id from v$sqlarea where lower(sql_fulltext) like lower('%YB_GJYB_JSXX%'))"  sysdate-1 sysdate

    Total
  Seconds     AAS %This   SQL_ID        FIRST_SEEN          LAST_SEEN
--------- ------- ------- ------------- ------------------- -------------------
     6540      .1   47%   ahytdrykz325h 2023-11-28 09:12:32 2023-11-29 09:00:17
     3790      .0   28%   0bbu7zmfq2p01 2023-11-28 16:53:30 2023-11-28 17:41:45
     1230      .0    9%   dvsnk0fx2ymxv 2023-11-28 09:22:21 2023-11-29 08:58:54
      470      .0    3%   5tg9qn23771dm 2023-11-28 09:28:34 2023-11-29 08:46:41
      350      .0    3%   0w7p63mf2zuvm 2023-11-28 09:34:05 2023-11-28 09:53:22
      320      .0    2%   fdqrrjj1rav0p 2023-11-28 09:32:42 2023-11-28 09:52:41
      270      .0    2%   at6gqskrf4vx6 2023-11-28 09:49:24 2023-11-29 08:24:28
      200      .0    1%   8yuy9m0s42q9z 2023-11-28 09:32:01 2023-11-28 17:20:13
      130      .0    1%   dgzbbc4fyhapp 2023-11-28 10:00:46 2023-11-29 08:59:15
      100      .0    1%   1ryqfb20gr2cv 2023-11-28 09:44:04 2023-11-28 17:10:55
       60      .0    0%   1mfk9rfa8awtx 2023-11-28 15:19:55 2023-11-29 08:26:11
       50      .0    0%   1kjx9pq6qj97r 2023-11-28 09:27:22 2023-11-29 08:57:00
       50      .0    0%   fcrnnbwz0s6rv 2023-11-28 09:30:48 2023-11-29 08:58:23
       40      .0    0%   fj2726zmwx7qx 2023-11-28 11:04:33 2023-11-29 08:45:39
       30      .0    0%   c8tp54drtxyt7 2023-11-28 09:31:09 2023-11-29 08:59:15
       30      .0    0%   dhhqycyz0qa6n 2023-11-29 08:54:56 2023-11-29 08:57:21
       30      .0    0%   dksyfc8nn5mpv 2023-11-29 08:55:17 2023-11-29 08:55:38
       20      .0    0%   b1swm62hx4fv0 2023-11-28 16:36:16 2023-11-28 16:38:51
       10      .0    0%   33syqtmm5un7g 2023-11-28 17:05:55 2023-11-28 17:05:55
       10      .0    0%   5473xs0tgdygx 2023-11-28 15:38:00 2023-11-28 15:38:00
       10      .0    0%   94kyth26ykppt 2023-11-29 08:07:57 2023-11-29 08:07:57
       10      .0    0%   dpzbp3rmvc2gq 2023-11-28 09:32:11 2023-11-28 09:32:11
       10      .0    0%   dua8ys3kbj1xc 2023-11-28 15:16:28 2023-11-28 15:16:28
       10      .0    0%   g5x0qkyax24hf 2023-11-28 18:04:18 2023-11-28 18:04:18
       10      .0    0%   g86sn8n0z7vrv 2023-11-28 11:51:49 2023-11-28 11:51:49
25 rows selected.