Shrinking TEMP segments in 11GR2

Learnt recently from Oracle product documentation, its about segment space allocation for Global Temporary tables.


Like permanent tables, temporary tables are defined in the data dictionary. Temporary segments are allocated when data is first inserted. Until data is loaded in a session the table appears empty. Temporary segments are deallocated at the end of the transaction for transaction-specific temporary tables and at the end of the session for session-specific temporary tables.


rajesh@11GR2> select (select decode(extent_management,'LOCAL','*',' ') ||
  2                 decode(segment_space_management,'AUTO','a ','m ')
  3            from dba_tablespaces where tablespace_name = b.tablespace_name) || nvl(b.tablespace_name,
  4               nvl(a.tablespace_name,'UNKOWN')) name,
  5         kbytes_alloc kbytes,
  6         kbytes_alloc-nvl(kbytes_free,0) used,
  7         nvl(kbytes_free,0) free,
  8         ((kbytes_alloc-nvl(kbytes_free,0))/
  9                            kbytes_alloc)*100 pct_used,
 10         nvl(largest,0) largest,
 11         nvl(kbytes_max,kbytes_alloc) Max_Size,
 12         decode( kbytes_max, 0, 0, (kbytes_alloc/kbytes_max)*100) pct_max_used
 13  from ( select sum(bytes)/1024 Kbytes_free,
 14                max(bytes)/1024 largest,
 15                tablespace_name
 16         from  sys.dba_free_space
 17         group by tablespace_name ) a,
 18       ( select sum(bytes)/1024 Kbytes_alloc,
 19                sum(maxbytes)/1024 Kbytes_max,
 20                tablespace_name
 21         from sys.dba_data_files
 22        where 1 = 0
 23         group by tablespace_name
 24         union all
 25        select sum(bytes)/1024 Kbytes_alloc,
 26                sum(maxbytes)/1024 Kbytes_max,
 27                tablespace_name
 28         from sys.dba_temp_files
 29         group by tablespace_name  )b
 30  where a.tablespace_name (+) = b.tablespace_name;
NAME                               KBYTES       USED       FREE   PCT_USED    LARGEST   MAX_SIZE PCT_MAX_USED
------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- ------------
*m TEMP                             16376      16376          0        100          0   33554416   .048804306

Now, the space allocated in Temporary tablespace ( TEMP ) is 16376 KB before loading data's into Global Temporary tables.

rajesh@11GR2>
rajesh@11GR2> create global temporary table t(
  2     x ,
  3     y ,
  4     z )
  5  on commit preserve rows
  6  as
  7  select level,rpad('*',100,'*'),sysdate
  8  from dual
  9  connect by level <= 100000;
Table created.
Elapsed: 00:00:00.29
rajesh@11GR2>
rajesh@11GR2> select (select decode(extent_management,'LOCAL','*',' ') ||
  2                 decode(segment_space_management,'AUTO','a ','m ')
  3            from dba_tablespaces where tablespace_name = b.tablespace_name) || nvl(b.tablespace_name,
  4               nvl(a.tablespace_name,'UNKOWN')) name,
  5         kbytes_alloc kbytes,
  6         kbytes_alloc-nvl(kbytes_free,0) used,
  7         nvl(kbytes_free,0) free,
  8         ((kbytes_alloc-nvl(kbytes_free,0))/
  9                            kbytes_alloc)*100 pct_used,
 10         nvl(largest,0) largest,
 11         nvl(kbytes_max,kbytes_alloc) Max_Size,
 12         decode( kbytes_max, 0, 0, (kbytes_alloc/kbytes_max)*100) pct_max_used
 13  from ( select sum(bytes)/1024 Kbytes_free,
 14                max(bytes)/1024 largest,
 15                tablespace_name
 16         from  sys.dba_free_space
 17         group by tablespace_name ) a,
 18       ( select sum(bytes)/1024 Kbytes_alloc,
 19                sum(maxbytes)/1024 Kbytes_max,
 20                tablespace_name
 21         from sys.dba_data_files
 22        where 1 = 0
 23         group by tablespace_name
 24         union all
 25        select sum(bytes)/1024 Kbytes_alloc,
 26                sum(maxbytes)/1024 Kbytes_max,
 27                tablespace_name
 28         from sys.dba_temp_files
 29         group by tablespace_name  )b
 30  where a.tablespace_name (+) = b.tablespace_name;
NAME                               KBYTES       USED       FREE   PCT_USED    LARGEST   MAX_SIZE PCT_MAX_USED
------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- ------------
*m TEMP                             16376      16376          0        100          0   33554416   .048804306

Now, the space allocated in Temporary tablespace ( TEMP ) is still 16376 KB, so where does the segments for Temp tablespace is getting allocated and how to find that?

These information will be available in V$TEMPSEG_USAGE.

rajesh@11GR2> exec print_table(' select * from v$tempseg_usage ');
     USERNAME------------ RAJESH
     USER---------------- RAJESH
     SESSION_ADDR-------- 2F3580BC
     SESSION_NUM--------- 6
     SQLADDR------------- 2A0A883C
     SQLHASH------------- 1029988163
     SQL_ID-------------- 9babjv8yq8ru3
     TABLESPACE---------- TEMP
     CONTENTS------------ TEMPORARY
     SEGTYPE------------- DATA
     SEGFILE#------------ 201
     SEGBLK#------------- 128
     EXTENTS------------- 1
     BLOCKS-------------- 128
     SEGRFNO#------------ 1
     USERNAME------------ RAJESH
     USER---------------- RAJESH
     SESSION_ADDR-------- 2F3580BC
     SESSION_NUM--------- 6
     SQLADDR------------- 2A0A883C
     SQLHASH------------- 1029988163
     SQL_ID-------------- 9babjv8yq8ru3
     TABLESPACE---------- TEMP
     CONTENTS------------ TEMPORARY
     SEGTYPE------------- DATA
     SEGFILE#------------ 201
     SEGBLK#------------- 1920
     EXTENTS------------- 14
     BLOCKS-------------- 1792
     SEGRFNO#------------ 1

PL/SQL procedure successfully completed.
Elapsed: 00:00:00.54
rajesh@11GR2>

Temporary segments are allocated as data added into database and segments are deallocated at the end of the Transaction ( for Transaction specific temp tables ) or the end of session ( for session specific temp tables). But the Temporary data files added to that Temporary tablespace will never shrink by default untill you request.

rajesh@11GR2> insert /*+ append */ into t
  2  select level,rpad('*',100,'*'),sysdate
  3  from dual
  4  connect by level <= 100000;
100000 rows created.
Elapsed: 00:00:02.49
rajesh@11GR2> commit;
Commit complete.
Elapsed: 00:00:00.00
rajesh@11GR2> select count(*) from t;
  COUNT(*)
----------
    200000
Elapsed: 00:00:01.15
rajesh@11GR2> select (select decode(extent_management,'LOCAL','*',' ') ||
  2                 decode(segment_space_management,'AUTO','a ','m ')
  3            from dba_tablespaces where tablespace_name = b.tablespace_name) || nvl(b.tablespace_name,
  4               nvl(a.tablespace_name,'UNKOWN')) name,
  5         kbytes_alloc kbytes,
  6         kbytes_alloc-nvl(kbytes_free,0) used,
  7         nvl(kbytes_free,0) free,
  8         ((kbytes_alloc-nvl(kbytes_free,0))/
  9                            kbytes_alloc)*100 pct_used,
 10         nvl(largest,0) largest,
 11         nvl(kbytes_max,kbytes_alloc) Max_Size,
 12         decode( kbytes_max, 0, 0, (kbytes_alloc/kbytes_max)*100) pct_max_used
 13  from ( select sum(bytes)/1024 Kbytes_free,
 14                max(bytes)/1024 largest,
 15                tablespace_name
 16         from  sys.dba_free_space
 17         group by tablespace_name ) a,
 18       ( select sum(bytes)/1024 Kbytes_alloc,
 19                sum(maxbytes)/1024 Kbytes_max,
 20                tablespace_name
 21         from sys.dba_data_files
 22        where 1 = 0
 23         group by tablespace_name
 24         union all
 25        select sum(bytes)/1024 Kbytes_alloc,
 26                sum(maxbytes)/1024 Kbytes_max,
 27                tablespace_name
 28         from sys.dba_temp_files
 29         group by tablespace_name  )b
 30  where a.tablespace_name (+) = b.tablespace_name;
NAME                               KBYTES       USED       FREE   PCT_USED    LARGEST   MAX_SIZE PCT_MAX_USED
------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- ------------
*m TEMP                             29688      29688          0        100          0   33554416   .088477177
Elapsed: 00:00:00.04
rajesh@11GR2>
rajesh@11GR2> discon
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
rajesh@10GR2> connect rajesh/*****
Connected.
rajesh@11GR2> select count(*) from t;
  COUNT(*)
----------
         0
Elapsed: 00:00:00.00
rajesh@11GR2> select (select decode(extent_management,'LOCAL','*',' ') ||
  2                 decode(segment_space_management,'AUTO','a ','m ')
  3            from dba_tablespaces where tablespace_name = b.tablespace_name) || nvl(b.tablespace_name,
  4               nvl(a.tablespace_name,'UNKOWN')) name,
  5         kbytes_alloc kbytes,
  6         kbytes_alloc-nvl(kbytes_free,0) used,
  7         nvl(kbytes_free,0) free,
  8         ((kbytes_alloc-nvl(kbytes_free,0))/
  9                            kbytes_alloc)*100 pct_used,
 10         nvl(largest,0) largest,
 11         nvl(kbytes_max,kbytes_alloc) Max_Size,
 12         decode( kbytes_max, 0, 0, (kbytes_alloc/kbytes_max)*100) pct_max_used
 13  from ( select sum(bytes)/1024 Kbytes_free,
 14                max(bytes)/1024 largest,
 15                tablespace_name
 16         from  sys.dba_free_space
 17         group by tablespace_name ) a,
 18       ( select sum(bytes)/1024 Kbytes_alloc,
 19                sum(maxbytes)/1024 Kbytes_max,
 20                tablespace_name
 21         from sys.dba_data_files
 22        where 1 = 0
 23         group by tablespace_name
 24         union all
 25        select sum(bytes)/1024 Kbytes_alloc,
 26                sum(maxbytes)/1024 Kbytes_max,
 27                tablespace_name
 28         from sys.dba_temp_files
 29         group by tablespace_name  )b
 30  where a.tablespace_name (+) = b.tablespace_name;
NAME                               KBYTES       USED       FREE   PCT_USED    LARGEST   MAX_SIZE PCT_MAX_USED
------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- ------------
*m TEMP                             29688      29688          0        100          0   33554416   .088477177
Elapsed: 00:00:00.01
rajesh@11GR2>

Now even after records got removed for Global temporary tables, Temporary data files doesn't shrink by default untill you Alter tablespace to Shrink.

rajesh@11GR2> alter tablespace TEMP shrink space;
Tablespace altered.
Elapsed: 00:00:00.28
rajesh@11GR2> select (select decode(extent_management,'LOCAL','*',' ') ||
  2                 decode(segment_space_management,'AUTO','a ','m ')
  3            from dba_tablespaces where tablespace_name = b.tablespace_name) || nvl(b.tablespace_name,
  4               nvl(a.tablespace_name,'UNKOWN')) name,
  5         kbytes_alloc kbytes,
  6         kbytes_alloc-nvl(kbytes_free,0) used,
  7         nvl(kbytes_free,0) free,
  8         ((kbytes_alloc-nvl(kbytes_free,0))/
  9                            kbytes_alloc)*100 pct_used,
 10         nvl(largest,0) largest,
 11         nvl(kbytes_max,kbytes_alloc) Max_Size,
 12         decode( kbytes_max, 0, 0, (kbytes_alloc/kbytes_max)*100) pct_max_used
 13  from ( select sum(bytes)/1024 Kbytes_free,
 14                max(bytes)/1024 largest,
 15                tablespace_name
 16         from  sys.dba_free_space
 17         group by tablespace_name ) a,
 18       ( select sum(bytes)/1024 Kbytes_alloc,
 19                sum(maxbytes)/1024 Kbytes_max,
 20                tablespace_name
 21         from sys.dba_data_files
 22        where 1 = 0
 23         group by tablespace_name
 24         union all
 25        select sum(bytes)/1024 Kbytes_alloc,
 26                sum(maxbytes)/1024 Kbytes_max,
 27                tablespace_name
 28         from sys.dba_temp_files
 29         group by tablespace_name  )b
 30  where a.tablespace_name (+) = b.tablespace_name;
NAME                               KBYTES       USED       FREE   PCT_USED    LARGEST   MAX_SIZE PCT_MAX_USED
------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- ------------
*m TEMP                              2040       2040          0        100          0   33554416   .006079677
Elapsed: 00:00:00.04
rajesh@11GR2>

Grouping Sets

Learnt something newly today, its about specifying the set of groups that you want to create using Grouping sets.

rajesh@10GR2> select x,y,z
  2  from t1
  3  order by 1;

         X          Y          Z
---------- ---------- ----------
         1          2          3
         2          2         10
         3          5          5

Elapsed: 00:00:00.01
rajesh@10GR2>
rajesh@10GR2> select sum(x),sum(y),sum(z)
  2  from t1
  3  order by 1;

    SUM(X)     SUM(Y)     SUM(Z)
---------- ---------- ----------
         6          9         18

Elapsed: 00:00:00.01

So one way to get the aggregated data's along with all the actual values is.

rajesh@10GR2>
rajesh@10GR2> select x,y,z
  2  from t1
  3  union all
  4  select sum(x),sum(y),sum(z)
  5  from t1
  6  order by 1
  7  /

         X          Y          Z
---------- ---------- ----------
         1          2          3
         2          2         10
         3          5          5
         6          9         18

Elapsed: 00:00:00.29
rajesh@10GR2>

The other is using super-aggregate function using CUBE and filtering out the needed aggregates like below.

rajesh@10GR2> select sum(x),sum(y),sum(z)
  2  from t1
  3  group by cube(z,y,x)
  4  having grouping(x)+grouping(z)+grouping(y) = 0 or
  5         grouping(x)+grouping(z)+grouping(y) = 3
  6  order by 1
  7  /

    SUM(X)     SUM(Y)     SUM(Z)
---------- ---------- ----------
         1          2          3
         2          2         10
         3          5          5
         6          9         18

Elapsed: 00:00:00.00
rajesh@10GR2>

But the coolest way is to use Groping sets and specify only the needed set of groups.

rajesh@10GR2> select sum(x),sum(y),sum(z)
  2  from t1
  3  group by grouping sets( (x,y,z),() )
  4  order by 1
  5  /

    SUM(X)     SUM(Y)     SUM(Z)
---------- ---------- ----------
         1          2          3
         2          2         10
         3          5          5
         6          9         18

Elapsed: 00:00:00.01
rajesh@10GR2>

Now lets benchmark this three stuffs on high data volume sets.

rajesh@10GR2> insert into t1(x,y,z)
  2  select mod(level,10),level,level
  3  from dual
  4  connect by level < = 1000000
  5  /

1000000 rows created.

Elapsed: 00:00:03.39
rajesh@10GR2> commit;

Commit complete.

Elapsed: 00:00:00.23
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> exec dbms_stats.gather_table_stats(user,'T1');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.82
rajesh@10GR2>
rajesh@10GR2> set autotrace traceonly explain statistics;
rajesh@10GR2>
rajesh@10GR2> select x,y,z
  2  from t1
  3  union all
  4  select sum(x),sum(y),sum(z)
  5  from t1
  6  order by 1
  7  /

1000001 rows selected.

Elapsed: 00:00:28.71

Execution Plan
----------------------------------------------------------
Plan hash value: 1154063651

-------------------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |  1016K|    37M|       | 11365   (1)| 00:02:17 |
|   1 |  SORT ORDER BY       |      |  1016K|    37M|   101M|  1113  (52)| 00:00:14 |
|   2 |   UNION-ALL          |      |       |       |       |            |          |
|   3 |    TABLE ACCESS FULL | T1   |  1016K|    37M|       |   557   (3)| 00:00:07 |
|   4 |    SORT AGGREGATE    |      |     1 |    39 |       |            |          |
|   5 |     TABLE ACCESS FULL| T1   |  1016K|    37M|       |   557   (3)| 00:00:07 |
-------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       4966  consistent gets
          0  physical reads
          0  redo size
   15806765  bytes sent via SQL*Net to client
      73722  bytes received via SQL*Net from client
       6668  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
    1000001  rows processed

rajesh@10GR2>
rajesh@10GR2> select sum(x),sum(y),sum(z)
  2  from t1
  3  group by cube(z,y,x)
  4  having grouping(x)+grouping(z)+grouping(y) = 0 or
  5         grouping(x)+grouping(z)+grouping(y) = 3
  6  order by 1
  7  /

1000001 rows selected.

Elapsed: 00:01:46.45

Execution Plan
----------------------------------------------------------
Plan hash value: 3795960770

-------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |  1016K|    37M|   695  (23)| 00:00:09 |
|   1 |  SORT ORDER BY         |      |  1016K|    37M|   695  (23)| 00:00:09 |
|*  2 |   FILTER               |      |       |       |            |          |
|   3 |    SORT GROUP BY       |      |  1016K|    37M|   695  (23)| 00:00:09 |
|   4 |     GENERATE CUBE      |      |  1016K|    37M|   695  (23)| 00:00:09 |
|   5 |      SORT GROUP BY     |      |  1016K|    37M|   695  (23)| 00:00:09 |
|   6 |       TABLE ACCESS FULL| T1   |  1016K|    37M|   557   (3)| 00:00:07 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(GROUPING("X")+GROUPING("Z")+GROUPING("Y")=0 OR
              GROUPING("X")+GROUPING("Z")+GROUPING("Y")=3)


Statistics
----------------------------------------------------------
        315  recursive calls
         39  db block gets
       2483  consistent gets
      40208  physical reads
          0  redo size
   15806780  bytes sent via SQL*Net to client
      73722  bytes received via SQL*Net from client
       6668  SQL*Net roundtrips to/from client
          1  sorts (memory)
          2  sorts (disk)
    1000001  rows processed

rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> select sum(x),sum(y),sum(z)
  2  from t1
  3  group by grouping sets( (x,y,z),() )
  4  order by 1
  5  /

1000001 rows selected.

Elapsed: 00:00:40.87

Execution Plan
----------------------------------------------------------
Plan hash value: 2567933839

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |  1016K|    37M|   695  (23)| 00:00:09 |
|   1 |  SORT ORDER BY        |      |  1016K|    37M|   695  (23)| 00:00:09 |
|   2 |   SORT GROUP BY ROLLUP|      |  1016K|    37M|   695  (23)| 00:00:09 |
|   3 |    TABLE ACCESS FULL  | T1   |  1016K|    37M|   557   (3)| 00:00:07 |
------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
         43  recursive calls
          7  db block gets
       2483  consistent gets
       5430  physical reads
          0  redo size
   15806780  bytes sent via SQL*Net to client
      73722  bytes received via SQL*Net from client
       6668  SQL*Net roundtrips to/from client
          1  sorts (memory)
          1  sorts (disk)
    1000001  rows processed

See, Grouping set performs data aggregation upon selecting table 'T' and answers it smartly.