Advice Needed -Free space in tablespace for performance
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Advice Needed -Free space in tablespace for performance

  1. #1
    Join Date
    Nov 2005
    Posts
    5

    Red face Advice Needed -Free space in tablespace for performance

    Could anybody provide some thoughts about the free space inside an Oracle database. Does the
    amount of free space inside the tablespace containing all the tables inside an OLTP Production
    database affect its performance.

    Should there be a threshold for free space on the disk where the datafiles are located. As the SYSTEM
    and UNDO tablespaces are on AUTOEXTEND mode, is it possible to find how much space they would swell
    on at the worst peak.

    And lastly, is it better to increase the datafile size or add another datafile to increase tablespace size.
    The database in question is an OLTP Production database on Oracle 9205 & Solaris 5.8

    Please do let me know if there is any other info that I missed to provide here.
    Thanks in advance...

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    free space doesnt matter, its irrelevant

    undo shouldnt be on autoextend, cant tell how much they would 'swell' as they should be fairly static

    the thing with datafiles is do whatever keeps your io spread evenly across all your devices

  3. #3
    Join Date
    Jun 2005
    Posts
    31
    Quote Originally Posted by dba-k
    Should there be a threshold for free space on the disk where the datafiles are located. As the SYSTEM
    and UNDO tablespaces are on AUTOEXTEND mode, is it possible to find how much space they would swell
    on at the worst peak.
    I agree to Davey's statement not to use autoextend. When unusual jobs (yearly journals / batch jobs, or mass-updates during application-software upgrades) are tested on fully sized pre-production then just watch the rollback segments and the archivelog-volume (that archivelog destination doesn't run full).

    Quote Originally Posted by dba-k
    And lastly, is it better to increase the datafile size or add another datafile to increase tablespace size.
    The database in question is an OLTP Production database on Oracle 9205 & Solaris 5.8
    Regarding file size: As most of my databases medium OLTP-databases (300 GB to 2 TB) I limit filesize to 4 GB, in history rather 2 GB.

    *) Running backup in 5 streams and 7 files per backup-set requires at least 35 equal big datafiles. Having a few small files for the small and tiny tablespaces and all the usage in 10 x 30 GB the 5 backup streams will work only a few minutes, and the last one will run very long...
    ==> What's your current volume and backup-duration ?

    *) when copying the data files for creating your fully-sized pre-production system the same rules apply.
    *) The situations when filesizes greater than 2 GB or 4 GB cause problems are decreasing year by year, but e.g. tar will for longer time be limited to 8 GB files. In history I remember a copy-tool for secure file transfer stopped ad 2 GB and a compression tool too.
    *) If you really want to use excessive file sizes, then make sure that all tools used and the backup system support such huge files.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width