Oracle truncate and insert (back up table data took too much time

We have large table (many columns ) Table1 but only small number of column need backup and do further processing
Here is the plan. We create another Table2 and only include few columns (essential data)

When we need backup whole table and do some clean work

TRUNCATE TABLE table2 (59 seconds)
INSERT /*+ PARALLEL */ INTO table2 SELECT col1, col2, col3 FROM tabl1 (229 seconds)
TRUNCATE TABLE table1 (182 seconds)

The table1 have about 50M rows. but run with above step , it took too much time.
I dont understand why TRUNCATE table took such long time (i thought may be only few seconds right as no data in it?). Can anyone explain it?
The reason we design in this way is, we have some operation need update whole table1 certain column with specific data, but this procedure take too much time. So we design a way like above.

we cannt drop table1 and need it always online (table1 can be accessed by many sessions) but we can truncate it data (because we believe truncate Table1 should be much quicker than UPDATE table1). We have a idle process which will process data from Table2 in the quiet time and INSERT it back to Table1 when system not busy.
Table2 is only accessed by one session so it dont have Lock. however we have no idea why truncate table2 even take such long time (table2 should be no data or very small mount of data) but it looks ORacle truncate both table1 and table2 with such long time.

I just did some test by adding statistics to Table2 before truncate by following:

BEGIN DBMS_STATS.GATHER_TABLE_STATS (ownname =>:user,tabname =>:orgTable, estimate_percent=>100, method_opt=>'FOR ALL COLUMNS SIZE AUTO', degree=>8, cascade=>true, no_invalidate=>FALSE); END;:user=testuser1:orgTable=Table2

With table stats regen, the truncate table only take 0.1 seconds. however above table stats regen took 88 seconds.

While truncate does not need to reformat data blocks and is therefore considered “fast”, it does need to (1) obtain an exclusive lock on the table (2) force a checkpoint and (3) deallocate all of its extents in the file header while also updating the data dictionary.

So:

#1a ensure there is no DML activity occurring in the table when you attempt the truncate,

#1b, check the wait interface (ASH data) to ensure you’re not being blocked on something protecting the dictionary or shared pool (row cache lock, library cache lock, etc.),

#2 ensure DBWR isn’t slammed during that time or the table being truncated has an excessive number of dirty buffers in the cache, or your database server is CPU starved (talk to your DBA – you’ll see local write wait for you process and DBWR slaves 100% on I/O),

#3 ensure there aren’t an excessive number of extents in the table.

SELECT extents FROM user_segments WHERE segment_name = 'TABLE1'

If the table is partitioned, make that SUM(extents). Now, if it reports many thousands of extents ( > 10,000 ) then that’s likely your problem. You want to use larger extent sizes so you end up with fewer of them. Ideally we want dozens or hundreds. Once it gets into the thousands it starts to have negative impacts. Tens of thousands really starts to hurt – not just truncates, but full table scans take a lot longer when there are these many extents. Talk to your DBA about how the allocation is defined for the tablespace (uniform extents vs. autoallocate/system) and move the table to a tablespace that will allocate larger (and hence fewer) extents.

For the insert, INSERT /+ PARALLEL/ INTO table2 is not a valid hint. Make sure you do not leave out the * delimiters in the hint, add the ENABLE_PARALLEL_DML hint (without which, your actual load operation will not be parallelized unless you’ve explicitly enabled parallel DML in the session) and also provide an actual DOP (degree of parallelism) for your PARALLEL hint – don’t use the PARALLEL hint by itself like that or it can overallocate PX slaves:

 INSERT /*+ ENABLE_PARALLEL_DML PARALLEL(16) */ INTO table2 SELECT ...

Make sure as well that you have no triggers or foreign keys on that table. In parallel DML mode, indexes are okay, but your best performance will be from a naked table with no indexes at all.

Ultimately every performance issue requires examining the wait data (v$session, v$active_session_history, etc.) to determine exactly what’s happening. In the world of Oracle we don’t have to guess – it will tell us exactly what’s going on if we know what to look for. But as that’s not something that can be done in a forum like this, the above are some ideas of things to check that can commonly lead to slow truncate and insert performance.

Filed under: Kiến thức lập trình - @ 15:53

Thẻ: oracle-database

Thiết kế website giá rẻ

Danh mục

Oracle truncate and insert (back up table data took too much time