The statistics of the global temporary table cannot be collected, if it is collected, its statistic is definitely 0, it will cause the execution plan is not allowed, so we should lock up its statistic information and prevent the system from collecting automatically.
-- Unlock the table statistics First, then delete the table statistics, and finally lock the table statistics
Declare
V_sql varchar2 (a);
cursor rowList1 is
Select' Begin dbms_stats.unlock_table_stats (user, ' ' | | table_name | | "); End; '
from user_tables s
where s.temporary = ' Y ';
cursor rowList2 is
Select' Begin dbms_stats.delete_table_stats (user, ' ' | | table_name | | "); End; '
from user_tables s
where s.temporary = ' Y ';
cursor rowList3 is
Select' Begin dbms_stats.lock_table_stats (user, ' ' | | table_name | | "); End; '
from user_tables s
where s.temporary = ' Y ';
Begin
Open rowList1;
Open rowList2;
Open rowList3;
Loop
fetch rowList1 into v_sql;
Executeimmediate v_sql;
exit when rowlist1%notfound;
Endloop;
Loop
fetch rowList2 into v_sql;
Executeimmediate v_sql;
exit when rowlist2%notfound;
Endloop;
Loop
fetch rowList3 into v_sql;
Executeimmediate v_sql;
exit when rowlist3%notfound;
Endloop;
close rowList1;
close rowList2;
close rowList3;
End ;
-- stattype_locked= ' All ' It means that the statistics of the table are locked
Select s.table_name,s.stattype_locked from user_tab_statistics s where s.stattype_locked= ' All ' ;
Oracle Locks Temporary Table statistics