Search My Oracle Blog

Custom Search

15 January, 2011

GLOBAL TEMPORARY TABLEs and GATHER_TABLE_STATS

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.
.
.
.

9 comments:

Anand 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

Anand 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

Pavan Kumar said...

Hi Hemanth,

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

How the above statement is relevant to the MY_GTT_PARALLEL table drop issue.

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

Pavan Kumar said...

Hi hemanth,
Reason for MY_GTT_PARALLEL DROP TABLE failed, As per my understanding when you carry out the drop command on GTT_PARALLEL, then previous stats gathering might be continuing and it's returned/thrown the error.If we check the lock type then it might be on hold with type TO.

The above might be the reason and as looking into the basic functionality of GTT can be preserved for the whole session or just for the current transaction.

I doubt it.
Let me know your comments

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

Pavan Kumar said...

Hi Hemanth,

Thanks for update.. :)
I did not concentrated on the session disconnect.

Lession learnt.
Thanks dude.. !!

I hope some day.. I can meet you in Hyderabad.. !!

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com