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;

 

成都创新互联公司是专业的江干网站建设公司，江干接单;提供成都做网站、网站设计,网页设计,网站设计,建网站,PHP网站建设等专业做网站服务;采用PHP框架,可快速的进行江干网站开发网页制作和功能扩展;专业做搜索引擎喜爱的网站,专业的做网站团队,希望更多企业前来合作!

 

==

 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;

本文题目：HighcostSQL
链接URL：http://sczitong.cn/article/jjsjii.html