Oracle Rant

Oracle has a table type of Global Temporay table that is unique to each session using it. You define the table as a Global Temporary table. What is useful about this type of table is that session A can insert rows into this table and session B can also insert rows into this table. Session A will never see Session B’s rows and vice versa. You can also define if the rows are preserved after a commit or deleted. Once a user disconnects they can never access those rows again. Temporary tables can work well in different types of environments where you want to code to use certain tables but not have the data mix with those of other sessions. The bad thing is that if you collect statistics, they may be wrong. For example, we found Oracle doing nested loops when it should have been doing a hash join.

What did we do to correct this? We deleted statistics on temporary tables and then locked the table stats. It is quite simple to do, and there are two commands that you can use.

exec dbms_stats.delete_table_stats('SCHEMA', 'TABLE_NAME');

exec dbms_stats.lock_table_stats('SCHEMA', 'TABLE_NAME);

That’s about all there is to it. Now when a query runs against this temporary table, Oracle will automatically do dynamic sampling. To change the level of the dynamic sampling, you can alter the parameter optimizer_dynamic_sampling. I believe the default is 2. For more information on the different levels of dynamic sampling, please visit the Oracle Performance Tuning Guide and the Oracle Database Reference.