If you're loading data, how is it a read only app? :)
If you:
- Define the GTT as ON COMMIT PRESERVE ROWS
- Use direct path loads (append hint)
- Commit after loading data (to avoid ORA-12838)
The optimizer will gather basic stats as part of the load. See below.
Needing to commit has implications for your transactions. But as this is "read only",
presumably that's not a problem.
If this method is infeasible for you, to gather stats on a table in another schema you need the ANALYZE ANY privilege. Or you can create a a wrapper procedure in the table owner's schema that calls gather stats with definer's rights. Then grant the other user access to this wrapper, e.g.:
create or replace procedure gather as
begin
dbms_stats.gather_table_stats ( ownname => null, tabname => 'gtt' );
end;
/
grant execute on gather to app_user;
Online gather example:
create global temporary table gtt ( c1 int )
on commit preserve rows;
insert /*+ append */into gtt
select level from dual connect by level <= 10;
commit;
set serveroutput off
alter session set statistics_level = all;
select count(*) from gtt;
select * from dbms_xplan.display_cursor ( format => 'ALLSTATS LAST');
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 3 | 1 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 3 | 1 |
| 2 | TABLE ACCESS FULL| GTT | 1 | 10 | 10 |00:00:00.01 | 3 | 1 |
----------------------------------------------------------------------------------------------
Note
-----
- Global temporary table session private statistics used
truncate table gtt;
insert /*+ append */into gtt
select level from dual connect by level <= 1000;
commit;
set serveroutput off
alter session set statistics_level = all;
select count(*) from gtt;
select * from dbms_xplan.display_cursor ( format => 'ALLSTATS LAST');
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 4 | 2 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 4 | 2 |
| 2 | TABLE ACCESS FULL| GTT | 1 | 1000 | 1000 |00:00:00.01 | 4 | 2 |
----------------------------------------------------------------------------------------------
Note
-----
- Global temporary table session private statistics used