[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.