Hello AskTom Team,
We have a following code snippet after a batch process which times out with ORA-01013: user requested cancel of current operation and consumes lot of TEMP space. Our customer complained to us they are running out of 1TB or temp space.
for i in ( select TBL_NAME AS table_name from <table_name> where ID=<some_id>)
loop
DBMS_STATS.gather_table_stats(sys_context('USERENV', 'CURRENT_SCHEMA'), i.table_name);
end loop;
Oracle.DataAccess.Client.OracleException ORA-01013: user requested cancel of current operation
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 87
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 1173
ORA-01013: user requested cancel of current operation
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 1201
ORA-06512: at "SYS.DBMS_STATS", line 14827
Above runs for several hours and times out.
Is there anything we could do about this error?
Thanks!
A lot of that will depends on what defaults/preferences are set, and what version you are on etc.
The biggest sort cost for dbms_stats is normally working out distinct values per column. *Most* dbms_stats operations are optimized nowadays to avoid *large* amounts of TEMP by doing what is known as one-pass distinct sampling, but they only can take advantage of that if the estimate size is default ('auto') and also what histograms are being calculated and the size of them.
(And obviously the size of the table).
So take a look at DBA_TAB_STAT_PREFS for the table(s) concerned, and also the system wide defaults. ORA-01013 is not a temp space error - so either someone explicitly cancelled the operation, or perhaps a resource management rule of some sort intervened.