[20210112]ashtop查詢特定表的SQL語句.txt
[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.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2749422/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20210113]ashtop查詢特定表的SQL語句2.txtSQL
- ashtop查詢wwwtl555222com 153316O3951特定表的SQL語句2SQL
- SQL語句查詢表結構SQL
- mysql 查詢建表語句sqlMySql
- [20160815]查詢相關表的sql語句.txtSQL
- SQL單表查詢語句總結SQL
- SQL查詢語句 (Oracle)SQLOracle
- SQL server 查詢語句SQLServer
- SQL mother查詢語句SQL
- sql查詢語句流程SQL
- SQL Server阻塞查詢語句SQLServer
- SQL查詢語句使用 (轉)SQL
- sql 查詢經典語句SQL
- 查詢效率低下的sql的語句SQL
- SQL server資料庫表碎片比例查詢語句SQLServer資料庫
- 多表查詢建表語句
- mysql查詢效率慢的SQL語句MySql
- 查詢執行慢的SQL語句SQL
- SQL SERVER 條件語句的查詢SQLServer
- 查詢正在執行的SQL語句SQL
- postgresql dba常用sql查詢語句SQL
- SQL查詢語句精華文章(轉)SQL
- oracle、my sql、sql隨機查詢語句OracleSQL隨機
- 查詢全表掃描語句
- Laravel 框架查詢執行的 SQL 語句Laravel框架SQL
- 在mysql查詢效率慢的SQL語句MySql
- SQL基礎的查詢語句烈鉍SQL
- 查詢Oracle正在執行的SQL語句OracleSQL
- 幾個定位、查詢session的sql語句SessionSQL
- ORACLE 鎖表的解決方法及查詢引起鎖表SQL語句[轉]OracleSQL
- ORACLE 鎖表的解決方法及查詢引起鎖表SQL語句方法OracleSQL
- SQL Server 查詢歷史執行的SQL語句SQLServer
- SQL SERVER 資料庫查詢表和欄位資訊語句SQLServer資料庫
- SQL Server-簡單查詢語句SQLServer
- SQL語句巢狀查詢問題SQL巢狀
- 查詢阻塞與被阻塞SQL語句SQL
- Sql Server系列：查詢分頁語句SQLServer
- 查詢sql語句執行次數SQL