Is parallel DML, that is, insert in use? If yes, take a close look at the PQ
statistics during this load and if the contention waits are high you might
consider reducing the degree of parallelism in use on this task.
If Oracle PQ does not apply is there only one insert process or several
concurrent processes? Again if several consider limiting the number.
If neither of the above is true search Oracle support for HW contention issues
related to your full version of Oracle.
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Venkat Krish
Sent: Wednesday, March 23, 2016 4:15 PM
To: oracle-l <oracle-l@xxxxxxxxxxxxx>
Subject: 11.2 space pre-allocation algorithm
Hi-
Running into this weird problem & just wanted to check if anyone else had
encountered the same.
DB Version: 11.2.0.4
Env: Test env ( Write intensive load)
Sudden surge of inserts into a segment ( each day is a diff partition),
sometime causes the segment to grow rapidly to the point it affects the inserts
themselves ( manifesting as "HW contention")
I have read MOS ID : 743773.1 & have had pre-alloc algorithm disabled a long
back
"_enable_space_preallocation" is set to 0
tablespace is bigfile tablespace & at the time of this weirdness, I noticed
that tablespace % usage was just around 80% when this weirdness started.
Any pointers on where to look at ?
or
Anyone knows what internal data , Oracle looks at segment/table and decides to
allocate space proactively based & how it arrives at the size to extend?
--tablespace info
TOTAL_GB FREE_GB
-------- --------
650 130 <-- when problem started
...
650 43 <-- when problem ended. ( >1 hour of segment growth)
--segment info
dba_segments (size in GB) for that segment+ partition
SIZE_GB
-------
16.31G <-- problem started
...
97.37G <-- when allocation ended
(segment has only data incoming..no deletes or updates)
Thanks in adv,
Venkat