ITPub博客

首页 > 数据库 > Oracle > Oracle 是分区表,但条件不带分区条件的SQL

Oracle 是分区表,但条件不带分区条件的SQL

原创 Oracle 作者:maohaiqing0304 时间:2014-08-13 11:29:41 0 删除 编辑
Oracle  是分区表,但条件不带分区条件的SQL(筛选条件:当天,查询超过1s,某些SQL类型)

/*
查找是分区表,但条件不带分区的SQL
COMMAND_TYPE:
查找的视图:V$SQLCOMMAND
2:INSERT
3:SELECT
6:UPDATE
7:DELETE
189:MERGE
注释:
先通过SQL执行计划视图中查询OPERATION||' '||OPTIONS=TABLE ACCESS FULL 对应ID上一层是PARTITION RANGE ALL
的SQL_ID,再查找详细的SQL语句(+筛选条件)
*/

SELECT S.SQL_TEXT,
       S.SQL_FULLTEXT,
       S.SQL_ID,
       ROUND (ELAPSED_TIME / 1000000 / ( CASE
               WHEN (EXECUTIONS = 0 OR NVL (EXECUTIONS, 1 ) = 1THEN
                1
               ELSE
                EXECUTIONS
             END ),
             2 ) "执行时间'S'",
       P1.OBJECT_OWNER,
       P1.OBJECT_NAME,
       P1.OPERATION,
       S.LAST_LOAD_TIME,
       --P1.P_PLAN_HASH_VALUE,
       S.PLAN_HASH_VALUE
  FROM V$SQLAREA S
  JOIN ( SELECT DISTINCT /*去重是因为 1SQL多次调用,执行计划一样  不去重会出现多值 最终SQL会出现多个*/
                        A.SQL_ID,
                        A.OBJECT_OWNER,
                        A.OBJECT_NAME,
                        P.OPERATION
          FROM (SELECT P.SQL_ID,
                       P.OBJECT_OWNER,
                       P.OBJECT_NAME,
                       P.PLAN_HASH_VALUE,
                       P.OPERATION || ' ' || P.OPTIONS "OPERATION",
                       P.ID, --不带ID 若一个SQL 2个分区表且2个分区表都没有加分区条件 会产生笛卡尔集
                       P.HASH_VALUE,
                       P.PLAN_HASH_VALUE P_PLAN_HASH_VALUE
                  FROM V$SQL_PLAN P
                 WHERE P.OPERATION || ' ' || P.OPTIONS =
                       'PARTITION RANGE ALL' ) P --查找执行计划是‘PARTITION RANGE ALL’ 分区全扫 ,而不是‘PARTITION RANGE SINGLE’部分分区扫描
          JOIN (SELECT SQL_ID,
                      P.OBJECT_OWNER,
                      P.OBJECT_NAME,
                      P.PLAN_HASH_VALUE,
                      P.OPERATION || ' ' || P.OPTIONS,
                      P.ID - 1 ID --执行计划 显示‘PARTITION RANGE ALL’在‘TABLE ACCESS FULL’ 下一行 也就是id-1和分区全扫的id,全部关联后才能过滤出真正的表
                      P.HASH_VALUE
                 FROM V$SQL_PLAN P
                WHERE (P.OBJECT_NAME IN
                      ( SELECT PT.TABLE_NAME FROM USER_PART_TABLES PT))
                  AND P.OPERATION || ' ' || P.OPTIONS = 'TABLE ACCESS FULL' --查找执行计划是‘TABLE ACCESS FULL’ 表全扫...
                  AND P.OBJECT_OWNER = '&USERNAME'
                  AND TO_CHAR(P.TIMESTAMP, 'YYYY-MM-DD' ) =
                      TO_CHAR( SYSDATE 'YYYY-MM-DD' )) A
            ON P.SQL_ID = A.SQL_ID
           AND P.ID = A.ID --2个关联条件最终得出 是分区表但没带分区条件的表/sql_id...等
        ) P1
    ON S.SQL_ID = P1.SQL_ID
  WHERE ROUND (ELAPSED_TIME / 1000000 / ( CASE
               WHEN (EXECUTIONS = 0 OR NVL (EXECUTIONS, 1 ) = 1THEN
                1
               ELSE
                EXECUTIONS
             END ),
             2 ) > 1 --100 0000微秒=1S
   AND S.PARSING_SCHEMA_NAME = '&USERNAME'
   AND TO_CHAR(S.LAST_LOAD_TIME, 'YYYY-DD-MM' ) =
       TO_CHAR( SYSDATE 'YYYY-DD-MM' )
   AND S.COMMAND_TYPE IN (2 35 6 189)
  ORDER BY S.ELAPSED_TIME DESC ;

祝好~

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28602568/viewspace-1250423/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
擅长SQL编写及SQL优化,分析瓶颈,性能调优、故障处理,根据实际情况定制备份策略; 擅长编写脚本来实现自动化功能,600+SQL优化经验案例,为人热爱学习,喜欢钻研技术,对工作认真负责。

注册时间:2013-03-13

  • 博文量
    121
  • 访问量
    2293878