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')
댓글 0
| 번호 | 제목 | 글쓴이 | 날짜 | 조회 수 |
|---|---|---|---|---|
| 11 | (23c 신기능) GROUP BY 절에 컬럼 별칭이나 포지션 사용 | 명품관 | 2023.04.26 | 965 |
| 10 | (23c 신기능) UPDATE 문장을 위한 Default 값 NULL 설정 가능 | 명품관 | 2023.04.26 | 613 |
| » | (23c 신기능) PL/SQL 에서 SQL로 Transpiler 자동변환(Automatic PL/SQL to SQL Transpiler) | 명품관 | 2023.04.25 | 266 |
| 8 | (23c 신기능) INTERVAL 데이터타입에 집계함수와 분석함수 사용 가능 | 명품관 | 2023.04.20 | 363 |
| 7 | (23c 신기능) 23c에서 개발자를 위해 새로 추가된 DB_DEVELOPER_ROLE role | 명품관 | 2023.04.19 | 259 |
| 6 | (23c 신기능) IF EXISTS와 IF NOT EXISTS 사용 | 명품관 | 2023.04.18 | 1467 |
| 5 | (23c 신기능) Annotation 사용하기(comment와 유사한) | 명품관 | 2023.04.18 | 220 |
| 4 | (23c 신기능) Direct Joins for UPDATE and DELETE Statements(직접 조인을 사용한 UPDATE, DELETE) | 명품관 | 2023.04.12 | 212 |
| 3 | JSON-Relational Duality View 튜토리얼 | 명품관 | 2023.04.10 | 173 |
| 2 | 오라클 프로세스 prefix가 ora_ 에서 db_로 변경 | 명품관 | 2023.04.06 | 206 |
| 1 | Oracle Database 23c Free Install with Oracle Linux 8.2(리눅스 8.2 버전에서 오라클 23c 설치) [1] | 명품관 | 2023.04.05 | 1599 |