3

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.

3
  • Which version of Oracle are you on?
    – Joe Obbish
    Jul 3, 2017 at 21:24
  • We use Oracle 11g
    – Nachtgold
    Jul 4, 2017 at 10:34
  • But if there is a 12c option, that could be a good argument to update the system.
    – Nachtgold
    Jul 4, 2017 at 10:45

2 Answers 2

2

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.

1
  • 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.
    – Nachtgold
    Jul 4, 2017 at 10:38
1

Not really; but if the goal is to improve the performance of the query that aggregates information in the GTT, you could set OPTIMIZER_DYNAMIC_SAMPLING to maybe 5 or higher, if your Oracle version is 10g or higher.

That would encourage Oracle's optimizer to work harder to determine the best plan by running some sampling queries.

You can use that via a hint:

SELECT /*+ OPT_PARAM('OPTIMIZER_DYNAMIC_SAMPLING' '5') */ * from gtt;
1
  • We want to "sample" before the real work will done.
    – Nachtgold
    Jul 4, 2017 at 10:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.