Auto DOP, Cell Offload, and Oracle EBS

Imagine you’ve recently migrating your Oracle e-Business Suite to Exadata, and you’ve decided to implement automatic degree of parallelism, or Auto DOP. How does this impact Smart Scan?
Let’s test by first ensuring that our Auto DOP is set to MANUAL:
APPS @ visx1> alter system set parallel_degree_policy=MANUAL;
System altered.
Elapsed: 00:00:00.02
APPS @ visx1> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.04
APPS @ visx1>
Now we’ll run a “full scan” on a GL table, GL_BALANCES, and measure its Smart Scan efficiency:
APPS @ visx1> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.00
APPS @ visx1> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.21
APPS @ visx1> select /*+ full (x) */ count(*) from gl_balances x;
  COUNT(*)
———-
   9220395
1 row selected.
Elapsed: 00:00:04.62
APPS @ visx1> set echo on
APPS @ visx1> @exa_ss2_mystat
APPS @ visx1> set lines 100
APPS @ visx1> set echo off
NNAME    VALUE
———————————————————— ——————–
cell physical IO bytes eligible for predicate offload    1,091,272,704
cell physical IO interconnect bytes      119,399,600
cell physical IO interconnect bytes returned by smart scan      117,212,336
session pga memory max 4,584,784
session pga memory 3,601,744
session logical reads  381,733
CPU used by this session      297
cell flash cache read hits      267
8 rows selected.
Elapsed: 00:00:00.01
Eligible MB   SS Returned MB    Pct Saved
——————– ——————– ————
      1,041      114 89.06
1 row selected.
As you can see above, we’ve attained nearly a 90% cell offload efficiency.
Next, let’s enable Auto DOP (assuming IO calibration is complete):
APPS @ visx1> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.01
APPS @ visx1> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.26
APPS @ visx1> select /*+ full (x) */ count(*) from gl_balances x;
  COUNT(*)
———-
   9220395
1 row selected.
Elapsed: 00:00:28.71
APPS @ visx1> set echo on
APPS @ visx1> @exa_ss2_mystat
APPS @ visx1> set lines 100
APPS @ visx1> set echo off
NAME VALUE
—————————————- ——————–
cell physical IO interconnect bytes 1,093,001,216
session pga memory    4,012,016
session pga memory max    4,012,016
session logical reads      135,830
cell flash cache read hits 2,045
CPU used by this session  244
6 rows selected.
Elapsed: 00:00:00.04
no rows selected
Elapsed: 00:00:00.00
As we can see, the query ran in 27.46 seconds and did not benefit from Smart Scan.
So do we give up on Auto DOP?  Well, I’d like to say no.  Let’s reset parallel_degree_policy to the default, bounce, and then set to LIMITED:
SYS @ visx1> alter system reset parallel_degree_policy;
System altered.
Elapsed: 00:00:00.01
SYS @ visx1> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
[[email protected] sqlmon]$ srvctl stop database -d visx
[[email protected] sqlmon]$ srvctl start database -d visx
SYS @ visx1> alter system set parallel_degree_policy=LIMITED;
System altered.
Elapsed: 00:00:00.03
SYS @ visx1> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
[[email protected] sqlmon]$ ./s
SQL*Plus: Release 11.2.0.2.0 Production on Mon Jan 30 21:33:39 2012
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
@
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Session altered.
APPS @ visx1> it
APPS @ visx1> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.01
APPS @ visx1> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.14
APPS @ visx1> select /*+ full (x) */ count(*) from gl_balances x;
  COUNT(*)
———-
   9220395
1 row selected.
Elapsed: 00:00:01.06
APPS @ visx1> set echo on
APPS @ visx1> @exa_ss2_mystat
APPS @ visx1> set lines 100
APPS @ visx1> set echo off
NAME    VALUE
———————————————————— ——————–
cell physical IO bytes eligible for predicate offload    1,091,272,704
cell physical IO interconnect bytes      119,649,168
cell physical IO interconnect bytes returned by smart scan      117,134,224
session pga memory max       16,136,176
session pga memory 4,077,552
session logical reads  136,290
cell flash cache read hits      307
CPU used by this session       46
8 rows selected.
Elapsed: 00:00:00.09
Eligible MB   SS Returned MB    Pct Saved
——————– ——————– ————
      1,041      114 89.03
1 row selected.
Elapsed: 00:00:00.00
Viola, we’re back to being able to use Smart Scan for our queries.  So the tricks are:
1) Set parallel_degree_policy=LIMITED
2) Set “_parallel_statement_queuing”=TRUE
3) Color your tables with a DEFAULT DOP