Re: Extents size.

First I've heard from Ferenc for awhile, even if it is 2 years old.

Using extreme cases like this really doesn't make for a good example. I know an instructor that dropped/recreated/imported and entire database because the 'drop table' ran for 2 days with no end in sight.

Extents were 300,000+.

I suspect that that the graph of time vs. number of extents in data dictionary operations has a rather steep curve on the right side. It would be interesting to see where the curve breaks, as it's not likely a linear rise.

Of course, if you're using LMT's, this exercise is a waste of time. :)

Jared

On Tuesday 02 October 2001 15:16, Mohammad Rafiq wrote:
> Further to my previous message, I am reproudcing an email of a list member
> on this subject. It is really interesting to read it..
>
>
> Reply-To: Ferenc Mantfeld <Ferenc_Mantfeld_at_pagenet.com>
> To: "oracle list" <oracle_at_telelists.com>
> Date: Tue, 20 Jul 1999 16:25:15 -0500
>
> Hi all
>
> We know that Oracle often gives us enough rope to hang
> ourselves with. In
> Kevin Loney's presentation at IOUG this year, he brought up
> an interesting
> point about unlimited extents being one of the more
> boneheaded idea Oracle
> came up with, so I decided to conduct my own experiment :
>
> Environment :
> HP V2500, 16 CPU's running at 450 Mhz, 8 GB Ram, 2.5 GBPS
> fiber channel port
> connection, running Oracle 7.3.4.4 on HP-UX 11.0.
> DB_BLOCK_SIZE=8192
> In other words, one real kick-ass machine
>
> One table :
>
> 1,2 million rows in a single extent of 120 MB.
>
> Export this table.
> Set timing on
> Drop it : 3.5 seconds.
>
> Recreate the table as follows :
> Create table my_tab ( ......) storage (initial 16 k next 8 k
> pctincrease 0
> minextetns 1 maxextents 30000 ) pctfree 5
> tablespace my_own_persoanl_ts ;
> Make a note of the datafile numbers for your tablespace, you
> will need this
> to determine how far you are from being done :
>
> import your old table (ignore=y, indexes=no), check your
> alert log , when
> you see :
> ORA-1631: max # extents 30000 reached in table/cluster
> DBA_USER.S_ORG_PROD
> then ps_ef | grep -i imp
> and kill the process.
>
> Now you have a 30,000 extent table.
> First turn on tracing for tkprof :
> SQL > alter session set sql_trace true ;
> session altered
> SQL >
>
> Now drop the table :
> SBPRD-DBA_USER> drop table s_org_prod;
>
> table dropped
> Elapsed: 01:53:42.89
>
> SQL >
>
> In the meantime, from another session, logged in as SYS
> You know that this is the only table that lives in your
> particular datafile
> , so :
> SQL > select count (*) from uet$ where file#=5 ;
>
> COUNT(*)
> ----------
> 26638
> SBPRD-DBA_USER> /
>
> COUNT(*)
> ----------
> 26254
>
> (so you can see the allocated user extent table decreasing).
>
> Bottom line is that from the recursive DML being done on uet$
> and fet$, the
> process has slowed down from 3.5 seconds to almost two hours,
> and we are not
> talking a Mickey Mouse machine here.
>
> Just another reminder that good database design ALWAYS pays
> off, and we are
> to look at new "benefits" that the rdbms offers us, with
> optimistic
> skepticism .
>
> Hope this benchmark is of use to someone out there. If you
> like, I can mail
> the output result of the tkprof (sys=yes) to you.
>
> Regards :
>
> Ferenc Mantfeld
> Oracle DBA
>
>
>
>
> MOHAMMAD RAFIQ
>
>
>
> Reply-To: ORACLE-L_at_fatcity.com
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Date: Tue, 02 Oct 2001 13:15:30 -0800
>
> Why is that? And would that only count for an object in a dictionary
> managed tablespace? Would the time/speed it takes for drops and truncates
> really matter as far as performance is concerned? What I mean is who would
> set storage specs for objects with the speed it takes for truncates and
> drops of that object in mind? It would seem to me that if an object is
> getting dropped or truncated that often that speed should be an issue,
> there are bigger problems at hand. Guru's correct me if I'm wrong please.
> Later, Ivan
>
> -----Original Message-----
> Sent: Tuesday, October 02, 2001 3:56 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Any DDL like drop table and truncate table definately take longer with
> 10,000 extents than 1 extent. Try it. There was a test result 1 year back
> by
>
> a list member on that.
>
> Regards
>
> MOHAMMAD RAFIQ
>
>
>
> Reply-To: ORACLE-L_at_fatcity.com
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Date: Tue, 02 Oct 2001 07:55:28 -0800
>
> That is completely a myth. There is no notable performance different with
> a table with 10,000 extents and one with 1.
>
> The only problem is when it comes to the bitmaps when dealing with LMT and
> cluster when dealing with dictionary managed. When you query the extent
> views, or do space management type processes.
>
> "Do not criticize someone until you walked a mile in their shoes, that way
> when you criticize them, you are a mile a way and have their shoes."
>
> Christopher R. Spence
> Oracle DBA
> Phone: (978) 322-5744
> Fax: (707) 885-2275
>
> Fuelspot
> 73 Princeton Street
> North, Chelmsford 01863
>
>
>
> -----Original Message-----
> Sent: Monday, October 01, 2001 7:15 PM
> To: Multiple recipients of list ORACLE-L
>
> May be it is good practice to keep number of extents to be less than 50, no
> matter what the size of extent.
>
>
>
> -----Original Message-----
> Sent: Monday, October 01, 2001 3:35 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Hello,
>
> I'll do an reorganization of a database (about 140 gigs). Some people say
> that it'd be good to use 128K, 4M and 128M extents. I saw somewhere it'd be
> 160K, 4M and 160M. Which size do you advice me ? I have also many small
> indexes (less than 16K).
>
> Regards,
>
> Thanh-truc Nguyen
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Thanh-truc Nguyen
> INET: truc_at_nguyen.as
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> 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).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Wong, Bing
> INET: bing.wong_at_IngramMicro.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> 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).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Christopher Spence
> INET: cspence_at_FuelSpot.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> 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).
>
>
> _________________________________________________________________
> Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Mohammad Rafiq
> INET: rafiq9857_at_hotmail.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> 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).
>
>
> _________________________________________________________________
> Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jared Still
  INET: jkstill_at_cybcon.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).