v$sql 을 사용해서 전체 시스템의 쿼리 수행 통계정보를 조회하는 쿼리

v$sql 의 값들은 누적치.

SQL 수행횟수로 나눈 평균값, SQL한번 수행당 얼만만큼의 일량과 시간을 소비하는 지 계산해야 의미 있는 분석이 가능함.

1. 스키마별 쿼리 수행 통계

select parsing_schema_name 

     , count(*) sql_cnt

     , count(distinct substr(sql_text, 1, 100)) sql_cnt2

     , sum(executions) executions

     , round(avg(buffer_gets/executions)) buffer_gets

     , round(avg(disk_reads/executions)) disk_reads

     , round(avg(rows_processed/executions)) rows_processed

     , round(avg(elapsed_time/executions/1000000),2) "ELAPSED_TIME(AVG)"

     , count(case when elapsed_time/executions/1000000 >= 10 then 1 end) "BAD SQL"

     , round(max(elapsed_time/executions/1000000),2) "ELAPSED_TIME(MAX)"

from   v$sql

where  last_active_time >= sysdate - 7

and    executions > 0

group by parsing_schema_name

2. 실행예

3. 개별쿼리별 조회

select 

         sql_id  , child_number , sql_text  , sql_fulltext   , parsing_schema_name      -- (1)

       , sharable_mem    , persistent_mem    , runtime_mem                             --(2)

       , loads    , invalidations    , parse_calls    , executions   , fetches   , rows_processed  --(3)

       , cpu_time   , elapsed_time                                                             -- (4)

       , buffer_gets   , disk_reads   , sorts                                                  -- (5)

       , application_wait_time , concurrency_wait_time                                    -- (6)

       , cluster_wait_time , user_io_wait_time                                               -- (6)

       , first_load_time , last_active_time                                                     --(7)

from v$sql;

1) 라이브러리 캐시에 적재된 SQL 커서 자체에 대한 정보

2) SQL 커서에 의해 사용되는 메모리 사용량

3) 하드파싱 및 무효화 발생횟수, Parse, Execute, Fetch Call 발생 횟수, Execute 또

는 Fetch Call 시점에 처리한 로우 건수 등

4) SQL을 수행하면서 사용된 CPU time과 소요시간(microsecond)

5) SQL을 수행하면서 발생한 논리적 블록 읽기와 디스크 읽기, 그리고 소트 발생 횟수

6) SQL 수행 도중 대기 이벤트 때문에 지연이 발생한 시간(microsecond)

7) 커서가 라이브러리 캐시에 처음 적재된 시점, 가장 마지막에 수행된 시점