Create an explain plan for query over a global temporary table

explainoracletemporary-tables

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.

Best Answer

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.

Related Solutions

SQL Server 2005 – INSERT Performance: Temporary Tables vs Table Variables

The obvious difference between the two plans is that the fast one is parallel and the slower one serial.

This is one of the limitations of plans that insert into table variables. As mentioned in the comments (and it seems as though it had the desired effect) you could try doing

INSERT INTO @DATA ( ... ) 
EXEC('SELECT .. FROM ...')

to see if that gets around the limitation.

Mysql – How to copy the MySQL EXPLAIN result into a table

If you are running on Linux OS you could save it into a file.

Example:

User='root'
Pass='text'
Host='10.0.0.223' (If it's remote)

On command line run:

mysql -u $User -p$Pass --host=$Host -e "EXPLAIN SELECT * FROM mysql.user LIMIT 0,2" | tee -a test.txt

mysql -e will execute the query on the command line.

tee -a will read from standard input and write to file test.txt

And you'll get:

enter image description here

You can use perl to replace the death lines to any delimiter you want (I used comma in this example).

perl -wnlpi -e 's/\s+/,/g;' text.txt