Table Full Scan을 수행하는 SQL 추출

원본자료:http://www.dbguide.net/knowledge.db?cmd=view&boardConfigUid=19&boardUid=176417

 

 

SELECT /*+ ORDERED USE_NL(A SQ)*/
       SQL_FULLTEXT
      ,SQ.SQL_ID
      ,PLAN_HASH_VALUE
      ,SQ.MODULE
      ,TRUNC(CPU_TIME/DECODE(EXECUTIONS,0,1,EXECUTIONS)/1000/1000,2) AS CPU_ONE
      ,TRUNC(ELAPSED_TIME/DECODE(EXECUTIONS,0,1,EXECUTIONS)/1000/1000,2) AS EPLAP
      ,TRUNC(BUFFER_GETS/DECODE(EXECUTIONS,0,1,EXECUTIONS)/1000/1000,2) AS BUFFER_GET
FROM (SELECT DISTINCT SQL_ID
        FROM V$SQL_PLAN
       WHERE OBJECT_OWNER NOT IN ('SYS','SYSTEM')
          AND OPTIONS LIKE '%FULL%'
      )A
     ,V$SQLAREA SQ
WHERE SQ.SQL_ID=A.SQL_ID
--AND SQ.MODULE NOT IN ('DBMS_SCHEDULER',);
AND SQ.MODULE IN ('JDBC Thin Client')
AND SQ.SQL_FULLTEXT LIKE '%PCA%'