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
;
祝好~