V$SQLAREAの情報取得
忘れないようにメモ
select
parsing_schema_name
--, SQL_FULLTEXT
, OPTIMIZER_MODE as "モード"
, OPTIMIZER_COST as "コスト"
, round(cpu_time / 1000000, 2) as "cpu_time 秒"
, round(elapsed_time / 1000000, 2) as "elapsed_time 秒"
, round(APPLICATION_WAIT_TIME / 1000000, 2) as "アプリケーション待機時間"
, round(CONCURRENCY_WAIT_TIME / 1000000, 2) as "同時実行性待機時間"
, round(CLUSTER_WAIT_TIME / 1000000, 2) as "クラスタの待機時間"
, round(USER_IO_WAIT_TIME / 1000000, 2) as "ユーザーI/O待機時間"
, round(PLSQL_EXEC_TIME / 1000000, 2) as "PL/SQL実行時間"
, ROWS_PROCESSED as "戻す行数の合計"
, SORTS as "実行されたソート数"
, FETCHES as "SQL文に関連するフェッチ数"
, PARSE_CALLS as "解析コール数"
, DISK_READS as "ディスク読取り数"
, DIRECT_WRITES as "直接書込み数"
, BUFFER_GETS as "バッファ取得数"
from
v$sqlarea
where
elapsed_time > 10000000
--and application_wait_time > '0';
--and sql_text like '%aaa%'
--and parsing_schema_name like '%aaa%'
and parsing_schema_name is not null
and parsing_schema_name <> 'SYSTEM'
and parsing_schema_name <> 'SYS'
and parsing_schema_name <> 'SYSMAN'
and parsing_schema_name <> 'DBSNMP'