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
[oracle@cm01dbm01 sqlmon]$ srvctl stop database -d visx
[oracle@cm01dbm01 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
[oracle@cm01dbm01 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
