[20210113]ashtop查詢特定表的SQL語句2.txt
[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.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2749595/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20210112]ashtop查詢特定表的SQL語句.txtSQL
- ashtop查詢wwwtl555222com 153316O3951特定表的SQL語句2SQL
- SQL語句查詢表結構SQL
- mysql 查詢建表語句sqlMySql
- SQL單表查詢語句總結SQL
- SQL查詢語句 (Oracle)SQLOracle
- SQL server 查詢語句SQLServer
- SQL mother查詢語句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
- 一句簡單的SQL查詢語句的背後...SQL
- 根據emp,dept,salgrade表進行的sql查詢語句(1)SQL