Different Performance from Standard Edition and Enterprise Edition? 2

21 11 2010

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.


Actions

Information

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 139 other followers

%d bloggers like this: