Oracle 11g如何估计所需的TEMP表空间? [英] Oracle 11g how to estimate needed TEMP tablespace?

问题描述

我们对某些表进行了初始批量加载(源和目标均为Oracle 11g).该过程如下:1.截断，2.删除索引(PK和唯一索引)，3.批量插入，4.创建索引(同样是PK和唯一索引).现在我收到以下错误:

We do an initial bulk load of some tables (both, source and target are Oracle 11g). The process is as follows: 1. truncate, 2. drop indexes (the PK and a unique index), 3. bulk insert, 4. create indexes (again the PK and the unique index). Now I got the following error:

alter table TARGET_SCHEMA.MYBIGTABLE
add constraint PK_MYBIGTABLE primary key (MYBIGTABLE_PK)
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

因此，很显然，TEMP表空间对于创建PK来说太小了(仅供参考，该表有6列和约22亿条记录).所以我这样做了:

So obviously TEMP tablespace is to small for PK creation (FYI the table has 6 columns and about 2.2 billion records). So I did this:

explain plan for
select line_1,line_2,line_3,line_4,line_5,line_6,count(*) as cnt
from SOURCE_SCHEMA.MYBIGTABLE
group by line_1,line_2,line_3,line_4,line_5,line_6;

select * from table( dbms_xplan.display );
/*
-----------------------------------------------------------------------------------------------
| Id  | Operation          | Name             | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                  |  2274M|    63G|       |    16M  (2)| 00:05:06 |
|   1 |  HASH GROUP BY     |                  |  2274M|    63G|   102G|    16M  (2)| 00:05:06 |
|   2 |   TABLE ACCESS FULL| MYBIGTABLE       |  2274M|    63G|       |   744K  (7)| 00:00:14 |
-----------------------------------------------------------------------------------------------
*/

这是如何确定创建PK所需的TEMP表空间(在我的情况下为102 GB)吗?还是您会做出不同的估算?

Is this how to tell how much TEMP tablespace will be needed for PK creation (102 GB in my case)? Or would you make the estimate differently?

附加:PK仅存在于目标系统上.但是很公平，所以我在目标PK上运行您的查询:

Additional: The PK only exists on the target system. But fair point, so I run your query on target PK:

explain plan for
select MYBIGTABLE_PK 
from TARGET_SCHEMA.MYBIGTABLE
group by MYBIGTABLE_PK ;

-------------------------------------------------------------------------------------------
| Id  | Operation          | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                      |     1 |    13 |     3  (34)| 00:00:01 |
|   1 |  HASH GROUP BY     |                      |     1 |    13 |     3  (34)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| MYBIGTABLE           |     1 |    13 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

那我现在该怎么读?

推荐答案

这是一个很好的问题.

This is a good question.

首先，如果您创建以下主键

First, If you create the following primary key

alter table TARGET_SCHEMA.MYBIGTABLE 
     add constraint PK_MYBIGTABLE primary key (MYBIGTABLE_PK)

然后您应该查询

explain plan for 
     select PK_MYBIGTABLE 
     from SOURCE_SCHEMA.MYBIGTABLE 
     group by PK_MYBIGTABLE 

要获取估算值(请确保您收集统计信息exec dbms_stats.gather_table_stats('SOURCE_SCHEMA','MYBIGTABLE').

To get an estimate (make sure you gather stats exec dbms_stats.gather_table_stats('SOURCE_SCHEMA','MYBIGTABLE').

第二，您可以查询V$TEMPSEG_USAGE以查看在抛出之前消耗了多少临时块，并且可以查询v$session_longops以查看已完成的总过程中有多少.

Second , you can query V$TEMPSEG_USAGE to see how much temp blocks were consumed before you got thrown and v$session_longops to see how much of the total process you finished.

Oracle 文档建议创建专用的临时表空间，该进程不会干扰任何其他操作.

Oracle docs suggests creating a dedicated temp tablespace for the process to not disturb any other operations.

如果您找到更准确的解决方案，请发表修改.

Please post an edit if you find a more accurate solution.

这篇关于Oracle 11g如何估计所需的TEMP表空间?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！