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

来源：这里教程网时间：2026-03-03 16:21:43 作者：

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

编辑推荐：

返回列表

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

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推