ITPub博客

首页 > 数据库 > Oracle > [20210113]ashtop查询特定表的SQL语句2.txt

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

原创 Oracle 作者:lfree 时间:2021-01-13 11:36:32 0 删除 编辑

[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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2818
  • 访问量
    6618272