Oracle Database Discussions

Thomas Morgan wrote:

I recently dealt with an issue where a query would not complete when it was using a global temporary table.  Once I replaced the temporary with a permanent table, it worked and returned in a few seconds.

Has anybody experienced this behavior?

If yes, what causes it and can something be done about it?  Also, is there any documentation I can read to learn about this?

I checked and there was plenty of temp space.

This happens in an Oracle Exadata 11g environment.

Thanks,

Thomas

You have a mystery & we have no clues.

I suspect that Problem Exists Between Keyboard And Chair.

CBO does a better job when it has accurate & current statistics; regardless of table type.

CBO does better job when it can use INDEX with accurate & current statistics.

Thomas Morgan wrote:

I recently dealt with an issue where a query would not complete when it was using a global temporary table.  Once I replaced the temporary with a permanent table, it worked and returned in a few seconds.

Has anybody experienced this behavior?

If yes, what causes it and can something be done about it?  Also, is there any documentation I can read to learn about this?

I checked and there was plenty of temp space.

This happens in an Oracle Exadata 11g environment.

Thanks,

Thomas

GTT's can work just fine, but the problem you will have is most likely with statistics. It's not until 12c that you can have "session specific statistics" https://oracle-base.com/articles/12c/session-private-statistics-for-global-temporary-tables-12cr1

Another thing I have seen in my days is developers creating GTT's and not using things like constraints or indexes, things you would typically always find on a permanent table. Constraints allow Oracle to create better cardinality estimates when processing a query (if you have a number column and put a check constraint that says it must be greater than 0 and someone asks where column = 0 Oracle knows the answer must return 0 rows).

Why your query "would not complete" would be very helpful to know, you would want to examine the execution plan for the query against the GTT (using EXPLAIN PLAN) and compare that to the one for the permanent table and see what's different.

If you have a GTT with a pretty steady state volume and distribution of data you could look at setting the statistics and locking them, or you could rely on dynamic sampling. A good article about that Ask Tom: On Dynamic Sampling

Cheers,

I recently dealt with an issue where a query would not complete when it was using a global temporary table.  Once I replaced the temporary with a permanent table, it worked and returned in a few seconds.

Sounds like someone else had an exclusive lock on the table.

Was anyone doing DDL or altering the table at that time?

You would have to check

a.  The statistics on the table

b.  The presence / absence of indexes

c.  The Execution Plan and expected cardinality (number of rows) in each step of the Execution Plan.

The difference should then be visible.

Hemant K Chitale

Thanks for the great suggestions.

We are using dynamic sampling.

We do not have any indexes on the temp table as the number of rows that are placed in it due to filtering is only in the thousands and we want it to be fully scanned when a session run the report.

FYI, the query is composed of four queries and a final select.  The first three with clauses execute fine (verified by using a select * right after each).  The last with query runs against a table that has almost 2 billion rows.  Furthermore this table incurs a lot of deletes.  My theory (although I do not know how to prove) is that it is this table that is causing the delay.

Thomas

Thomas Morgan wrote:

FYI, the query is composed of four queries and a final select.  The first three with clauses execute fine (verified by using a select * right after each).  The last with query runs against a table that has almost 2 billion rows.  Furthermore this table incurs a lot of deletes. 
My theory (although I do not know how to prove) is that it is this table that is causing the delay.


Thomas

post EXPLAIN PLAN for lasy query

ALTER SESSION SET SQL_TRACE=TRUE

the resultant trace file can be used to see where time is being spent

Did you observe a difference in execution plan between the query using a global temporary table and a standard heap table?

This would suggest difference in table statistics, are the statistics on the GTT representative of the data it contains? If you use the same GTT for different uses then id suspect the stats on it only represent one scenario, best to have one GTT for each use.

Do you have access to the diagnostics or tuning pack?

please post query and both versions of execution plan. Include any observations you have e.g. query one spent a lot of time waiting on event "db file sequential read". Trace file would be great, use tkprof to analyze the files.

My theory (although I do not know how to prove) is that it is this table that is causing the delay.

Please explain how that 'theory' is supported by the facts you posted:

Once I replaced the temporary with a permanent table, it worked and returned in a few seconds.

Any 'delay' such as you ask about is caused by waiting for 'something' to happen.

That 'something' might, indeed, be that 'other table activity' but that would have NOTHING to do with the GTT.

Or it might be as simple as a 'lock' on the GTT or other dependent object (including that 'other table').

You need to gather info about the state of things when the problem occurs. You will need to gather some info yourself manually since info such as lock activity isn't recorded.

"A lot of deletes" --- does it many many individual DELETE statements (That seems to be strange usage of a GTT)    OR  one single large DELETE ?

Hemant K Chitale