Global Temporary Table Performance Issue

After a long  time am writing something and this write-up is based on live performance critical issue which i faced. During these critical issue we don’t have much time to ponder on; so need to be quick; so for sharing the same I have prepared on test case.

Teams complained that their query(more specific execution of package) behaved very badly; earlier it used to take 10-15 mins max but per current situation it is going on and on and on…  on the other hand they were also receiving temp segments alerts more specially query ends up with insufficient temp space. So here I started my investigation

so I checked which session is running that  query and what all relevant events it is waiting on; what plan it is using etc…which is normal check and also checked  the temp file; what a wonder  temp file size is 100+Gb which is nothing but equivalent to Data files so something is fishy here.

Kept a track of query it was changing its wait events more-likely( during one snap time it was waited on “In latch mem undo”; to the other snap waited on cache buffer chain and then started with direct path write temp; so from here it is clear that  query is using temp. But what was the statement; the statement was CREATED TABLE statement with few tables used in the inline view but at one go plan looks ok to me and if we try to run the same statement in our session then execution time comes out to be in .06 ms but for customer it was running past 45 mins.

I checked stats it was fine so where is the issue now why it is going for temp space; then during analysis  find out that nested loop is the culprit(between the large data) and still temp space ;why optimizer is going for nested loop; the same statement running fine in our session; so from here the curiousness started by looking at the wait statistics; it was clear that it has to do with temp space so checked the object type and one of the table came out as Global Temporary Table(GTT) so what is GTT all about and why simple statement failed with temp segment? Why optimizer is playing with the query.

Statement

create /*+PARALLEL(tmp_OLDC , 8 ) */      table tmp_OLDC as
        select tmp_OLDC1.*,
        OBV.OB2REL ,
        DN.NAMEE,
        DN.DIME,
        DN.PARER   DN_BER
        from  tmp_OLDC1 ,
                 NUA  ,
                 DIN
        where
                  NUA.N2OBJ = tmp_OLDC1.CID
            and NUA.N2NUM = DN.DID;

GTT is temporary table which is one of the feature of Oracle for temporary storing the data( and it is session specific; each session can have its own data  but for other session it wont be available); it Is helpful in many ways(there is lot of material for GTT on web so one can check easily).

Since GTT is useful so why it messed  around here; it was nothing but the stats gathered on the GTT tables; Since  storage is temporary so stats wont help optimizer  to estimate the cardinality and the factor(access, which join to use); optimizer will assign some roughly calculated stats and which may prove fatal for the query and here it was same.

If you try to gather stats as well in your session then  the data in the GTT table gets deleted as gathering of the stats fires two implicit kind of commit statement and when commit is fired the temp table will releases its own session data and  becomes an empty table especially for “ON COMMIT DELETE” clause of temp table.

For the DR; it was nested loop for millions of rows due  to wrong estimation  by optimizer plan was showing only 10-20 rows and hence query was running  too slow and for Refresh(it was only select statement with 4 tables using one inline view) it  was due to Merge Join where cardinality estimation was one and actual rows were in million and both  of the scenario eating all the temp space.

So I asked to delete the gathered stats on GTT tables and in parallel checked the plan and the plan was changed(Used Hash join which is more likely best) and the issue solved in a quick mode the whole package which was executed in 1 Hrs  finished in 1.5 mins;  fast no.

But again all above is not proved by me Isn’t it?

So let’s go ahead with the test plan; in test  plan I have created two GTT; two regular table all are having same columns and table structure and as far as GTT tables concerned treated one of them as on commit delete and one for preserving the rows(attached table definition). Each one is populated in other the script(GTT_TEST_TEMP.sql) with 900000 rows and in the same script I placed one select statement using all the tables; since I don’t want query to work on same plan hence I placed flush shared_pool at the start of the script.

<---------------------------Table Creation Script--------------------------------------------------------------------->

CREATE GLOBAL TEMPORARY TABLE my_temp_table (

  column1  NUMBER,

  column2  NUMBER

) ON COMMIT DELETE ROWS;

CREATE GLOBAL TEMPORARY TABLE my_temp_table_1 (

  column1  NUMBER,

  column2  NUMBER

) ON COMMIT Preserve ROWS;

CREATE TABLE my_table_1 (

  column1  NUMBER,

  column2  NUMBER

);

CREATE TABLE my_table_2 (

  column1  NUMBER,

  column2  NUMBER

);

<-----------------Script GTT_TMP_TEST_EXEC.sql----------------------------------------------------------------->

spool temp_test_gather_only_reg.txt

set timing on;

set feedback on;

alter system flush shared_pool;

truncate table my_table_1;

truncate table my_table_2;

truncate table my_temp_table_1;

truncate table my_temp_table;

---execute dbms_stats.gather_table_stats('PART','MY_TEMP_TABLE');

---execute dbms_stats.gather_table_stats('PART','MY_TEMP_TABLE_1');

---execute dbms_stats.gather_table_stats('PART','MY_TABLE_1');

---execute dbms_stats.gather_table_stats('PART','MY_TABLE_2');

---execute dbms_stats.set_table_stats('PART','MY_TEMP_TABLE',numrows=>900000);

----execute dbms_stats.set_table_stats('PART','MY_TEMP_TABLE_1',numrows=>900000);

----execute dbms_stats.set_table_stats('PART','MY_TABLE_1',numrows=>900000);

----execute dbms_stats.set_table_stats('PART','MY_TABLE_2',numrows=>900000);

----execute dbms_stats.delete_table_stats('PART','MY_TEMP_TABLE');

---execute dbms_stats.delete_table_stats('PART','MY_TEMP_TABLE_1');

-----execute dbms_stats.delete_table_stats('PART','MY_TABLE_1');

----execute dbms_stats.delete_table_stats('PART','MY_TABLE_2');

begin

for i  in 1..900000 loop

insert into my_table_1 values(i,i+1);

end loop;

commit;

end;

/

----execute dbms_stats.gather_table_stats('PART','MY_TABLE_1');

declare

begin

for i  in 1..900000 loop

insert into my_table_2 values(i,i+1);

end loop;

commit;

end;

/

----execute dbms_stats.gather_table_stats('PART','MY_TABLE_2');

declare

begin

for i  in 1..900000 loop

insert into my_temp_table values(i,i+1);

end loop;

end;

/

declare

begin

for i  in 1..900000 loop

insert into my_temp_table_1 values(i,i+1);

end loop;

end;

/

explain plan for

select

mytemp.column1,t2.col2 from my_temp_table mytemp,(select

temp.column2 col2 from my_temp_table_1

temp,  my_table_1 my1,my_table_2 my2  where my1.column1=my2.column1

and my1.column1=temp.column1  and my2.column1=temp.column1) t2  where

t2.col2=mytemp.column1;

select * from table(dbms_xplan.display);

select mytemp.column1,t2.col2 from my_temp_table mytemp,(select temp.column2 col2 from my_temp_table_1 temp,

 my_table_1 my1,my_table_2 my2

 where my1.column1=my2.column1

 and my1.column1=temp.column1

 and my2.column1=temp.column1) t2

 where t2.col2=mytemp.column1;

spool off

-------------------------------- 

Since the main crux here is the statistics with which optimizer plays and the same query which eats all the temp space; here in script I kept gather statement as well but I make them on and off as to cover every possibility. So what all questions can come to one’s mind  for this script which is having

a)      What will happen when we collect stats only on GTT and not for regular table?

b)      What if I delete stats of GTT?

c)       What if I collects stats only on Regular table and not on GTT?

d)      What if I collect stats on all tables; it might also happen that due to lack of one of the table stats optimizer generates wrong plan?

e)      What if I collect stats before insertion and after insertion on tables; does plan changes?

f)       What if I use dynamic sampling as dynamic sampling  by optimizer may come with some cardinality estimate to come up with correct plan?

g)      What if I set the table stats to fool optimizer?

So will consider one by one

First Scenario:

a) What will happen when we collect stats  only on GTT and not for regular table?

Here I will turn off the statistics of regular table(MY_TABLE_1, MY_TABLE_2) and collect stats on GTT(MY_TEMP_TABLE, MY_TEMP_TABLE_1)(attached GTT_TMP_TEST_EXEC.sql) and we will see how <plan goes for the select query; execution time and the sort space usage.

So now the sql is ready; first table will get truncated; shared_pool will be flushed; insertion will take place and then execution plan and execution of the query.

SQL> select table_name,last_analyzed from dba_tables where table_name in ('MY_TABLE_1','MY_TABLE_2');------No stats on regular table.

TABLE_NAME                     LAST_ANAL

------------------------------ ---------

MY_TABLE_2

MY_TABLE_1

Temp space is 18G which is more than enough for the select statement; so let’s execute the same and analyze it quickly.

Average elapsed time taken by the insertion took 55 sec for each table.

Plan comes out for the select query; and culprit is here < MERGE JOIN CARTESIAN > did u notice the rows for which optimizer worked on is coming out with 1( single cardinality)  though we have 9Lacs of rows in every table.

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 534856521

--------------------------------------------------------------------------

| Id  | Operation              | Name            | Rows  | Bytes | Cost  |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT       |                 |     1 |    65 |    10 |

|*  1 |  HASH JOIN             |                 |     1 |    65 |    10 |

|*  2 |   HASH JOIN            |                 |     1 |    52 |     7 |

|   3 |    MERGE JOIN CARTESIAN|                 |     1 |    26 |     4 |

|   4 |     TABLE ACCESS FULL  | MY_TEMP_TABLE   |     1 |    13 |     2 |

|   5 |     BUFFER SORT        |                 |    82 |  1066 |     2 |

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

|   6 |      TABLE ACCESS FULL | MY_TABLE_1      |    82 |  1066 |     2 |

|   7 |    TABLE ACCESS FULL   | MY_TEMP_TABLE_1 |     1 |    26 |     2 |

|   8 |   TABLE ACCESS FULL    | MY_TABLE_2      |    82 |  1066 |     2 |

--------------------------------------------------------------------------

And above query failed with ERROR at line 1:

ORA-01652: unable to extend temp segment by 128 in table space TEMP; so 18 GB utilized; for such small volume; not good and the output of the query  which is nothing but temp error came in

Elapsed: 00:05:57.20; so if temp space would have increased then it would have taken more  time to process the same.

Why optimizer chooses merge join Cartesian; here what I think is Oracle uses mathematical clause which is transitive clause where it  do like if x=y y=z so why not go for x=z and because of that it might went not to join all the tables.

So our first test case proved that collecting statistics on GTT is not good but do we have tested all scenario NO; so to prove my point I have taken in and out of the script.

Going ahead with other test case

B) What if I delete stats of GTT? And not collecting stats on regular table.

So for this I will delete the stats on GTT and will analyze the same  by running same script and here I will comment the statement stats gathering on GTT.

SQL> execute dbms_stats.delete_table_stats('PART','MY_TEMP_TABLE');

  execute dbms_stats.delete_table_stats('PART','MY_TEMP_TABLE_1');

   PL/SQL procedure successfully completed.

Here also elapsed timing of insertion is same 50+ sec and plan came out as

Plan hash value: 748710130

------------------------------------------------------------------------

| Id  | Operation            | Name            | Rows  | Bytes | Cost  |

------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |                 |   100 |  6500 |    41 |

|*  1 |  HASH JOIN           |                 |   100 |  6500 |    41 |

|*  2 |   HASH JOIN          |                 |   100 |  5200 |    23 |

|*  3 |    HASH JOIN         |                 |    82 |  2132 |     5 |

|   4 |     TABLE ACCESS FULL| MY_TABLE_1      |    82 |  1066 |     2 |

|   5 |     TABLE ACCESS FULL| MY_TABLE_2      |    82 |  1066 |     2 |

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

|   6 |    TABLE ACCESS FULL | MY_TEMP_TABLE_1 |  8168 |   207K|    17 |

|   7 |   TABLE ACCESS FULL  | MY_TEMP_TABLE   |  8168 |   103K|    17 |

------------------------------------------------------------------------

Elapsed: 00:00:00.48

   COLUMN1       COL2

---------- ----------

         2          2

         3          3

         4          4

         5          5

         6          6

And the rows are returned in mili-sec just after explain plan; so plan changes all the way here; optimizer did some random calculation based on tables and generated cardinality here it is 8168; so it proves deleting stats helped optimizer to generate some appropriate plan.

Going with 3^rd Case

c)       What if I collects stats only on Regular table and not on GTT?

It may come to our mind if suppose one of the table is not properly analyzed then it may turn up the whole plan and we have seen many such cases so do we have it here as well?

So collect the stats before insertion on regular table and not on GTT; here I will turn on the gathering stats statement on regular table but before insertion so we may land up in different cardinality.  here is the plan; again the plan came with merge join which eaten up all temp space; so from here we can comment the statistics is very  much necessary for the other table as there can be impact on whole plan. But we need to make sure where and when we need to use the same.

-------------------------------------------------------------------------

| Id  | Operation             | Name            | Rows  | Bytes | Cost  |

-------------------------------------------------------------------------

|   0 | SELECT STATEMENT      |                 |     1 |    65 |    40 |

|*  1 |  HASH JOIN            |                 |     1 |    65 |    40 |

|   2 |   MERGE JOIN CARTESIAN|                 |     1 |    39 |    22 |

|*  3 |    HASH JOIN          |                 |     1 |    26 |     5 |

|   4 |     TABLE ACCESS FULL | MY_TABLE_1      |     1 |    13 |     2 |

|   5 |     TABLE ACCESS FULL | MY_TABLE_2      |     1 |    13 |     2 |

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

|   6 |    BUFFER SORT        |                 |  8168 |   103K|    20 |

|   7 |     TABLE ACCESS FULL | MY_TEMP_TABLE   |  8168 |   103K|    17 |

|   8 |   TABLE ACCESS FULL   | MY_TEMP_TABLE_1 |  8168 |   207K|    17 |

-------------------------------------------------------------------------

d)      What if I collect stats on all tables; it might also happen that due to lack of one of the table stats optimizer generates wrong plan?

Here I’ll be turning on  the statistics for all the tables; but ill do before insertion; why so and why not after insertion? If you are through here with my article till nowJ you must have noticed that I mentioned gathering stats fires two implicit commits and this is not good for GTT as GTT data gets deleted<specially for the clause on commit >; so plan will be same as above; so it would again fail with temp space issue.

e)      What if I collect stats before insertion and after insertion on tables; does plan changes?

Here after insertion and collecting stats won’t help for GTT as this I already mentioned above; but what about regular table if we gather stats after insertion; logically query should work fine as at-least now optimizer will come up with good estimate; let us check and it worked fine; plan came as expected and query returned resultJ

------------------------------------------------------------------------

| Id  | Operation            | Name            | Rows  | Bytes | Cost  |

------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |                 |     1 |    49 |   756 |

|*  1 |  HASH JOIN           |                 |     1 |    49 |   756 |

|*  2 |   HASH JOIN          |                 |     1 |    36 |   738 |

|*  3 |    HASH JOIN         |                 |  8168 |   247K|   373 |

|   4 |     TABLE ACCESS FULL| MY_TEMP_TABLE_1 |  8168 |   207K|    17 |

|   5 |     TABLE ACCESS FULL| MY_TABLE_1      |   900K|  4394K|   284 |

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------

|   6 |    TABLE ACCESS FULL | MY_TABLE_2      |   900K|  4394K|   284 |

|   7 |   TABLE ACCESS FULL  | MY_TEMP_TABLE   |  8168 |   103K|    17 |

------------------------------------------------------------------------

Elapsed: 00:00:00.76

   COLUMN1       COL2

---------- ----------

         2          2

         3          3

         4          4

         5          5

         6          6

         7          7

But if I collect stats on GTT before insertion and on regular table after insertion then?

Then again this scenario will end up with temp space issue and merge Cartesian will be culprit; let’s check;  see plan for regular table plan ends up with right cardinality but for GTT it messed again and I again faced the error  temp table-space error.

--------------------------------------------------------------------------

| Id  | Operation              | Name            | Rows  | Bytes | Cost  |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT       |                 |     1 |    49 |   575 |

|*  1 |  HASH JOIN             |                 |     1 |    49 |   575 |

|*  2 |   HASH JOIN            |                 |     1 |    44 |   289 |

|   3 |    MERGE JOIN CARTESIAN|                 |     1 |    18 |   286 |

|   4 |     TABLE ACCESS FULL  | MY_TEMP_TABLE   |     1 |    13 |     2 |

|   5 |     BUFFER SORT        |                 |   900K|  4394K|   284 |

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

|   6 |      TABLE ACCESS FULL | MY_TABLE_1      |   900K|  4394K|   284 |

|   7 |    TABLE ACCESS FULL   | MY_TEMP_TABLE_1 |     1 |    26 |     2 |

|   8 |   TABLE ACCESS FULL    | MY_TABLE_2      |   900K|  4394K|   284 |

--------------------------------------------------------------------------

f)       What if I use dynamic sampling as dynamic sampling  by optimizer may come with some cardinality estimate to come up with correct plan?

Dynamic sampling is another option which can strike our mind but does that work here as well? Just to give you brief dynamic sampling is to improve the optimizer's ability to generate good execution plans. During the compilation of a SQL statement, the optimizer decides whether to use DS or not by considering whether the available statistics are sufficient to generate a good execution plan. If the available statistics are not enough, dynamic sampling will be used. It is typically used to compensate for missing or insufficient statistics that would otherwise lead to a very bad plan.

Dynamic sampling can take value from 0-10(meaning of 0-10 is available on net so you may refer web); it is init parameter(optimizer_dynamic_sampling) can be set at session level  and also we can use it as hint(/*+dynamic sampling(t,2)*/).

Dynamic sampling works only when the objects are unanalyzed and this can be proved by seeing the explain plan.

For this let us create one regular table and try to see if sampling hint works with analyzed table or not

SQL> create table  test_sampling ( x number);

Table created.

Elapsed: 00:00:00.85

SQL> insert into test_sampling values(10);

1 row created.

Elapsed: 00:00:00.01

SQL> commit;

Commit complete.

Elapsed: 00:00:00.00

SQL> exec dbms_stats.gather_table_stats('PART','TEST_SAMPLING');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.30

SQL> EXPLAIN PLAN FOR SELECT /*+DYNAMIC_SAMPLING(T1,2)*/ COUNT(*) FROM TEST_SAMPLING T1;

Explained.

Elapsed: 00:00:00.02

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 2411379599

------------------------------------------------------------

| Id  | Operation          | Name          | Rows  | Cost  |

------------------------------------------------------------

|   0 | SELECT STATEMENT   |               |     1 |     2 |

|   1 |  SORT AGGREGATE    |               |     1 |       |

|   2 |   TABLE ACCESS FULL| TEST_SAMPLING |     1 |     2 |

------------------------------------------------------------

Note

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

-----

   - cpu costing is off (consider enabling it)

No sing of sampling; let me delete the stats and try again.

SQL> exec dbms_stats.delete_table_stats('PART','TEST_SAMPLING');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.11

SQL>  EXPLAIN PLAN FOR SELECT /*+DYNAMIC_SAMPLING(T1,2)*/ COUNT(*) FROM TEST_SAMPLING T1;

Explained.

Elapsed: 00:00:00.00

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 2411379599

------------------------------------------------------------

| Id  | Operation          | Name          | Rows  | Cost  |

------------------------------------------------------------

|   0 | SELECT STATEMENT   |               |     1 |     2 |

|   1 |  SORT AGGREGATE    |               |     1 |       |

|   2 |   TABLE ACCESS FULL| TEST_SAMPLING |     1 |     2 |

------------------------------------------------------------

Note

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

-----

   - cpu costing is off (consider enabling it)

   - dynamic sampling used for this statement

Did you notice; dynamic sampling is usedJ;

So back to our script I will delete the stats of all the tables and will try to put the hint using dynamic sampling; you can set it as session level parameter as well; but here I used hint and the select query I changed in script as

select/*+dynamic_sampling(mytemp,2) dynamic_sampling(t2,2)*/

mytemp.column1,t2.col2 from my_temp_table mytemp,(select

/*+dynamic_sampling(temp,2)dynamic_sampling(my1,2) dynamic_sampling(my2,2)*/ temp.column2 col2 from my_temp_table_1

temp,  my_table_1 my1,my_table_2 my2  where my1.column1=my2.column1

and my1.column1=temp.column1  and my2.column1=temp.column1) t2  where

t2.col2=mytemp.column1;

and now execute the script and let’s see how it goes; no wonder it worked well dynamic sampling used and the query shown result as well; so it workedJ

Plan hash value: 2880531366

--------------------------------------------------------------------------------

| Id  | Operation            | Name            | Rows  | Bytes |TempSpc| Cost  |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |                 |     1 |    65 |       |  2723 |

|*  1 |  HASH JOIN           |                 |     1 |    65 |       |  2723 |

|*  2 |   HASH JOIN          |                 |     1 |    52 |    27M|  2437 |

|*  3 |    HASH JOIN         |                 |   756K|    18M|    18M|  1220 |

|   4 |     TABLE ACCESS FULL| MY_TABLE_2      |   756K|  9610K|       |   284 |

|   5 |     TABLE ACCESS FULL| MY_TABLE_1      |   956K|    11M|       |   284 |

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

|   6 |    TABLE ACCESS FULL | MY_TEMP_TABLE_1 |   859K|    21M|       |   283 |

|   7 |   TABLE ACCESS FULL  | MY_TEMP_TABLE   |   937K|    11M|       |   283 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("TEMP"."COLUMN2"="MYTEMP"."COLUMN1")

   2 - access("MY1"."COLUMN1"="TEMP"."COLUMN1" AND

              "MY2"."COLUMN1"="TEMP"."COLUMN1")

   3 - access("MY1"."COLUMN1"="MY2"."COLUMN1")

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Note

-----

   - cpu costing is off (consider enabling it)

   - dynamic sampling used for this statement

27 rows selected.

Elapsed: 00:00:00.74

   COLUMN1       COL2

---------- ----------

         2          2

         3          3

         4          4

         5          5

         6          6

         7          7

         8          8

         9          9

Now to check the last option i.e.

g)      What if I set the table stats to fool optimizer?

Since I know the value of insertion in all the tables what I will do here as I will set the dummy stats for the all the table and try to run the scripts again for me logically it should work fine; I have incorporated the stats statement for all the table in the script for eg.

execute dbms_stats.set_table_stats('PART','MY_TEMP_TABLE',numrows=>900000); so let’s execute; and it worked well; as optimizer calculated right plan based on our stats and it uses same plan which is using for its own and look at the cardinality same as our stats.

--------------------------------------------------------------------------------

| Id  | Operation            | Name            | Rows  | Bytes |TempSpc| Cost  |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |                 |     1 |    65 |       |  1795 |

|*  1 |  HASH JOIN           |                 |     1 |    65 |       |  1795 |

|*  2 |   HASH JOIN          |                 |     1 |    52 |    32M|  1775 |

|*  3 |    HASH JOIN         |                 |   900K|    22M|    21M|   718 |

|   4 |     TABLE ACCESS FULL| MY_TABLE_1      |   900K|    11M|       |    17 |

|   5 |     TABLE ACCESS FULL| MY_TABLE_2      |   900K|    11M|       |    17 |

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

|   6 |    TABLE ACCESS FULL | MY_TEMP_TABLE_1 |   900K|    22M|       |    17 |

|   7 |   TABLE ACCESS FULL  | MY_TEMP_TABLE   |   900K|    11M|       |    17 |

--------------------------------------------------------------------------------

Elapsed: 00:00:00.43

   COLUMN1       COL2

---------- ----------

         2          2

         3          3

         4          4

         5          5

        

So I think I finished all the possibilities that can come during this situationJ; just to summarize; GTT is helpful in many ways but do use it on purpose; especially for the queries which involves GTT and also such weird behavior of temp space and irrelevant join then do take care of below info

a)      Do not gather stats on GTT.

b)      If possible you can use dynamic sampling; then make sure not to gather stats.

c)       If you somehow know how much volume can persists in GTT then do fool optimizer by setting the stats.

d)       Gather_Schema_stats doesn’t gather stats on GTT by default; if at all needed then you need to use gather_temp=true as a parameter in schema stats.

All above is as per my observation; so before going to any issue do check it properly.