CODEWITHSUNDEEP
oracle-databaseperformance
Dreamer
Dreamer

Reputation: 7549

How to turn off stats on Global Temporary Table and impact

Hello I am not very experienced on Oracle DB administration, I have several queries that started to work really slow and all involve with temp table (12c). I see several posts talking about disable stats on GTT (Global Temporary Table), however I didn't find much but just following setting to disable

exec dbms_scheduler.disable('SYS.GATHER_STATS_JOB');

my question is any specific way to turn off stats ONLY to GTT, and what is the negative impact on above command?

Upvotes: 0

Views: 1147

Answers (1)

B Samedi
B Samedi

Reputation: 380

This disables statistics gathering on a table

begin
    dbms_stats.delete_table_stats('TABLE_OWNER', 'TABLE_NAME');
    dbms_stats.lock_table_stats('TABLE_OWNER', 'TABLE_NAME');
end;
/

However a better idea is to collect statistics on the table when it is filled with representative data set:

begin
    dbms_stats.gather_table_stats('TABLE_OWNER', 'TABLE_NAME');
    dbms_stats.lock_table_stats('TABLE_OWNER', 'TABLE_NAME');
end;
/

https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_stats.htm#i1043993

Upvotes: 1

Related Questions