Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2002
    Location
    Dublin, Ireland
    Posts
    13

    Unanswered: too many database files

    Hi,

    I have an Oracle 8i database. It currently uses 38 database files and is complaining about too much disk I/O.

    Does anyone know if it is possible to somehow "merge" these files into fewer, larger files?

    Ta,

    Kev

  2. #2
    Join Date
    May 2003
    Location
    Dublin, Ireland
    Posts
    44

    Re: too many database files

    Kev,

    There is no 'easy' ;-) way to merge datafiles. Anyway it will not solve your problem.

    You should do some analysis to find out which files are responsible for most of the disk I/Os (data files, redo logs, etc.). And take it from there. Solutions are different depending on source of the problem.


    Originally posted by kevincherubini
    Hi,

    I have an Oracle 8i database. It currently uses 38 database files and is complaining about too much disk I/O.

    Does anyone know if it is possible to somehow "merge" these files into fewer, larger files?

    Ta,

    Kev

  3. #3
    Join Date
    Jan 2003
    Location
    Woking
    Posts
    107

    Re: too many database files

    Originally posted by sjacek
    Kev,

    There is no 'easy' ;-) way to merge datafiles. Anyway it will not solve your problem.

    You should do some analysis to find out which files are responsible for most of the disk I/Os (data files, redo logs, etc.). And take it from there. Solutions are different depending on source of the problem.

    Hi,

    What you could be doing is export the entire database.
    Reorganize your data files with the same tablespace names
    and import it back again.

    I dont know if this is the right method, but we fixed the same
    problem in this fashion.

    Hope it Helps.
    nn

  4. #4
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    204

    Re: too many database files

    Originally posted by kevincherubini
    Hi,

    I have an Oracle 8i database. It currently uses 38 database files and is complaining about too much disk I/O.

    Does anyone know if it is possible to somehow "merge" these files into fewer, larger files?

    Ta,

    Kev

    What I/O is causing the problems? The only thing merging the files would do is minimize the number of file headers that need updated, correct? If the I/O is due to large DML activity, then putting everything into a fewer number of files might do more harm than good.

    You need to analyze what is causing the high I/O and try to isolate those objects to disks and cotrollers with lower I/O usage.

    HTH,
    Patrick

  5. #5
    Join Date
    Aug 2002
    Location
    Dublin, Ireland
    Posts
    13
    Thanks for the helpful comments.

    I agree that I should track down the cause of the I/O before doing anything drastic with my datafiles.

    I thought it was an issue with shared memory (not enough data being cached), but I've increased just about everything in the SGA without any real difference.

    Where do I go next to track down the source of the problem?

    The actual message I'm getting from my tuning tool is:

    "Too high ratio of average number of blocks scanned by the DBWR (database writer) - 430.4 as compared to all scans on the LRU chain. DBWR is working too hard. This may be caused by the large number of database files."

    Eternally grateful for any help, free beer* to anyone who sorts this out for me.

    Thanks,

    Kev

    *Free beer must be collected in a pub in Dublin, Ireland.

  6. #6
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Larry Murphy's on the corner of Fitzwilliam Street next to AIB?

    Unfortunately I can't help with your Disk I/O :-(

    Free half of beer to anyone who tells me the answer first :-)

    Cheers
    Bill

  7. #7
    Join Date
    Nov 2002
    Posts
    833
    create new tablesspaces with the fewer number of datafiles

    move to objects from the old tablesspaces to the new ones

    select 'alter table ' || table_name || ' move tablespace new_datatblsspace; ' from all_tables where owner = <owner> ...
    select 'alter index ' || index_name || ' rebuild tablespace new_idxtblsspace; ' from all_indexes where owner = <owner> ...

    ....

    keep in mind tables with lob columns cannot be moved this way .. therefore use exp/imp modifying the storage clause

    all references objects may become invalid

  8. #8
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Just a quick correction to Osy's post, you can move tables with lobs, there are lob storage parameters for move tablespace....

    ALTER TABLE <tabname> MOVE TABLESPACE <tsname> LOB (<lobcolumn>) STORE AS (TABLESPACE <lobtsspace> ENABLE STORAGE IN ROW PCTVERSION 40 NOCACHE NOLOGGING);

    You'll have to determine your own storage parameters for the lob, and you have to perform the command for each lob in the table.

    HTH
    Bill

Posting Permissions

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