I have a stored procedure that using a global temp table called ITEMS with delete on commit (session based stats). Inside the stored procedure, sometimes 1 row gets added to ITEMS, sometimes 200,000 rows to that table. ITEMS is then used in a few other queries/updates in the stored procedure.

I add just 1 row to ITEMS, run stats, *then* compile the stored procedure. Then when executing the stored proc, the proc will insert into ITEMS. If it has a only adds a few items to ITEMS, it's relatively fast. If it has thousands of items, then it's excessively slow.

Of course, if I add 200,000 rows to ITEMS, run stats, *then* compile the stored procedure. Then the results are flipped.

I thought about using EXECUTE IMMEDIATE to solve this, but I don't like the idea for a lot of reasons including that it's not easy to edit and I don't get compile time errors.

Is there any other way to make the SQLs in the stored procedure run with adaptive execution plans?

procedure sp is
begin
  
  insert into gtt values (1);
 dbms_stats.set_table_stats('','GTT',numrows=>1,numblks=>1,no_invalidate=>false);

  insert into gtt select ....;
  x := sql%rowcount;
  dbms_stats.set_table_stats('','GTT',numrows=>x,numblks=>x/50,no_invalidate=>false);
  
end;
/