Oracle

PostgreSQL과 ORACLE의 Lateral Inline View에 대한 짤막한 내용

PostgreSQL 연구와 집필 때문에 중단한 기술 포스팅을 오랜만에 다시 시작합니다.
이번 시간에 살펴볼 내용은 Lateral Inline View입니다.

Lateral Inline View란?


Lateral Inline View를 그대로 해석하면 “측면 인라인 뷰”이며, 이는 Lateral Inline View의 동작 원리를 매우 적절히 표현한 것으로 보입니다.

Lateral Inline View에 대해서 알게 된 것은 매우 오래전에 오동규 님의 블로그를 통해서입니다.

그동안 이런 것이 있다는 정도만을 기억한 채로 PostgreSQL을 연구하던 중에 PostgreSQL은 9.3 버전부터 Lateral Inline View를 지원한다는 것을 알게 됐습니다.

그래서 Oracle의 지원 여부를 확인해보니 12c부터 지원이 된다는 사실을 알았습니다. (아래 링크 참조. Oracle 공부를 게을리한 탓에 너무 늦게 안 셈이죠. ㅠㅠ)

https://oracle-base.com/articles/12c/lateral-inline-views-cross-apply-and-outer-apply-joins-12cr1

Lateral Inline View의 활용


Lateral Inline View는 크게 2가지 용도로 활용할 수 있을 것 같습니다.

  1.  JPPD (Join Predicate Push-Down)을 사용자가 직접 구현
  2. 테이블과 Function 조인

1. JPPD (Join Predicate Push-Down)을 사용자가 직접 구현

JPPD를 직접 구현하는 것은 PostgreSQL에서는 반드시 필요한 부분입니다. 왜냐면 PostgreSQL은 아직 View Merging이나 JPPD가 매우 제약적인 상황에서만 가능하기 때문입니다. 따라서 튜닝을 위해서는 Lateral View를 직접 사용해야하는 경우가 많습니다.

그런데 ORACLE은 버전이 올라갈수록 이전 버전에서 존재하던 Complex View Merging과 JPPD의 제약 사항을 점차 해결하고 있습니다. 12c 버전에서는 JPPD가 안되는 경우를 거의 찾아보기 힘들 정도입니다. 그나마 안되는 경우는 MINUS의 경우입니다. 이 같은 경우에는 Lateral View를 사용해서 JPPD를 구현함으로써 튜닝이 가능합니다. (아래 링크 참조)

https://asktom.oracle.com/pls/apex/f?p=100:11:::NO:RP:P11_QUESTION_ID:9530805900346903033

2. 테이블과 Function 조인

Lateral Inline View를 이용하면 테이블 추출 값을 Function의 아규먼트로 입력하는 작업을 매우 편리하게 할 수 있습니다.

아래는 T1 테이블의 모든 블록(0~28571)의 Frozen 상태를 확인하기 위해서 get_raw_page function에 0~28571을 입력한 후에 t_infomask 칼럼의 FROZEN 비트를 확인하는 예제입니다.

select substr(cast(b.t_infomask::bit(16) as text),7,1) frozen_flag, 
       count(*)
from  (select i from generate_series(0,28571) a(i)) a, 
       LATERAL (select * from heap_page_items(get_raw_page('t1', a.i))) b
group by substr(cast(b.t_infomask::bit(16) as text),7,1);

아래는 ORACLE에서 사용자 Function을 생성한 후에 테이블 추출 값을 이용해서 Function 결과를 출력하는 예제

create or replace function func1(p1 IN NUMBER)
return NUMBER
is
  v_ret number;
begin
  v_ret := p1+1;
  return v_ret;
end;
/

select a.*, b.*
from   (select level lv from dual connect by level<=10) a,
       LATERAL (select func1(a.lv) from dual) b;    
        LV FUNC1(A.LV)
---------- -----------
         1           2
         2           3
         3           4
         4           5
         5           6
         6           7
         7           8
         8           9
         9          10
        10          11

이상으로 Laeral Inline View에 대한 간단한 설명을 마무리하겠습니다.

리눅스 perf 명령어를 이용한 오라클 Kernel Function 분석 방법

일반적으로 오라클 성능 분석 방법론은 대기 시간의 분석에 초점이 맞춰져 있습니다. 즉, 응답시간을 CPU 사용시간과 대기시간으로 구분하고 대기시간은 개별 대기이벤트의 대기시간으로 세분화함으로써 성능 저하의 원인을 빠르게 진단하는 방법을 사용합니다. 이러한 분석의 기본 전제는 대기시간보다 CPU 사용시간의 비율이 높다면 현재 수행되는 프로세스는 정상적으로 일을 잘하고 있다는 것입니다. 반대로 말하면 대기시간이 비중이 높을수록 성능 이슈가 있다는 것을 의미하기도 합니다.

그런데 간혹 “CPU 사용률(%)이 높은 프로세스가 정상적으로 일을 처리하고 있는지?” 라는 의문이 생길 때가 있습니다. 일반적으로 이런 경우에 사용할 수 있는 방법은 truss, tusc, strace등과 같은 유틸리티를 이용해서 OS 시스템 콜을 분석하는 것입니다. 하지만 이러한 툴들이 제공하는 최하위 레벨의 시스템 콜을 분석하는 것은 매우 어려운 일입니다.

이를 보완하기 위한 방법으로 리눅스의 경우에는 “perf” 툴을 이용해서 스택 트레이스를 확인할 수 있으며, 이를 통해 오라클 커널 function을 확인할 수 있습니다.

(more…)

V$SESSION 뷰의 WAIT_TIME_MICRO 및 TIME_SINCE_LAST_WAIT_MICRO 칼럼에 대한 명확한 이해

1. SECONDS_IN_WAIT 및 WAIT_TIME 칼럼의 문제점


11g 부터 제공되는 WAIT_TIME_MICROTIME_SINCE_LAST_WAIT_MICRO 칼럼은 기존에 제공되던 SECONDS_IN_WAIT 및 WAIT_TIME 칼럼의 한계를 뛰어넘는 매우 유용한 데이터를 제공합니다. 기존에 제공되는 SECONDS_IN_WAIT 및 WAIT_TIME 칼럼의 한계점은 다음과 같습니다.

  • 세션 상태가 WAITING인 경우, SECONDS_IN_WAIT 칼럼은 대기이벤트가 지속된 시간을 제공합니다. 하지만 enqueue를 제외한 일반적인 대기이벤트 (예를 들어 db file sequential read) 들의 1회 대기 시간은 대략 수 밀리세컨 ~ 수십 밀리세컨 정도이므로 초 단위로 제공되는 SECONDS_IN_WAIT 칼럼에서는 대부분 0으로 표시됩니다.
  • 세션 상태가 WAITING이 아닌 경우 (ON CPU 상태인 경우), CPU를 사용한 시간을 확인할 수 가 없습니다.
  • 세션 상태가 WAITING이 아닌 경우 (ON CPU 상태인 경우), 이전 대기가 지속된 시간을 확인할 수 없습니다. 단순히 이전 대기가 1/100초 이내였는지 정도의 여부만을 확인할 수 있을 뿐입니다.
이러한 구조적인 한계점으로 인해 11g 부터는 SECONDS_IN_WAIT 및 WAIT_TIME 칼럼 대신에 WAIT_TIME_MICRO 및 TIME_SINCE_LAST_WAIT_MICRO 칼럼을 사용하는 것을 권고하고 있습니다.

(more…)

V$SESSION 뷰의 LAST_CALL_ET 및 SQL_EXEC_START 칼럼에 대한 명확한 이해

1. LAST_CALL_ET 및 SQL_EXEC_START 칼럼 개요


V$SESSION 뷰의 LAST_CALL_ET (Last Call Elapsed Time) 칼럼은 이름에서도 알 수 있듯이 마지막 콜을 수행한 후, 현재시점까지의 경과 시간을 제공합니다. 따라서 세션 상태가 ‘ACTIVE’ 라면 LAST_CALL_ET 칼럼에서 제공하는 수치는 최근에 수행한 SQL의 수행 시간이며, 세션 상태가 ‘INACTIVE’라면 IDLE 상태가 지속된 시간을 의미합니다. 따라서 현재 세션이 수행하는 SQL의 응답시간을 확인할 때나 INACTIVE 상태가 지속된 시간을 확인할 때 LAST_CALL_ET 칼럼은 매우 유용합니다.

(more…)

V$OSSTAT, V$SYS_TIME_MODEL 및 V$SYSTEM_EVENT 뷰를 이용한 시스템 레벨 모니터링 방안

이번 시간에는 시스템 레벨의 모니터링에 유용한 3개의 뷰를 이용해서 작성한 모니터링 스크립트를 소개합니다. (꼬박 하루동안 이것만 🙂) 3개의 뷰는 V$OSSTAT, V$SYS_TIME_MODELV$SYSTEM_EVENT 이며 뷰들의 용도는 다음과 같습니다.

  • V$OSSTAT : OS CPU, Run-Queue, Memory, Swap 등의 정보를 제공하기 위해 사용합니다.
  • V$SYS_TIME_MODEL
    1. CPU(%)를 DB CPU(%)과 NON DB CPU(%)로 구분해서 제공하기 위해 사용합니다. 또한 DB CPU(%)는 Foreground CPU(%) 와 Background CPU(%)로 구분합니다. (이를 위해 V$OSSTAT 뷰의 일부 데이터를 이용합니다)
    2. V$SYSTEM_EVENT 뷰와 조인해서 DB CPU, DB Elapsed Time, Wait Class, Wait Event 발생 현황을 트리 구조로 제공합니다.

(more…)