What is 12c dbms_stats.gather_schema_stats doing by default? It uses predefined dbms_stats preferences. Here an example of how to show those preferences on 12c – some of the listed preferences below are new to 12c. Then an example of how to show what a gather_schema_stats call will do for default calls.
Here is a call to dbms_stats.gather_schema_stats with the option “GATHER AUTO” which uses predefined preferences.
begin
dbms_stats.gather_schema_stats(
ownname=> ‘MYSCHEMA’ ,
options=> ‘GATHER AUTO’);
end;
/
select dbms_stats.get_prefs(pname=>’AUTOSTATS_TARGET’) from dual;
select dbms_stats.get_prefs(pname=>’CASCADE’) from dual;
select dbms_stats.get_prefs(pname=>’CONCURRENT’) from dual;
select dbms_stats.get_prefs(pname=>’DEGREE’) from dual;
select dbms_stats.get_prefs(pname=>’ESTIMATE_PERCENT’) from dual;
select dbms_stats.get_prefs(pname=>’METHOD_OPT’) from dual;
select dbms_stats.get_prefs(pname=>’NO_INVALIDATE’) from dual;
select dbms_stats.get_prefs(pname=>’GRANULARITY’) from dual;
select dbms_stats.get_prefs(pname=>’PUBLISH’) from dual;
select dbms_stats.get_prefs(pname=>’INCREMENTAL’) from dual;
select dbms_stats.get_prefs(pname=>’INCREMENTAL_STALENESS’) from dual;
select dbms_stats.get_prefs(pname=>’INCREMENTAL_LEVEL’) from dual;
select dbms_stats.get_prefs(pname=>’STALE_PERCENT’) from dual;
select dbms_stats.get_prefs(pname=>’GLOBAL_TEMP_TABLE_STATS’) from dual;
select dbms_stats.get_prefs(pname=>’TABLE_CACHED_BLOCKS’) from dual;
select dbms_stats.get_prefs(pname=>’OPTIONS’) from dual;
Here’s one call to get_prefs and the output:
So now you might ask, how do I know what the call to gather_schema_stats with option gather auto does?
Well – to test that – create a schema with a couple CTAS tables as follows.
While connected as SYS:
create user myschema identified by myschema;
grant connect, resource to myschema;
alter user myschema quota unlimited on USERS;
While connect as myschema:
create table junk as select * from dba_objects;
create table junk2 as select * from dba_objects;
create table junk3 as select * from dba_objects;
While connected SYS (make sure there are no stats by explicitly deleting them):
exec DBMS_STATS.DELETE_TABLE_STATS (ownname=>’MYSCHEMA’,tabname=>’JUNK’);
exec DBMS_STATS.DELETE_TABLE_STATS (ownname=>’MYSCHEMA’,tabname=>’JUNK2′);
exec DBMS_STATS.DELETE_TABLE_STATS (ownname=>’MYSCHEMA’,tabname=>’JUNK3′);
SET SERVEROUTPUT ON
declare
v_object_list dbms_stats.objecttab;
begin
dbms_stats.gather_schema_stats(
ownname => ‘MYSCHEMA’,
options => ‘LIST AUTO’,
— options => ‘LIST STALE’,
— options => ‘LIST EMPTY’,
objlist => v_object_list
);
dbms_output.put_line(‘The number of objects found to create stats on: ‘||to_char( v_object_list.count ));
for idx in 1..v_object_list.count loop
dbms_output.put_line(
rpad(v_object_list(idx).ownname,30) ||
rpad(v_object_list(idx).objtype, 6) ||
rpad(v_object_list(idx).objname,30)
);
end loop;
end;
/
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
The number of objects found to create stats on: 3
MYSCHEMA TABLE JUNK
MYSCHEMA TABLE JUNK2
MYSCHEMA TABLE JUNK3
OK, 3 tables with no statistics – just what we assumed since we deleted statistics from them. Now lets collect stats on the entire schema show then show what needs to be collected – should be an empty set.
EXEC dbms_stats.gather_schema_stats(ownname=>’MYSCHEMA’, options => ‘GATHER AUTO’);
declare
v_object_list dbms_stats.objecttab;
begin
dbms_stats.gather_schema_stats(
ownname => ‘MYSCHEMA’,
options => ‘LIST AUTO’,
— options => ‘LIST STALE’,
— options => ‘LIST EMPTY’,
objlist => v_object_list
);
dbms_output.put_line(‘The number of objects found to create stats on: ‘||to_char( v_object_list.count ));
for idx in 1..v_object_list.count loop
dbms_output.put_line(
rpad(v_object_list(idx).ownname,30) ||
rpad(v_object_list(idx).objtype, 6) ||
rpad(v_object_list(idx).objname,30)
);
end loop;
end;
/
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
The number of objects found to create stats on: 0