datapump import taking extremly long time
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: datapump import taking extremly long time

  1. #1
    Join Date
    Mar 2005
    Posts
    143

    datapump import taking extremly long time

    I do a nightly export of our production database exporting 4 schemas (this is not part of my backup plan, so please hold off the "a nightly export is not a backup" comments). I then import the data into a database on a different server with a different OS. this DB is used for reporting purposes in case we loose conectivity to our production server. the database doesnot need realtime data. my problem is the server this used to run on crashed, so I am now using another server running Microsoft 2008 server. the import consists of 1548 tables total. 527 tables have no rows of data in them. and 7 tables are over 1 gig. all the others range in size smaller then a gig. all the datafiles are on the same physical disk. the server has 4 gigs of ram and other things running on it. I have the sga set to 500MB's. and the Ram is pretty much all used up so I don't think I can increase the sga. the import is taking a real long time. I mean over 24 hours. I thought the import would be I/O intense and not cpu or memory intense. Is that true. I can't figure out why this import is taking so long. The old server had the same ram and a slower cpu. and the SGA was set the same. an import on that server took 5 hours tops.

  2. #2
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    trace impdp to find what is going wrong in the new server.

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  3. #3
    Join Date
    Mar 2005
    Posts
    143
    I set the trace to TRACE=1FF0300, and I am looking through the file now. It seems to be filled with the following line:
    KUPC: 14:49:25.579: Before Listen: consumer = KUPC$A_1_20100316142027 from queue = SYS.KUPC$S_1_20100316142015

    any idea what that means?
    Last edited by jayjabour; 03-17-2010 at 09:13 AM.

  4. #4
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    well, no idea what it is.

    however, would you mind to trace only master and worker process (trace=480300) instead of full trace (trace=1FF0300)?

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  5. #5
    Join Date
    Mar 2005
    Posts
    143
    I don't mind at all. I appreciate the help. I figured a full trace would be good, however I am open to any ideas. let me set the trace and run it again.

  6. #6
    Join Date
    Mar 2005
    Posts
    143
    ok, I did notice something different between the 2 servers. Please forgive me if this is stupid and not relivent, but the new server has SATA drives and the old server has SCSI drives. Could this be part of my problem. I noticed through OEM that during the import te I/O was just through the roof. Could the SATA drives not be able to handle the I/O of an import as well as SCSI?

  7. #7
    Join Date
    Mar 2010
    Location
    North Carolina, USA
    Posts
    17
    One thing I have found is, any tables which contain datatypes of TIMESTAMP make the export/import process quite a bit longer, even if using datapump...

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