November 21, 2010
(Forward to the Next Post in the Series)
As I am sure that quite a few people are aware, the Standard Edition of Oracle Database is likely to be suitable for many of the smaller databases that you might find in your organization, as long as the following are not needed:
- Parallel execution
- Bitmap indexes
- Conversion of B*tree indexes to bitmap indexes in execution plans to permit bitmap index join operations
- Partitioning (this is an extra cost option)
- Diagnostic Pack, Tuning Pack, and the other extra cost license options
- Streams
- ???
Are there other differences, possibly related to optimizer or execution behavior? I created a 10.2.0.5 database on the Standard Edition and Enterprise Edition, and then performed a couple of tests. First, I specified the following CPU (system) statistics for both databases to help eliminate potential differences in the workload statistics:
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)
Next, a simple table is created with an index in an ASSM autoallocate tablespace, the table and index statistics are collected at a 100% sample size without histograms, and then the table and index extent sizes are displayed:
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
The output of the above query was identical for both databases.
Now the test script with a very simple SQL statement, where the test script is actually divided into two halves, the first of which will be thrown away so that physical reads from recursive SQL statements may be factored out. In the test script:
- The buffer cache is flushed twice to force physical reads
- The fetch array size for SQL*Plus is set to 1,000 rows
- Output of the rows to the screen is surpressed
- The trace file is given a name, making it easy to find the trace file at a later time
- A 10046 trace at level 8 (wait events), and a 10053 trace at level 1 (to permit seeing the query optimizer’s decisions) are enabled
- The test query is executed with a hint to force an index access path, if one exists
- The above steps are repeated a second time (only the results of the second execution are used).
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
What are the statistics displayed by autotrace?
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
Notice in the above the difference in the number of blocks read by physical reads, the Standard Edition performed an additional 84 block reads (some of these might have been multi-block reads). Why the difference? Was it because the SQL*Plus client from 11.2.0.1 was connected to the Standard Edition, and the SQL*Plus client from 10.2.0.5 was connected to the Enterprise Edition? Let’s take a look at the TKPROF summaries (processed by TKPROF from 10.2.0.5):
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
In the above, we see that physical reads only happened during the fetch, the Row Source Operation execution plans are identical, and yet the wait events are different.
Let’s look at the 10053 output in the raw trace file.
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 ...
Well, the 10053 portion of the trace file did not reveal much of a difference, other than parallel execution being disabled in Standard Edition, while it is enabled in Enterprise Edition. Let’s take a look at the 10046 portion of the trace file, specifically the retrieval of the first 2,001 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
From the above, we see that the Standard Edition reads blocks 8204 and 8535 of the index, and then reads 8 blocks (at this point, the extent size of the index segment was likely 64KB) of the index starting at block 8225. Standard Edition then reads the table blocks using mostly db file scattered reads of 8 blocks each.
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
Interestingly, Enterprise Edition seems to be favoring single block reads for the index and table blocks, with an occasional db file parallel read wait to pick up non-adjacent blocks from the table (in all but one case throughout the trace file just two blocks were read at a time during the db file parallel read waits).
What happens if we try to force a full table scan in Standard Edition and Enterprise Edition?
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_JUNK'; ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 8'; ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1'; SELECT /*+ FULL(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_FULL'; ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 8'; ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1'; SELECT /*+ FULL(T1) FULL */ * 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
The statistics in both cases are identical:
Statistics --------------------------------------------------- 1 recursive calls 0 db block gets 15484 consistent gets 15386 physical reads 0 redo size 826458 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
If we try to force parallel execution (something not possible on Standard Edition):
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_PJUNK'; ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 8'; ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1'; SELECT /*+ FULL(T1) PARALLEL(T1 8 ) */ * 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_PFULL'; ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 8'; ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1'; SELECT /*+ FULL(T1) PARALLEL(T1 8 ) PFULL */ * 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
We see the following output.
Standard Edition:
Statistics --------------------------------------------------- 1 recursive calls 0 db block gets 15484 consistent gets 15386 physical reads 0 redo size 826458 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 --------------------------------------------------- 45 recursive calls 4 db block gets 16534 consistent gets 15390 physical reads 672 redo size 826458 bytes sent via SQL*Net to client 1482 bytes received via SQL*Net from client 92 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 90010 rows processed
The Row Source Operation execution plan, directly from the trace files.
Standard Edition:
STAT #20 id=1 cnt=90010 pid=0 pos=1 obj=47847 op='TABLE ACCESS FULL T1 (cr=15484 pr=15386 pw=0 time=50102 us)'
Enterprise Edition:
STAT #26 id=1 cnt=90010 pid=0 pos=1 obj=0 op='PX COORDINATOR (cr=4 pr=1 pw=0 time=76752 us)' STAT #26 id=2 cnt=0 pid=1 pos=1 obj=0 op='PX SEND QC (RANDOM) :TQ10000 (cr=0 pr=0 pw=0 time=0 us)' STAT #26 id=3 cnt=0 pid=2 pos=1 obj=0 op='PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us)' STAT #26 id=4 cnt=0 pid=3 pos=1 obj=51662 op='TABLE ACCESS FULL T1 (cr=0 pr=0 pw=0 time=0 us)'
———–
Suprisingly, in the above very simple test cases, very different behaviors are experienced between the Standard Edition of Oracle Database 10.2.0.5 and the Enterprise Edition of Oracle Database 10.2.0.5. During the index range scan Standard Edition’s runtime engine preferred to perform multi-block reads of the index and table in batches of 8 blocks, while Enterprise Edition’s runtime engine primarily performed single block reads with an occasional read of two non-adjacent blocks. Somewhat surprisingly, even though it primarily performed single block reads, according to the time reported in the Row Source Operation execution plan, Enterprise Edition completed the test slightly faster than Standard Edition (the difference could be due to placement of the datafiles; scattering of extents across the two datafiles – the Standard Edition database was set up with two datafiles in the DEFAULT TABLESPACE for the user, while Enterprise Edition had a single datafile; or file caching/read-ahead at the operating system or disk sub-system level). Standard Edition could not implement the PARALLEL hint, and as a result completed that test in 65% of the time required by Enterprise Edition.
One might wonder what would happen with a more complicated example, or if the test cases were repeated in either 11.2.0.1 or 11.2.0.2.
Nice!
Hi Charles,
(I haven’t read your other posts on the subject yet)
Strange behaviour, indeed. How does one implement an index range scan using multi-block, scattered reads?! And table access by index ROWID? The only situation I can think of where this would make sense would be if your MREADTIM was smaller than your SREADTIM (which explicitly is NOT the case in your tests). Well, maybe also if – as in your test – the physical order of the index leaf blocks matched the logical order very closely. But that would be way too much intelligence to expect from the Standard Edition CBO, given that – AFAIK – there is no statistic about this.
Also, your MBRC is 16, not 8…
Off to the next post, then.
Cheers,
Flado
Flado,
Thanks for leaving the comment. Now that you mention MREADTIM, SREADTIM, and MBRC, I will mention Chris Antognini has a well timed blog article (http://antognini.ch/2010/11/workload-system-statistics-in-11g/) that shows an odd bug with MREADTIM and SREADTIM – yes, SREADTIM is greater in Chris’ blog article, but that is not the point of the blog article. You will see in the third article in this series that I had the following system (CPU) parameters set on my laptop:
Back in September I had collected workload statistics on the 11.2.0.1 database on the laptop and came up with some odd values for SREADTIM and MREADTIM – extremely large numbers on a laptop with a RAID 0 array of 256GB SSD drives. At the time I thought that I had just done something wrong, or that the numbers were so low that Oracle Database simply panicked, or some sort of number overflow happened, thus causing the odd values. It is a bit reassuring to see that Chris was able to identify the problem. So, I manually set the values for SREADTIM and MREADTIM to what I believed to be realistic values for the configuration. I *think* that I also saw odd numbers on the Enterprise Edition 11.2.0.1 database that was used for this blog article, so I copied the statistics from the Enterprise Edition 10.2.0.5 database as the standard for all of the tests.
I am hoping to finish part 4 of this blog article series today. The data load portion (the reproducible data) of the test requires between 1 and 3 hours for each database, so I ran out of time to complete the tests. The next article should be quite long.
Regarding your comment about MBRC and 8, you are correct that the value was set to 16. However, that value should only be used during the cost calculation when the query is hard parsed. The actual physical read size should be limited to the size of the smallest of the DB_FILE_MULTIBLOCK_READ_COUNT (defaulted to 128), or the physical extent size (initially 8 blocks, but increased to to 128 blocks, and then increased again) or 1MB (on most operating system platforms). The odd part is that Standard Edition continued to read 8 table blocks at a time (64KB) rather than increasing to 128 blocks at a time (1MB) once the first 16 table extents were processed. I have not yet found the reason for the 8 block limit – there might be a hidden parameter that limits the read size to 8 blocks, but I have not found that parameter yet.