Oracle 11g 如何估计所需的TEMP 表空间?

2021-12-30 00:00:00 oracle11g oracle

我们对一些表进行初始批量加载(源和目标都是 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 |
-------------------------------------------------------------------------------------------

那么我现在必须如何阅读?

So how would I have to read this now?

推荐答案

这是个好问题.

首先，如果你创建如下主键

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 docs 建议创建一个专门的进程的临时表空间不会干扰任何其他操作.

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.

相关文章