Oracle 是分割槽表，但條件不帶分割槽條件的SQL

dbhelper發表於2014-11-27
OracleSQL
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 ) = 1) THEN
                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 ) = 1) THEN
                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 , 3, 5 , 6 , 189)
  ORDER BY S.ELAPSED_TIME DESC ;

祝好~

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8494287/viewspace-1349447/，如需轉載，請註明出處，否則將追究法律責任。

相關文章