RE: Storage guidelines in 9iR1 ??

Since I am using LMTs exclusively (so far only in 8.1.7) I was curious and did some tests on a 9.2.0.1 system on Linux. I didn't find a particular slowdown during load using sqlldr and my data doesn't show a performance degradation at ~1024 extents for the sql (count(0), sum where, and sum group by, all by fts). In my case it happens somewhere beyond 3000 extents. The cpu time for full scans stayed linear through the entire range (128 up to 6400 extents); the elapsed times initially stayed very close to the cpu time, but beyond 3000 (3072?) grew quickly to 2-3 times cpu time.

I haven't done any comparison to DMT.

At 11:10 AM 3/21/2003 -0800, you wrote:
>Gaja,
>
>You're correct. I should have quantified what I meant by significant. As
>well as given more detail on what I was doing. That said, here is what I
>remember of what I was doing....
>
>Specifically, At the request of management, I was testing the performance
>and extent allocation of locally managed tablespaces v.s. dictionary
managed
>tablespaces. I was to give a summary of my results and a recommendation as
>to how new tablespaces were to be created.
>
>That said, I create 2 tablespaces. One dictionary managed and one locally
>managed (uniform extent size) on the same instance, same logical volume on
>the disk array and same extent sizes (1mb)
>
>The same table was created in both tablespaces, using the default storage
>clauses.
>
>I used SQL Loader to load the same data into both tables tablespaces
>multiple times. The source file was about 1mb.
>
>I was mostly testing non-direct path insert performance (via sqlldr) and
>select performance via several scripts (using sqlplus).
>
>What I found was that the performance of sqlldr stayed remarkably steady
for
>the dictionary-managed tablespace well past 2000 extents. The sqlldr
>run-times increased by about 5-10% for the locally-managed after about 1024
>extents had been reached.
>
>The performance of the select statements degraded in a linear fashion,
based
>on the number of rows. The exception was that the LMT table saw a 5-10%
>degradation in performance after about 1024 extents were reached.
>
>No updates or deletes were performed on the tables. Also, there were no
>indexes or constraints on the tables. Nor did I generate statistics.
>
>And this was repeatable as I dropped and recreated the tablespaces several
>times.
>
>The methodology was as follows, recording the timing at each step
>1) Load the file one time into each of the tables
>2) note the number of extents
>3) perform the selects
> -- count(*)
> -- select * from xxx where id = 1;
> -- a select with a group by.

Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com

This email communication is intended as a private communication for the sole use of the primary addressee and those individuals listed for copies in the original message. The information contained in this email is private and confidential and if you are not an intended recipient you are hereby notified that copying, forwarding or other dissemination or distribution of this communication by any means is prohibited. If you are not specifically authorized to receive this email and if you believe that you received it in error please notify the original sender immediately. We honour similar requests relating to the privacy of email communications.

Cette communication par courrier electronique est une communication privee a l'usage exclusif du destinataire principal ainsi que des personnes dont les noms figurent en copie. Les renseignements contenus dans ce courriel sont confidentiels et si vous n'etes pas le destinataire prevu, vous etes avise, par les presentes que toute reproduction, tout transfert ou toute autre forme de diffusion de cette communication par quelque moyen que ce soit est interdit. Si vous n'etes pas specifiquement autorise a recevoir ce courriel ou si vous croyez l'avoir recu par erreur, veuillez en aviser l'expediteur original immediatement. Nous respectons les demandes similaires qui touchent la confidentialite des communications par courrier electronique.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Wolfgang Breitling
  INET: breitliw_at_centrexcc.com

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).