[Oracle] V$SQL 활용한 SQL 성능 분석

V$SQL 라이브러리 캐시에 캐싱된 각 child 커서에 대한 통계정보를 보여줌

 

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 --7
     , first_load_time, last_active_time --8
 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. 커서가 라이브러리 캐시에 처음 적재된 시점, 가장 마지막에 수행된 시점 

 

 

v$sql 통계치는 누적값임 적절한 계산을 통해 의미있는 분석 가능

 select parsing_schema_name 
      , count(1) sql_cnt
      , count(distinct substr(sql_text,1,100)) sql_cnt2
      , sum(executions) exec_cnt
      , round(avg(buffer_gets/executions)) as buffer_gets_avg --논리적i/o
      , round(avg(disk_reads/executions)) as disk_reads_avg --디스크 i/o
      , round(avg(rows_processed/executions)) as rows_processed_avg
      , round(avg(elapsed_time/executions/1000000),2) as elapsed_time_avg --평균 소요시간
      , count(case when elapsed_time/executions/1000000 >= 10 then 1 end) as bad_sql --슬로우쿼리 
      , round(max(elapsed_time/executions/1000000),2) as elapsed_time_max
  from v$sql
 where last_active_time > sysdate-1 and executions > 0
 group by parsing_schema_name;

 

 

 

출처 : 오라클 성능 고도화 원리와 해법 1 V$SQL