Re: performance comparision between LMT and DMT

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 <ORACLE-L_at_fatcity.com> 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
>
>guangmei_at_yahoo.com
>http://www.geocities.com/guangmei/
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

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: ListGuru_at_fatcity.com (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).