Re: Increase tablespace, which way is better?

[Tanel Poder]

Hi!   In multi-user environments IO times won't probably be much slower with 1M extensions, because disk reading heads will very likely move away from original location right after first request to serve other sessions request. Also, if you think about indexed access to tables, then data from completely different locations of Oracle datafiles are read anyway.   You just should make sure that your extent sizes match the (multiple of) size of db_block_size * db_file_multiblock_read_count. That way if you even got 32 block size * 64 d_f_m_r_c = 2MB and autoextend 1M you won't have any problem, because if extent size is 2MB, 2MB is autoextended anyway.   I personally prefer to precreate datafiles (usually with uniform sizes) for highly loaded systems and monitor space in them, for the reason that otherwise lot's of extra IO is generated when extending 100M for example. Also, the session extending segment, has to wait until the extension is complete... This might cause locking explosions etc.. in high-concurrency systems etc.. Instead, I monitor the free space inside tablespaces and add datafiles during low activity periods.   But for small databases in sense of disk usage and load, I usually use autoextend and monitor free space on disk just to avoid extra maintenance.   Tanel. ----- Original Message ----- From: Jos To: Multiple recipients of list ORACLE-L Sent: Saturday, July 19, 2003 5:49 PM Subject: Re: Increase tablespace, which way is better? Please correct me if I am wrong. With option 1, the datafile will dynamically extend, 1M at a time and there is no guarantee the datafile is contiguous on disk. That will increase your I/O time when Oracle need to find data all over the disk. And also with autoextend, you may run out of space on that disk at any time (ie someone else created a big datafile on that disk), with option 2 at least you know you have 100M available for that datafile on the disk. Jos "Liu, Jack" <[EMAIL PROTECTED]> wrote: Hi, I want to increase tablespace, just want to know which way is better: 1. ALTER TABLESPACE SYSTEM ADD DATAFILE '/u01/oradata/orcl/users02.dbf' SIZE 1M AUTOEXTEND ON NEXT 1M MAXSIZE 100M;   2.alter tablespace system add datafile '/u01/oradata/orcl/users02.dbf' size 100m;   Thanks,   Jack    Yahoo! Mobile - Check & compose your email via SMS on your Telstra or Vodafone mobile.