ORACLE 9i V$SQLAREA
From OTN 10g R2 v$sqlarea
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2.htm#REFRN30256
From OTN 9i R2 v$sqlarea
http://download.oracle.com/docs/cd/B10501_01/server.920/a96536/ch3204.htm#1126299
1. v$sqlarea
A.
뷰를 조회한 내용은 아래와 같다.
SQL> desc v$sqlarea
Name
Null? Type
-----------------------------------------
-------- ----------------------------
SQL_TEXT
VARCHAR2(1000)
SHARABLE_MEM NUMBER
PERSISTENT_MEM NUMBER
RUNTIME_MEM NUMBER
SORTS NUMBER
VERSION_COUNT NUMBER
LOADED_VERSIONS NUMBER
OPEN_VERSIONS NUMBER
USERS_OPENING NUMBER
FETCHES
NUMBER
EXECUTIONS NUMBER
USERS_EXECUTING NUMBER
LOADS NUMBER
FIRST_LOAD_TIME
VARCHAR2(19)
INVALIDATIONS NUMBER
PARSE_CALLS NUMBER
DISK_READS NUMBER
BUFFER_GETS NUMBER
ROWS_PROCESSED NUMBER
COMMAND_TYPE NUMBER
OPTIMIZER_MODE
VARCHAR2(25)
PARSING_USER_ID NUMBER
PARSING_SCHEMA_ID NUMBER
KEPT_VERSIONS NUMBER
ADDRESS
RAW(4)
HASH_VALUE NUMBER
MODULE
VARCHAR2(64)
MODULE_HASH NUMBER
ACTION
VARCHAR2(64)
ACTION_HASH NUMBER
SERIALIZABLE_ABORTS NUMBER
CPU_TIME
NUMBER
ELAPSED_TIME NUMBER
IS_OBSOLETE VARCHAR2(1)
CHILD_LATCH NUMBER
위에서 스스로 생각하기에 중요한 부분을 적색으로 표시하였다.
SQL_TEXT, VERSION_COUNT, FETCHES,
EXECUTIONS, INVALIDATIONS
PARSE_CALLS, OPTIMIZER_MODE, CPU_TIME
B.
위의 컬럼에 대해 설명하면 아래와
같다.
i.
SQL_TEXT
말 그대로 SQL TEXT 를 저장하고 있는 컬럼이다. TYPE을 보면 알다시피 VARCHAR2(1000) 으로 길이가 제한되어있다. 10G R2 REF 를
찾아보면 SQL_FULLTEXT 라고 CLOB 으로 제공되는
컬럼이 있다. /*+ 1000 이 넘어가면 어떤 일이 벌어지는지 샘플을 만들어 볼 필요가 있다. 주석도 텍스트에 들어가는지 추가 테스트 */
ii.
VERSION_COUNT
현재 이 PARENT CURSOR 에 연결되어있는 CHILD CURSOR 의
수
/*+ CHILD CURSOR 는
이 값이 어떻게 나오는지 확인 */
iii.
FETCHES
FETCHED 회수
iv.
EXECUTIONS
CHILD CURSORS 를 통튼
실행회수의 합
v.
INVALIDATIONS
CHILD CURSORS 의 무효화
회수(DDL 과 관련있다.)
vi.
PARSE_CALLS
부모 CURSOR 에 연결되어있는 CHILDE CURSOR의 PARSE CALLS 의 총 합
vii.
OPTIMIZER_MODE
SQL 이 실행될 때의 OPTIMIZER MODE
viii.
CPU_TIME
말그대로
사용한 CPU TIME 이다. 9i R2 에는 9.2 에 대한 REF 가 나와있고 해당 내용에는 이 컬럼에 대한
소개가 없다. (문서가 오래된 듯)
2. V$sqlarea 의 활용
A.
수행되었던 sql 의 조회
B.
특정 sql 이 cpu, mem i/o 자원을 얼마나 사용했는지 조회
C.
특정 sql 이 어떤 optimizer mode로 실행플랜을 생성했는지
확인
( 실행플랜 내용 자체는 이 뷰에는
존재하지 않는다 )
D.
literal
sql 의 존재 확인
3. Literal SQL 확인 예제
declare
tmp
number;
begin
for I in
1..100 loop
execute
immediate ‘select ‘||i||’ from dual’ into tmp;
end loop;
end;
/
위 쿼리는
수행하는 쿼리에 대해 모두 HARD PARSING 즉 실행플랜을 가지게 하는 PL/SQL 블록이다. 실행 후 v$sqlarea
를 조회해 보겠다.
SQL> get tmp
1 select sql_text,executions
2 from v$sqlarea
3* where
sql_text like '%from dual%'
SQL> /
SQL_TEXT
EXECUTIONS
--------------------------------------------------
----------
select 1 from dual 1
select 2 from dual 1
select 3 from dual 1
select 4 from dual 1
select 5 from dual 1
select 6 from dual 1
~~~ 중략 ~~~
SQL_TEXT
EXECUTIONS
--------------------------------------------------
----------
select 100 from dual 1
select sql_text,executions from v$sqlarea where sq 2
l_text like '%from dual%'
select sql_text,executions,parsing_user_id from v$ 3
sqlarea where sql_text like '%from dual%'
declare tmp number; begin
for i in 1
1..100 loop execute immediate 'sele
ct '||i||' from dual' into tmp; end loop;
end;
위 내용을
보면 1~100 까지 있는걸 확인할 수 있다. 각각은 한번씩
수행되고 말았다. LITERAL SQL은 여러모로 시스템에 악영향을 끼친다. 여기서 깊히 보기는 힘들고 추후에 다시 언급하도록 하겠다. ( LITERAL
SQL은 SGA MEMORY 단편화 문제를 촉발한다. )
위 문제를
해결하는 방안으로 바인드 변수를 사용하거나 CURSOR_SHARING PARAMETER 를 가지고 해결하는
방법이 있다. 후자는 별로 권하는 분위기는 아닌 듯 하다;
/*+ 바인드 변수 사용해 literal sql 축소하는 예제 추가 */