临时表空间实验笔记

实验数据库版本：

11.2.0.4

实验内容：

1、在临时表空间中新建两种类型的临时表，做相应操作；

2、对两种类型的临时表以及普通表进行增删操作，观察性能消耗；

实验结果：

三种类型的表的增改操作，只在redo的产生上有差异；

新增的情况下，事务级临时表 < 会话级的临时表 <  普通标；

使用delete删除的情况下，事务级临时表 < 普通表 < 会话级临时表；【按理临时表产生的redo应该小于普通表的】

实验结论：

作为中间表使用时，事务级的临时表比较适用，不用担心受其他线程干扰，也不用人为释放空间，而且产生的redo相对较少；

实验内容：

一、创建临时表空间

CREATE TEMPORARY TABLESPACE TMP TEMPFILE '/u01/oracle/app/oracle/oradata/orcl/tmp01.dbf' SIZE 8G AUTOEXTEND OFF;

二、设置默认表空间

alter database default temporary tablespace TMP;

三、创建临时表（会话级）

SQL> CREATE GLOBAL TEMPORARY TABLE TMP_TEST ON COMMIT PRESERVE ROWS AS SELECT * FROM emp where 1=0;

Table created.

SQL> select count(0) from emp;

  COUNT(0)

----------

    14

SQL> select count(0) from tmp_test;

  COUNT(0)

----------

    0

SQL> insert into tmp_test select * from emp;

14 rows created.

SQL> select count(0) from tmp_test;

  COUNT(0)

----------

    14

SQL> commit;

Commit complete.

进行事务提交后不清空临时表数据；

SQL> select count(0) from tmp_test;

  COUNT(0)

----------

    14

四、创建临时表（事务级）

SQL> CREATE GLOBAL TEMPORARY TABLE TMP_TEST1 ON COMMIT DELETE ROWS AS SELECT * FROM emp;

Table created.

SQL> select count(0) from TMP_TEST1;

  COUNT(0)

----------

     0

SQL> insert into TMP_TEST1 select * from emp;

14 rows created.

SQL> select count(0) from TMP_TEST1;

  COUNT(0)

----------

    14

SQL> commit;

Commit complete.

进行事务提交后，临时表数据被清除

SQL> select count(0) from TMP_TEST1;

  COUNT(0)

----------

     0

五、为临时表创建索引，需另起会话创建，否则报以下错误

SQL> create index INDEX_TMP7 on scott.TMP_TEST (DEPTNO,ENAME);

create index INDEX_TMP7 on scott.TMP_TEST (DEPTNO,ENAME)

                                 *

ERROR at line 1:

ORA-14452: attempt to create, alter or drop an index on temporary table already in use

六、临时表与普通表性能对比

1.1 普通表新增数据

SQL>  create table scott.emp_1 tablespace test as select * from scott.emp where 1=0;

Table created.

SQL> insert into scott.emp_1 select * from scott.emp;

14 rows created.

Execution Plan

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

Plan hash value: 3956160932

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

| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | INSERT STATEMENT     |     |    14 |   532 |     3   (0)| 00:00:01 |

|   1 |  LOAD TABLE CONVENTIONAL | EMP_1 |     |     |          |      |

|   2 |   TABLE ACCESS FULL     | EMP     |    14 |   532 |     3   (0)| 00:00:01 |

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

Statistics

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

    167  recursive calls

     55  db block gets

    279  consistent gets

      0  physical reads

        7008  redo size

    838  bytes sent via SQL*Net to client

    803  bytes received via SQL*Net from client

      3  SQL*Net roundtrips to/from client

     13  sorts (memory)

      0  sorts (disk)

     14  rows processed

1.2 临时表新增数据

1.2.1 会话级临时表

SQL> insert into scott.TMP_TEST select * from scott.emp;

14 rows created.

Execution Plan

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

Plan hash value: 3956160932

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

| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | INSERT STATEMENT     |        |     14 |    532 |      3   (0)| 00:00:01 |

|   1 |  LOAD TABLE CONVENTIONAL | TMP_TEST |        |        |         |        |

|   2 |   TABLE ACCESS FULL     | EMP        |     14 |    532 |      3   (0)| 00:00:01 |

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

Statistics

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

      8  recursive calls

     83  db block gets

      7  consistent gets

      4  physical reads

        2364  redo size

    838  bytes sent via SQL*Net to client

    806  bytes received via SQL*Net from client

      3  SQL*Net roundtrips to/from client

      1  sorts (memory)

      0  sorts (disk)

     14  rows processed

1.2.2 事务级临时表

SQL> insert into scott.TMP_TEST1 select * from scott.emp;

14 rows created.

Execution Plan

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

Plan hash value: 3956160932

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

| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | INSERT STATEMENT     |         |      14 |     532 |       3   (0)| 00:00:01 |

|   1 |  LOAD TABLE CONVENTIONAL | TMP_TEST1 |         |         |          |         |

|   2 |   TABLE ACCESS FULL     | EMP         |      14 |     532 |       3   (0)| 00:00:01 |

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

Statistics

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

      1  recursive calls

      7  db block gets

      7  consistent gets

      0  physical reads

     208  redo size

    838  bytes sent via SQL*Net to client

    807  bytes received via SQL*Net from client

      3  SQL*Net roundtrips to/from client

      1  sorts (memory)

      0  sorts (disk)

     14  rows processed

2.1 普通表删除数据

SQL> delete from scott.emp_1;

14 rows deleted.

Execution Plan

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

Plan hash value: 1189782787

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

| Id  | Operation       | Name  | Rows  | Cost (%CPU)| Time       |

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

|   0 | DELETE STATEMENT   |       |     1 |     3   (0)| 00:00:01 |

|   1 |  DELETE        | EMP_1 |       |        |       |

|   2 |   TABLE ACCESS FULL| EMP_1 |     1 |     3   (0)| 00:00:01 |

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

Note

-----

   - dynamic sampling used for this statement (level=2)

Statistics

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

      4  recursive calls

     17  db block gets

     15  consistent gets

      0  physical reads

        3992  redo size

    839  bytes sent via SQL*Net to client

    779  bytes received via SQL*Net from client

      3  SQL*Net roundtrips to/from client

      1  sorts (memory)

      0  sorts (disk)

     14  rows processed

2.2 临时表删除数据

2.2.1 会话级临时表

SQL> delete from  scott.TMP_TEST;

14 rows deleted.

Execution Plan

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

Plan hash value: 3232491841

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

| Id  | Operation       | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | DELETE STATEMENT   |          |     1 |    65 |     2    (0)| 00:00:01 |

|   1 |  DELETE        | TMP_TEST |       |       |        |          |

|   2 |   TABLE ACCESS FULL| TMP_TEST |     1 |    65 |     2    (0)| 00:00:01 |

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

Note

-----

   - dynamic sampling used for this statement (level=2)

Statistics

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

     78  recursive calls

    182  db block gets

    128  consistent gets

      0  physical reads

       16108  redo size

    839  bytes sent via SQL*Net to client

    783  bytes received via SQL*Net from client

      3  SQL*Net roundtrips to/from client

      8  sorts (memory)

      0  sorts (disk)

     14  rows processed

2.2.2 事务级临时表

SQL> delete from scott.TMP_TEST1;

14 rows deleted.

Execution Plan

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

Plan hash value: 1557634753

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

| Id  | Operation       | Name      | Rows  | Cost (%CPU)| Time     |

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

|   0 | DELETE STATEMENT   |           |     1 |     2     (0)| 00:00:01 |

|   1 |  DELETE        | TMP_TEST1 |       |        |           |

|   2 |   TABLE ACCESS FULL| TMP_TEST1 |     1 |     2     (0)| 00:00:01 |

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

Note

-----

   - dynamic sampling used for this statement (level=2)

Statistics

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

      5  recursive calls

     14  db block gets

      7  consistent gets

      0  physical reads

        3240  redo size

    840  bytes sent via SQL*Net to client

    783  bytes received via SQL*Net from client

      3  SQL*Net roundtrips to/from client

      1  sorts (memory)

      0  sorts (disk)

     14  rows processed