Re: Adding datafile hungs instance for 60 seconds

 Grzegorz,

There is no checkpoint on tablespace level, it's an ordinary checkpoint: it tried to write its SCN to all (online) datafile headers. But the datafile header of your new datafile was in exclusive mode as it was extended.

simple solution: create & extend the datafile in small chunks (you can do the math based on your IOs throughout).
Oracle introduced Space Management Coordinator Process (SMCO) (I assume in 11.2) to extend datafiles asynchronous to the demand for new space. _enable_space_preallocation can be used to turn this on/off (it also works for securefile LOBs).

I hope this helps somehow,
 Martin

Am Fr., 4. Mai 2018 um 18:15 Uhr schrieb GG <grzegorzof_at_interia.pl>:

> Hello,
>
> I've been hit by strange hang issue during execution of alter
> tablespace tbs add datafile '/u02...' size 1g autoextend on .
> The tbs is where huge lob segment exists (around 1TB in total) and the
> SGA is around 160GB (with huge pages enabled on linux).
> The db was rather idle load 1.0 (cpu_count 32) but issue appeared when
> around 700 sessions started to waiting on cpu and after that on
> commit (log file sync). I'm wondering if there is a checkpoint on
> tablespace level done as a part of adding datafile or anything special ?
> It has lasted around 1minute and after this db was idle again with
> nothing in alertlog (except RMS0 process killed due to 30sec hang) .
> It is EE 11.2.0.4 with latest PSU.
> Any ideas more than welcome :).
>
> Regards .
> Grzegorz
>
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l