복사되었습니다!
OS 환경 : Oracle Linux 9.6 (64bit)
DB 환경 : Oracle AI Database 23.26.2.0.0 ai
방법 : 오라클 26ai select ai 로컬 llm 이용(로컬 gpu 서버) 방법
본문에서는 select ai를 로컬 llm(gpu 서버)를 이용해 실행하는 방법을 설명함
select ai를 사용하기 위해선 먼저 dbms_cloud 패키지가 필요함
해당 패키지 설치는 아래 글을 참고하여 설치하면 됨
참고 : 오라클 23ai 신기능 dbms_cloud 설치(non-adb) ( https://positivemh.tistory.com/1203 )
참고로 로컬 llm(gpu 서버)가 아닌 openai의 api를 사용해서 select ai를 수행하는 방법은 아래 게시글을 참고하면 됨
참고 : 오라클 26ai 신기능 Select AI 사용 테스트(non-adb) ( https://positivemh.tistory.com/1205 )
테스트
LLM용 ACL 생성
*유저이름은 IMSI이고 OPENAI api를 사용함
*pdb에서 sys 유저로 수행
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL>
BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => 'ai.localllm.com',
ace => xs$ace_type(privilege_list => xs$name_list('http'),
principal_name => 'IMSI',
principal_type => xs_acl.ptype_db)
);
END;
/
PL/SQL procedure successfully completed.
|
imsi 유저에 DBMS_CLOUD_AI 권한 부여
|
1
2
3
|
SQL> grant execute on dbms_cloud_ai to imsi;
Grant succeeded.
|
참고로 오라클 text 미설치시 에러남
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
SQL> BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL (
credential_name => 'LOCAL_SLM_CRED',
username => 'IMSI',
password => 'none' -- 실제 키가 없으므로 임의의 값 입력
);
END;
/ 2 3 4 5 6 7 8
BEGIN
*
ERROR at line 1:
ORA-20000: ORA-04063: package body "C##CLOUD$SERVICE.DBMS_CLOUD_INTERNAL" has errors
ORA-06508: PL/SQL: could not find program unit being called: "C##CLOUD$SERVICE.DBMS_CLOUD_INTERNAL"
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 2257
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 11216
ORA-06512: at line 2
|
참고 : 오라클 26ai CDB, PDB에 Oracle Text 컴포넌트 설치 ( https://positivemh.tistory.com/1369 )
LLM용 자격증명 생성(일반 유저에서 수행)
로컬 LLM은 실제 api 키가 없으므로 임의의 값 입력
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL>
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL (
credential_name => 'LOCAL_SLM_CRED',
username => 'IMSI',
password => 'none' -- 실제 키가 없으므로 임의의 값 입력
);
END;
/
PL/SQL procedure successfully completed.
|
imsi 유저에 샘플 테이블 생성
|
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
|
SQL>
drop table products purge;
CREATE TABLE products
(
product_id NUMBER PRIMARY KEY,
product_name VARCHAR2(100) NOT NULL,
category VARCHAR2(50) NOT NULL,
brand VARCHAR2(50) NOT NULL,
price NUMBER(10, 2) NOT NULL,
stock_quantity NUMBER DEFAULT 0 NOT NULL,
rating NUMBER(2, 1),
launch_date DATE,
product_status VARCHAR2(20) DEFAULT 'ACTIVE');
INSERT INTO products
(
product_id,
product_name,
category,
brand,
price,
stock_quantity,
rating,
launch_date,
product_status
)
VALUES
(1, 'Galaxy Book Pro', 'Laptop', 'Samsung', 1890000, 15, 4.7, DATE '2026-01-15', 'ACTIVE'),
(2, 'LG Gram Style', 'Laptop', 'LG', 1750000, 8, 4.5, DATE '2025-11-20', 'ACTIVE'),
(3, 'MacBook Air', 'Laptop', 'Apple', 1590000, 20, 4.9, DATE '2026-03-10', 'ACTIVE'),
(4, 'Galaxy S26', 'Smartphone', 'Samsung', 1350000, 35, 4.8, DATE '2026-02-05', 'ACTIVE'),
(5, 'iPhone 17', 'Smartphone', 'Apple', 1490000, 0, 4.9, DATE '2025-09-20', 'SOLD_OUT'),
(6, 'Pixel 10', 'Smartphone', 'Google', 1190000, 12, 4.4, DATE '2025-10-15', 'ACTIVE'),
(7, 'AirPods Pro', 'Earphones', 'Apple', 349000, 42, 4.8, DATE '2025-08-30', 'ACTIVE'),
(8, 'Galaxy Buds', 'Earphones', 'Samsung', 229000, 28, 4.3, DATE '2025-07-12', 'ACTIVE'),
(9, 'OLED Gaming Monitor', 'Monitor', 'LG', 890000, 5, 4.6, DATE '2025-05-01', 'ACTIVE'),
(10, 'Smart Monitor M8', 'Monitor', 'Samsung', 650000, 0, 4.2, DATE '2024-06-10', 'DISCONTINUED');
COMMIT;
|
데이터 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
SQL>
set lines 200 pages 1000
col product_name for a20
col category for a20
col brand for a10
select * from products;
PRODUCT_ID PRODUCT_NAME CATEGORY BRAND PRICE STOCK_QUANTITY RATING LAUNCH_DA PRODUCT_STATUS
---------- -------------------- -------------------- ---------- ---------- -------------- ---------- --------- --------------------
1 Galaxy Book Pro Laptop Samsung 1890000 15 4.7 15-JAN-26 ACTIVE
2 LG Gram Style Laptop LG 1750000 8 4.5 20-NOV-25 ACTIVE
3 MacBook Air Laptop Apple 1590000 20 4.9 10-MAR-26 ACTIVE
4 Galaxy S26 Smartphone Samsung 1350000 35 4.8 05-FEB-26 ACTIVE
5 iPhone 17 Smartphone Apple 1490000 0 4.9 20-SEP-25 SOLD_OUT
6 Pixel 10 Smartphone Google 1190000 12 4.4 15-OCT-25 ACTIVE
7 AirPods Pro Earphones Apple 349000 42 4.8 30-AUG-25 ACTIVE
8 Galaxy Buds Earphones Samsung 229000 28 4.3 12-JUL-25 ACTIVE
9 OLED Gaming Monitor Monitor LG 890000 5 4.6 01-MAY-25 ACTIVE
10 Smart Monitor M8 Monitor Samsung 650000 0 4.2 10-JUN-24 DISCONTINUED
10 rows selected.
|
Select AI 프로필 생성
*pdb의 imsi 유저에서 생성함, 하나는 openai 호환 모델을 사용했고(TEST_LOCAL_SLM1), 하나는 CLOVA 모델을 사용해서 생성했음(TEST_LOCAL_SLM2)
provider_endpoint는 llm 서버의 endpoint 주소로 설정함
|
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
|
SQL>
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'TEST_LOCAL_SLM1',
attributes => '{"provider": "openai",
"comments": "true",
"model": "gpt-oss-20b",
"provider_endpoint": "https://ai.localllm.com",
"case_sensitive_values" : "false",
"temperature": 0.2,
"max_tokens": 4096,
"conversation": "true",
"credential_name": "LOCAL_SLM_CRED",
"object_list": [{"owner": "IMSI", "name": "PRODUCTS"}]}'
);
END;
/
PL/SQL procedure successfully completed.
SQL>
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'TEST_LOCAL_SLM2',
attributes => '{"provider": "openai",
"comments": "true",
"model": "hcx-dash-002",
"provider_endpoint": "https://ai.localllm.com",
"case_sensitive_values" : "false",
"temperature": 0.2,
"max_tokens": 4096,
"conversation": "true",
"credential_name": "LOCAL_SLM_CRED",
"object_list": [{"owner": "IMSI", "name": "PRODUCTS"}]}'
);
END;
/
PL/SQL procedure successfully completed.
|
참고. profile 삭제
|
1
2
3
|
SQL> exec dbms_cloud_ai.drop_profile(profile_name => 'TEST_LOCAL_SLM1');
PL/SQL procedure successfully completed.
|
Open AI 호환모델을 사용하는 프로필을 설정(imsi 유저로 실행)
|
1
2
3
|
SQL> exec dbms_cloud_ai.set_profile (profile_name => 'TEST_LOCAL_SLM1');
PL/SQL procedure successfully completed.
|
이제 Select AI를 사용할수 있음
가장 높은 가격의 제품을 조회해봄
What is the most expensive product? 라는 질문으로 각 기능을 테스트해봄
Local LLM을 이용해 Select AI runsql 기능 테스트
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL>
set timing on
select ai runsql 'What is the most expensive product?';
RESPONSE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sorry, unfortunately a valid SELECT statement could not be generated for your natural language prompt. Here is some more information to help you further:
I?m not sure which set of products you?re referring to. Could you let me know the context or provide the list of products you?re comparing? That way I can tell you which one is the most expensive.
Elapsed: 00:00:08.40
|
에러가 발생함, 설명을 읽어보면 profile의 테이블 정보가 제대로 전달이 되지 않은것 처럼 보임
CLOVA 모델을 사용하는 프로필을 설정(imsi 유저로 실행)
|
1
2
3
|
SQL> exec dbms_cloud_ai.set_profile (profile_name => 'TEST_LOCAL_SLM2');
PL/SQL procedure successfully completed.
|
동일 질문 재수행
|
1
2
3
4
5
6
7
8
9
|
SQL>
set timing on
select ai runsql 'What is the most expensive product?';
RESPONSE
--------------------------------------------------------------------------------
Sorry, unfortunately a valid SELECT statement could not be generated for your na
Elapsed: 00:00:19.57
|
동일한 에러가 발생함, 이건 설명까지 끊겨 버림
open ai의 api키를 넣은 profile로 select ai 수행시에는 에러가 발생되지 않고 잘됨
|
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
|
SQL>
BEGIN
DBMS_CLOUD_AI.create_profile(
profile_name => 'TEST_OPENAI',
attributes => '{"provider": "openai",
"comments": "true",
"model": "gpt-4o-mini",
"temperature": 0.2,
"max_tokens": 4096,
"conversation": "true",
"credential_name": "OPENAI_CRED",
"object_list": [{"owner": "IMSI", "name": "PRODUCTS"}]}');
END;
/
PL/SQL procedure successfully completed.
SQL> exec dbms_cloud_ai.set_profile (profile_name => 'TEST_OPENAI');
PL/SQL procedure successfully completed.
SQL>
set timing on
select ai runsql 'What is the most expensive product?';
Product Name Price
------------------ ----------
Galaxy Book Pro 1890000
Elapsed: 00:00:01.93
|
에러가 발생하지 않고 잘됨
db 재기동 후 수행해봐도 동일했음
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL> startup force
ORACLE instance started.
Total System Global Area 1574163776 bytes
Fixed Size 5027136 bytes
Variable Size 402653184 bytes
Database Buffers 1040187392 bytes
Redo Buffers 8855552 bytes
Vector Memory Area 117440512 bytes
Database mounted.
Database opened.
|
어노테이션 추가 후 재진행
|
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
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
|
SQL>
ALTER TABLE products ANNOTATIONS
(
ADD "Display Label" 'Product Information',
ADD "Description" 'Stores product details including price, inventory, rating, launch date, and sales status',
ADD "Business Purpose" 'Product search and sales analysis',
ADD "Data Language" 'English'
);
ALTER TABLE products MODIFY product_id ANNOTATIONS
(
ADD "Display Label" 'Product ID',
ADD "Description" 'Unique identifier for each product',
ADD "Primary Key" 'Yes'
);
ALTER TABLE products MODIFY product_name ANNOTATIONS
(
ADD "Display Label" 'Product Name',
ADD "Description" 'Name of the product displayed to customers',
ADD "Example" 'Galaxy Book Pro'
);
ALTER TABLE products MODIFY category ANNOTATIONS
(
ADD "Display Label" 'Product Category',
ADD "Description" 'Category used to classify products by type',
ADD "Example Values" 'Laptop, Smartphone, Earphones, Monitor'
);
ALTER TABLE products MODIFY brand ANNOTATIONS
(
ADD "Display Label" 'Brand',
ADD "Description" 'Brand or manufacturer of the product',
ADD "Example Values" 'Samsung, LG, Apple, Google'
);
ALTER TABLE products MODIFY price ANNOTATIONS
(
ADD "Display Label" 'Product Price',
ADD "Description" 'Selling price of one product',
ADD "Currency" 'KRW',
ADD "Unit" 'Korean Won'
);
ALTER TABLE products MODIFY stock_quantity ANNOTATIONS
(
ADD "Display Label" 'Stock Quantity',
ADD "Description" 'Current quantity of products available in inventory',
ADD "Unit" 'Items',
ADD "Business Rule" 'A value of zero means the product is out of stock'
);
ALTER TABLE products MODIFY rating ANNOTATIONS
(
ADD "Display Label" 'Product Rating',
ADD "Description" 'Average customer rating of the product',
ADD "Minimum Value" '0',
ADD "Maximum Value" '5'
);
ALTER TABLE products MODIFY launch_date ANNOTATIONS
(
ADD "Display Label" 'Launch Date',
ADD "Description" 'Date when the product was officially released',
ADD "Date Format" 'YYYY-MM-DD'
);
ALTER TABLE products MODIFY product_status ANNOTATIONS
(
ADD "Display Label" 'Product Status',
ADD "Description" 'Current sales status of the product',
ADD "Allowed Values" 'ACTIVE, SOLD_OUT, DISCONTINUED',
ADD "ACTIVE" 'The product is currently available for sale',
ADD "SOLD_OUT" 'The product is temporarily unavailable because inventory is zero',
ADD "DISCONTINUED" 'The product is no longer available for sale'
);
|
Open AI 호환모델을 사용하는 프로필을 설정 후 쿼리 재수행(imsi 유저로 실행)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
SQL> exec dbms_cloud_ai.set_profile (profile_name => 'TEST_LOCAL_SLM1');
PL/SQL procedure successfully completed.
SQL>
set timing on
select ai runsql 'What is the most expensive product?';
RESPONSE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sorry, unfortunately a valid SELECT statement could not be generated for your natural language prompt. Here is some more information to help you further:
I?m not sure which set of products you?re referring to. Could you let me know:
1. The list or category of products you have in mind (e.g., a specific store, brand, or product line)?
2. Any additional details that might help narrow it down (price range, type of product, etc.)?
That way I can give you the most accurate answer.
Elapsed: 00:00:07.85
|
여전히 테이블을 못찾는것 같음
질문에 products 테이블도 명시해봄
|
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
|
SQL> select ai runsql 'What is the most expensive product in the products table?';
RESPONSE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sorry, unfortunately a valid SELECT statement could not be generated for your natural language prompt. Here is some more information to help you further:
**SQL to get the most expensive product**
```sql
-- Option 1: Return the single row with the highest price
SELECT *
FROM products
ORDER BY price DESC
LIMIT 1; -- (use TOP 1 for SQL Server, FETCH FIRST 1 ROW ONLY for Oracle)
-- Option 2: Return all products that tie for the highest price
SELECT *
FROM products
WHERE price = (SELECT MAX(price) FROM products);
```
- **`ORDER BY price DESC LIMIT 1`** gives you the first row when sorted by price descending ? the most expensive product.
- **`WHERE price = (SELECT MAX(price) ?)`** returns every product that shares the maximum price, useful if you want to handle ties.
Pick the one that best fits your needs.
Elapsed: 00:00:25.77
|
output이 더 나오긴 하지만 초반에 Sorry~ 메세지가 나오고 마크다운 형식으로 나옴
select ai가 원하는대로 동작하지 않음
OpenAI 모델 사용 테스트2
|
1
2
3
|
SQL> exec dbms_cloud_ai.set_profile (profile_name => 'TEST_OPENAI');
PL/SQL procedure successfully completed.
|
OpenAI LLM을 이용해 Select AI runsql 기능 테스트
|
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
|
SQL>
set timing on
select ai showsql 'Find the most expensive product in each category. Return the category, product name, brand, price, rating, and the price difference compared with the average price of that category.';
RESPONSE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT "P"."CATEGORY" AS "Category",
"P"."PRODUCT_NAME" AS "Product Name",
"P"."BRAND" AS "Brand",
"P"."PRICE" AS "Price",
"P"."RATING" AS "Rating",
("P"."PRICE" - AVG("P2"."PRICE")) AS "Price Difference"
FROM "IMSI"."PRODUCTS" "P"
JOIN "IMSI"."PRODUCTS" "P2" ON "P"."CATEGORY" = "P2"."CATEGORY"
GROUP BY "P"."CATEGORY", "P"."PRODUCT_NAME", "P"."BRAND", "P"."PRICE", "P"."RATING"
HAVING "P"."PRICE" = MAX("P2"."PRICE")
Elapsed: 00:00:03.78
SQL>
set timing on
select ai runsql 'Find the most expensive product in each category. Return the category, product name, brand, price, rating, and the price difference compared with the average price of that category.';
Category Product Name Brand Price Rating Price Difference
-------------------- ------------------------------ ---------- ---------- ---------- ----------------
Laptop Galaxy Book Pro Samsung 1890000 4.7 146666.667
Smartphone iPhone 17 Apple 1490000 4.9 146666.667
Earphones AirPods Pro Apple 349000 4.8 60000
Monitor OLED Gaming Monitor LG 890000 4.6 120000
Elapsed: 00:00:03.47
|
openai 모델 사용시 어려운 질문도 잘 표시됨
참고로 로컬 LLM 모델 profile 설정 후 아래 SQL들을 10번 정도 연속으로 수행시키면 1번 정도는 제대로된 결과값이 나오는걸 확인하였음
|
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
|
SQL>
ALTER SYSTEM FLUSH SHARED_POOL;
exec dbms_cloud_ai.drop_profile(profile_name => 'TEST_LOCAL_SLM1');
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'TEST_LOCAL_SLM1',
attributes => '{"provider": "openai",
"comments": "true",
"model": "gpt-oss-20b",
"provider_endpoint": "https://ai.localllm.com",
"case_sensitive_values" : "false",
"temperature": 0.2,
"max_tokens": 4096,
"conversation": "true",
"credential_name": "LOCAL_SLM_CRED",
"object_list": [{"owner": "IMSI", "name": "PRODUCTS"}]}'
);
END;
/
exec dbms_cloud_ai.set_profile (profile_name => 'TEST_LOCAL_SLM1');
set timing on
select ai runsql 'What is the most expensive product?';
|
결론 :
OpenAI 모델로 select ai 수행시 결과가 잘나오는 반면
로컬 LLM으로 select ai 수행시 결과가 제대로 나오지 않음
테스트를 위해 annotation까지 넣어봤지만 제대로 안된것을 보면 내 로컬 서버의 LLM 설정에 문제가 있을수도 있는듯함
추후 다시 테스트해봐야 할것 같음
참조 :
오라클 26ai 신기능 Select AI 사용 테스트(non-adb) ( https://positivemh.tistory.com/1205 )
https://docs.oracle.com/en/database/oracle/oracle-database/26/refrn/V-MAPPED_SQL.html
'ORACLE > Admin' 카테고리의 다른 글
| 오라클 19c insert 시 undo 발생량 확인 및 감소 테스트 (0) | 2026.07.29 |
|---|---|
| 오라클 26ai autoupgrade시 block checking 이벤트로 인한 hang 현상 (0) | 2026.07.16 |
| 오라클 19c CLOB 컬럼 STORAGE IN ROW 옵션별 저장 위치 확인 테스트 (0) | 2026.07.05 |
| 오라클 11gR2 CLOB 컬럼 STORAGE IN ROW 옵션별 용량 확인 테스트 (0) | 2026.07.04 |
| 오라클 26ai 신기능 Select AI Agent 사용 테스트 (0) | 2026.06.27 |