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

来源：互联网发布：2016年淘宝双11抢红包编辑：程序博客网时间：2024/06/15 19:32

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

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

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 ;

祝好~

0 0