Parallel Read가 가능한 Enterprise Edition의 SQL Plan 확장성과 기능으로 인하여 Standard Edition과의 쿼리 수행 성능 차이가 있습니다.
대표적인 것이 자동적으로 수행되는 Parallel read (파라미터: parallel_execution_enabled) 가 Enterprise Edition 에서는 동작하지만 Standard Edition 에서는 안됩니다.
이에 따라 테이블의 사이즈가 클수록 동일 서버에서 Edition 별 쿼리 조회 성능이 달라집니다.
몇건 안되는 테이블이라면 수행속도는 비슷하지만 테라급 대용량 데이터 DB 에서는 성능 차이가 커집니다.
.
Sql hint parallel옵션 or table에 degree value 값을 을 안주더라도 테이블에서 읽을 때 내부적으로 옵티마이저가 판단해서 동작합니다.
테이블의 사이즈가 클수록 차이가 납니다. 되도록 저는 조회 건수가 많고 사이즈가 큰 테라급 DB는 Enterprise Edition 버전을 써야 함을 Guide 드립니다.
<Enterprise Edition 과 Standard Edition의 성능 테스트 결과>
1.테스트 환경 구성
EXEC DBMS_STATS.SET_SYSTEM_STATS('MBRC',16)
EXEC DBMS_STATS.SET_SYSTEM_STATS('CPUSPEED',2664)
EXEC DBMS_STATS.SET_SYSTEM_STATS('SREADTIM',8)
EXEC DBMS_STATS.SET_SYSTEM_STATS('MREADTIM',10)
EXEC DBMS_STATS.SET_SYSTEM_STATS('MAXTHR',19181568)
CREATE TABLE T1 NOLOGGING AS
SELECT
MOD(ROWNUM, 100000) ID,
LPAD('A',100,'A') A
FROM
DUAL
CONNECT BY
LEVEL <= 1000000;
CREATE INDEX IND_T1_ID ON T1(ID) NOLOGGING;
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',ESTIMATE_PERCENT=>NULL,METHOD_OPT=>'FOR ALL COLUMNS SIZE 1')
SELECT
SEGMENT_NAME SEGMENT,
COUNT(*) EXTENTS,
BYTES/1024 EXT_SIZE_KB,
(COUNT(*) * BYTES)/1048576 TOTAL_MB
FROM
DBA_EXTENTS
WHERE
OWNER=USER
AND SEGMENT_NAME IN ('IND_T1_ID','T1')
GROUP BY
SEGMENT_NAME,
BYTES
ORDER BY
SEGMENT_NAME,
BYTES;
SEGMENT EXTENTS EXT_SIZE_KB TOTAL_MB
------------ ---------- ----------- ----------
IND_T1_ID 16 64 1
IND_T1_ID 17 1024 17
T1 16 64 1
T1 63 1024 63
T1 8 8192 64
· 2. Trace 설정
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH BUFFER_CACHE;
SET ARRAYSIZE 1000
SET AUTOTRACE TRACEONLY STATISTICS
ALTER SESSION SET TRACEFILE_IDENTIFIER = 'SQL_10046_10053';
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 8';
ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';
SELECT /*+ INDEX(T1) */
*
FROM
T1
WHERE
ID BETWEEN 1000 AND 10000;
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';
ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF';
SET AUTOTRACE OFF
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH BUFFER_CACHE;
SET AUTOTRACE TRACEONLY STATISTICS
ALTER SESSION SET TRACEFILE_IDENTIFIER = 'SQL_10046_10053_IND';
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 8';
ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';
SELECT /*+ INDEX(T1) IND */
*
FROM
T1
WHERE
ID BETWEEN 1000 AND 10000;
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';
ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF';
SET AUTOTRACE OFF
Standard Edition:
Statistics
---------------------------------------------------
1 recursive calls
0 db block gets
90292 consistent gets
1669 physical reads
0 redo size
503244 bytes sent via SQL*Net to client
1462 bytes received via SQL*Net from client
92 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
90010 rows processed
Enterprise Edition:
Statistics
---------------------------------------------------
1 recursive calls
0 db block gets
90292 consistent gets
1585 physical reads
0 redo size
503244 bytes sent via SQL*Net to client
1482 bytes received via SQL*Net from client
92 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
90010 rows processed
Standard Edition:
SELECT /*+ INDEX(T1) IND */
*
FROM
T1
WHERE
ID BETWEEN 1000 AND 10000
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 92 0.14 1.28 1669 90292 0 90010
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 94 0.15 1.31 1669 90292 0 90010
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 47
Rows Row Source Operation
------- ---------------------------------------------------
90010 TABLE ACCESS BY INDEX ROWID T1 (cr=90292 pr=1669 pw=0 time=157359 us)
90010 INDEX RANGE SCAN IND_T1_ID (cr=282 pr=202 pw=0 time=48156 us)(object id 47848)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 92 0.00 0.00
db file sequential read 5 0.04 0.06
db file scattered read 208 0.03 1.06
SQL*Net message from client 92 0.00 0.09
SQL*Net more data to client 180 0.00 0.00
Enterprise Edition:
SELECT /*+ INDEX(T1) IND */
*
FROM
T1
WHERE
ID BETWEEN 1000 AND 10000
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 92 0.28 1.33 1585 90292 0 90010
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 94 0.29 1.36 1585 90292 0 90010
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 164
Rows Row Source Operation
------- ---------------------------------------------------
90010 TABLE ACCESS BY INDEX ROWID T1 (cr=90292 pr=1585 pw=0 time=128823 us)
90010 INDEX RANGE SCAN IND_T1_ID (cr=282 pr=191 pw=0 time=33849 us)(object id 51663)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 93 0.00 0.00
SQL*Net message from client 93 0.00 0.14
db file sequential read 1506 0.02 0.95
db file parallel read 36 0.02 0.08
SQL*Net more data to client 180 0.00 0.00
파라미터
Standard Edition:
PARAMETERS USED BY THE OPTIMIZER
********************************
*************************************
PARAMETERS WITH ALTERED VALUES
******************************
_pga_max_size = 368640 KB
sqlstat_enabled = true
*********************************
Bug Fix Control Environment
***************************
fix 4611850 = enabled
fix 4663804 = enabled
...
fix 8855396 = enabled
*************************************
PARAMETERS WITH DEFAULT VALUES
******************************
optimizer_mode_hinted = false
optimizer_features_hinted = 0.0.0
parallel_execution_enabled = false /* This is the only difference */
parallel_query_forced_dop = 0
parallel_dml_forced_dop = 0
parallel_ddl_forced_degree = 0
parallel_ddl_forced_instances = 0
_query_rewrite_fudge = 90
optimizer_features_enable = 10.2.0.5
_optimizer_search_limit = 5
cpu_count = 8
active_instance_count = 1
parallel_threads_per_cpu = 2
hash_area_size = 131072
bitmap_merge_area_size = 1048576
sort_area_size = 65536
sort_area_retained_size = 0
_sort_elimination_cost_ratio = 0
_optimizer_block_size = 8192
_sort_multiblock_read_count = 2
_hash_multiblock_io_count = 0
_db_file_optimizer_read_count = 8
_optimizer_max_permutations = 2000
pga_aggregate_target = 1843200 KB
_query_rewrite_maxdisjunct = 257
_smm_auto_min_io_size = 56 KB
_smm_auto_max_io_size = 248 KB
_smm_min_size = 1024 KB
_smm_max_size = 184320 KB
_smm_px_max_size = 921600 KB
_cpu_to_io = 0
_optimizer_undo_cost_change = 10.2.0.5
parallel_query_mode = enabled
parallel_dml_mode = disabled
parallel_ddl_mode = enabled
optimizer_mode = all_rows
...
Enterprise Edition:
PARAMETERS USED BY THE OPTIMIZER
********************************
*************************************
PARAMETERS WITH ALTERED VALUES
******************************
_pga_max_size = 368640 KB
sqlstat_enabled = true
*********************************
Bug Fix Control Environment
***************************
fix 4611850 = enabled
fix 4663804 = enabled
...
fix 8855396 = enabled
*************************************
PARAMETERS WITH DEFAULT VALUES
******************************
optimizer_mode_hinted = false
optimizer_features_hinted = 0.0.0
parallel_execution_enabled = true /* This is the only difference */
parallel_query_forced_dop = 0
parallel_dml_forced_dop = 0
parallel_ddl_forced_degree = 0
parallel_ddl_forced_instances = 0
_query_rewrite_fudge = 90
optimizer_features_enable = 10.2.0.5
_optimizer_search_limit = 5
cpu_count = 8
active_instance_count = 1
parallel_threads_per_cpu = 2
hash_area_size = 131072
bitmap_merge_area_size = 1048576
sort_area_size = 65536
sort_area_retained_size = 0
_sort_elimination_cost_ratio = 0
_optimizer_block_size = 8192
_sort_multiblock_read_count = 2
_hash_multiblock_io_count = 0
_db_file_optimizer_read_count = 8
_optimizer_max_permutations = 2000
pga_aggregate_target = 1843200 KB
_query_rewrite_maxdisjunct = 257
_smm_auto_min_io_size = 56 KB
_smm_auto_max_io_size = 248 KB
_smm_min_size = 1024 KB
_smm_max_size = 184320 KB
_smm_px_max_size = 921600 KB
_cpu_to_io = 0
_optimizer_undo_cost_change = 10.2.0.5
parallel_query_mode = enabled
parallel_dml_mode = disabled
parallel_ddl_mode = enabled
optimizer_mode = all_rows
...
.
Standard Edition (obj# 47847 is the table T1, obj# 47848 is the index):
PARSE #20:c=15600,e=27480,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=141267613561
EXEC #20:c=0,e=25,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=141267613640
WAIT #20: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=141267613658
WAIT #20: nam='db file sequential read' ela= 19084 file#=8 block#=8204 blocks=1 obj#=47848 tim=141267632782
WAIT #20: nam='db file sequential read' ela= 7665 file#=8 block#=8535 blocks=1 obj#=47848 tim=141267640532
WAIT #20: nam='db file scattered read' ela= 21195 file#=8 block#=8225 blocks=8 obj#=47848 tim=141267661785
WAIT #20: nam='db file scattered read' ela= 19110 file#=7 block#=3081 blocks=8 obj#=47847 tim=141267680973
FETCH #20:c=0,e=67348,p=18,cr=4,cu=0,mis=0,r=1,dep=0,og=1,tim=141267681023
WAIT #20: nam='SQL*Net message from client' ela= 293 driver id=1413697536 #bytes=1 p3=0 obj#=47847 tim=141267681352
WAIT #20: nam='db file sequential read' ela= 41703 file#=7 block#=3880 blocks=1 obj#=47847 tim=141267723105
WAIT #20: nam='SQL*Net message to client' ela= 0 driver id=1413697536 #bytes=1 p3=0 obj#=47847 tim=141267723145
WAIT #20: nam='db file scattered read' ela= 18782 file#=7 block#=4273 blocks=8 obj#=47847 tim=141267741978
WAIT #20: nam='db file scattered read' ela= 16561 file#=7 block#=6857 blocks=8 obj#=47847 tim=141267758605
WAIT #20: nam='db file scattered read' ela= 32914 file#=8 block#=25 blocks=8 obj#=47847 tim=141267791584
WAIT #20: nam='db file scattered read' ela= 21405 file#=8 block#=825 blocks=8 obj#=47847 tim=141267813063
WAIT #20: nam='db file scattered read' ela= 16306 file#=8 block#=1617 blocks=8 obj#=47847 tim=141267829447
WAIT #20: nam='db file scattered read' ela= 11456 file#=8 block#=2417 blocks=8 obj#=47847 tim=141267840982
WAIT #20: nam='db file scattered read' ela= 17458 file#=8 block#=5817 blocks=8 obj#=47847 tim=141267858514
WAIT #20: nam='db file scattered read' ela= 6765 file#=8 block#=6337 blocks=8 obj#=47847 tim=141267865352
WAIT #20: nam='db file scattered read' ela= 21666 file#=7 block#=3881 blocks=8 obj#=47847 tim=141267887503
WAIT #20: nam='SQL*Net more data to client' ela= 33 driver id=1413697536 #bytes=2001 p3=0 obj#=47847 tim=141267887676
WAIT #20: nam='SQL*Net more data to client' ela= 18 driver id=1413697536 #bytes=2001 p3=0 obj#=47847 tim=141267888243
FETCH #20:c=0,e=207317,p=73,cr=1003,cu=0,mis=0,r=1000,dep=0,og=1,tim=141267888690
WAIT #20: nam='SQL*Net message from client' ela= 1209 driver id=1413697536 #bytes=1 p3=0 obj#=47847 tim=141267889922
WAIT #20: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=47847 tim=141267889993
WAIT #20: nam='db file scattered read' ela= 24491 file#=8 block#=1625 blocks=8 obj#=47847 tim=141267914777
WAIT #20: nam='SQL*Net more data to client' ela= 20 driver id=1413697536 #bytes=2001 p3=0 obj#=47847 tim=141267915146
WAIT #20: nam='db file scattered read' ela= 25155 file#=8 block#=6345 blocks=8 obj#=47847 tim=141267940465
WAIT #20: nam='SQL*Net more data to client' ela= 21 driver id=1413697536 #bytes=2001 p3=0 obj#=47847 tim=141267940964
WAIT #20: nam='db file scattered read' ela= 564 file#=7 block#=3089 blocks=8 obj#=47847 tim=141267941767
WAIT #20: nam='db file scattered read' ela= 17109 file#=7 block#=4281 blocks=8 obj#=47847 tim=141267959039
FETCH #20:c=0,e=69163,p=32,cr=1003,cu=0,mis=0,r=1000,dep=0,og=1,tim=141267959123
.
Enterprise Edition (obj# 51662 is the table T1, obj# 51663 is the index):
PARSE #42:c=15600,e=28517,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=141264134068
EXEC #42:c=0,e=26,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=141264134147
WAIT #42: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=141264134166
WAIT #42: nam='db file sequential read' ela= 14880 file#=7 block#=3131892 blocks=1 obj#=51663 tim=141264149083
WAIT #42: nam='db file sequential read' ela= 7090 file#=7 block#=3148754 blocks=1 obj#=51663 tim=141264156220
WAIT #42: nam='db file sequential read' ela= 11698 file#=7 block#=3148170 blocks=1 obj#=51663 tim=141264167995
WAIT #42: nam='db file sequential read' ela= 4910 file#=7 block#=3107460 blocks=1 obj#=51662 tim=141264172961
FETCH #42:c=0,e=38813,p=4,cr=4,cu=0,mis=0,r=1,dep=0,og=1,tim=141264172997
WAIT #42: nam='SQL*Net message from client' ela= 334 driver id=1413697536 #bytes=1 p3=0 obj#=51662 tim=141264173378
WAIT #42: nam='db file parallel read' ela= 22424 files=1 blocks=9 requests=9 obj#=51662 tim=141264195881
WAIT #42: nam='db file sequential read' ela= 3893 file#=7 block#=3133372 blocks=1 obj#=51662 tim=141264199834
WAIT #42: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=51662 tim=141264199867
WAIT #42: nam='db file sequential read' ela= 275 file#=7 block#=3148171 blocks=1 obj#=51663 tim=141264200254
WAIT #42: nam='db file sequential read' ela= 8689 file#=7 block#=3144263 blocks=1 obj#=51662 tim=141264208995
WAIT #42: nam='db file sequential read' ela= 286 file#=7 block#=3133373 blocks=1 obj#=51662 tim=141264209397
WAIT #42: nam='db file sequential read' ela= 9317 file#=7 block#=3136498 blocks=1 obj#=51662 tim=141264218889
WAIT #42: nam='db file sequential read' ela= 28310 file#=7 block#=3139625 blocks=1 obj#=51662 tim=141264247378
WAIT #42: nam='db file sequential read' ela= 6375 file#=7 block#=3142717 blocks=1 obj#=51662 tim=141264253979
WAIT #42: nam='SQL*Net more data to client' ela= 54 driver id=1413697536 #bytes=2001 p3=0 obj#=51662 tim=141264254135
WAIT #42: nam='db file sequential read' ela= 298 file#=7 block#=3107461 blocks=1 obj#=51662 tim=141264254547
WAIT #42: nam='db file sequential read' ela= 8215 file#=7 block#=3145806 blocks=1 obj#=51662 tim=141264262927
WAIT #42: nam='db file sequential read' ela= 9011 file#=7 block#=3134936 blocks=1 obj#=51662 tim=141264272106
WAIT #42: nam='db file sequential read' ela= 3218 file#=7 block#=3148172 blocks=1 obj#=51663 tim=141264275539
WAIT #42: nam='db file sequential read' ela= 331 file#=7 block#=3138063 blocks=1 obj#=51662 tim=141264275929
WAIT #42: nam='db file sequential read' ela= 314 file#=7 block#=3141175 blocks=1 obj#=51662 tim=141264276463
WAIT #42: nam='db file sequential read' ela= 298 file#=7 block#=3144264 blocks=1 obj#=51662 tim=141264276979
WAIT #42: nam='SQL*Net more data to client' ela= 34 driver id=1413697536 #bytes=2001 p3=0 obj#=51662 tim=141264277128
WAIT #42: nam='db file sequential read' ela= 299 file#=7 block#=3133374 blocks=1 obj#=51662 tim=141264277531
WAIT #42: nam='db file sequential read' ela= 351 file#=7 block#=3136499 blocks=1 obj#=51662 tim=141264278134
WAIT #42: nam='db file sequential read' ela= 302 file#=7 block#=3139626 blocks=1 obj#=51662 tim=141264278652
FETCH #42:c=0,e=105439,p=26,cr=1003,cu=0,mis=0,r=1000,dep=0,og=1,tim=141264278839
WAIT #42: nam='SQL*Net message from client' ela= 1528 driver id=1413697536 #bytes=1 p3=0 obj#=51662 tim=141264280396
WAIT #42: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=51662 tim=141264280492
WAIT #42: nam='db file sequential read' ela= 321 file#=7 block#=3148173 blocks=1 obj#=51663 tim=141264280856
WAIT #42: nam='db file parallel read' ela= 359 files=1 blocks=2 requests=2 obj#=51662 tim=141264281277
WAIT #42: nam='db file sequential read' ela= 332 file#=7 block#=3145807 blocks=1 obj#=51662 tim=141264281840
WAIT #42: nam='db file sequential read' ela= 332 file#=7 block#=3134937 blocks=1 obj#=51662 tim=141264282332
WAIT #42: nam='db file sequential read' ela= 289 file#=7 block#=3138064 blocks=1 obj#=51662 tim=141264282835
WAIT #42: nam='db file sequential read' ela= 288 file#=7 block#=3141176 blocks=1 obj#=51662 tim=141264283339
WAIT #42: nam='db file sequential read' ela= 325 file#=7 block#=3144265 blocks=1 obj#=51662 tim=141264283878
WAIT #42: nam='SQL*Net more data to client' ela= 32 driver id=1413697536 #bytes=2001 p3=0 obj#=51662 tim=141264283957
WAIT #42: nam='db file sequential read' ela= 276 file#=7 block#=3133375 blocks=1 obj#=51662 tim=141264284409
WAIT #42: nam='db file sequential read' ela= 284 file#=7 block#=3148174 blocks=1 obj#=51663 tim=141264284877
WAIT #42: nam='db file sequential read' ela= 274 file#=7 block#=3136500 blocks=1 obj#=51662 tim=141264285216
WAIT #42: nam='db file sequential read' ela= 321 file#=7 block#=3139627 blocks=1 obj#=51662 tim=141264285771
WAIT #42: nam='db file sequential read' ela= 330 file#=7 block#=3142719 blocks=1 obj#=51662 tim=141264286316
WAIT #42: nam='db file sequential read' ela= 339 file#=7 block#=3107463 blocks=1 obj#=51662 tim=141264286826
WAIT #42: nam='db file sequential read' ela= 292 file#=7 block#=3145808 blocks=1 obj#=51662 tim=141264287279
WAIT #42: nam='SQL*Net more data to client' ela= 32 driver id=1413697536 #bytes=2001 p3=0 obj#=51662 tim=141264287381
WAIT #42: nam='db file sequential read' ela= 238 file#=7 block#=3134938 blocks=1 obj#=51662 tim=141264287715
WAIT #42: nam='db file sequential read' ela= 319 file#=7 block#=3138065 blocks=1 obj#=51662 tim=141264288240
WAIT #42: nam='db file sequential read' ela= 3642 file#=7 block#=3141177 blocks=1 obj#=51662 tim=141264292099
FETCH #42:c=0,e=11849,p=18,cr=1003,cu=0,mis=0,r=1000,dep=0,og=1,tim=141264292285