정적 SQL의 이점

• 정적 SQL은 컴파일 시간에 검사된다.
• PL/SQL은 데이터 유형, 크기 등의 유효성을 검사한다.
• 종속성이 설정되고 데이터 사전에 유지된다.
• 데이터베이스 객체가 변경되더라도 코드가 자동으로 이 변화에 맞도록 진화한다.
• 정적 SQL은 한번 파싱된 후 여러번 실행된다.
• 정적 SQL이 보다 빠르다.

동적 SQL을 대체할 기회를 찾아라

CREATE OR REPLACE FUNCTION get_value_dyn(p_empno IN NUMBER, p_cname IN VARCHAR2)
RETURN VARCHAR2
AS
    l_value VARCHAR2(4000);
BEGIN
    EXECUTE IMMEDIATE
    'SELECT ' || p_cname || ' FROM emp WHERE empno = :x'
    INTO l_value
    USING p_empno;
    RETURN l_value;
END;
/

CREATE OR REPLACE FUNCTION get_value_static(p_empno IN NUMBER, p_cname IN VARCHAR2)
RETURN VARCHAR2
AS
    l_value VARCHAR2(4000);
BEGIN
    SELECT DECODE(UPPER(p_cname),'ENAME',ename
                                ,'EMPNO',empno
                                ,'HIREDATE',TO_CHAR(hiredate,'yyyymmdd') )
      INTO l_value
      FROM emp
     WHERE empno = p_empno;
    RETURN l_value;
END;
/

EXEC runstats_pkg.rs_start;

DECLARE
    l_dummy VARCHAR2(30);
BEGIN
    FOR i IN 1 .. 500
    LOOP
        FOR x IN (SELECT empno FROM emp)
        LOOP
            l_dummy := get_value_dyn(x.empno, 'ENAME');
            l_dummy := get_value_dyn(x.empno, 'EMPNO');
            l_dummy := get_value_dyn(x.empno, 'HIREDATE');
        END LOOP;
    END LOOP;
END;
/

EXEC runstats_pkg.rs_middle;

DECLARE
    l_dummy VARCHAR2(30);
BEGIN
    FOR i IN 1 .. 500
    LOOP
        FOR x IN (SELECT empno FROM emp)
        LOOP
            l_dummy := get_value_static(x.empno, 'ENAME');
            l_dummy := get_value_static(x.empno, 'EMPNO');
            l_dummy := get_value_static(x.empno, 'HIREDATE');
        END LOOP;
    END LOOP;
END;
/

EXEC runstats_pkg.rs_stop(1000);

Run1 ran in 1102 hsecs
Run2 ran in 703 hsecs
run 1 ran in 156.76% of the time

Name                                 Run1      Run2       Diff
STAT...session cursor cache ya     21,003         4    -20,999
STAT...opened cursors cumulati     21,005         5    -21,000
STAT...parse count (total)         21,005         5    -21,000
LATCH.shared pool                  63,565    21,566    -41,999
LATCH.library cache pin           127,062    43,064    -83,998
LATCH.library cache               127,087    43,088    -83,999

Run1 latches total versus runs -- difference and pct
Run1       Run2       Diff        Pct
463,431    253,318    -210,113    182.94%

정적 SQL 요약

• 동적 SQL은 정적 SQL이 더이상 실용적이지 않을때, 즉, 수백줄의 코드 작성이 불가피할때에만 쓸수 있는 카드이다.
• 동적 SQL 작성시엔 DBMS_SQL 패키지를 이용하라
  • DBMS_SQL 은 원시 동적 SQL에 없는 확장성을 높일 수 있는 가능성을 제공한다.
  • DBMS_SQL 은 절차 API 이기 ?문에 한번 파스 여러번 실행을 제공한다.
  • 극히 제한적으로 같은 동적 SQL문을 실행한다는 사실을 알고 있다면 원시 동적 SQL을 사용하는것이 좋다.