Different Performance from Standard Edition and Enterprise Edition? 1

21 11 2010

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.


Actions

Information

3 responses

21 11 2010
Damir Vadas

Nice!

24 11 2010
Flado

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

24 11 2010
Charles Hooper

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:

-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
  Using WORKLOAD Stats
  CPUSPEED: 2714 millions instructions/sec
  SREADTIM: 0.100000 milliseconds
  MREADTIM: 0.200000 millisecons
  MBRC: 78 blocks
  MAXTHR: 302080 bytes/sec
  SLAVETHR: -1 bytes/sec

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

Join 140 other followers

%d bloggers like this: