V$SQLAREAから情報取得（TEMP_SEG付き）

忘れないにメモ

select

    b.parsing_schema_name                         

  , b.SQL_FULLTEXT

  , b.OPTIMIZER_MODE                   as "モード"

  , b.OPTIMIZER_COST                   as "コスト"

  , round(b.cpu_time / 1000000, 2)     as "cpu_time 秒"

  , round(b.elapsed_time / 1000000, 2) as "elapsed_time 秒"

  , round(b.APPLICATION_WAIT_TIME / 1000000, 2) as "アプリケーション待機時間"

  --, round(b.CONCURRENCY_WAIT_TIME / 1000000, 2) as "同時実行性待機時間"

  --, round(b.CLUSTER_WAIT_TIME / 1000000, 2)     as "クラスタの待機時間"

  --   , round(b.USER_IO_WAIT_TIME / 1000000, 2)     as "ユーザーI/O待機時間"

  --   , round(b.PLSQL_EXEC_TIME / 1000000, 2)       as "PL/SQL実行時間"

  --   , b.ROWS_PROCESSED                            as "戻す行数の合計"

  --   , b.SORTS                                     as "実行されたソート数"

  --   , b.FETCHES                                   as "SQL文に関連するフェッチ数"

  --   , b.PARSE_CALLS                               as "解析コール数"

  , b.DISK_READS                       as "ディスク読取り数"

  , b.DIRECT_WRITES                    as "直接書込み数"

  , b.BUFFER_GETS                      as "バッファ取得数"

  , a.LAST_TEMPSEG_SIZE

  , a.MAX_TEMPSEG_SIZE

  , c.LAST_TEMPSEG_SIZE

  , c.MAX_TEMPSEG_SIZE

  , b.SQL_TEXT                         as "SQL" 

from

  v$sqlarea b

  , V$SQL_PLAN_STATISTICS_ALL a

  , V$SQL_WORKAREA c 

where

  b.elapsed_time > 10000000

  --and application_wait_time > '0';

  --and sql_text like '%aaa%'

  and b.parsing_schema_name is not null 

  and b.parsing_schema_name <> 'SYSTEM' 

  and b.parsing_schema_name <> 'SYS' 

  and b.parsing_schema_name <> 'SYSMAN' 

  and b.parsing_schema_name <> 'DBSNMP' 

  and a.address = b.address 

  AND a.hash_value = b.hash_value 

  and c.address = b.address 

  AND c.hash_value = b.hash_value 

  and ( 

    ( 

      a.last_tempseg_size is not null 

      or a.max_tempseg_size is not null

    ) 

    or ( 

      c.last_tempseg_size is not null 

      or c.max_tempseg_size is not null

    )

  )