10.V$SQL

## V$SQL 특징

\- v$sql은 다른 동적 뷰 처럼 누적값이 저장 되어있다.
\- v$sql의 각 컬럼에 대해서 SQL수행 횟수로 나눈 평균값으로 분석을 해야한다.
\- v$sql은 쿼리가 수행을 마칠때 마다 갱신 되며, 오랫동안 수행되는 쿼리는 5초마다 갱신된다.

\- v$sql_plan을 통해 실행계획을 확인
\- v$sql_plan_statistics를 통해 각 Row Source별 수행 통계를 확인
\- v$sql_bind_capture를 통해 정해진 기간에 한번씩 샘플링한 바인드 변수 값을 확인 할 수 있다.
  더 자주 샘플링하도록 하려면 \_cursor_bind_capture_interval 파라미터 값을 줄이면 되고, 기본 설정 값은 900초다.

## v$sql 을 이용한  SQL 튜닝 예시

select parsing_schema_name "업무"

       , count(*) "SQL 개수"

       , count(distinct substr(sql_text, 1, 100)) "SQL 개수(Unique)"

       , sum(executions) "수행횟수"

       , round(avg(buffer_gets/executions)) "논리적 I/O"

       , round(avg(disk_reads/executions)) "디스크 I/O"

       , round(avg(rows_processed/executions)) "처리건수"

       , round(avg(elapsed_time/executions/1000000),2) "평균소요시간"

       , count(case when elapsed_time/executions/1000000 >= 10 then 1 end) "악성SQL (10초 이상)"

       , round(max(elapsed_time/executions/1000000),2) "최대 소요시간"

from v$sql

where parsing_schema_name in ('원무', '공통', '진료', '사업/행정', '진료지원')

   and last_active_time >= to_date('20090315','yyyymmdd')

group by parsing_schema_name;


 - 세 번째 컬럼 'SQL개수(Unique)'는 SQL 문자열 중 선행 100개 문자가 같으면 동일한 SQL인 것으로 간주하고 집계한 것이다.
 - 같은 SQL인데도 바인드 변수를 사용하지 않으면 Literal 상수값 별로 오라클이 다른 sql_id를 부여해 SQL 개수가 무수히 많은 것으로 집계되는 오류를 보완하려는 것이다.
 - SQL을 식별할 목적으로 select, insert, update, delete 키워드 바로 뒤에 주석으로 고유한 SQL 식별자를 적어 놓기 때문에 선행 100개 문자가 같으면 동일SQL로 간주한다.

 

\- '진료지원' 업무에서 라이브러리캐시에 로드된 총 SQL 개수가 8,680개인데, Unique하게는 1,822개이므로 바인드 변수를 사용하지 않아 각각 하드파싱을 발생시키며 캐시에 로드된 SQL 비중이 매우 높은 것을 알 수 있다.
   SQL이 공유될 수 있도록 바인드 변수를 사용하는 방식으로 프로그램을 수정할 필요가 있다.

\- '공통' 업무를 보면, 한번 수행할 때의 평균 논리적 I/O가 11,688개로 다른 업무에 대해 매우 높게 나타나고 있다.
   논리적 I/O가 많다 보니 디스크 I/O도 많고 당연히 쿼리 소요시간도 가장 높게 나타나고 있다.
   게다가 SQL개수는 가장 적지만 수행 횟수는 가장 많다. 따라서 가장 먼저 시급하게 튜닝해야 할 대상 업무로 판단할 수 있다.

# 오라클은 v$sql을 포함해 SQL 커서와 관련된 각종 수행 통계를 주기적으로 AWR에 저장한다.
#  스냅샷 시점에 캐시에 남아 있는 SQL의 수행 통계 모두를 저장 하지는 않고, 아래의 기준에 따라 Top SQL만 저장한다.

\- Parse Calls
\- Executions
\- Buffer Gets
\- Disk Reads
\- Elapsed Time
\- CPU Time
\- Wait Time
\- Version Count
\- Sharable Memory

## 11g New feature Colored SQL

\- 사용자가 명시적으로 지정한 커서의 수행통계가 AWR에 주기적으로 수집되도록 하는 기능
\- 단, 스냅샷 시점네 캐시에서 밀려나도 없는 SQL 정보까지 저장할 수는 없다.

# 사용 예시

-- 사용자 임의로 AWR 수집되도록 설정 --


begin

dbms_workload_repository.add_colored_sql(sql_id => '803b7z0t84sp7')

end;


-- Colored SQL 조회 --


select * from dba_hist_colored_sql;

select * from wrm$_colored_sql;


-- AWR에 저장된 Colored SQL 실행계획 조회 --


select * from table(dbms_xplan.display_awr('803b7z0t84sp7', NULL, NULL, 'basic rows bytes cost'));


-- Colored SQL 제거 --

begin

dbms_workload_repository.remove_colored_sql('803b7z0t84sp7')

end;