[본문스크랩] [SGA튜닝]V$SQLAREA에 관하여~

고흐 | 고흐
http://blog.naver.com/novaculite/120012048251

V$SQLAREA

V$SQLAREA는 SQL튜닝 등에 유용하게 이용 될 수 있는 데이터딕셔너리의 성능 뷰 입니다.  V$SQL, V$SQLTEXT와 같은 뷰들도 비슷한 정보를 제공 합니다. V#SQLAREA는 SGA내 SHARE POOL에 존재하는 SQL문에 대한 PARSE 정보 즉 SQL TEXT, PARSING횟수, REPARSING 횟수와 EXECUTE에 관한 정보 EXECUTE 횟수, RE-LOADING 횟수, SQL문이 재사용되지 못한 횟수, SORTING 횟수, SQL문중 메모리를 많이 점유하는 SQL 확인, 많은 DISK IO를 일으키는 SQL문을 검사 할 때 유용하게 사용 됩니다.

실습을 위한 Table인 myemp를 생성 합니다.

SQL>conn scott/tiger
SQL> create table myemp (
  2      empno number not null primary key,
  3      ename varchar2(20) not null,
  4      sal number(7,2),
  5      job varchar2(20),
  6      mgr number);

테이블이 생성되었습니다.

Table을 생성 후 SQL*Loader를 이용하여 데이터를 6만5000건 정도 입력 하였습니다.
SQL*Loader에 관한 사항은 Oracle Tip에서 확인 바라구요, 방법에 대해 간단히 설면 드리면 Excel에서 데이터를 몇 개 만들어 복사를 해서 65000 건 만든 후 저장 할ㄸ CSV 파일로 저장(emp.csv)을 하여 공백으로 구분토록 하였습니다. 아래의 emp.ctl 파일과 emp.csv 파일을 같은 폴더에 둔 후 SQL*Loader를 실행 하기 바랍니다.

다음은 콘트롤 파일 입니다.

Emp.ctl
LOAD DATA
INFILE 'emp.csv'      
APPEND                  
INTO TABLE myemp
FIELDS TERMINATED BY ','
(empno, ename, sal, job, mgr)

SQL*Loader를 실행 합니다.

D:\sqlloader>sqlldr userid=scott/tiger control='emp.ctl' log=emp.log

Scott/tiger로 접속을 하여 다음과 같은 질의를 실행 해 봅니다.

SQL>select ename, sal from myemp order by job, ename        
SQL>select job, sum(sal) from myemp              
SQL>select job, sum(sal) from myemp group by job        

다음 관리자 계정으로 접속을 하여 V$SQLAREA를 확인해 보겠습니다.

SQL> conn / as sysdba
연결되었습니다.
SQL> select sql_text,
  2         version_count,
  3         loads,
  4         invalidations,
  5         parse_calls,
  6         sorts
  7  from v$sqlarea
  8  where instr(sql_text,'myemp') > 1
  9  and   command_type in (2,3,6,7)
10  order by sql_text
11  /


대충 아래와 같은 결과가 나타납니다.

sql_text                                       version_count  loads  invalidations  parse_calls sorts
----------------------------------------------------------------------------------------------------
select ename, sal from myemp order by job, ename        1        1        0               1         1
select job, sum(sal) from myemp                              1        1        0               0         0
select job, sum(sal) from myemp group by job                1        1        1               0         0


만약 SHARED_POOL을 CLEAR할려면 다음과 같이 하세요~

SQL> conn / as sysdba
연결되었습니다.
SQL> alter system flush shared_pool;

물론 CLEAR 한후 V$SQLAREA를 이용하여 조회 한다면 비어 있겠죠^^;



command_type의 2는 insert문, 3은 select문, 6은 update문, 7은 delete문을 나타내며 컬럼들의 의미는 다음과 같습니다.

Version_count : 사용자 계정은 다르지만 테이블 이름이 같은 경우에 이를 식별하기 위해 사용하는 컬럼 입니다. 만약 서로 다른 계정에서 각각 만든 이름이 같은 테이블을 실행시 SQL_TEXT는 하나만 존재 하지만 version_count는 다르게 나타납니다. 만약 scott계정의 myemp 테이블에서 select * from myemp를 실행 후 sys계정에서 myemp를 똑같이 만들어 실행 한다면  나중에 실행되는 select * from myemp만 X$SQLAREA로 조회되며 version_count는 2가 되는 것입니다.

Load : parsing시 SQL문이 Library Cache 영역에서 발견되지 않으면 parsing후 parse 정보를 library cache로 로딩하게 됩니다. Loads가 1이라면 처음으로 로드 된 것을 의미 합니다. 즉 Library Cache에 로드된 횟수를 나타내므로 1보다 큰 경우엔 좋지 못합니다.

Invalidation : library cache에 이미 SQL문장이 한번 이상 수행되었다고 할 때 그 SQL문장은 로드되어 있을 겁니다. 그때 사용자가 alter table, drop, analyze등을 myemp에 대해 수행 했다고 한다면 현재 library cache에 있는 parsing 정보에 대해 더 이상 무결성을 보장 할 수 없으므로 SQL문이 실행 된다면 다시 parse 과정을 거치게 됩니다. 그러한 경우에 invalidations 컬럼이 set 됩니다.

parse_calls : 실행한 SQL 문장이 처음 사용된 문장이라면 LOADS 컬럼에 1이 할당 됩니다. 이후 다른 사용자(또는 동일한 세션)에 의해 SQL문장이 재사용 되어 진다면 PAESING은 발생하지 않고 이미 PARSING된 정보를 재 사용 합니다. 이때 parse_calss 컴럼의 값이 증가 합니다.