Skip to Main Content
  • Questions

  • Can I optimize the auto extend operation by changing the resizing value?

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Assaf.

Asked: April 18, 2017 - 11:27 am UTC

Last updated: April 19, 2017 - 12:56 am UTC

Version: 12.1.0.2.0

Viewed 1000+ times

You Asked

Hi,

I'm using Oracle database 12.1.0.2.0 on Windows 64-bit
The alert log is showing many "Resize operation completed for file#..." messages and the "Resource monitor" is showing I/O intensive usage.
select * from DBA_DATA_FILES shows that the INCREMENT_BY is 1 for all my datafiles.
The question is, is there a best practice for the resize in order to reduce the I/O activity?
Is it right to assume that the larger the INCREMENT_BY value is, the less resize operations will be done? and if so should it improve my DB performance or should I let Oracle manage it?

Thanks in advanced,
Assaf.

and Connor said...

My general practice (without much scientific exploration) has been to use a "next" of something between 50-100m. So normally the background space mgt process will try extend my datafile so foreground sessions dont get blocked, but if I fill a file so fast that it doesn't have time to catch up, then a foreground session will only wait a small amount of time for a 50-100m increase.

Obviously for niche cases, you could adjust that to whatever you like.


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks for you answer, Connor

Assaf Vizner, April 19, 2017 - 5:29 am UTC

Hi,
I changed the INCREMENT_BY value of all my data files by setting the NEXT value to 100M using this command:
SQL> ALTER DATABASE DATAFILE 'E:\ORACLE\ORADATA\NNVSDB\TS_NSA_DATA_01.DBF' AUTOEXTEND ON NEXT 100M;

Database altered.

I also find out that creating your Tablespace using OMF (i.e. where "db_create_file_dest" is configured), creates the datafiles with default initial size of 100MB, sets the AUTOEXTEND to ON automatically with default NEXT value of 100MB.

Found this article very useful:
https://hemantoracledba.blogspot.co.il/2009/06/autoextend-on-next-size.html

Thanks again,
Assaf
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.