Temporary segment | Temporary segments are created by Oracle when a SQL statement needs a temporary database area to complete execution. When the statement finishes execution, the extents in the temporary segment are returned to the system for future use. |
这句话的意思是说临时表的段 the temporary segment 分配是在a temporary database area,这个临时数据库区域到底在哪呢,
SQL> select session_addr,blocks from v$sort_usage;
no rows selected
SQL> CREATE GLOBAL TEMPORARY TABLE temp_test(col1 number) ON COMMIT DELETE ROWS;
Table created.
SQL> insert into temp_test values(10);
1 row created.
SQL> select session_addr,blocks from v$sort_usage;
SESSION_ADDR BLOCKS
---------------- ----------
00000003E507B8F0 128
---------------- ----------
00000003E507B8F0 128
SQL> select sid from v$mystat where rownum=1;
SID
----------
151
----------
151
SQL> select saddr from v$session where sid=151;
SADDR
----------------
00000003E507B8F0
----------------
00000003E507B8F0
SQL> commit
2 /
2 /
Commit complete.
SQL> select session_addr,blocks from v$sort_usage;
no rows selected
看来这个 temporary database area就是临时表空间
看来这个 temporary database area就是临时表空间
另开一个session,
SQL> insert into temp_test values(10);
1 row created.
SQL> select dbms_rowid.rowid_object(rowid) data_object_id#,
2 dbms_rowid.rowid_relative_fno(rowid) rfile#,
3 dbms_rowid.rowid_block_number(rowid) block#,
4 dbms_rowid.rowid_row_number(rowid) row# from temp_test;
2 dbms_rowid.rowid_relative_fno(rowid) rfile#,
3 dbms_rowid.rowid_block_number(rowid) block#,
4 dbms_rowid.rowid_row_number(rowid) row# from temp_test;
DATA_OBJECT_ID# RFILE# BLOCK# ROW#
--------------- ---------- ---------- ----------
4194697 1 394 0
返回到第一个窗口
--------------- ---------- ---------- ----------
4194697 1 394 0
返回到第一个窗口
SQL> select dbms_rowid.rowid_object(rowid) data_object_id#,
2 dbms_rowid.rowid_relative_fno(rowid) rfile#,
3 dbms_rowid.rowid_block_number(rowid) block#,
4 dbms_rowid.rowid_row_number(rowid) row# from temp_test;
DATA_OBJECT_ID# RFILE# BLOCK# ROW#
--------------- ---------- ---------- ----------
4194569 1 266 0
--------------- ---------- ---------- ----------
4194569 1 266 0
分配了不同的extent,所以也会在sga中有不同的bh,这也是临时表的数据不会相互干扰的处理方式
Use the following guidelines to specify DEFAULT STORAGE:
Set INITIAL=NEXT.Since a process always writes data equal to
SORT_AREA_SIZE to a temporary segment, a good value for the extent
size is (n*s + b)
where: n is a positive integer
s is the value of SORT_AREA_SIZE initialization parameter
b is the value of DB_BLOCK_SIZE initialization parameter
Using this value optimizes temporary segment usage by allowing
sufficient space for a header block and multiple sort run data to be
stored in each extent.
Specify a PCTINCREASE of zero to ensure that all extents are of the
same size.
The MAXEXTENTS parameter only affects a temporary segment if the tablespace
is a PERMANENT tablespace.
Proper calculation of the Default Storage clause parameters will increase
performance and maximize use of storage.
Set INITIAL=NEXT.Since a process always writes data equal to
SORT_AREA_SIZE to a temporary segment, a good value for the extent
size is (n*s + b)
where: n is a positive integer
s is the value of SORT_AREA_SIZE initialization parameter
b is the value of DB_BLOCK_SIZE initialization parameter
Using this value optimizes temporary segment usage by allowing
sufficient space for a header block and multiple sort run data to be
stored in each extent.
Specify a PCTINCREASE of zero to ensure that all extents are of the
same size.
The MAXEXTENTS parameter only affects a temporary segment if the tablespace
is a PERMANENT tablespace.
Proper calculation of the Default Storage clause parameters will increase
performance and maximize use of storage.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16158219/viewspace-630677/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/16158219/viewspace-630677/