Truncate Slow in 11.2.0.3 and Higher (Doc ID 1667223.1)

Last updated on FEBRUARY 27, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.3 to 12.1.0.1 [Release 11.2 to 12.1]
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:

Cache                Get Requests Pct Miss Scan Reqs Pct Miss Mod Reqs Final Usage
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

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms