Notes on truncate table

Notes on truncate table

--- The following is a summary of the quotations of itpub's predecessors.

 

After truncate table, oracle recycles the index of the table and its table to the initial size, that is, the size of the initial allocated segments.
Truncate and drop are both ddl statements, and the operation takes effect immediately. The original data is not stored in rollback segment and cannot be rolled back.

Truncate table execution is slow for the following reasons:
First, you must understand that the truncate table is a DDL operation and the HWM is reset.
1. Check whether the DML operation has locked some records.
2. segment header Competition

The slow truncate table may be related to the number of extent tables.
For example, if the size of a table is 100 M, each 10 M has 10 extent
The size of another table is 50 M, with each 8 K and 6400 extent
The truncate of the second table is much slower than that of the first table.

Generally, because there are too many extent, the extent is recycled during truncate.
This is also one of the advantages of local management over dictionary management.
If you are worried about the same problem next time
You can consider using
The truncate table test reuse STORAGE statement can prevent hung from recycling extent.

We need to use the method of recycling multiple times.
For example, if a M Table is recycled for 20 M each time, it may feel better.
Truncate table t4 reuse STORAGE;
Alter table test_tun deallocate unused keep 80 M;
Alter table test_tun deallocate unused keep 60 M;
Alter table test_tun deallocate unused keep 40 M;
Alter table test_tun deallocate unused keep 20 M;
Truncate table test_tun drop storage;

If the truncate table is very slow, you can diagnose it as follows:
1. query the wait events of the corresponding session in the v $ session_wait view.
2. You can use oradebug hanganalyze to analyze the cause of system suspension.
3. For the purpose of testing, you can create an event of 10046 level 8.

 

###################
### BUG lists
###################

When truncate, does dbwr occupy high cpu? Try workround (alter system flush buffer_cache; and then truncate) in the following document. If it takes effect, you can upgrade it to 10.2.0.4.3.

Isn't this bug Hard to say? If the log size is insufficient, it will cause the log to switch to hang, causing dbwr waiting, and there will be a lot of free buffer busy waiting, while truncate will do the checkpoint again, so at this time, the front-end process also needs to wait for dbwr, resulting in extended wait of enqueue RO

---------------------------------------------
Bug 8544896 Waits for "enq: RO-fast object reuse" with high DBWR CPU
This note gives a brief overview of bug 8544896.
The content was last updated on: 08-JAN-2010
Click here for details of each of the sections below.

Affects:
Product (Component) Oracle Server (Rdbms)
Range of versions believed to be affected Versions> = 10.2.0.4
Versions confirmed as being affected 10.2.0.4

Platforms affected Generic (all/most platforms affected)

It is believed to be a regression in default behaviour thus:
Regression introduced in 10.2.0.4

Fixed:
This issue is fixed in 10.2.0.4.3 (Patch Set Update)

Symptoms: Related:
Performance Affected (General)
Performance Of Certain Operations Affected
Waits for "enq: RO-fast object reuse"
Truncate
_ DB_FAST_OBJ_TRUNCATE

Description
This problem is introduced in 10.2.0.4.

Sessions can wait on "enq: RO-fast object reuse" while DBWR consumes
Lots of CPU when ming truncate type operations.

Workaround
Flush the buffer cache before truncating
OR
Set _ db_fast_obj_truncate = FALSE.
Please note: The above is a summary description only. Actual symptoms can vary. Matching to any symptoms here does not confirm that you are encountering this problem. Always consult with Oracle Support for advice.

References
Bugs: 8544896 (This link will only work for PUBLISHED bugs)
Note: 245840.1 Information on the sections in this article

Truncate table and delete * from

There are two main differences. Truncate is the overall deletion, and delete is the one-by-one deletion. 2. truncate does not write server logs, and delete writes to server logs. This is why truncate is faster than delete. Therefore, the impact is as follows: 1. Truncate is 2 faster. Truncate does not activate trigger3. Truncate reset Identity

Which of the following statements is incorrect after the truncate table command is executed ()

A