15 January, 2011

GLOBAL TEMPORARY TABLEs and GATHER_TABLE_STATS


UPDATE NOTE :  These tests, published in January 2011 are for version 11.2.0.1   There are differences in 12.1 and above, so do not rely on these for 12.1 behaviour.

If you use a Global Temporary Table in your application code, watch where and how you run GATHER_TABLE_STATS on it.
(I prefer to NOT have statistics on a GTT and to delete statistics if present ; so that Oracle can do Dynamic Sampling against the table when using it in a query).

If your GTT has been defined as ON COMMIT DELETE ROWS, the GATHER_TABLE_STATS call will result in rows being deleted. This is because the GATHER_TABLE_STATS issues an implicit commit.
If your GTT has been defined as ON COMMIT PRESERVE ROWS, the GATHER_TABLE_STATS will not delete rows in the table.

Here is a demonstration with comments :

SQL> REM Demo impact of Gather Stats on GTTs
SQL>
SQL> drop table MY_GTT_DELETE ;
drop table MY_GTT_DELETE
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> create global temporary table MY_GTT_DELETE (object_id number, object_name varchar2(30)) on commit DELETE rows;

Table created.

SQL> drop table MY_GTT_PRESERVE;
drop table MY_GTT_PRESERVE
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> create global temporary table MY_GTT_PRESERVE (object_id number, object_name varchar2(30)) on commit PRESERVE rows;

Table created.

SQL> drop table MY_GTT_PARALLEL;
drop table MY_GTT_PARALLEL
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> create global temporary table MY_GTT_PARALLEL (object_id number, object_name varchar2(30)) on commit PRESERVE rows;

Table created.

SQL>
SQL>
SQL> REM ###############################################################################################
SQL> REM -- first : a table that is defined as ON COMMIT DELETE ROWS
SQL> insert into MY_GTT_DELETE select object_id, object_name from dba_objects where object_id is not null;

76701 rows created.

SQL> -- verify that rows are present
SQL> select count(*) from MY_GTT_DELETE;

COUNT(*)
----------
76701

SQL> -- now gather stats
SQL> exec dbms_stats.gather_table_stats('','MY_GTT_DELETE');

PL/SQL procedure successfully completed.

SQL> -- look for the rows now
SQL> select count(*) from MY_GTT_DELETE;

COUNT(*)
----------
0

SQL> select num_rows from user_tables where table_name = 'MY_GTT_DELETE';

NUM_ROWS
----------
0

SQL> --- The table has no rows. Apparently the gather_table_stats issued a COMMIT
SQL> ---- This also means that any other DML that I had executed in my current session also got COMMITted !!
SQL>
SQL>
SQL> REM ###############################################################################################
SQL> REM -- second : a table that is defined as ON COMMIT PRESERVE ROWS
SQL> insert into MY_GTT_PRESERVE select object_id, object_name from dba_objects where object_id is not null;

76701 rows created.

SQL> -- verify that rows are present
SQL> select count(*) from MY_GTT_PRESERVE;

COUNT(*)
----------
76701

SQL> -- now gather stats
SQL> exec dbms_stats.gather_table_stats('','MY_GTT_PRESERVE');

PL/SQL procedure successfully completed.

SQL> -- look for the rows now
SQL> select count(*) from MY_GTT_PRESERVE;

COUNT(*)
----------
76701

SQL> select num_rows from user_tables where table_name = 'MY_GTT_PRESERVE';

NUM_ROWS
----------
76701

SQL> --- The table still has rows
SQL>
SQL> REM ###############################################################################################
SQL> REM Check to see if the rows persist across sessions
SQL> REM ---- they shouldn't !
SQL> disconnect
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> connect hemant/hemant
Connected.
SQL> select count(*) from MY_GTT_PRESERVE;

COUNT(*)
----------
0

SQL> select num_rows from user_tables where table_name = 'MY_GTT_PRESERVE';

NUM_ROWS
----------
76701

SQL> --- the table has no rows but the statistics reflect the earlier row count
SQL> exec dbms_stats.gather_table_stats('','MY_GTT_PRESERVE');

PL/SQL procedure successfully completed.

SQL> select num_rows from user_tables where table_name = 'MY_GTT_PRESERVE';

NUM_ROWS
----------
0

SQL>
SQL>
SQL> REM ###############################################################################################
SQL> REM -- last : a table where I use DEGREE in the GATHER_STATS
SQL> insert into MY_GTT_PARALLEL select object_id, object_name from dba_objects where object_id is not null;

76701 rows created.

SQL> -- verify that rows are present
SQL> select count(*) from MY_GTT_PARALLEL;

COUNT(*)
----------
76701

SQL> -- now gather stats
SQL> exec dbms_stats.gather_table_stats('','MY_GTT_PARALLEL',degree=>4,estimate_percent=>100);

PL/SQL procedure successfully completed.

SQL> -- look for the rows now
SQL> select count(*) from MY_GTT_PARALLEL;

COUNT(*)
----------
76701

SQL> --- The table still has rows
SQL> select num_rows from user_tables where table_name = 'MY_GTT_PARALLEL';

NUM_ROWS
----------
76701

SQL>
SQL> REM ###############################################################################################
SQL> REM Can anyone tell my why the 3rd DROP TABLE fails here ?
SQL> REM -- note that I have not disconnected and reconnected
SQL> DROP TABLE MY_GTT_DELETE;

Table dropped.

SQL> DROP TABLE MY_GTT_PRESERVE;

Table dropped.

SQL> DROP TABLE MY_GTT_PARALLEL;
DROP TABLE MY_GTT_PARALLEL
*
ERROR at line 1:
ORA-14452: attempt to create, alter or drop an index on temporary table already
in use


SQL>


Thus, the GATHER_TABLE_STATS call on MY_GTT_DELETE actually deleted rows.
.
.
.

11 comments:

Anonymous said...

Hi Sir,

Thanks for the article.I tried the same on 11gR2 and was not able to drop the table "MY_GTT_PRESERVE" even.

Thought i am not very sure why we can''t drop the table, but as per my observation "Row Exclusive (mode 3)" is held by the session permanently and when drop table is fired session acquires two "TO - Temporary Table Object Enqueue" lock with "Row Exclusive (mode 3) is held which is not the case with "on commit delete rows"


13:23:56 SYS@MATRIX> @lock_details

Username SID Term Table Name COMMAND Lock Held Lock Requested ID1 - ID2 Lock Type
---------- ----- ------ ------------------------------ ------------------------- -------------------- -------------------- ------------------ -----------------------------
ANAND 134 ananpr None BACKGROUND Share NONE 100-0 AE - ????
ak-lap

None BACKGROUND Row Exclusive NONE 65921-1 TO - Temporary Table Object Enqueue
None BACKGROUND Row Exclusive NONE 75818-1 TO - Temporary Table Object Enqueue


Any global temporary table with "on commit preserve rows" has to be first truncated and then only drop will succeed.Seems like some kind of binding is there with the session.

Hemant K Chitale said...

Anand,
My results were on 11.2.0.1 on 32-bit Linux.
Hemant K Chitale

Anonymous said...

Hi Sir,

My results is from 11.2.0.1 on 32-bit Windows.

13:19:48 ANAND@MATRIX> select num_rows from user_tables where table_name = 'MY_GTT_PRESERVE';

NUM_ROWS
----------
72747

Elapsed: 00:00:00.03
13:19:50 ANAND@MATRIX>
13:23:20 ANAND@MATRIX>
13:23:21 ANAND@MATRIX> drop table MY_GTT_PRESERVE;
drop table MY_GTT_PRESERVE
*
ERROR at line 1:
ORA-14452: attempt to create, alter or drop an index on temporary table already in use


Elapsed: 00:00:00.06
13:23:50 ANAND@MATRIX> truncate table MY_GTT_PRESERVE;

Table truncated.

Elapsed: 00:00:00.01
13:24:23 ANAND@MATRIX> drop table MY_GTT_PRESERVE;

Table dropped.

Elapsed: 00:00:00.03
13:25:03 ANAND@MATRIX>

Hemant K Chitale said...

Notice that I had DISCONNECTed and re-CONNECTed.
That is why I was able to drop the first two tables. I had created a new session. The table contents are "gone" when I disconnect.

(dang ! I have given away the answer to my question posed at the end !).

Hemant K Chitale

Hemant K Chitale said...

Pavan,
The statement "Thus, the GATHER_TABLE_STATS call on MY_GTT_DELETE actually deleted rows." is relevant to the MY_GTT_DELETE table.
(The statement is part of the blog text, not the SQL statements, REMs and outputs).

What is relevant to the MY_GTT_PARALLEL table is the question about why the DROP TABLE failed.

Hemant

Hemant K Chitale said...

Pavan,

Normally, per the documentation, a GTT must be TRUNCATEd before it can be dropped.

However, also note that the data in a GTT persists only for the duration of the session. Therefore, if the session ends (e.g. when I disconnect) even without TRUNCATing the table, I can drop it at the next session.

During my tests I did a disconnect and reconnect when testing MY_GTT_PRESERVE. Therefore, the previous session that had populated the first two tables had ended. That is why I could drop the first two tables.
However, after I populated MY_GTT_PARALEL, I continued operating in the same session. This prevented me from dropping the table.

Hemant

Anonymous said...

Hi Hemant,

We are facing one strange issue.
We have procedures for reporting purpose that use temporary tables. Every time we delete data from
procedure at start of executing, populate new data into temporary tables and use ref cursors to
return data.
Sometimes procedures takes very long time to executes (more than 30 min) even for small result, if
we drop temporary table and recreate tables, procedures executes very fast but after few days again
performance issue come.
What could be reason that after recreating temporary tables performance increases?


Regards,
Vrushali Bandiwadekar

Hemant K Chitale said...

Vrushali,
Without looking at the DDL, the code, volume of data, statistics and wait events around this, I can't say what could be the cause.
Is the GTT defined as ON COMMIT PRESERVE ROWS or DELETE ROWS ?
Do you issue TRUNCATE statements ?
Do you disconnect the session after retrieving data from the GTT ?

(I presume that when you say "temporary table", you do mean a GTT. Most uses of "temporary table" are what Oracle designates as *Permanent* tables).

Hemant K Chitale

Vrishu said...

Hi Hemant,

Thanks for your reply.
I noticed it today. Sorry for the delay, yes it is a GTT on commit delete rows.
The volume of data can be maximum 50,000 rows and never more than that.
There is no Truncate table command applied, the stored proc uses only delete+insert+retrieve data using GTT.
And this issue is intermittent.

Hemant K Chitale said...

Vrishu,

It could simply be that execution plans are changing. Your procedure seems to consist of multiple SQL calls. You'd have to determine which SQL calls are taking longer and check if their execution plans are changing.

Hemant

Unknown said...

Hi

Even we are facing similar problem. Once we drop and recreate the gtt , the speed is good and report comes in few min. but after somedays again the same issue persists and the report comes to stand still.

Regards
Leena Gala