Environment:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
In Oracle 12c parameter "OPTIONS" is added to procedure "DBMS_STATS.GATHER_TABLE_STATS".
SQL> desc dbms_stats.gather_table_stats Parameter Type Mode Default?
---------------- ----------------------- ---- --------
OWNNAME VARCHAR2 IN
TABNAME VARCHAR2 IN
PARTNAME VARCHAR2 IN Y
ESTIMATE_PERCENT NUMBER IN Y
BLOCK_SAMPLE BOOLEAN IN Y
METHOD_OPT VARCHAR2 IN Y
DEGREE NUMBER IN Y
GRANULARITY VARCHAR2 IN Y
CASCADE BOOLEAN IN Y
STATTAB VARCHAR2 IN Y
STATID VARCHAR2 IN Y
STATOWN VARCHAR2 IN Y
NO_INVALIDATE BOOLEAN IN Y
STATTYPE VARCHAR2 IN Y
FORCE BOOLEAN IN Y
CONTEXT SYS.DBMS_STATS.CCONTEXT IN Y
OPTIONS VARCHAR2 IN Y <<<<<
options: Further specification of which objects to gather statistics:
- GATHER - gathers statistics on all objects in the schema
- GATHER AUTO - gathers all necessary statistics automatically. Oracle implicitly determines which objects need new statistics.
Looks like the same as in "DBMS_STATS.GATHER_SCHEMA_STATS".
We use the new option in our ETL process developed in Oracle Data Integrator.
At runtime we see the gathering statistics step elapsed time, even when there are no data changes. That's strange and not expected.
Testcase:
Connected to Oracle Database 12c Enterprise Edition Release 12.1.0.2.0
col last_analyzed format a30
set timing on
set echo on
*************************************************
1. DELETE all statistics
*************************************************
begin
dbms_stats.delete_table_stats
(ownname => user
,tabname => 'TST_CUSTOMERS'
);
end;
/
PL/SQL procedure successfully completed
Executed in 2.918 seconds
select count(1) from TST_CUSTOMERS;
COUNT(1)
----------
662602
Executed in 0.047 seconds
select table_name, last_analyzed from user_tables where table_name = 'TST_CUSTOMERS';
TABLE_NAME LAST_ANALYZED
-------------------------------------------------------------------------------- ------------------------------
TST_CUSTOMERS
Executed in 0.016 seconds
select 'STALE_PERCENT' as preference, dbms_stats.get_prefs('STALE_PERCENT') as value from dual
union all
select 'CASCADE' as preference, dbms_stats.get_prefs('CASCADE') as value from dual
union all
select 'OPTIONS' as preference, dbms_stats.get_prefs('OPTIONS') as value from dual;
PREFERENCE VALUE
------------- --------------------------------------------------------------------------------
STALE_PERCENT 10
CASCADE DBMS_STATS.AUTO_CASCADE
OPTIONS GATHER
Executed in 0.031 seconds
**************************************************
2. GATHER_SCHEMA_STATS: first time collect statistics
**************************************************
begin
dbms_stats.gather_schema_stats
(ownname => user
,options => 'GATHER AUTO'
,estimate_percent => dbms_stats.auto_sample_size
,method_opt => 'for all columns size auto'
,cascade => true
);
end;
/
PL/SQL procedure successfully completed
Executed in 44.101 seconds
select table_name, last_analyzed from user_tables where table_name = 'TST_CUSTOMERS';
TABLE_NAME LAST_ANALYZED
-------------------------------------------------------------------------------- ------------------------------
TST_CUSTOMERS 09-09-2015 11:18:47
Executed in 0.031 seconds
****************************************************************************************************
3. GATHER_SCHEMA_STATS: No data changes, not necessary to collect statistics, expected costs no time
****************************************************************************************************
begin
dbms_stats.gather_schema_stats
(ownname => user
,options => 'GATHER AUTO'
,estimate_percent => dbms_stats.auto_sample_size
,method_opt => 'for all columns size auto'
,cascade => true
);
end;
/
PL/SQL procedure successfully completed
Executed in 0.328 seconds
select table_name, last_analyzed from user_tables where table_name = 'TST_CUSTOMERS';
TABLE_NAME LAST_ANALYZED
-------------------------------------------------------------------------------- ------------------------------
TST_CUSTOMERS 09-09-2015 11:18:47
Executed in 0.015 seconds
***************************************************************************************************
4. GATHER_TABLE_STATS: No data changes, not necessary to collect statistics, expected costs no time
***************************************************************************************************
begin
dbms_stats.gather_table_stats
(ownname => user
,tabname => 'TST_CUSTOMERS'
,options => 'GATHER AUTO'
,estimate_percent => dbms_stats.auto_sample_size
,method_opt => 'for all columns size auto'
,cascade => true
);
end;
/
PL/SQL procedure successfully completed
Executed in 48.985 seconds
select table_name, last_analyzed from user_tables where table_name = 'TST_CUSTOMERS';
TABLE_NAME LAST_ANALYZED
-------------------------------------------------------------------------------- ------------------------------
TST_CUSTOMERS 09-09-2015 11:20:02
Executed in 0.031 seconds
***************************************************************************************************
5. GATHER_TABLE_STATS: No data changes, not necessary to collect statistics, expected costs no time
***************************************************************************************************
begin
dbms_stats.gather_table_stats
(ownname => user
,tabname => 'TST_CUSTOMERS'
,options => 'GATHER AUTO'
,estimate_percent => dbms_stats.auto_sample_size
,method_opt => 'for all columns size auto'
,cascade => true
);
end;
/
PL/SQL procedure successfully completed
Executed in 49.593 seconds
select table_name, last_analyzed from user_tables where table_name = 'TST_CUSTOMERS';
TABLE_NAME LAST_ANALYZED
-------------------------------------------------------------------------------- ------------------------------
TST_CUSTOMERS 09-09-2015 11:20:52
Executed in 0.015 seconds
SQL>
Summary:
step 2: Executed in 44.101 seconds
step 3: Executed in 0.328 seconds -> OK
step 4: Executed in 48.985 seconds
step 5: Executed in 49.593 seconds
I expect the execution time of step 4 and 5 is the same as step 3.
What goes wrong with the option gather auto in the gather_table_stats procedure?