11.2 space pre-allocation algorithm

From: Venkat Krish <venkat.lear_at_gmail.com>
Date: Wed, 23 Mar 2016 16:14:42 -0400
Message-ID: <CAOV8h7vhWGMSqQbhgaQCp7jVhiy2PbUNNFy5C8LkcjGVywAVqQ_at_mail.gmail.com>



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 Wed Mar 23 2016 - 21:14:42 CET

Original text of this message