Re: 11.2 space pre-allocation algorithm

No parallel DML.. this is all serial inserts ..one row/exec.

The real interesting part is this..

I have a GOLD copy in SAN & after every test we lay that copy over, to have consistent start. before each test

This issue/behavior wasn't seen all these months and just yesterday showed up.. and it appears the database was relaid & left idle for 2 days..3rd day was actual test.. I think the db sensing the sudden of workload probably goes berserk & allocates way more in advance anticipating more load.

My curiosity is to find
-- why pre-allocation happens even when init parameter was specifically set to turn it off.? ( may be something else is triggering this) -- what historical data does Oracle internally refer to ..that helps to arrive at decision ..where is it stored? ( I might try to clear them, if all else fails)

Hopefully I have rephrased my questions better that before and hope this would get some answers .

Regards.

On Thu, Mar 24, 2016 at 2:36 PM, Powell, Mark <mark.powell2_at_hpe.com> wrote:

> 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