Wednesday, September 7, 2011

Find which SQL operations cause one pass and multipass work area executions

SELECT S.SQL_FULLTEXT,
       W.OPERATION_TYPE,
       W.ESTIMATED_OPTIMAL_SIZE,
       W.ESTIMATED_ONEPASS_SIZE,
       W.LAST_MEMORY_USED,
       W.LAST_EXECUTION,
       W.OPTIMAL_EXECUTIONS,
       W.ONEPASS_EXECUTIONS,
       W.MULTIPASSES_EXECUTIONS,
       W.LAST_TEMPSEG_SIZE,
       W.MAX_TEMPSEG_SIZE
  FROM V$SQL_WORKAREA W, V$SQL S
 WHERE     W.ADDRESS = S.ADDRESS
       AND W.HASH_VALUE = S.HASH_VALUE
       AND W.CHILD_NUMBER = S.CHILD_NUMBER
       AND (W.ONEPASS_EXECUTIONS > 0 OR W.MULTIPASSES_EXECUTIONS > 0);