温馨提示×

温馨提示×

您好,登录后才能下订单哦!

密码登录×
登录注册×
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》

Highcost SQL

发布时间:2020-06-18 14:34:43 来源:网络 阅读:322 作者:llc018198 栏目:关系型数据库
select s.sid,

       s.serial#,

       s.username,

       s.sql_id,

       m.physical_reads,

       m.logical_reads,

       m.hard_parses,

       m.physical_read_pct,

       trunc(m.logical_read_pct) as logical_read_pct,

       m.cpu / 100 as CPU

  from v$sessmetric m, v$session s

 where (m.physical_reads > 100 or m.cpu > 100 or m.logical_reads > 100)

   and m.session_id = s.sid

   and m.session_serial_num = s.serial#

   and s.username is not null

   and s.status = 'ACTIVE'

 group by s.sid,

          s.serial#,

          s.username,

          m.physical_reads,

          m.logical_reads,

          m.hard_parses,

          m.physical_read_pct,

          m.logical_read_pct,

          cpu,

          s.sql_id

 order by m.physical_reads, m.cpu, m.logical_reads;

 

 

==

Highcost SQL

 SELECT *

  FROM (SELECT *

          FROM (SELECT *

                  FROM (SELECT u.name owner,

                               o.name table_name,

                               NULL partition_name,

                               NULL subpartition_name,

                               m.inserts,

                               m.updates,

                               m.deletes,

                               m.timestamp,

                               decode(bitand(m.flags, 1), 1, 'YES', 'NO') truncated,

                               m.drop_segments

                          FROM sys.mon_mods_all$ m,

                               sys.obj$          o,

                               sys.tab$          t,

                               sys.user$         u

                         WHERE o.obj# = m.obj#

                           AND o.obj# = t.obj#

                           AND o.owner# = u.user#

                        UNION ALL

                        SELECT u.name,

                               o.name,

                               o.subname,

                               NULL,

                               m.inserts,

                               m.updates,

                               m.deletes,

                               m.timestamp,

                               decode(bitand(m.flags, 1), 1, 'YES', 'NO'),

                               m.drop_segments

                          FROM sys.mon_mods_all$ m, sys.obj$ o, sys.user$ u

                         WHERE o.owner# = u.user#

                           AND o.obj# = m.obj#

                           AND o.type# = 19

                        UNION ALL

                        SELECT u.name,

                               o.name,

                               o2.subname,

                               o.subname,

                               m.inserts,

                               m.updates,

                               m.deletes,

                               m.timestamp,

                               decode(bitand(m.flags, 1), 1, 'YES', 'NO'),

                               m.drop_segments

                          FROM sys.mon_mods_all$ m,

                               sys.obj$          o,

                               sys.tabsubpart$   tsp,

                               sys.obj$          o2,

                               sys.user$         u

                         WHERE o.obj# = m.obj#

                           AND o.owner# = u.user#

                           AND o.obj# = tsp.obj#

                           AND o2.obj# = tsp.pobj#)

                 WHERE owner NOT LIKE '%SYS%'

                   AND owner NOT LIKE 'XDB'

                UNION ALL

                SELECT *

                  FROM (SELECT u.name owner,

                               o.name table_name,

                               NULL partition_name,

                               NULL subpartition_name,

                               m.inserts,

                               m.updates,

                               m.deletes,

                               m.timestamp,

                               decode(bitand(m.flags, 1), 1, 'YES', 'NO') truncated,

                               m.drop_segments

                          FROM sys.mon_mods$ m,

                               sys.obj$      o,

                               sys.tab$      t,

                               sys.user$     u

                         WHERE o.obj# = m.obj#

                           AND o.obj# = t.obj#

                           AND o.owner# = u.user#

                        UNION ALL

                        SELECT u.name,

                               o.name,

                               o.subname,

                               NULL,

                               m.inserts,

                               m.updates,

                               m.deletes,

                               m.timestamp,

                               decode(bitand(m.flags, 1), 1, 'YES', 'NO'),

                               m.drop_segments

                          FROM sys.mon_mods$ m, sys.obj$ o, sys.user$ u

                         WHERE o.owner# = u.user#

                           AND o.obj# = m.obj#

                           AND o.type# = 19

                        UNION ALL

                        SELECT u.name,

                               o.name,

                               o2.subname,

                               o.subname,

                               m.inserts,

                               m.updates,

                               m.deletes,

                               m.timestamp,

                               decode(bitand(m.flags, 1), 1, 'YES', 'NO'),

                               m.drop_segments

                          FROM sys.mon_mods$   m,

                               sys.obj$        o,

                               sys.tabsubpart$ tsp,

                               sys.obj$        o2,

                               sys.user$       u

                         WHERE o.obj# = m.obj#

                           AND o.owner# = u.user#

                           AND o.obj# = tsp.obj#

                           AND o2.obj# = tsp.pobj#)

                 WHERE owner NOT LIKE '%SYS%'

                   AND owner NOT LIKE '%XDB%')

         ORDER BY inserts DESC)

 WHERE rownum <= 50;

向AI问一下细节

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

AI