내맘대로긍정이 알려주는
Oracle 23ai 신기능
무료 세미나 발표자료
다운로드
trending_flat
OS 환경 : Oracle Linux 7.6 (64bit)
DB 환경 : Oracle Database 19.21.0.0
방법 : 오라클 19c 스탠다드 에디션, 엔터프라이즈 에디션 기능 차이
오라클은 라이센스에 따라 크게 스탠다드 에디션과 엔터프라이즈 에디션 2개의 에디션을 구분해 사용할 수 있음
스탠다드 에디션은 규모가 작은 시스템에 사용하고 엔터프라이즈 에디션은 대규모 시스템에 주로 사용함
엔터프라이즈 에디션에서 강력한 기능들을 사용할수 있어 대부분 엔터프라이즈 에디션을 사용함
대표적인 기능으로 압축 기능, 병렬 기능, 튜닝팩 지원(라이센스필요), RAT 기능 등이 존재함
이 옵션들에 대해 스탠다드 에디션, 엔터프라이즈 에디션에서 각각 어떻게 동작하는지 확인해봄
1. 압축 기능 테스트
2. 병렬 기능 테스트
3. 튜닝팩 지원 테스트
4. RAT 기능 테스트
5. 기타 기능 확인
1. 압축 기능 테스트
샘플 테이블 생성(스탠다드 에디션)
|
1
2
3
4
5
6
|
SQL> create table test1
row store compress basic
as select * from dba_objects;
ERROR at line 3:
ORA-00439: feature not enabled: Basic Compression
|
에러 발생함
샘플 테이블 생성(엔터프라이즈 에디션)
|
1
2
3
4
5
|
SQL> create table test1
row store compress basic
as select * from dba_objects;
Table created.
|
정상 생성됨
2. 병렬 기능 테스트
샘플 테이블 생성
|
1
2
3
|
SQL> create table test2 as select * from dba_objects;
Table created.
|
병렬 쿼리 실행(스탠다드 에디션)
|
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
|
SQL>
set autotrace on lines 200 pages 1000
col object_name for a40
select * from (
select /*+ parallel(test2) */ object_name, count(*) from test2
group by object_name)
where rownum<= 10;
OBJECT_NAME COUNT(*)
---------------------------------------- ----------
ABSPATH 2
ACCESS$ 1
ACCHK_EVENTS 2
ACCHK_EVENTS_FK 1
ACCHK_EVENTS_TIMESTAMP 1
ACCHK_SESSION 2
ACCHK_SESSION_TIMESTAMP 1
ACCHK_STATISTICS 2
ACCHK_STATISTICS_FK 1
ACCHK_STATISTICS_TIMESTAMP 1
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2043799200
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 790 | 110 (2)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 24323 | 1876K| 110 (2)| 00:00:01 |
|* 3 | SORT GROUP BY STOPKEY| | 24323 | 1567K| 110 (2)| 00:00:01 |
| 4 | TABLE ACCESS FULL | TEST2 | 24323 | 1567K| 108 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=10)
3 - filter(ROWNUM<=10)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
4 - SEL$2 / TEST2@SEL$2
U - parallel(test2)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Statistics
----------------------------------------------------------
14 recursive calls
0 db block gets
541 consistent gets
392 physical reads
0 redo size
913 bytes sent via SQL*Net to client
480 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed
|
병렬기능 미작동함
병렬 쿼리 실행(엔터프라이즈 에디션)
|
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
|
SQL>
set autotrace on lines 200 pages 1000
col object_name for a40
select * from (
select /*+ parallel(test) */ object_name, count(*) from test
group by object_name)
where rownum<= 10;
OBJECT_NAME COUNT(*)
---------------------------------------- ----------
ADMINAUTH$ 1
ADO_IMSEGSTAT$ 1
ADO_IMSEGTASKDETAILS$ 1
ADR_INCIDENT_FILE_T 1
ADR_INCIDENT_INFO_T 1
ADR_INCIDENT_T 3
ADR_LOG_MSG_SUPPL_ATTRS_T 2
AGGXQIMP 1
ALERT_QT 1
ALERT_QUE 1
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1822973627
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 790 | 32 (4)| 00:00:01 | | | |
|* 1 | COUNT STOPKEY | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | 22174 | 1710K| 32 (4)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
|* 4 | COUNT STOPKEY | | | | | | Q1,01 | PCWC | |
| 5 | VIEW | | 22174 | 1710K| 32 (4)| 00:00:01 | Q1,01 | PCWP | |
|* 6 | SORT GROUP BY STOPKEY | | 22174 | 1429K| 32 (4)| 00:00:01 | Q1,01 | PCWP | |
| 7 | PX RECEIVE | | 22174 | 1429K| 32 (4)| 00:00:01 | Q1,01 | PCWP | |
| 8 | PX SEND HASH | :TQ10000 | 22174 | 1429K| 32 (4)| 00:00:01 | Q1,00 | P->P | HASH |
| 9 | HASH GROUP BY | | 22174 | 1429K| 32 (4)| 00:00:01 | Q1,00 | PCWP | |
| 10 | PX BLOCK ITERATOR | | 22174 | 1429K| 31 (0)| 00:00:01 | Q1,00 | PCWC | |
| 11 | TABLE ACCESS FULL| TEST2 | 22174 | 1429K| 31 (0)| 00:00:01 | Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=10)
4 - filter(ROWNUM<=10)
6 - filter(ROWNUM<=10)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- automatic DOP: Computed Degree of Parallelism is 4 because of degree limit
Statistics
----------------------------------------------------------
38 recursive calls
0 db block gets
572 consistent gets
593 physical reads
0 redo size
697 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
10 rows processed
|
병렬기능 작동함
3. 튜닝팩 지원 테스트
ash 뷰 count 조회(스탠다드 에디션)
|
1
2
3
4
5
|
SQL> select count(*) from v$active_session_history;
COUNT(*)
----------
0
|
0건임(데이터가 쌓이지 않음)
ash 뷰 count 조회(엔터프라이즈 에디션)
|
1
2
3
4
5
|
SQL> select count(*) from v$active_session_history;
COUNT(*)
----------
24823
|
24823건임(데이터가 쌓이고 있음)
awr 관련 뷰 count 조회(스탠다드 에디션)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
SQL>
set serveroutput on
DECLARE
v_count NUMBER;
v_exec_str VARCHAR2(1000);
BEGIN
FOR r IN (SELECT view_name FROM dba_views WHERE view_name LIKE 'DBA_HIST_SQL%' order by 1) LOOP
v_exec_str := 'SELECT COUNT(*) FROM ' || r.view_name;
EXECUTE IMMEDIATE v_exec_str INTO v_count;
DBMS_OUTPUT.PUT_LINE(r.view_name || ' : ' || v_count);
END LOOP;
END;
/
DBA_HIST_SQLBIND : 0
DBA_HIST_SQLCOMMAND_NAME : 0
DBA_HIST_SQLSTAT : 0
DBA_HIST_SQLTEXT : 0
DBA_HIST_SQL_BIND_METADATA : 0
DBA_HIST_SQL_PLAN : 0
DBA_HIST_SQL_SUMMARY : 0
DBA_HIST_SQL_WORKAREA_HSTGRM : 0
|
모두 0건임(데이터가 쌓이지 않음)
awr 관련 뷰 count 조회(엔터프라이즈 에디션)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
SQL>
set serveroutput on
DECLARE
v_count NUMBER;
v_exec_str VARCHAR2(1000);
BEGIN
FOR r IN (SELECT view_name FROM dba_views WHERE view_name LIKE 'DBA_HIST_SQL%' order by 1) LOOP
v_exec_str := 'SELECT COUNT(*) FROM ' || r.view_name;
EXECUTE IMMEDIATE v_exec_str INTO v_count;
DBMS_OUTPUT.PUT_LINE(r.view_name || ' : ' || v_count);
END LOOP;
END;
/
DBA_HIST_SQLBIND : 39698
DBA_HIST_SQLCOMMAND_NAME : 194
DBA_HIST_SQLSTAT : 9962
DBA_HIST_SQLTEXT : 884
DBA_HIST_SQL_BIND_METADATA : 3762
DBA_HIST_SQL_PLAN : 22564
DBA_HIST_SQL_SUMMARY : 139
DBA_HIST_SQL_WORKAREA_HSTGRM : 1393
|
모두 데이터가 들어있음(데이터가 쌓이고 있음)
4. RAT 기능 테스트
RAT 명령어 중 하나인 DBMS_WORKLOAD_REPLAY.ADD_CAPTURE 실행(스탠다드 에디션)
|
1
2
3
4
5
6
7
8
|
SQL> select dbms_workload_replay.add_capture ('TEST') from dual;
*
ERROR at line 1:
ORA-00438: Real Application Testing Option not installed
ORA-06512: at "SYS.PRVT_SMGUTIL", line 84
ORA-06512: at "SYS.DBMS_WRR_PROTECTED", line 53
ORA-06512: at "SYS.DBMS_WORKLOAD_REPLAY", line 578
ORA-06512: at line 1
|
ORA-00438: Real Application Testing Option not installed가 발생함
스탠다드 에디션에는 RAT 기능이 설치되어 있지 않기때문에 이 에러가 발생함
RAT 명령어 중 하나인 DBMS_WORKLOAD_REPLAY.ADD_CAPTURE 실행(엔터프라이즈 에디션)
|
1
2
3
4
5
6
7
|
SQL> select dbms_workload_replay.add_capture ('TEST') from dual;
*
ERROR at line 1:
ORA-20223: Error: Invalid Input. No active schedule has been defined
ORA-06512: at "SYS.DBMS_WORKLOAD_REPLAY_I", line 5485
ORA-06512: at "SYS.DBMS_WORKLOAD_REPLAY", line 579
ORA-06512: at line 1
|
ORA-20223: Error: Invalid Input. No active schedule has been defined가 발생함
ADD_CAPTURE 명령 수행 전 다른 절차를 진행하지 않아 발생하는 에러임
5. 기타 기능 확인
v$option 확인(스탠다드 에디션)
|
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
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
|
SQL>
set lines 200 pages 1000
col parameter for a50
col value for a10
select parameter, value from v$option order by 1;
PARAMETER VALUE
-------------------------------------------------- ----------
ASM Proxy Instance FALSE
Active Data Guard FALSE
Adaptive Execution Plans FALSE
Advanced Analytics FALSE
Advanced Compression FALSE
Advanced Index Compression FALSE
Advanced replication FALSE
Application Role FALSE
Automatic Data Optimization FALSE
Automatic Storage Management FALSE
Backup Encryption FALSE
Basic Compression FALSE
Bit-mapped indexes FALSE
Block Change Tracking FALSE
Block Media Recovery FALSE
Cache Fusion Lock Accelerator FALSE
Centrally Managed User FALSE
Change Data Capture FALSE
Coalesce Index TRUE
Connection multiplexing TRUE
Connection pooling TRUE
Cross Transportable Backups FALSE
DICOM TRUE
Data Mining FALSE
Data Redaction FALSE
Database queuing TRUE
Database resource manager FALSE
Deferred Segment Creation FALSE
Duplexed backups FALSE
Enterprise User Security FALSE
Exadata Discovery FALSE
Export transportable tablespaces FALSE
Fast-Start Fault Recovery FALSE
File Mapping FALSE
Fine-grained Auditing FALSE
Fine-grained access control FALSE
Flashback Data Archive TRUE
Flashback Database FALSE
Flashback Table FALSE
Global Data Services FALSE
Heat Map FALSE
I/O Server FALSE
In-Memory Aggregation FALSE
In-Memory Column Store FALSE
Incremental backup and recovery TRUE
Instead-of triggers TRUE
Java TRUE
Join index FALSE
Managed Standby FALSE
Management Database FALSE
Materialized view rewrite FALSE
OLAP FALSE
OLAP Window Functions TRUE
Objects TRUE
Online Index Build FALSE
Online Redefinition FALSE
Oracle Data Guard FALSE
Oracle Database Vault FALSE
Oracle Label Security FALSE
Parallel backup and recovery FALSE
Parallel execution FALSE
Parallel load TRUE
Partitioning FALSE
Plan Stability TRUE
Point-in-time tablespace recovery FALSE
Privilege Analysis FALSE
Proxy authentication/authorization TRUE
Real Application Clusters FALSE
Real Application Security FALSE
Real Application Testing FALSE
Result Cache FALSE
SQL Plan Management FALSE
Sample Scan TRUE
SecureFiles Encryption FALSE
Server Flash Cache FALSE
Snapshot time recovery FALSE
Spatial FALSE
Streams Capture FALSE
Table Clustering FALSE
Transparent Application Failover TRUE
Transparent Data Encryption FALSE
Transparent Sensitive Data Protection FALSE
Trial Recovery FALSE
Unified Auditing FALSE
Unused Block Compression FALSE
XStream TRUE
Zone Maps FALSE
87 rows selected.
|
FALSE가 대부분임(기능들을 대부분 사용할 수 없는 상태)
v$option 확인(엔터프라이즈 에디션)
|
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
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
|
SQL>
set lines 200 pages 1000
col parameter for a50
col value for a10
select parameter, value from v$option order by 1;
PARAMETER VALUE
-------------------------------------------------- ----------
ASM Proxy Instance FALSE
Active Data Guard TRUE
Adaptive Execution Plans TRUE
Advanced Analytics TRUE
Advanced Compression TRUE
Advanced Index Compression TRUE
Advanced replication TRUE
Application Role TRUE
Automatic Data Optimization TRUE
Automatic Storage Management FALSE
Backup Encryption TRUE
Basic Compression TRUE
Bit-mapped indexes TRUE
Block Change Tracking TRUE
Block Media Recovery TRUE
Cache Fusion Lock Accelerator TRUE
Centrally Managed User TRUE
Change Data Capture TRUE
Coalesce Index TRUE
Connection multiplexing TRUE
Connection pooling TRUE
Cross Transportable Backups TRUE
DICOM TRUE
Data Mining TRUE
Data Redaction TRUE
Database queuing TRUE
Database resource manager TRUE
Deferred Segment Creation TRUE
Duplexed backups TRUE
Enterprise User Security TRUE
Exadata Discovery TRUE
Export transportable tablespaces TRUE
Fast-Start Fault Recovery TRUE
File Mapping TRUE
Fine-grained Auditing TRUE
Fine-grained access control TRUE
Flashback Data Archive TRUE
Flashback Database TRUE
Flashback Table TRUE
Global Data Services TRUE
Heat Map TRUE
I/O Server FALSE
In-Memory Aggregation TRUE
In-Memory Column Store TRUE
Incremental backup and recovery TRUE
Instead-of triggers TRUE
Java TRUE
Join index TRUE
Managed Standby TRUE
Management Database FALSE
Materialized view rewrite TRUE
OLAP TRUE
OLAP Window Functions TRUE
Objects TRUE
Online Index Build TRUE
Online Redefinition TRUE
Oracle Data Guard TRUE
Oracle Database Vault FALSE
Oracle Label Security FALSE
Parallel backup and recovery TRUE
Parallel execution TRUE
Parallel load TRUE
Partitioning TRUE
Plan Stability TRUE
Point-in-time tablespace recovery TRUE
Privilege Analysis TRUE
Proxy authentication/authorization TRUE
Real Application Clusters FALSE
Real Application Security TRUE
Real Application Testing TRUE
Result Cache TRUE
SQL Plan Management TRUE
Sample Scan TRUE
SecureFiles Encryption TRUE
Server Flash Cache TRUE
Snapshot time recovery TRUE
Spatial TRUE
Streams Capture TRUE
Table Clustering TRUE
Transparent Application Failover TRUE
Transparent Data Encryption TRUE
Transparent Sensitive Data Protection TRUE
Trial Recovery TRUE
Unified Auditing FALSE
Unused Block Compression TRUE
XStream TRUE
Zone Maps TRUE
87 rows selected.
|
TRUE가 대부분임(기능들을 대부분 사용할 수 있는 상태)
결론 :
테스트 결과 스탠다드 에디션에서는 특정 기능(압축 기능, 병렬 기능, 튜닝팩 기능, RAT 기능 등)이 동작하지 않거나, 에러가 발생하면서 수행되지 않거나
튜닝팩 관련 뷰 같은 경우에는 데이터 자체가 쌓이지 않음
DB를 제대로 관리하려면 엔터프라이즈 에디션을 사용하는게 좋아보임
참고로 19c에서는 RAC는 엔터프라이즈 에디션에서만 설치가 가능함
참조 :
2089230.1
https://docs.oracle.com/en/database/oracle/oracle-database/19/upgrd/migrating-from-standard-to-enterprise--database.html#GUID-294262A1-9E53-4C76-8FFA-086BD6CC4EE1
https://docs.oracle.com/en/database/oracle/oracle-database/19/upgrd/migrating-from-enterprise-to-standard-database.html#GUID-FD2DB65B-0E54-4ECE-8DF3-8E8F12FBD005
https://docs.oracle.com/en/database/oracle/oracle-database/19/dblic/Licensing-Information.html#GUID-0F9EB85D-4610-4EDF-89C2-4916A0E7AC87
https://positivemh.tistory.com/440
https://positivemh.tistory.com/808
https://positivemh.tistory.com/810
https://positivemh.tistory.com/1017
'ORACLE > Admin' 카테고리의 다른 글
| 오라클 클라우드(OCI) 무료 계정 생성 가이드 (0) | 2024.04.06 |
|---|---|
| 오라클 19c DML 시 REDO 발생량 확인(nologging, append, parallel 등) (4) | 2024.04.01 |
| 오라클 19c PL/SQL 의 Xplan 실행계획 확인 (0) | 2024.03.25 |
| 오라클 19c DBMS_APPLICATION_INFO 클라이언트 정보 설정 (0) | 2024.03.25 |
| 오라클 19c 리스너 ADMIN_RESTRICTIONS_LISTENER 파라미터 설정 (0) | 2024.03.22 |