V$SQL

(1) V$SQL의 정의와 특징

• 라이브러리 캐시에 캐싱되어 있는 각 Child에 대한 수행통계 뷰이다.
• 개별 SQL 커서의 수행 통계를 분석할 목적으로 많이 활용된다.
• 집중 튜닝이 필요한 대상 SQL을 선정하는 데 활용된다.
• 튜닝 전후 성능 향상도를 비교할 목적으로 통계를 내는 데도 활용된다.

(2) V$SQL의 컬럼 정보

select sql_id, child_number, sql_text, sql_fulltext, parsing_schema_name --① 라이브러리 캐시에 적재된 SQL 커서 자체에 대한 정보
, sharable_mem, persistent_mem ,runtime_mem                              --② SQL 커서에 의해 사용되는 메모리 사용량
, loads, invalidations, parse_calls, executions, fetches, rows_processed --③ 하드파싱 및 무효화 발생횟수, Parse, Execute, Fetch Call 발생 횟수
, Execute, Fetch Call 발생 횟수, Execute 또는 Fetch Call 시점에 처리한 로우 건수 등
, cpu_time, elapsed_time                                                 --④ SQL을 수행하면서 사용된 CPU time과 소요시간(microsecond)
, buffer_gets, disk_reads, sorts                                         --⑤ SQL을 수행하면서 발생한 논리적 블럭 읽기와 디스크 읽기, 그리고 소트 발생 횟수
, application_wait_time, concurrency_wait_time                           --⑥ SQL 수행 도중 대기 이벤트 때문에 지연이 발생한 시간(microsecond)
, cluster_wait_time, user_io_waait_time                                  --⑥ SQL 수행 도중 대기 이벤트 때문에 지연이 발생한 시간(microsecond)
, first_load_time, last_active_time                                      --⑦ 커서가 라이브러리 캐시에 처음 적재된 시점, 가장 마지막에 수행된 시점
from v$sql;

(3) SQL 한번 수행당 일량과 시간을 보여주는 쿼리

select parsing_schema_name 
    , count(*) sql_cnt 
    , count(distinct substr(SQL_TEXT, 1, 100)) sql_cnt2 
    , sum(executions) 
    , round(avg(buffer_gets/executions)) bugger_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 parsing_schema_name in ('원무', '공통', '진료', '사업/행정', '진료지원') 
    and last_active_time >= to_date('20200629','yyyymmdd') 
    group by parsing_schema_name;

(4) v$sql과 조인해서 추가 정보를 얻을 수 있는 뷰

v$sql_plan : 실행계획
v$sql_plan_statistics : 각 Row Source별 수행 통계
v$sql_bind_capture : 샘플링한 바인드 변수 값

(5) AWR 저장

• SQL 커서와 관련된 각종 수행 통계를 주기적으로 저장한다.
• 스냅샷 시점에 캐시에 남아있던 커서의 수행 통계만 저장한다.
• 다음 기준에 따라 Top SQL만 수집한다.
  • Parse Calls , Executions , Buffer Gets , Disk Reads , Elapsed Time , CPU Time , Wait Time , Version Count , Sharable Memory
• Colored SQL : 사용자가 명시적으로 지정한 커서의 수행통계가 AWR에 주기적으로 수집되도록 마크하는 기능이다.

[Colored SQL 지정방법]

-- 지정
SQL> begin
dbms_workload_repository.add_colored_sql(sql_id => 'abr8dkhpuh3gx');
end;
-- 조회
SQL> select * from dba_hist_colored_sql;
==================================
      DBID SQL_ID        CREATE_T
---------- ------------- --------
2822792603 abr8dkhpuh3gx 16/04/02
==================================
-- 해제
SQL> begin
     dbms_workload_repository.remove_colored_sql('abr8dkhpuh3gx');
     end;
-- 조회
SQL> select * from dba_hist_colored_sql;
선택된 레코드가 없습니다.