Interesting test. One detail - the import will be creating indexes, which means it will be acquiring and formatting o/s blocks for the temp tablespace on demand.
You might check how much used space you have in your temp, and check how long it would take to format by recreating that sized DMT tablespace. (35 minutes seems a bit long - but it was an 8GB t/s). If you have the time to repeat the test two more times, you could run it with a clean DMT and a clean LMT system, but keep taking snapshots of v$sesstat and v$session_event for the import process (or just use a logoff trigger to capture the values at the end of the import). PLay safe and take v$system_event and v$sysstat as well before and after the import. It would be interesting to see if the extra time showed up as CPU time or Wait time, and see what else we could infer from where the extra CPU/Waits were recorded. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) ____England______January 21/23 The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -----Original Message----- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 20 December 2002 04:56 >Hi: > >We have several oracle 8173 dbs on Solaris 2.8. Recently I was planning to >move all dictionary managered tablespace to locally-managered tablespace >(except SYSTEM ts), because there are many advantages using lmt as I read. >Today I did the following on a test db and here is what I did and found: > >1. create a new locally-managered tablespace (8 files, each with 2048M in >size), uniform extent 500K >2, cretae a temp tablespace (LMT) with tempfile (4 files, each 2000M in >size), uniform extent 2M, very quick to create. >3. drop an old dmt wich holds data for a schema (say user "SCH") >4. alter user SCH to set the default ts and temporary ts to tbs I created in >step 1 and 2 >5. run imp to import schema SCH from dump file. > >The import run successfully and the whole time was 5 hours and 40 minutes. >This is about 35 minutes longer than the time it took last week! > >The only thing different was that the old tbs were NOT lmt, the dump file >are basically the same, not much data change and there is no other stuff >running on the machine. So my question is that if this is normal or I miss >something here? Another questions is that from your experience how much >improvement in terms of performance did you see after you convert dmt to >lmt? > >TIA > >Guang Mei > >[EMAIL PROTECTED] >http://www.geocities.com/guangmei/ > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
