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.
Charles Hooper's Oracle Notes 
Leave a comment