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

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

[20210113]ashtop查询特定表的SQL语句2.txt --//昨天写的链接:http://blog.itpub.net/267265/viewspace-2749422/=>[20210112]ashtop查询特定表的SQL语句.txt --//实际上我的查询仅仅包含包含某个表字符串的sql语句,可能根本不包括该表,而且我还忽略比如视图以及同义词等情况. --//我决定重新改写语句. $ cat ref_t.sql accept owner prompt 'Please enter Name of Table Owner : ' accept table_name prompt 'Please enter Table Name to show reference SQLs for: ' set verify off column sql_text format a58 word_wrapped select /*+ ordered use_hash(d) use_hash(c) */ c.kglobt03 sql_id, sum(c.kglobt13) disk_reads, sum(c.kglobt14) logical_reads, sum(c.kglhdexc) executions, c.kglnaobj sql_text from sys.x$kglob o, sys.x$kgldp d, sys.x$kglcursor c where o.inst_id = userenv('Instance') and d.inst_id = userenv('Instance') and c.inst_id = userenv('Instance') and o.kglnaown = upper(nvl('&Owner',user)) and o.kglnaobj = upper('&Table_name') and d.kglrfhdl = o.kglhdadr and c.kglhdadr = d.kglhdadr group by c.kglnaobj,c.kglobt03 order by 3; --undefine owner --undefine table_name clear breaks --//以上脚本我用来查询相关表的sql语句.修改如下: $ cat ashtt.sql -------------------------------------------------------------------------------- -- -- File name: ashtop.sql -- Purpose: Display top ASH time (count of ASH samples) grouped by your -- specified dimensions -- -- Author: Tanel Poder -- Copyright: (c) http://blog.tanelpoder.com -- -- Usage: -- @ashtop <grouping_cols> <filters> <fromtime> <totime> -- -- Example: -- @ashtop username,sql_id session_type='FOREGROUND' sysdate-1/24 sysdate -- -- Other: -- This script uses only the in-memory V$ACTIVE_SESSION_HISTORY, use -- @dashtop.sql for accessiong the DBA_HIST_ACTIVE_SESS_HISTORY archive -- -------------------------------------------------------------------------------- COL "%This" FOR A7 --COL p1 FOR 99999999999999 --COL p2 FOR 99999999999999 --COL p3 FOR 99999999999999 COL p1text FOR A30 word_wrap COL p2text FOR A30 word_wrap COL p3text FOR A30 word_wrap COL p1hex FOR A17 COL p2hex FOR A17 COL p3hex FOR A17 COL AAS FOR 9999.9 COL totalseconds HEAD "Total|Seconds" FOR 99999999 COL event FOR A40 WORD_WRAP with sqla as (SELECT /*+ MATERIALIZE ordered use_hash(d) use_hash(c) */ DISTINCT c.kglobt03 sql_id FROM sys.x$kglob o ,sys.x$kgldp d ,sys.x$kglcursor c WHERE o.inst_id = USERENV ('Instance') AND d.inst_id = USERENV ('Instance') AND c.inst_id = USERENV ('Instance') AND o.kglnaown = upper(nvl('&5',user)) AND o.kglnaobj = upper('&6') AND d.kglrfhdl = o.kglhdadr AND c.kglhdadr = d.kglhdadr) SELECT * FROM ( SELECT /*+ LEADING(a) USE_HASH(u) */ COUNT(*) totalseconds , ROUND(COUNT(*) / ((CAST(&4 AS DATE) - CAST(&3 AS DATE)) * 86400), 1) AAS , LPAD(ROUND(RATIO_TO_REPORT(COUNT(*)) OVER () * 100)||'%',5,' ')||' |' "%This" , &1 -- , SUM(CASE WHEN wait_class IS NULL THEN 1 ELSE 0 END) "CPU" -- , SUM(CASE WHEN wait_class ='User I/O' THEN 1 ELSE 0 END) "User I/O" -- , SUM(CASE WHEN wait_class ='Application' THEN 1 ELSE 0 END) "Application" -- , SUM(CASE WHEN wait_class ='Concurrency' THEN 1 ELSE 0 END) "Concurrency" -- , SUM(CASE WHEN wait_class ='Commit' THEN 1 ELSE 0 END) "Commit" -- , SUM(CASE WHEN wait_class ='Configuration' THEN 1 ELSE 0 END) "Configuration" -- , SUM(CASE WHEN wait_class ='Cluster' THEN 1 ELSE 0 END) "Cluster" -- , SUM(CASE WHEN wait_class ='Idle' THEN 1 ELSE 0 END) "Idle" -- , SUM(CASE WHEN wait_class ='Network' THEN 1 ELSE 0 END) "Network" -- , SUM(CASE WHEN wait_class ='System I/O' THEN 1 ELSE 0 END) "System I/O" -- , SUM(CASE WHEN wait_class ='Scheduler' THEN 1 ELSE 0 END) "Scheduler" -- , SUM(CASE WHEN wait_class ='Administrative' THEN 1 ELSE 0 END) "Administrative" -- , SUM(CASE WHEN wait_class ='Queueing' THEN 1 ELSE 0 END) "Queueing" -- , SUM(CASE WHEN wait_class ='Other' THEN 1 ELSE 0 END) "Other" , TO_CHAR(MIN(sample_time), 'YYYY-MM-DD HH24:MI:SS') first_seen , TO_CHAR(MAX(sample_time), 'YYYY-MM-DD HH24:MI:SS') last_seen -- , MAX(sql_exec_id) - MIN(sql_exec_id) FROM (SELECT a.* , TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p1 ELSE null END, '0XXXXXXXXXXXXXXX') p1hex , TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p2 ELSE null END, '0XXXXXXXXXXXXXXX') p2hex , TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p3 ELSE null END, '0XXXXXXXXXXXXXXX') p3hex FROM gv$active_session_history a) a , dba_users u WHERE a.user_id = u.user_id (+) AND &2 AND sql_id IN (SELECT sql_id FROM sqla ) AND sample_time BETWEEN &3 AND &4 GROUP BY &1 ORDER BY TotalSeconds DESC , &1 ) WHERE ROWNUM <= 30 / $ cat dashtt.sql -------------------------------------------------------------------------------- -- -- File name: dashtop.sql -- Purpose: Display top ASH time (count of ASH samples) grouped by your -- specified dimensions -- -- Author: Tanel Poder -- Copyright: (c) http://blog.tanelpoder.com -- -- Usage: -- @dashtop <grouping_cols> <filters> <fromtime> <totime> -- -- Example: -- @dashtop username,sql_id session_type='FOREGROUND' sysdate-1/24 sysdate -- -- Other: -- This script uses only the AWR's DBA_HIST_ACTIVE_SESS_HISTORY, use -- @dashtop.sql for accessiong the V$ ASH view -- -------------------------------------------------------------------------------- COL "%This" FOR A6 --COL p1 FOR 99999999999999 --COL p2 FOR 99999999999999 --COL p3 FOR 99999999999999 COL p1text FOR A30 word_wrap COL p2text FOR A30 word_wrap COL p3text FOR A30 word_wrap COL p1hex FOR A17 COL p2hex FOR A17 COL p3hex FOR A17 with sqla as (SELECT /*+ MATERIALIZE ordered use_hash(d) use_hash(c) */ DISTINCT c.kglobt03 sql_id FROM sys.x$kglob o ,sys.x$kgldp d ,sys.x$kglcursor c WHERE o.inst_id = USERENV ('Instance') AND d.inst_id = USERENV ('Instance') AND c.inst_id = USERENV ('Instance') AND o.kglnaown = upper(nvl('&5',user)) AND o.kglnaobj = upper('&6') AND d.kglrfhdl = o.kglhdadr AND c.kglhdadr = d.kglhdadr) SELECT * FROM ( SELECT /*+ LEADING(a) USE_HASH(u) */ LPAD(ROUND(RATIO_TO_REPORT(COUNT(*)) OVER () * 100)||'%',5,' ') "%This" , &1 , 10 * COUNT(*) "TotalSeconds" -- , 10 * SUM(CASE WHEN wait_class IS NULL THEN 1 ELSE 0 END) "CPU" -- , 10 * SUM(CASE WHEN wait_class ='User I/O' THEN 1 ELSE 0 END) "User I/O" -- , 10 * SUM(CASE WHEN wait_class ='Application' THEN 1 ELSE 0 END) "Application" -- , 10 * SUM(CASE WHEN wait_class ='Concurrency' THEN 1 ELSE 0 END) "Concurrency" -- , 10 * SUM(CASE WHEN wait_class ='Commit' THEN 1 ELSE 0 END) "Commit" -- , 10 * SUM(CASE WHEN wait_class ='Configuration' THEN 1 ELSE 0 END) "Configuration" -- , 10 * SUM(CASE WHEN wait_class ='Cluster' THEN 1 ELSE 0 END) "Cluster" -- , 10 * SUM(CASE WHEN wait_class ='Idle' THEN 1 ELSE 0 END) "Idle" -- , 10 * SUM(CASE WHEN wait_class ='Network' THEN 1 ELSE 0 END) "Network" -- , 10 * SUM(CASE WHEN wait_class ='System I/O' THEN 1 ELSE 0 END) "System I/O" -- , 10 * SUM(CASE WHEN wait_class ='Scheduler' THEN 1 ELSE 0 END) "Scheduler" -- , 10 * SUM(CASE WHEN wait_class ='Administrative' THEN 1 ELSE 0 END) "Administrative" -- , 10 * SUM(CASE WHEN wait_class ='Queueing' THEN 1 ELSE 0 END) "Queueing" -- , 10 * SUM(CASE WHEN wait_class ='Other' THEN 1 ELSE 0 END) "Other" , TO_CHAR(MIN(sample_time), 'YYYY-MM-DD HH24:MI:SS') first_seen , TO_CHAR(MAX(sample_time), 'YYYY-MM-DD HH24:MI:SS') last_seen FROM (SELECT a.* , TO_CHAR(CASE WHEN session_state = 'ON CPU' THEN p1 ELSE null END, '0XXXXXXXXXXXXXXX') p1hex , TO_CHAR(CASE WHEN session_state = 'ON CPU' THEN p2 ELSE null END, '0XXXXXXXXXXXXXXX') p2hex , TO_CHAR(CASE WHEN session_state = 'ON CPU' THEN p3 ELSE null END, '0XXXXXXXXXXXXXXX') p3hex FROM dba_hist_active_sess_history a) a , dba_users u WHERE a.user_id = u.user_id (+) AND &2 AND sql_id IN (SELECT sql_id FROM sqla ) AND sample_time BETWEEN &3 AND &4 AND snap_id IN (SELECT snap_id FROM dba_hist_snapshot WHERE sample_time BETWEEN &3 AND &4) -- for partition pruning GROUP BY &1 ORDER BY "TotalSeconds" DESC , &1 ) WHERE ROWNUM <= 20 / --//简单测试: > @ dashtt sql_id,event 1=1 trunc(sysdate) trunc(sysdate)+1 xxxx_yyy ms_cf01 Total %This SQL_ID EVENT Seconds FIRST_SEEN LAST_SEEN ------ ------------- ---------------------------------------- --------- ------------------- ------------------- 32% fagcu20tqqc7x 90 2021-01-13 00:49:41 2021-01-13 10:43:53 25% abwrcfvwk3g18 70 2021-01-13 03:28:17 2021-01-13 09:46:57 21% g0zzq8wx5rjwa 60 2021-01-13 08:46:40 2021-01-13 10:22:41 14% fcqbzpqstq4ns 40 2021-01-13 08:35:19 2021-01-13 10:41:53 4% 01wwrnjjytb5z 10 2021-01-13 09:47:37 2021-01-13 09:47:37 4% 40dgpux1au2dx db file parallel read 10 2021-01-13 09:19:04 2021-01-13 09:19:04 6 rows selected.

编辑推荐：

返回列表

[20210113]ashtop查询特定表的SQL语句2.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：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推