Different Performance from Standard Edition and Enterprise Edition? 2

November 21, 2010

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

In the previous article in this series I compared the Standard Edition of Oracle 10.2.0.5 with the Enterprise Edition of Oracle 10.2.0.5 when a simple SQL statement was executed, looking for examples of different performances in the two editions.  This article repeats the tests from the previous article, this time with the Standard Edition of Oracle 11.2.0.1 and the Enterprise Edition of Oracle 11.2.0.1.  Once again, the following workload CPU (system) statistics will be set for both databases:

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)

The test table and its index are created:

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;

Just like in the earlier article, the table was created in an ASSM autoallocate tablespace, so the extents for the objects appeared as follows:

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 

If we execute the test script:

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 

We see the following statistics output by the two editions (once again, only from the second half of the script):

Standard Edition:

Statistics
---------------------------------------------------
          1  recursive calls
          0  db block gets
      90292  consistent gets
       1629  physical reads
          0  redo size
     503259  bytes sent via SQL*Net to client
       1509  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
       1623  physical reads
          0  redo size
     503259  bytes sent via SQL*Net to client
       1510  bytes received via SQL*Net from client
         92  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      90010  rows processed 

Just as in the earlier article, we see that the Standard edition performed more physical block reads than the Enterprise edition.  Next, taking a look at the captured trace files using TKPROF.

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.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       92      0.15       0.45       1629      90292          0       90010
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       94      0.17       0.47       1629      90292          0       90010

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 51 

Rows     Row Source Operation
-------  ---------------------------------------------------
  90010  TABLE ACCESS BY INDEX ROWID T1 (cr=90292 pr=1629 pw=0 time=125858 us cost=90255 size=9542226 card=90021)
  90010   INDEX RANGE SCAN IND_T1_ID (cr=282 pr=216 pw=0 time=17668 us cost=203 size=0 card=90021)(object id 20276)

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 scattered read                         47        0.03          0.10
  SQL*Net message from client                    92        0.00          0.11
  db file parallel read                          24        0.03          0.03
  db file sequential read                      1222        0.02          0.11 

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.03       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       92      0.15       0.69       1623      90292          0       90010
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       94      0.18       0.71       1623      90292          0       90010

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 194 

Rows     Row Source Operation
-------  ---------------------------------------------------
  90010  TABLE ACCESS BY INDEX ROWID T1 (cr=90292 pr=1623 pw=0 time=131236 us cost=90255 size=9542226 card=90021)
  90010   INDEX RANGE SCAN IND_T1_ID (cr=282 pr=216 pw=0 time=16516 us cost=203 size=0 card=90021)(object id 83180)

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 scattered read                         54        0.05          0.31
  SQL*Net message from client                    92        0.00          0.12
  db file parallel read                          18        0.04          0.04
  db file sequential read                      1214        0.00          0.11 

From the above, it appears that the Standard Edition of Oracle Database 11.2.0.1 has gained the ability to use db file parallel reads for this SQL statement – it performed 24 such reads, while the Enterprise Edition only performed 18.  The difference probably means that we will see some interesting differences in the generated trace files.

Once again, the only difference in the 10053 portion of the trace files was this line:

parallel_execution_enabled          = false   /* This is True 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# 20275 is the table T1, obj# 20276 is the index):

PARSE #2:c=15600,e=17980,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=2892477391,tim=176265018485
EXEC #2:c=0,e=24,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2892477391,tim=176265018563
WAIT #2: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=0 tim=176265018598
WAIT #2: nam='db file scattered read' ela= 76 file#=8 block#=8320 blocks=8 obj#=20276 tim=176265018735
WAIT #2: nam='db file scattered read' ela= 19381 file#=8 block#=8648 blocks=8 obj#=20276 tim=176265038168
WAIT #2: nam='db file scattered read' ela= 102 file#=8 block#=8344 blocks=8 obj#=20276 tim=176265038360
WAIT #2: nam='db file scattered read' ela= 98 file#=7 block#=3200 blocks=8 obj#=20275 tim=176265038517
FETCH #2:c=0,e=19935,p=32,cr=4,cu=0,mis=0,r=1,dep=0,og=1,plh=2892477391,tim=176265038551
WAIT #2: nam='SQL*Net message from client' ela= 284 driver id=1413697536 #bytes=1 p3=0 obj#=20275 tim=176265038872
WAIT #2: nam='db file parallel read' ela= 36748 files=2 blocks=9 requests=9 obj#=20275 tim=176265075691
WAIT #2: nam='db file scattered read' ela= 32752 file#=7 block#=3992 blocks=8 obj#=20275 tim=176265108569
WAIT #2: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=20275 tim=176265108641
WAIT #2: nam='db file sequential read' ela= 80 file#=8 block#=6462 blocks=1 obj#=20275 tim=176265108867
WAIT #2: nam='db file sequential read' ela= 67 file#=8 block#=948 blocks=1 obj#=20275 tim=176265109051
WAIT #2: nam='db file sequential read' ela= 62 file#=8 block#=2537 blocks=1 obj#=20275 tim=176265109286
WAIT #2: nam='db file sequential read' ela= 69 file#=7 block#=4000 blocks=1 obj#=20275 tim=176265109526
WAIT #2: nam='db file sequential read' ela= 29 file#=8 block#=5940 blocks=1 obj#=20275 tim=176265109709
WAIT #2: nam='db file sequential read' ela= 64 file#=8 block#=148 blocks=1 obj#=20275 tim=176265109901
WAIT #2: nam='db file sequential read' ela= 64 file#=7 block#=6981 blocks=1 obj#=20275 tim=176265110070
WAIT #2: nam='db file sequential read' ela= 62 file#=8 block#=1743 blocks=1 obj#=20275 tim=176265110247
WAIT #2: nam='db file sequential read' ela= 67 file#=7 block#=4398 blocks=1 obj#=20275 tim=176265110606
WAIT #2: nam='db file sequential read' ela= 65 file#=8 block#=6463 blocks=1 obj#=20275 tim=176265110842
WAIT #2: nam='db file sequential read' ela= 62 file#=8 block#=949 blocks=1 obj#=20275 tim=176265111020
WAIT #2: nam='db file sequential read' ela= 67 file#=8 block#=2538 blocks=1 obj#=20275 tim=176265111323
WAIT #2: nam='db file scattered read' ela= 101 file#=7 block#=4001 blocks=7 obj#=20275 tim=176265111656
FETCH #2:c=0,e=72863,p=36,cr=1003,cu=0,mis=0,r=1000,dep=0,og=1,plh=2892477391,tim=176265111762
WAIT #2: nam='SQL*Net message from client' ela= 1318 driver id=1413697536 #bytes=1 p3=0 obj#=20275 tim=176265113117
WAIT #2: nam='SQL*Net message to client' ela= 0 driver id=1413697536 #bytes=1 p3=0 obj#=20275 tim=176265113177
WAIT #2: nam='db file sequential read' ela= 67 file#=8 block#=5941 blocks=1 obj#=20275 tim=176265113282
WAIT #2: nam='db file sequential read' ela= 63 file#=8 block#=149 blocks=1 obj#=20275 tim=176265113449
WAIT #2: nam='db file sequential read' ela= 84 file#=7 block#=6982 blocks=1 obj#=20275 tim=176265113648
WAIT #2: nam='db file sequential read' ela= 53 file#=8 block#=1744 blocks=1 obj#=20275 tim=176265113797
WAIT #2: nam='db file sequential read' ela= 63 file#=7 block#=4399 blocks=1 obj#=20275 tim=176265114104
WAIT #2: nam='db file sequential read' ela= 63 file#=8 block#=6464 blocks=1 obj#=20275 tim=176265114305
WAIT #2: nam='db file sequential read' ela= 34 file#=8 block#=950 blocks=1 obj#=20275 tim=176265114420
WAIT #2: nam='db file sequential read' ela= 77 file#=8 block#=2539 blocks=1 obj#=20275 tim=176265114639
WAIT #2: nam='db file sequential read' ela= 65 file#=8 block#=5942 blocks=1 obj#=20275 tim=176265114953
WAIT #2: nam='db file sequential read' ela= 27 file#=8 block#=150 blocks=1 obj#=20275 tim=176265115064
WAIT #2: nam='db file sequential read' ela= 37 file#=7 block#=6983 blocks=1 obj#=20275 tim=176265115189
WAIT #2: nam='db file sequential read' ela= 66 file#=8 block#=1745 blocks=1 obj#=20275 tim=176265115337
WAIT #2: nam='db file sequential read' ela= 67 file#=7 block#=3208 blocks=1 obj#=20275 tim=176265115541
WAIT #2: nam='db file sequential read' ela= 49 file#=7 block#=4400 blocks=1 obj#=20275 tim=176265115721
FETCH #2:c=15600,e=2675,p=14,cr=1003,cu=0,mis=0,r=1000,dep=0,og=1,plh=2892477391,tim=176265115823 

Enterprise Edition (obj# 83179 is the table T1, obj# 83180 is the index):

PARSE #1:c=31200,e=18656,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=2892477391,tim=176274491584
EXEC #1:c=0,e=28,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2892477391,tim=176274491668
WAIT #1: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=0 tim=176274491707
WAIT #1: nam='db file scattered read' ela= 19945 file#=8 block#=170816 blocks=8 obj#=83180 tim=176274511716
WAIT #1: nam='db file scattered read' ela= 18407 file#=7 block#=1762512 blocks=8 obj#=83180 tim=176274530199
WAIT #1: nam='db file scattered read' ela= 20079 file#=7 block#=1638560 blocks=8 obj#=83180 tim=176274550345
WAIT #1: nam='db file scattered read' ela= 13687 file#=7 block#=1637552 blocks=8 obj#=83179 tim=176274564106
FETCH #1:c=0,e=72429,p=32,cr=4,cu=0,mis=0,r=1,dep=0,og=1,plh=2892477391,tim=176274564157
WAIT #1: nam='SQL*Net message from client' ela= 369 driver id=1413697536 #bytes=1 p3=0 obj#=83179 tim=176274564557
WAIT #1: nam='db file parallel read' ela= 48042 files=2 blocks=9 requests=9 obj#=83179 tim=176274612665
WAIT #1: nam='db file scattered read' ela= 20902 file#=7 block#=1638344 blocks=8 obj#=83179 tim=176274633689
WAIT #1: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=83179 tim=176274633751
WAIT #1: nam='db file sequential read' ela= 108 file#=7 block#=1760446 blocks=1 obj#=83179 tim=176274634001
WAIT #1: nam='db file sequential read' ela= 121 file#=7 block#=1639273 blocks=1 obj#=83179 tim=176274634381
WAIT #1: nam='db file sequential read' ela= 105 file#=8 block#=133664 blocks=1 obj#=83179 tim=176274634695
WAIT #1: nam='db file sequential read' ela= 103 file#=7 block#=1759924 blocks=1 obj#=83179 tim=176274634995
WAIT #1: nam='db file sequential read' ela= 103 file#=8 block#=170764 blocks=1 obj#=83179 tim=176274635264
WAIT #1: nam='db file sequential read' ela= 111 file#=8 block#=136645 blocks=1 obj#=83179 tim=176274635523
WAIT #1: nam='db file sequential read' ela= 111 file#=8 block#=132870 blocks=1 obj#=83179 tim=176274635889
WAIT #1: nam='db file sequential read' ela= 101 file#=8 block#=134062 blocks=1 obj#=83179 tim=176274636191
WAIT #1: nam='db file sequential read' ela= 109 file#=7 block#=1760447 blocks=1 obj#=83179 tim=176274636516
WAIT #1: nam='db file sequential read' ela= 104 file#=7 block#=1639274 blocks=1 obj#=83179 tim=176274636927
WAIT #1: nam='db file sequential read' ela= 104 file#=8 block#=133665 blocks=1 obj#=83179 tim=176274637262
FETCH #1:c=0,e=72866,p=28,cr=1003,cu=0,mis=0,r=1000,dep=0,og=1,plh=2892477391,tim=176274637445
WAIT #1: nam='SQL*Net message from client' ela= 1388 driver id=1413697536 #bytes=1 p3=0 obj#=83179 tim=176274638863
WAIT #1: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=83179 tim=176274638948
WAIT #1: nam='db file sequential read' ela= 117 file#=7 block#=1759925 blocks=1 obj#=83179 tim=176274639125
WAIT #1: nam='db file sequential read' ela= 103 file#=8 block#=170765 blocks=1 obj#=83179 tim=176274639381
WAIT #1: nam='db file sequential read' ela= 105 file#=8 block#=136646 blocks=1 obj#=83179 tim=176274639640
WAIT #1: nam='db file scattered read' ela= 1204 file#=7 block#=1638352 blocks=8 obj#=83179 tim=176274641051
WAIT #1: nam='db file sequential read' ela= 117 file#=8 block#=132871 blocks=1 obj#=83179 tim=176274641320
WAIT #1: nam='db file sequential read' ela= 105 file#=8 block#=134063 blocks=1 obj#=83179 tim=176274641630
WAIT #1: nam='db file sequential read' ela= 104 file#=7 block#=1760448 blocks=1 obj#=83179 tim=176274641938
WAIT #1: nam='db file sequential read' ela= 106 file#=7 block#=1639275 blocks=1 obj#=83179 tim=176274642303
WAIT #1: nam='db file sequential read' ela= 104 file#=8 block#=133666 blocks=1 obj#=83179 tim=176274642618
WAIT #1: nam='db file sequential read' ela= 101 file#=7 block#=1759926 blocks=1 obj#=83179 tim=176274642919
WAIT #1: nam='db file sequential read' ela= 106 file#=8 block#=170766 blocks=1 obj#=83179 tim=176274643172
WAIT #1: nam='db file sequential read' ela= 109 file#=8 block#=136647 blocks=1 obj#=83179 tim=176274643432
WAIT #1: nam='db file sequential read' ela= 104 file#=8 block#=132872 blocks=1 obj#=83179 tim=176274643787
WAIT #1: nam='db file sequential read' ela= 102 file#=8 block#=134064 blocks=1 obj#=83179 tim=176274644089
FETCH #1:c=0,e=5354,p=21,cr=1003,cu=0,mis=0,r=1000,dep=0,og=1,plh=2892477391,tim=176274644260 

Other than the location of the 7 block read using db file scattered read (before or after the second fetch), there is not much that apparently differentiates the two trace files.  Most of the blocks read in both trace files are read a single block at a time, with an occasional db file parallel read for two blocks or a db file scattered read for 7 or 8 blocks.

Forcing a full table scan produced the same statistics in both editions:

Statistics
---------------------------------------------------
          1  recursive calls
          0  db block gets
      15484  consistent gets
      15386  physical reads
          0  redo size
     826473  bytes sent via SQL*Net to client
       1509  bytes received via SQL*Net from client
         92  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      90010  rows processed  

What about the test that included the PARALLEL and FULL hints?

Standard Edition:

Statistics
---------------------------------------------------
          1  recursive calls
          0  db block gets
      15484  consistent gets
      15386  physical reads
          0  redo size
     826473  bytes sent via SQL*Net to client
       1509  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
---------------------------------------------------
         25  recursive calls
          0  db block gets
      15723  consistent gets
      15386  physical reads
          0  redo size
     826473  bytes sent via SQL*Net to client
       1510  bytes received via SQL*Net from client
         92  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      90010  rows processed 

Obviously, we should have expected some differences between the two editions when the PARALLEL hint was included, and we see a slight increase in the number of consistent gets, and 24 additional recursive calls.  Let’s take a look at the Row Source Operation execution plan from the raw trace file for the SQL statement with the PARALLEL hint.

Standard Edition:

STAT #3 id=1 cnt=90010 pid=0 pos=1 obj=20275 op='TABLE ACCESS FULL T1 (cr=15484 pr=15386 pw=0 time=23430 us cost=1231 size=9542226 card=90021)' 

Enterprise Edition:

STAT #8 id=1 cnt=90010 pid=0 pos=1 obj=0 op='PX COORDINATOR  (cr=5 pr=1 pw=0 time=14980 us)'
STAT #8 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 cost=170 size=9542226 card=90021)'
STAT #8 id=3 cnt=0 pid=2 pos=1 obj=0 op='PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us cost=170 size=9542226 card=90021)'
STAT #8 id=4 cnt=0 pid=3 pos=1 obj=83179 op='TABLE ACCESS FULL T1 (cr=0 pr=0 pw=0 time=0 us cost=170 size=9542226 card=90021)'  

———–

At least for the above simple test scripts, there is difference in the performance for the Standard Edition and the Enterprise Edition (as long as parallel execution is not attempted to be used).  The test case is, however, interesting as it illustrates potential differences in the performance when upgrading from Oracle Database 10.2.0.5 to Oracle Database 11.2.0.1.  We likely need a more complicated test case script to demonstrate the differences in performance for the two editions of Oracle Database 11.2.0.1.

Different Performance from Standard Edition and Enterprise Edition? 1

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.