오라클 23ai 신기능 SQL Transpiler

2024. 6. 29. 16:11 내맘대로긍정

프린트 하기

OS 환경 : Oracle Linux 8.4 (64bit)

DB 환경 : Oracle Database 23.4.0.24.05 ai Free

방법 : 오라클 23ai 신기능 SQL Transpiler(Automatic PL/SQL conversion into SQL)

오라클 23ai 에 도입된 SQL Transpiler (SQL 트랜스파일러 또는 변환기)는 SQL 내의 PL/SQL 함수를 SQL 로 자동 변환해주는 기능임
일반적으로 SQL에서 PL/SQL 을 호출하게 되면 Context Switching 이 발생하게 되어 성능상 좋지 않음
이 기능을 사용하면 Context Switching 이 발생하지 않아 성능이 향상됨
하지만 모든 PL/SQL 구문을 지원하는건 아니고, 추후 릴리즈에서 지원되는 구문이 더 늘어날 예정이라고함(지원되지 않는 구문은 기존 방식대로 수행됨)
그리고 기존에 함수가 포함된 쿼리를 수행한 뒤 xplan 을 확인하면 함수가 어떤 역할을 하는지는 xplan 결과에서 알수 없는데 이 기능을 사용하면 Predicate Information 부분에서 함수가 sql 로 변환되어서 보임
이 기능을 사용하기 위해선 sql_transpiler 파라미터를 on 으로 변경해주어야함

23ai 버전의 SQL Transpiler 지원되는 구문
- 기본 SQL 스칼라 타입: CHARACTER, DATETIME, NUMBER
- 문자열 타입: CHAR, VARCHAR, VARCHAR2, NCHAR 등
- 숫자 타입: NUMBER, BINARY DOUBLE 등
- 날짜 타입: DATE, INTERVAL, TIMESTAMP
- 지역 변수 (선언 시 초기화 선택 가능) 및 상수
- 매개 변수 (단순 기본값 선택 가능)
- 변수 할당문
- SQL 표현식으로 변환 가능한 표현식
- RETURN 문
- BOOLEAN 타입의 표현식 및 지역 변수

23ai 버전의 SQL Transpiler 지원되지 않는 구문
- 내장 SQL 문: 커서 선언, 명시적 커서, 참조 커서, execute-immediate 문 등
- PL/SQL 패키지 내부에 정의된 함수
- 패키지 변수 (공개 및 비공개)
- PL/SQL 고유 스칼라 타입: PLS_INTEGER
- PL/SQL 집합 타입: 레코드, 컬렉션, 테이블
- Oracle 객체 (ADT/UDT), XML, JSON
- 사용되지 않는 데이터 타입: LONG
- %TYPE 및 %ROWTYPE 속성
- 패키지 상태 (상수 및 변수)
- 지역적으로 정의된 PL/SQL 타입
- 지역적으로 정의된 (중첩된) 함수
- 다른 PL/SQL 함수 호출 (재귀 함수 호출 포함)
- 제어 흐름 문: LOOP, GOTO, RAISE
- 중첩된 DECLARE-BEGIN-EXCEPTION 블록
- CASE 제어 흐름 문 (SQL CASE 표현식은 지원)
- 트랜잭션 처리: COMMIT, ROLLBACK, LOCK-TABLE, PRAGMA AUTONOMOUS TRANSACTION, SELECT-FOR-UPDATE 등

테스트
입력값을 제곱해 출력하는 함수 생성

1
2
3
4
5
6
7
8

SQL>
create or replace function square_number(p_num number) return number is
begin
  return p_num * p_num;
end;
/
 
Function created.

sal의 제곱값이 3000000이 넘는 row를 찾는 샘플 쿼리 수행

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

SQL>
select /*+ gather_plan_statistics */ 
empno, ename, sal,square_number(sal)
from emp 
where square_number(sal) > 3000000;
 
     EMPNO ENAME             SAL SQUARE_NUMBER(SAL)
---------- ---------- ---------- ------------------
      7839 KING             5000           25000000
      7698 BLAKE            2850            8122500
      7782 CLARK            2450            6002500
      7566 JONES            2975            8850625
      7788 SCOTT            3000            9000000
      7902 FORD             3000            9000000
 
6 rows selected.

xplan 확인

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
43
44
45

SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST');
 
Plan hash value: 3956160932
 
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |       |     3 (100)|          |      6 |00:00:00.01 |       7 |
|*  1 |  TABLE ACCESS FULL| EMP  |      1 |      1 |    14 |     3   (0)| 00:00:01 |      6 |00:00:00.01 |       7 |
--------------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1 / "EMP"@"SEL$1"
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('23.1.0')
      DB_VERSION('23.1.0')
      OPT_PARAM('optimizer_dynamic_sampling' 0)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "EMP"@"SEL$1")
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("SQUARE_NUMBER"("SAL")>3000000)
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10], "SAL"[NUMBER,22]
 
Query Block Registry:
---------------------
 
  SEL$1 (PARSER) [FINAL]

Predicate Information 에 함수가 사용된것으로 표시되지만 함수의 내용은 볼수 없는 상태

sql_transpiler 파라미터 확인

1
2
3
4
5

SQL> show parameter sql_transpiler
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sql_transpiler                       string      OFF

off 상태임

sql_transpiler 파라미터 on 으로 변경

1
2
3

SQL> alter session set sql_transpiler = on;
 
Session altered.

샘플 쿼리 재수행

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

SQL>
select /*+ gather_plan_statistics */ 
empno, ename, sal,square_number(sal)
from emp 
where square_number(sal) > 3000000;
 
     EMPNO ENAME             SAL SQUARE_NUMBER(SAL)
---------- ---------- ---------- ------------------
      7839 KING             5000           25000000
      7698 BLAKE            2850            8122500
      7782 CLARK            2450            6002500
      7566 JONES            2975            8850625
      7788 SCOTT            3000            9000000
      7902 FORD             3000            9000000
 
6 rows selected.

xplan 확인

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
43
44
45

SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST');
 
Plan hash value: 3956160932
 
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |       |     3 (100)|          |      6 |00:00:00.01 |       7 |
|*  1 |  TABLE ACCESS FULL| EMP  |      1 |      9 |   126 |     3   (0)| 00:00:01 |      6 |00:00:00.01 |       7 |
--------------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1 / "EMP"@"SEL$1"
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('23.1.0')
      DB_VERSION('23.1.0')
      OPT_PARAM('optimizer_dynamic_sampling' 0)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "EMP"@"SEL$1")
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("SAL"*"SAL">3000000)
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10], "SAL"[NUMBER,22]
 
Query Block Registry:
---------------------
 
  SEL$1 (PARSER) [FINAL]

Predicate Information에 함수 SQL로 변환되어서 표시됨

결론 :
23ai 버전의 SQL Transpiler 사용시 Context Switching 이 발생하지 않아 효율적임
xplan의 Predicate Information 에서 함수가 SQL로 변환되어서 보이기 떄문에 성능 분석에 도움을 줌
현재 PL/SQL 구문이 지원되는것은 아니기 때문에 일부 경우에만 사용 가능함

참조 :

https://apex.oracle.com/pls/apex/features/r/dbfeatures/features?feature_id=1706
https://docs.oracle.com/en/database/oracle/oracle-database/23/tgsql/introduction-to-sql-tuning.html#GUID-C25CC846-7515-4527-8345-DAE2896EDAC8
https://blogs.oracle.com/coretec/post/sql-transpiler-in-23c
https://tuna.tistory.com/90

저작자표시

'ORACLE > Admin' 카테고리의 다른 글

오라클 23ai 신기능 Direct Load(Direct Path Insert) 에 대한 제약 해제 (0)	2024.07.02
오라클 23ai 신기능 xplan 의 Query Block Registry 개선 (0)	2024.07.01
오라클 23ai 신기능 세미나 발표자료(202406) (0)	2024.06.29
오라클 23ai 신기능 sqlplus 추가 명령 (0)	2024.06.26
오라클 23ai 신기능 테이블 엑세스 기록 확인 뷰(dba_table_access_stats) (0)	2024.06.22

내맘대로긍정

박영민 ympark.pro@gmail.com

Category

Menu

Comment

Link