温馨提示×

温馨提示×

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

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

Highcost SQL

发布时间:2020-06-18 14:34:43 阅读:324 作者:llc018198 栏目:关系型数据库
开发者测试专用服务器限时活动,0元免费领,库存有限,领完即止! 点击查看>>
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_allm,

                               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_allm, sys.objo, sys.useru

                         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_allm,

                               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_modsm,

                               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_modsm, sys.objo, sys.useru

                         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.tabsubparttsp,

                               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;

亿速云「云服务器」,即开即用、新一代英特尔至强铂金CPU、三副本存储NVMe SSD云盘,价格低至29元/月。点击查看>>

向AI问一下细节

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

AI

开发者交流群×