大家好！这两天在工作中让写一个找到是分区表但未使用分区条件的sql 对于大家也许不是很难，但对于刚刚踏入oracle数据库行业的我来说有些小挑战啊。不过还好，通过1 天半的努力，终于写出了自己满意的效果。

思路：

我先解释一下，我的书写的思路，

1 先通过深蓝色部分查出是分区表的并且是TABLE ACCESS FULL的信息

2 然后在用join 和 v$sql_plan的条件里是PARTITION RANGE ALL 的结果集做关联。到这里得出的结果是：是分区表，并且是TABLE ACCESS FULL和PARTITION RANGE ALL的内容

3 然后继续和v$sqlarea试图进行join操作目的是为了得到sql_text,sql_fulltext,还有执行时间。

4 这里和v$sqlarea做join的目的是确定这个sql的唯一性。

5 最终我想要知道是那个机器做的操作连接方式是程序啊还是人为连接的，是否有等待啊，所有最后和dba_HIST_ACTIVE_SESS_HISTORY（历史回话信息表）

试图进行 left outer join 因为如果用 join的话会过滤掉一些数据，所以我是以上面的数据为基准，得到最后的结果。

sql语句为：

select f1.*,

       f2.machine "机器名",
       f2.module "连接方式",
       --f2.sql_exec_start "sql执行开始时间",
       f2.event      "等待事件",
       f2.wait_class "等待原因"
from (select g1.*, d1.OBJECT_name, d1.a, d1.b
          from (select c1.*
                  FROM (select distinct a1.OBJECT_name,
                                        a1.operation || ' ' || a1.options a,
                                        b1.operation || ' ' || b1.options b,
                                        a1.SQL_ID,
                                        a1.HASH_VALUE
                          from (SELECT p.operation,
                                       p.options,
                                       p.OBJECT_name,
                                       p.ID id1,
                                       p.SQL_ID,
                                       p.HASH_VALUE
                                  FROM v$sql_plan p
                                 where p.object_name in
                                       (SELECT t.table_name
                                          from user_part_tables t)
                                   and p.operation = 'TABLE ACCESS'
                                   and p.options = 'FULL') a1
                          join((SELECT p.operation,
                                      p.options,
                                      p.OBJECT_name,
                                      p.ID + 1 id1,
                                      p.SQL_ID,
                                      p.HASH_VALUE
                                 FROM v$sql_plan p
                                where p.operation = 'PARTITION RANGE'
                                  and p.options = 'ALL')) b1
                            on (a1.sql_id = b1.sql_id and a1.id1 = b1.id1)) c1
                  join v$sqlarea s1
                    on (s1.SQL_ID = c1.SQL_ID and
                       s1.HASH_VALUE = c1.HASH_VALUE)) d1
          join (SELECT sql_text "正在执行的SQL",
                      SQL_FULLTEXT,
                      ROUND(ELAPSED_TIME / 1000000 / (CASE
                              WHEN (EXECUTIONS = 0 or NVL(EXECUTIONS, 1) = 1) THEN
                               1
                              ELSE
                               EXECUTIONS
                            END),
                            2) "执行时间'S'",
                      sql_id
                 FROM v$sqlarea s
                where S.COMMAND_TYPE IN (2, 3, 5, 6, 189)) g1
            on (d1.sql_id = g1.sql_id)) f1
left join (SELECT hi1.sql_id,
                    hi1.machine,
                    hi1.module,
                    max(hi1.action) action,
                    hi1.sql_opname,
                    max(hi1.sql_exec_start) sql_exec_start,
                    max(hi1.event) event,
                    max(hi1.wait_class) wait_class
               FROM dba_HIST_ACTIVE_SESS_HISTORY hi1
              group by hi1.sql_id, hi1.machine, hi1.module, hi1.sql_opname) f2
    on (f1.sql_id = f2.sql_id);

查询结果为：

结论：

这是我第一次写这么复杂的sql，很吃力啊,不过高兴的是可以慢慢的成长慢慢的了解更多。

ITPub博客

sql查询之【找到是分区表但未使用分区条件的sql】

最新文章