메뉴 건너뛰기

Korea Oracle User Group

23c Free Developer Release

PL/SQL 에서 SQL로 Transpiler 자동변환(Automatic PL/SQL to SQL Transpiler)

 

이번 23c 버전은 개발을 위한 편의 및 성능 개선 기능이 주를 이루는 듯한 인상을 줍니다.

 

이번에 확인하려는 기능은 Transpiler에 의해 PL/SQL function이 

자동으로 SQL 표현식으로 변화되는 것을 확인할려고 합니다.

이는 성능상으로 개선 효과가 있을 수 있습니다.

하지만 적용되는 부분이 극히 제한적일 듯 합니다.

사용 환경에 따라서 다르겠지만 제한적일 듯해서 거의 맛보기가 아닐까 생각이 듭니다.

 

SQL에서 PL/SQL 엔진으로 컨텍스트 스위치가 발생하는 전환이 없어지는 효과가 있기 때문에

성능적인 이득을 볼 수 있습니다. 하지만 위에서 이야기한대로 제한적이라서 활용도가 클지는 의문입니다.

그러나 이런 부분의 개선이 이어진다면 버전이 올라가면서 유의미한 내용이 될 수도 있다는 생각이 듭니다.

 

이 기능의 변환작업이 유저에게 인지되지 않은 상태에서 이뤄집니다.

 

즉, 유저는 where 절에 기존에 사용하던 방식으로 function을 사용했으나

Transpiler가 자동으로 변환해서 쿼리를 수행해 주는 것입니다.

 

이 기능은 세션, 시스템 레벨에서 활성화 시킬 수 있습니다.

 

단, 오라클이 새로운 기능을 제공할 때 특히 이런 기능의 경우

경험상 버그 발생 확률이 있으며 이런 부분에 유의를 해서 접근을 해야할 필요는 있을 거 같습니다.

 

아래 기준으로 Transpiler 자동변환이 가능한 상황과 아닌 경우가 있다고 합니다. 

 

가능한 상황

  • Basic SQL scalar types: CHARACTER, DATE-TIME, and NUMBER
  • String types (CHAR, VARCHAR, VARCHAR2, NCHAR, etc.)
  • Numeric types (NUMBER, BINARY DOUBLE, etc.)
  • Date types (DATE, TIME, INTERVAL, and TIMESTAMP)
  • Local variables (with optional initialization at declaration) and constants
  • Parameters with optional (simple) default values
  • Variable assignment statements
  • Expressions which can be translated into equivalent SQL expressions
  • IF-THEN-ELSE statements
  • RETURN statements
  • Expressions and local variables of BOOLEAN type

불가능한 상황

  • Embedded SQL statements. A transpiled function cannot contain a cursor declaration, explicit cursors, ref cursors, or an execute-immediate statement
  • Package variables, both public and private.
  • PL/SQL Specific Scalar Types: PLS INTEGER
  • PL/SQL Aggregate Types: Records, Collections, and Tables
  • Oracle Objects (ADT/UDT), XML, and JSON
  • Deprecated Datatypes: LONG
  • The %TYPE and %ROWTYPE attributes
  • Package state (both constants and variables)
  • Locally defined PL/SQL types
  • Locally defined (nested) functions
  • Calls to other PL/SQL functions (both schema-level and package level). This also precludes support for recursive function calls
  • Control-flow statements like LOOP, GOTO, and RAISE
  • Nested DECLARE-BEGIN-EXCEPTION blocks
  • CASE control-flow statements (note that this is different from the SQL CASE expressions which are supported by both SQL and PL/SQL)
  • Transaction processing like COMMIT, ROLLBACK, LOCK-TABLE, PRAGMA AUTONOMOUS TRANSACTION, SELECT-FOR-UPDATE, and others

 

아래에서 이 기능을 테스트해 보도록 하겠다.

 

먼저 사용할 function 을 생성해 보도록 하겠습니다.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SQL> set linesize 200 timing on        
SQL> drop function if exists func_phonenum;
 
함수가 삭제되었습니다.
 
경   과: 00:00:00.03
SQL> create function func_phonenum(ph_num varchar2)
  2  return varchar2
  3  is
  4  begin
  5      return substr(ph_num,1,3)||substr(ph_num,5,3)||substr(ph_num,9,4);
  6  end;
  7  /
 
함수가 생성되었습니다.
 
경   과: 00:00:00.02

 

위 function을 조건절에 사용하여 데이터를 조회해 봅니다.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SQL> col phone_number for a15
SQL> col job_id for a8
SQL> select e.employee_id
  2        ,e.phone_number
  3        ,e.job_id
  4        ,e.salary
  5        ,e.commission_pct
  6  from employees e
  7  where func_phonenum(e.phone_number) = '5151237777';
 
EMPLOYEE_ID PHONE_NUMBER    JOB_ID       SALARY COMMISSION_PCT
----------- --------------- -------- ---------- --------------
        203 515.123.7777    HR_REP         6500
 
경   과: 00:00:00.00

 

이제 위의 문장에 대해 Transpiler 기능이 꺼졌을 때 실행계획이 어떤지 확인해 보겠습니다.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
SQL> alter session set sql_transpiler='OFF';
 
세션이 변경되었습니다.
 
경   과: 00:00:00.00
SQL> explain plan for
  2  select e.employee_id
  3        ,e.phone_number
  4        ,e.job_id
  5        ,e.salary
  6        ,e.commission_pct
  7  from employees e
  8  where func_phonenum(e.phone_number) = '5151237777';
 
해석되었습니다.
 
경   과: 00:00:00.01
SQL> select * from table(dbms_xplan.display());
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1445457117
 
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |    34 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |     1 |    34 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
 
   1 - filter("FUNC_PHONENUM"("E"."PHONE_NUMBER")='5151237777')
 
13 행이 선택되었습니다.
 
경   과: 00:00:00.04

 

실행계획을 보면 filter 부분에 filter("FUNC_PHONENUM"("E"."PHONE_NUMBER")='5151237777') 이렇게 표시가 됩니다.

이 부분을 기억해 두시기 바랍니다.

 

이제 Transpilfer 기능을 켜고 다시 실행계획을 확인해 보도록 하겠습니다.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
SQL> alter session set sql_transpiler='ON';
 
세션이 변경되었습니다.
 
경   과: 00:00:00.00
SQL> explain plan for
  2  select e.employee_id
  3        ,e.phone_number
  4        ,e.job_id
  5        ,e.salary
  6        ,e.commission_pct
  7  from employees e
  8  where func_phonenum(e.phone_number) = '5151237777';
 
해석되었습니다.
 
경   과: 00:00:00.02
SQL> select * from table(dbms_xplan.display());
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1445457117
 
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |    34 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |     1 |    34 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
 
   1 - filter(SUBSTR("E"."PHONE_NUMBER",1,3)||SUBSTR("E"."PHONE_NUMBER",
              5,3)||SUBSTR("E"."PHONE_NUMBER",9,4)='5151237777')
 
14 행이 선택되었습니다.
 
경   과: 00:00:00.06

 

이번에는 filter 부분이 아래와 같이 기능을 OFF 했을 때랑은 다른 걸 확인할 수 있습니다.

filter(SUBSTR("E"."PHONE_NUMBER",1,3)||SUBSTR("E"."PHONE_NUMBER",

              5,3)||SUBSTR("E"."PHONE_NUMBER",9,4)='5151237777')

위로