Truncate Slow in 11.2.0.3 and Higher
(Doc ID 1667223.1)
Last updated on JANUARY 06, 2025
Applies to:
Oracle Database - Enterprise Edition - Version 11.2.0.3 to 12.1.0.1 [Release 11.2 to 12.1]Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
Symptoms
AWR reports following wait events in the TOP 5 Foreground Waits:
Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~
Event Waits Time(s) Avg wait (ms) % DB time Wait Class
<snip>
local write wait 493,380 1,585 3 11.78 User I/O
DFS lock handle 1,200,205 692 1 5.14 Other
enq: RO - fast object reuse 568,667 650 1 4.83 Application
The AWR Dictionary Cache Stats shows the following information:
dc_objects 12,651,937 1.09 0 163,989 3,924
dc_segments 5,048,565 0.01 0 136,674 4,119
The ASH reports the following statements that had waits listed in Top wait events:
Top SQL with Top Events
~~~~~~~~~~~~~~~~~~~~~~~~~
Sampled #
SQL ID Planhash of Executions % Activity Event % Event Top Row Source % RwSrc SQL Text
7a62909337ud7 2452080186 1761 18.83 local write wait 5.81 DDL STATEMENT 5.81 TRUNCATE TABLE ...
CPU + Wait for CPU 4.47 DDL STATEMENT 3.82
DFS lock handle 2.33 DDL STATEMENT 2.33
6tjfm0rv28q7p 348574753 785 8.43 local write wait 2.49 DDL STATEMENT 2.49 TRUNCATE TABLE ...
CPU + Wait for CPU 1.66 DDL STATEMENT 1.22
DFS lock handle 1.45 DDL STATEMENT 1.45
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Cause |
| Solution |
| References |