Create an explain plan for query over a global temporary table I have a query which aggregates data from a global temporary table. The table will be filled right before the aggregation. My goal is a reliable explain plan for that query.

Is there a way in OracleDBMS to simulate a fill level of the temporary table, witout the necessity to insert anything?

Example 

Normal query:

INSERT INTO gtt VALUES(..)
SELECT * FROM gtt


Explain plan:

EXPLAIN PLAN FOR SELECT * FROM gtt /* with hint for maybe 10k records */


Motivation: The goal is to find out whether the statement falls below the threshold values for costs and CPU. We want to find out, what kind or what amount of temporary data breaks our tuning concept.
For testing purposes, you can insert data in your GTT, gather statistics, and lock statistics. That will let you get an explain plan in another session as if your GTT had data there. You should unlock statistics after you're done with testing.

I do wonder why you have this request. As of Oracle 12c GTTs have session-private statistics by default. You can simply gather statistics after the insert. On Oracle 11g you can still safely gather statistics on the GTT if you're guaranteed to only have a single session querying the GTT at once. Otherwise you can delete statistics, lock them, and rely on dynamic sampling to get statistical information about the table. If your default level of dynamic sampling isn't sufficient you can set a level just for the GTT at the query level with the /*+ dynamic_sampling({alias} {level}) */ hint. Another option is to load a representative amount of data and to lock the statistics.