During the Exadata Database Machine Admin Workshop that I deliver this week in Munich, I got the question whether the gathering of Optimizer Statistics could be done via Smart Scan on Exadata. I didn’t know that and that particular topic seems not to be covered much yet in the docs or other Blogs that I researched briefly. Therefore I came up with this little test case here:

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SQL> alter session set workarea_size_policy=manual;

Session altered.

SQL> alter session set sort_area_size=1000000000;

Session altered.

SQL> create table sales as
select
'Oracle Enterprise Edition' as product,
mod(rownum,5) as channel_id,
mod(rownum,1000) as cust_id ,
5000 as amount_sold,
to_date
('01.' || lpad(to_char(mod(rownum,12)+1),2,'0') || '.2010' ,'dd.mm.yyyy')
as time_id
from dual connect by level<=1e7;

Table created.

We go on with the recommended method to gather statistics and compare that with 30 % and 100 % sample size:

SQL> connect adam/adam
Connected.
SQL> exec dbms_stats.gather_table_stats('ADAM','SALES',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE)

PL/SQL procedure successfully completed.

SQL> select name,value/1024/1024 as mb from v$mystat natural join v$statname
    where name='cell physical IO interconnect bytes returned by smart scan';

NAME                                                                     MB
---------------------------------------------------------------- ----------
cell physical IO interconnect bytes returned by smart scan                0

SQL> exec dbms_stats.delete_table_stats('ADAM','SALES')

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('ADAM','SALES',estimate_percent=>30)

PL/SQL procedure successfully completed.

SQL> select name,value/1024/1024 as mb from v$mystat natural join v$statname
    where name='cell physical IO interconnect bytes returned by smart scan';

NAME                                                                     MB
---------------------------------------------------------------- ----------
cell physical IO interconnect bytes returned by smart scan                0

SQL> exec dbms_stats.delete_table_stats('ADAM','SALES')

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('ADAM','SALES',estimate_percent=>100)

PL/SQL procedure successfully completed.

SQL> select name,value/1024/1024 as mb from v$mystat natural join v$statname
    where name='cell physical IO interconnect bytes returned by smart scan';

NAME                                                                     MB
---------------------------------------------------------------- ----------
cell physical IO interconnect bytes returned by smart scan       477.690498

Conclusion: It is possible to gather Optimizer Statistics via Smart Scan, but only with a 100 % sample size. Now the question is, whether AUTO_SAMPLE_SIZE will gather statistics faster although running without Smart Scan. The quality of the resulting stats will be very similar in both cases. If you are using ESTIMATE_PERCENT < 100 presently, consider to change to AUTO_SAMPLE_SIZE or 100 % on Exadata, as this may complete faster there with Smart Scan than your non-AUTO_SAMPLE_SIZE value does. As always: Don’t believe it, test it 🙂