Yep, in addition to T2 having a good throughput only when the job is heavily parallelized, the standard imp is also a quite low-perfomring import tool. And it can't be parallelized (unless you manually export to multiple different files and then run multiple imp's in parallel).
You should really be using Oracle's datapump (expdp and impdp). This can do parallel execution too. I noticed that you didn't mention whether it was the imp process or Oracle's background server process which had consumed 30minutes of CPU time during 5 hour execution (and thus had spent rest of 4h 30minutes waiting for something). If it was the imp process, then this can be understood as it has to sit idle while Oracle server is serving the array insert request. If it was the Oracle server process, then I would use my snapper script to understand where this extra time was spent (as Oracle records exactly on what the waiting time was spent). There are few options for tuning the imp speed, set recordlength=65535 and buffer=1048576 for example in imp command line. The above options don't help (they probably wont help much) then look into expdp/impdp... -- Regards, Tanel Poder http://blog.tanelpoder.com http://n.otepad.com - n.ote this! > -----Original Message----- > From: Darshak Modi [mailto:[EMAIL PROTECTED] > Sent: Thursday, August 28, 2008 21:59 > To: 'Tanel Poder'; perf-discuss@opensolaris.org > Subject: RE: [perf-discuss] Oracle 10g R1 performs extremely BAD > > This is standard imp tool of oracle. > > In vmstat it show blocked. In mpstat and iostat there are no > huge write/read going on. How will I find that its waiting for IO ? > > > Regards, > Darshak > > -----Original Message----- > From: Tanel Poder [mailto:[EMAIL PROTECTED] > Sent: Thursday, August 28, 2008 7:10 PM > To: 'Darshak Modi'; perf-discuss@opensolaris.org > Subject: RE: [perf-discuss] Oracle 10g R1 performs extremely BAD > > Hi Darshak, > > The key question is how did you try to import data? > > 1) Using an Oracle data loading tool or a custom written one? > > 2) Using only single thread or in parallel? > > If only 30 minutes of total 5 hours elapsed time was spent on > CPU, the load process was waiting on something a lot. Maybe > for IO (especially if the loading program commits too > frequently) or just network rountrips (if loading from a > remote server with too small insert array sizes). > > These are just guesses though, but the good thing is that you > can measure from inside Oracle, where the loading time is spent. > > There are many tools for querying Oracle's instrumentation > views (V$SESSION_EVENT and V$SESSTAT for example), I have > written a convenient script for this (which doesn't require > any installations nor db changes). > > You can get it from here along some instructions: > > http://blog.tanelpoder.com/2007/12/06/oracle-session-snapper-v 106-released/ > > You should run it with let say 1-minute interval on the > individual loading session or all sessions in db. You can > post the result so I (or other Oracle people here) can advise... > > -- > Regards, > Tanel Poder > http://blog.tanelpoder.com > http://n.otepad.com - n.ote this! > > > > -----Original Message----- > > From: [EMAIL PROTECTED] > > [mailto:[EMAIL PROTECTED] On Behalf Of Darshak > > Modi > > Sent: Thursday, August 28, 2008 21:12 > > To: perf-discuss@opensolaris.org > > Subject: [perf-discuss] Oracle 10g R1 performs extremely BAD > > > > Hi All, > > We are planning to move our application to T2 architecture blades. > > When we tried to install Oracle and import the data, it took nearly > > 5-6 hours which on a normal PC takes 30 mins. > > The server is 64 threads, 64GB RAM. The CPU is completely > idle, cores > > are idle, memory remains free still its now working > > > > When I see in vmstat it says the blocked threads increases. > > In ps I can see CPU time taken is 30 minutes where total > time taken is > > almost 5 hrs. > > > > > > > > Regards, > > Darshak > > > > _______________________________________________ > > perf-discuss mailing list > > perf-discuss@opensolaris.org > > _______________________________________________ perf-discuss mailing list perf-discuss@opensolaris.org
