create table进阶学习(二)_dba_free_temp_space_v$tempseg_usage_v$sort_segment

最新推荐文章于 2021-04-03 10:23:20 发布

clg10051

最新推荐文章于 2021-04-03 10:23:20 发布

阅读量252

点赞数

文章标签：数据库

在 http://space.itpub.net/9240380/viewspace-752388一文中,主要测试了全局临时表的语法及维护相关知识;
既然全局临时表数据不持续性存储在segment,哪么到底它存储在哪儿呢,经查阅官方手册,源文如下:
Allocation of Temporary Segments for Temporary Tables and Indexes

--oracle仅为当前会话的全局临时表自temporary tablespace分配segment
Oracle Database can also allocate temporary segments for temporary tables and their indexes. Temporary tables hold data that exists only
for the duration of a transaction or session. Each session accesses only the extents allocated for the session and cannot access extents allocated for other sessions.

Oracle Database allocates segments for a temporary table when the first INSERT into that table occurs. The insertion can occur explicitly or because of CREATE T
ABLE AS SELECT. The first INSERT into a temporary table allocates the segments for the table and its indexes, creates the root page for the indexes,
and allocates any LOB segments.

Segments for a temporary table are allocated in a temporary tablespace of the current user. Assume that the temporary tablespace assigned to
user1 is temp1 and the temporary tablespace assigned to user2 is temp2. In this case, user1 stores temporary data in the temp1 segments,
while user2 stores temporary data in the temp2 segments.

--测试创建全局临时表时临时表空间的占用情况
涉及到如下几个方面的内容:
1,临时表空间存储什么内容:
2,临时表空间的管理和维护

先说临时表空间存储什么内容

    --中间状态的排序结果,例如建索引
    Intermediate sort results

    --创建全局临时表和其上的索引,即我们要讨论的问题
    Temporary tables and temporary indexes

    --临时lob
    Temporary LOBs

     --临时b树
    Temporary B-trees

再来说临时表空间的管理和维护,oracle提供几个视图和字典

----v$sort_segment记录临时表空间排序段的分配与回收信息
You can view the allocation and deallocation of space in a temporary tablespace sort segment using the V$SORT_SEGMENT view.

--- v$tempseg_usage记录临时段中当前执行排序操作的相关信息
The V$TEMPSEG_USAGE view identifies the current sort users in those segments.

---如下简述了排序工作机制,排序一开始,则使用临时表空间的临时段,如果使用完毕,不会释放它,而是标记为free,便于下次重用;只有关闭库才会释放临时表空间的临时段空间
When a sort operation that uses temporary space completes, allocated extents in the sort segment are not deallocated; they are just

-----dba_temp_free_space记录总的分配和空闲的临时段信息
marked as free and available for reuse. The DBA_TEMP_FREE_SPACE view displays the total allocated and free space in each temporary tablespace.

--当然临时段可以收缩,请参阅官方手册
See "Viewing Space Usage for Temporary Tablespaces" for more information. You can manually shrink a locally managed temporary tablespace that
has a large amount of unused space. See "Shrinking a Locally Managed Temporary Tablespace" for details.

---如下开始测试:

---沿用上文全局临时表t_global_temp
SQL> insert into t_global_temp select level,level+2 from dual connect by level<3
e5;

299999 rows created.

SQL> select sid from v$mystat where rownum=1;

SID
----------
63

---开启另一会话查看

sysdba>select tablespace_name,segment_file from v$sort_segment;

TABLESPACE_NAME SEGMENT_FILE
-------------------------------------------------------------- ------------
TEMP 0

---v$tempseg_usage请参考:http://docs.oracle.com/cd/E11882_01/server.112/e25513/dynviews_3107.htm#i1421319
sysdba>select username,user from v$tempseg_usage;

USERNAME
------------------------------------------------------------
USER
------------------------------------------------------------
SCOTT
SYS

SCOTT
SYS

sysdba>select session_num,sql_id,tablespace from v$tempseg_usage;

SESSION_NUM SQL_ID
----------- --------------------------
TABLESPACE
--------------------------------------------------------------
13 44rj7z9m5z42y
TEMP

13 44rj7z9m5z42y
TEMP

sysdba>select sql_text from v$sql where sql_id='44rj7z9m5z42y';

SQL_TEXT
-----------------------------------------------------------------------------

insert into t_global_temp select level,level+2 from dual connect by level<3e5

---交替运行,如下列值在递增
--各列含义参阅:官方手册
sysdba>select segfile#,segblk#,extents,blocks from v$tempseg_usage;

SEGFILE#    SEGBLK#    EXTENTS     BLOCKS
---------- ---------- ---------- ----------
       201      70272          5        640
       201      70656          5        640

sysdba>/

SEGFILE#    SEGBLK#    EXTENTS     BLOCKS
---------- ---------- ---------- ----------
       201      70272         15       1920
       201      70656         19       2432

sysdba>/

SEGFILE#    SEGBLK#    EXTENTS     BLOCKS
---------- ---------- ---------- ----------
       201      70272         15       1920
       201      70656         19       2432

--如你退出操作临时排序段的会话,排序段会自动释放
sysdba>select segfile#,segblk#,extents,blocks from v$tempseg_usage;

no rows selected

--- v$sort_segment诊断排序段相当有用,请参考:http://docs.oracle.com/cd/E11882_01/server.112/e25513/dynviews_3041.htm#i1416861
sysdba>desc v$sort_segment;
Name Null? Type
----------------------------------------- -------- ------------------------

TABLESPACE_NAME                                    VARCHAR2(31)
SEGMENT_FILE                                       NUMBER
SEGMENT_BLOCK                                      NUMBER
EXTENT_SIZE                                        NUMBER
CURRENT_USERS                                      NUMBER --当前使用排序段用户数
TOTAL_EXTENTS                                      NUMBER --总排序段的区数
TOTAL_BLOCKS                                       NUMBER
USED_EXTENTS                                       NUMBER --已使用的排序段区数
USED_BLOCKS                                        NUMBER
FREE_EXTENTS                                       NUMBER ---未使用的排序段区数
FREE_BLOCKS                                        NUMBER
ADDED_EXTENTS                                      NUMBER --添加的排序段区数
EXTENT_HITS                                        NUMBER
FREED_EXTENTS                                      NUMBER --释放的排序段区数
FREE_REQUESTS                                      NUMBER --请求空闲排序段的个数
MAX_SIZE                                           NUMBER
MAX_BLOCKS                                         NUMBER
MAX_USED_SIZE                                      NUMBER
MAX_USED_BLOCKS                                    NUMBER
MAX_SORT_SIZE                                      NUMBER
MAX_SORT_BLOCKS                                    NUMBER
RELATIVE_FNO                                       NUMBER

sysdba>select tablespace_name from v$sort_segment;

TABLESPACE_NAME
--------------------------------------------------------------
TEMP

sysdba>select segment_file from v$sort_segment;

SEGMENT_FILE
------------
0

sysdba>select segment_block from v$sort_segment;

SEGMENT_BLOCK
-------------
0

sysdba>select extent_size from v$sort_segment;

EXTENT_SIZE
-----------
128

sysdba>select current_users from v$sort_segment;

CURRENT_USERS
-------------
2

sysdba>select total_extents from v$sort_segment;

TOTAL_EXTENTS
-------------
570

sysdba>select used_extents from v$sort_segment;

USED_EXTENTS
------------
34

sysdba>select free_extents from v$sort_segment;

FREE_EXTENTS
------------
536

sysdba>select freed_extents from v$sort_segment;

FREED_EXTENTS
-------------
0

sysdba>/

FREED_EXTENTS
-------------
0

sysdba>

---dba_temp_free_space请参阅
--它记录:DBA_TEMP_FREE_SPACE displays temporary space usage information at tablespace level.
http://docs.oracle.com/cd/E11882_01/server.112/e25513/statviews_5062.htm#I1023627

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-752400/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-752400/