RE: 11.2 space pre-allocation algorithm
Date: Thu, 24 Mar 2016 18:36:33 +0000
Message-ID: <1E24812FBE5611419EFAFC488D7CCDD132AEB5B4_at_G4W3290.americas.hpqcorp.net>
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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Venkat Krish Sent: Wednesday, March 23, 2016 4:15 PM To: oracle-l <oracle-l_at_freelists.org> 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
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Mar 24 2016 - 19:36:33 CET