Scads of extents in a dictionary managed tablespace...BAD!
In a dictionary managed tablespace with default storage of 104K/104K (I
didn't know this until way later / too late), I created a table (without
specifying storage) and loaded that thing up. It was taking up 7.5 GB
on disk when complete
PROTECTED]
Date: Sat, 28 Jun 2003 11:29:18 -0800
Scads of extents in a dictionary managed tablespace...BAD!
In a dictionary managed tablespace with default storage of 104K/104K (I
didn't know this until way later / too late), I created a table (without
specifying storage) and loaded that thing up
I just read a .pdf by a couple of people at oracle called Stop Fragmenting and start
living where it says not worry about the number of extents in a tablespace. However in
the administrators doc it says the following:
Estimate Table Size and Set Storage Parameters
Estimating the sizes of tables
It is the old argument that was made to justify one or only a few
extents. Empirically the idea does not hold up. This idea is now
classified as a myth but the Oracle docs have not caught up yet.
Allan
-Original Message-
Sent: Friday, June 20, 2003 11:20 AM
To: Multiple recipients
Two reasons:
a) if you go into extent map blocks then you will
suffer an overhead of at least 1 billionth of a
percent :-)
b) more seriously, its generally easier to pick up a
rogue table if its run into thousands of extents and
you had not intended it to. Its not a performance
problem per se
will
suffer an overhead of at least 1 billionth of a
percent :-)
b) more seriously, its generally easier to pick up a
rogue table if its run into thousands of extents and
you had not intended it to. Its not a performance
problem per se, but its indicative that its a segment
thats doing something
billionth of a
percent :-)
b) more seriously, its generally easier to pick up a
rogue table if its run into thousands of extents and
you had not intended it to. Its not a performance
problem per se, but its indicative that its a segment
thats doing something that was unforseen in the design
phase
Does this mean that if I dd the file onto a raw partition, it will be
scattered around the raw partition? Even though the dd-ing process is
unaware of the characteristics of the raw partition?
-Original Message-
Even without stripping you can't gaurantee it will be a contiguous
of a
percent :-)
b) more seriously, its generally easier to pick up a
rogue table if its run into thousands of extents and
you had not intended it to. Its not a performance
problem per se, but its indicative that its a segment
thats doing something that was unforseen in the design
phase..
hth
i understand, but alot of people have posted here that its ok to have smaller extents
anyway.
who is correct? or am i missing something?
From: Richard Ji [EMAIL PROTECTED]
Date: 2003/06/20 Fri PM 02:00:11 EDT
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: RE: oracle
ok, so should the number of extents in a table be considered in table design? there
are alot of .pdfs and people on this listserv that say it is irrelevant?
From: Rachel Carmichael [EMAIL PROTECTED]
Date: 2003/06/20 Fri PM 02:44:52 EDT
To: Multiple recipients of list ORACLE-L [EMAIL
of extents in a table be considered in table
design? there are alot of .pdfs and people on this listserv that say it is
irrelevant?
From: Rachel Carmichael [EMAIL PROTECTED]
Date: 2003/06/20 Fri PM 02:44:52 EDT
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: Re: oracle docs do say
No, I was talking about on file system only.
-Original Message-
Sent: Friday, June 20, 2003 2:20 PM
To: Multiple recipients of list ORACLE-L
Does this mean that if I dd the file onto a raw partition, it will be
scattered around the raw partition? Even though the dd-ing process is
is a
candidate for partitioning anyway. So if set a
ceiling of 4G on a segment, that's 4000 extents which
means about 8 extent map blocks (assuming 8k blocks).
I'm not too fussed about 8 blocks
c) DBA_EXTENTS takes 4 days to query
The most common query I've seen to this view was to
map file/block
Hi:
Oracle 8173 on Sun 2.8.
When we had tablespace created as DMT, I used to occuasionally find all
indexes in a schema which have multiple extents and run a script to compress
each of them into one single extent (maybe this is not necessary, but that's
another topic). But I find lately
PROTECTED]
Sent by: [EMAIL PROTECTED]
01/28/2003 12:06 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:extents question on LMT
Hi:
Oracle 8173 on Sun 2.8.
When we had tablespace created as DMT, I used
Is there any restriction
that the segments shouldn't cross more than 5 extents in oracle
7.3.
Pl. help me, as I heard
from somebody that the segments shouldn't cross more than 5 extents and my
indexes are in more than 20 extents.
Thanks in
advance
Anand
KumarDBA
ITW
Signode India
Ltd
Anand - This was the common wisdom several years ago. I used to rebuild
tables based on rules of thumb like this. Recently, several leading experts
have challenged this assumption, and now it is generally accepted that
trying to keep the number of extents below 5 wastes a lot of work for
nothing
thanks a
lot...
- Original Message -
From:
DENNIS
WILLIAMS
To: Multiple recipients of list ORACLE-L
Sent: Sunday, December 01, 2002 4:03
AM
Subject: RE: Table - Extents
Anand - This was the common wisdom several years ago. I used to
rebuildtables based
Hello,
I was very surprise when I saw what is below in the note
100960.1 at Metalink about the number of extents. Could-you
give me your opinion please ?
1.4 OBJECTS WHICH DECREASE PERFORMANCE Any object that is
returned from the following query will decrease
performance
The statement Any object that is returned from the following query will
decrease performance is false. Having large numbers of extents matters
only (1) if your application drops tables frequently and you're not
using LMT, or (2) your application inserts into a table that endures new
extent
Don Granaman wrote:
With compress=N, you will get essentially the same mish-mash of extents you
had previously -
Hmmm, that's hair-splitting, but if you reimport into a tablespace from
which all objects have been dropped and that you have coalesced, you
will have the same number of extents
.
- Kirti
-Original Message-
Sent: Sunday, June 30, 2002 9:28 PM
To: Multiple recipients of list ORACLE-L
With compress=N, you will get essentially the same mish-mash of extents you
had previously - unless you are importing into a LMT.
You have basically two options:
1) Pre-create the objects
I don't believe LMTs are supported in 7.3.x...
--- Don Granaman [EMAIL PROTECTED] wrote:
With compress=N, you will get essentially the same mish-mash of
extents you
had previously - unless you are importing into a LMT.
You have basically two options:
1) Pre-create the objects
essentially the same mish-mash of
extents you
had previously - unless you are importing into a LMT.
You have basically two options:
1) Pre-create the objects with a storage clause specifying the extent
sizes
you want - with initial and next the same size. Then import with
ignore=Y
Thanks everyone for your advice.
If I want to retrofit to 7.3.4 I have to create the tables sans storage
clause then import import the data .
Otherwise wait until the database upgrade.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Robert Monical
INET: [EMAIL
-L
Hello,
Running Oracle 7.3.4.4 on Win2k.
Trying to implement unlimited extents of the same size. (Same size works
for us).
Set the tablespace defaults
Dumped the users
Dropped the users
Imported the users
Tables still had the same mish-mash of extents and max extents 121
Here are my dump
With compress=N, you will get essentially the same mish-mash of extents you
had previously - unless you are importing into a LMT.
You have basically two options:
1) Pre-create the objects with a storage clause specifying the extent sizes
you want - with initial and next the same size
recipients of list ORACLE-L
Hello,
Running Oracle 7.3.4.4 on Win2k.
Trying to implement unlimited extents of the same size. (Same size works
for us).
Set the tablespace defaults
Dumped the users
Dropped the users
Imported the users
Tables still had the same mish-mash of extents and max extents 121
For the reported problem, (1) is the only option as (2) is not possible with
7.3.x database.
- Kirti
-Original Message-
Sent: Sunday, June 30, 2002 9:28 PM
To: Multiple recipients of list ORACLE-L
With compress=N, you will get essentially the same mish-mash of extents you
had
to implement unlimited extents of the same size. (Same size works
for us).
Set the tablespace defaults
Dumped the users
Dropped the users
Imported the users
Tables still had the same mish-mash of extents and max extents 121
Here are my dump params
USERID = system/@**
FILE = c:\dumps
Hello,
Running Oracle 7.3.4.4 on Win2k.
Trying to implement unlimited extents of the same size. (Same size works
for us).
Set the tablespace defaults
Dumped the users
Dropped the users
Imported the users
Tables still had the same mish-mash of extents and max extents 121
Here are my dump
Tim,
The tablespace is dictionary managed.
--- Tim Gorman [EMAIL PROTECTED] wrote:
I was hoping to see * column values from
DBA_TABLESPACES, not just
the
default storage column values. This would show
whether the
tablespace in
question was locally-managed (and SYSTEM or UNIFORM,
if so) as
Well! I'm out of ideas. The only other thing I can think of is a recent
ALTER TABLE which changed the INITIAL on the table since the load, but
that's grasping (gasping?). Still, could you look at LAST_DDL_TIME on
DBA_OBJECTS for the table, just to grasp that last straw?
- Original Message
Tim,
Thanks for all your help. I will check the
LAST_DDL_TIME field (although I didn't know what
INITIAL parameter can be modified) and will let you
know if something comes out of it.
Gene
--- Tim Gorman [EMAIL PROTECTED] wrote:
Well! I'm out of ideas. The only other thing I can
think of is
Hi,
I'm confused by the way some of my extents are
allocated in Oracle. I've talked to Oracle reps, I
thing I've posted here before and jsut when I thought
I got an understanring - ooops, everything is gone. So
I'll give it another try. I have a table with initial
set to 32K and next set to 1M
you expect all new extents
to be sized according to NEXT...
So, the newly added extents were probably INITIAL extents for the original
TEMPORARY segments, before they were merged into the table segment, perhaps?
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED
Tim,
Thanks for the explanation. The table I'm looking at
has an initial size 32K while the new extents are all
16K. Why would this happen?
thanks
Gene
--- Tim Gorman [EMAIL PROTECTED] wrote:
SQL*Loader in direct-parallel mode (i.e.
DIRECT=TRUE, PARALLEL=TRUE) first
loads into a TEMPORARY
the new extents are all
16K. Why would this happen?
thanks
Gene
--- Tim Gorman [EMAIL PROTECTED] wrote:
SQL*Loader in direct-parallel mode (i.e.
DIRECT=TRUE, PARALLEL=TRUE) first
loads into a TEMPORARY segment. After the load
completes, then the
TEMPORARY segment is merged
I'm looking
at
has an initial size 32K while the new extents are
all
16K. Why would this happen?
thanks
Gene
--- Tim Gorman [EMAIL PROTECTED] wrote:
SQL*Loader in direct-parallel mode (i.e.
DIRECT=TRUE, PARALLEL=TRUE) first
loads into a TEMPORARY segment. After the load
DBA_TABLESPACES show for
the tablespace involved?
- Original Message -
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
Sent: Tuesday, May 14, 2002 12:53 PM
Tim,
Thanks for the explanation. The table I'm looking
at
has an initial size 32K while the new extents
in the dba_Segments I see
that all the new extents are 16K and looks like many
of them are empty. I read a few documents regarding
sql*loader in parallel direct mode, but I'm still
confused as to why are these extents are all 16K
instead of being 32K (some) and 1M (most). Also why do
I see unused blocks
Can someone point me to good reading material on this
subject. Is one better than the other for performance
and manageability?
Syntactically the autoallocate is shorter and seems to
be more hands off (does that mean worry free also?).
TIA
=
Sundeep Maini
Consultant
Currently on
http://technet.oracle.com/doc/oracle8i_816/server.816/a76956/tspaces.htm
-Original Message-
Sent: Tuesday, March 26, 2002 12:54 PM
To: Multiple recipients of list ORACLE-L
Can someone point me to good reading material on this
subject. Is one better than the other for performance
and
Sundeep - Start by reading the classic paper How to Stop Defragmenting and
Start Living at this link:
http://www.dbatoolbox.com/WP2001/spacemgmt/defrag.htm
Actually, autoallocate and uniform extents work very well together. But you
need to understand the concepts behind them first. And make sure
And also Metalink doc 105120.1
Reddy, Madhusudana wrote:
http://technet.oracle.com/doc/oracle8i_816/server.816/a76956/tspaces.htm
-Original Message-
Sent: Tuesday, March 26, 2002 12:54 PM
To: Multiple recipients of list ORACLE-L
Can someone point me to good reading material
tables:
first 16 extents = 64K (8 blocks of 8K apiece)
next 63 extents = 1M (128 blocks of 8K apiece)
next ? extents = 8M (1024 blocks of 8K apiece)
In my tablespaces, I haven't seen more than 16 extents of 64K for any
segment, and I haven't seen more than 63 extents of 1M for any segment
There's a note on my website in the errata and addenda
to the book, chapter 8, about this. 64MB extents kick in
when the segment has grown to about 1GB.
However, oddities occur all over the place, particularly
when the tablespace has been exercised for a while.
It is possible for Oracle
Sundeep
Oops, egg on my face. Reading too fast, seeing one word and thinking
another. My understanding from Stop Defragmenting and Start Living was
that uniform extents was the direction Oracle was heading. The problem with
autoallocate is that you can end up with fragmented tablespaces
Reading descriptions of the effects of Autoallocate leads me to
think that Autoallocate is nothing more than PCTINCREASE redux--a
little less crude, but still no way to manage your space.
Paul Baumgartel
--- sundeep maini [EMAIL PROTECTED] wrote:
Dennis,
I mentioned Autoallocate and not
I heard of it some place. that is try to have three different
tablespaces. One for large table, one for medium size table and third
for small tables. Each of these tablespaces can have their own extent
sizes. Large having large extent size. Medium can have medium size
extents and same for small
.
extents are made up of blocks.
now I'll shut up before I embarrass myself anymore :)
Rachel
--- [EMAIL PROTECTED] wrote:
and I was off as well, if your extent size is less than the
blocksize
then you can have more than one extent in a block (divide the block
size by the extent
...
someone shoot me. I am spending WAY too much time being unemployed
these days.
extents are made up of blocks.
now I'll shut up before I embarrass myself anymore :)
Rachel
--- [EMAIL PROTECTED] wrote:
and I was off as well, if your extent size is less than the
blocksize
Hi
If DB block size is 8k then how many extents in one db block?
Thanks
-Seema
_
Join the worlds largest e-mail service with MSN Hotmail.
http://www.hotmail.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
Title: RE: EXTENTS?
it´s the other way around... how many db blocks in an extent.
an extent is a number of contiguous data blocks ...
-Mensaje original-
De: Seema Singh [mailto:[EMAIL PROTECTED]]
Enviado el: Lunes, 04 de Marzo de 2002 15:19
Para: Multiple recipients of list ORACLE
Depends on the size of the extent.
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Monday, March 04, 2002 1:18 PM
Hi
If DB block size is 8k then how many extents in one db block?
Thanks
-Seema
Depends on your extent size
-Original Message-
Sent: Monday, March 04, 2002 12:19 PM
To: Multiple recipients of list ORACLE-L
Hi
If DB block size is 8k then how many extents in one db block?
Thanks
-Seema
_
Join
you have it backwards... extents don't fit into blocks, blocks fit into
extents
and extent size is dependent on tablespace storage parameters and
objectg storage parameters
--- Seema Singh [EMAIL PROTECTED] wrote:
Hi
If DB block size is 8k then how many extents in one db block?
Thanks
Title: RE: EXTENTS?
I'm going to say 1. I don't think you can have multiple tables in the same DB block.
-Original Message-
From: Seema Singh [mailto:[EMAIL PROTECTED]]
Sent: Monday, March 04, 2002 10:19 AM
To: Multiple recipients of list ORACLE-L
Subject: EXTENTS?
Hi
If DB
recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:EXTENTS?
Hi
If DB block size is 8k then how many extents in one db block?
Thanks
-Seema
_
Join the world's largest e-mail service with MSN Hotmail
Title: RE: EXTENTS?
The Server Concepts manual Chapter on Data Blocks, Extents, and Segments would be a helpful Chapter to read.
-Original Message-
From: Kevin Lange [mailto:[EMAIL PROTECTED]]
Sent: Monday, March 04, 2002 1:28 PM
To: Multiple recipients of list ORACLE-L
Subject: RE
Isn't the minimum extent size something like 5 times the DB_BLOCK_SIZE?
If that's true then maximum .2 extents in DB block.
Or you are looking for this info?
db_block_size Max Extents
-- ---
2K121
4K
can have
different Db Block sizes in 9i - correct anybody?)
Hope this helps.
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Monday, March 04, 2002 1:19 PM
To: Multiple recipients of list ORACLE-L
Hi
If DB block size is 8k then how many extents in one db block
nick, you're thinking segments not extents.
and I was off as well, if your extent size is less than the blocksize
then you can have more than one extent in a block (divide the block
size by the extent size and round DOWN)
in general though, extents are not usually sized as small as the
database
[EMAIL PROTECTED] wrote:
in general though, extents are not usually sized as small as the
database block size
should extents be multiples of block size?
--
--
Bill Shrek Thater ORACLE DBA
[EMAIL PROTECTED
I am confused. Are you talking of multiple extents in a block? Throws my
fundamentals topsyturvy.
Raj
Rachel
, March 04, 2002 3:13 PM
To: Multiple recipients of list ORACLE-L
I am confused. Are you talking of multiple extents in a block? Throws my
fundamentals topsyturvy.
Raj
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jack C. Applewhite
INET: [EMAIL PROTECTED]
Fat City
to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:RE: EXTENTS?
nick, you're thinking segments not extents.
and I was off as well, if your extent size is less than the blocksize
then you can have more than one extent in a block
this helps.
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Monday, March 04, 2002 1:19 PM
To: Multiple recipients of list ORACLE-L
Hi
If DB block size is 8k then how many extents in one db block?
Thanks
-Seema
sigh...
someone shoot me. I am spending WAY too much time being unemployed
these days.
extents are made up of blocks.
now I'll shut up before I embarrass myself anymore :)
Rachel
--- [EMAIL PROTECTED] wrote:
and I was off as well, if your extent size is less than the
blocksize
That's what I thought too, but it will skip extents from any LMTs in use.
And getting extents info when LMTs are is use will be slower as compared to
DMTs due the way this info is stored in the bitmap in each datafile for the
LMT.
- Kirti
-Original Message-
Sent: Wednesday, February
--- Deshpande, Kirti [EMAIL PROTECTED]
wrote: That's what I thought too, but it will skip
extents
from any LMTs in use.
And getting extents info when LMTs are is use will
be slower as compared to
DMTs due the way this info is stored in the bitmap
in each datafile for the
LMT.
- Kirti
Kirti,
In LMT you can query the X$KTFBUE which is roughly equivalent to UET$ in
DMTs.
select v.name FILE NAME,count(x.KTFBUEFNO) TOTAL # of EXTENTS
from V$datafile v, X$KTFBUE x
where v.file#=X.ktfbuefno
group by v.name;
Ethan, Is this what you are looking for or something else?
Best
# of EXTENTS
from V$datafile v, X$KTFBUE x
where v.file#=X.ktfbuefno
group by v.name;
Ethan, Is this what you are looking for or something else?
Best Regards,
K Gopalakrishnan
Bangalore, INDIA
-Original Message-
Kirti
Sent: Thursday, February 28, 2002 7:13 AM
To: Multiple recipients
Thanks Connor, that is a lot faster. I think I will go with the idea of
just watching for any dramatic drops in DBA_FREE_SPACE. I have everything
tied down pretty tight but if a single object on a near empty tablespace
started to grow uncontrollably I wouldn't pick it up till tablespace hit 75%
Anyone recommend a faster access path for getting the total number of
extents in the database? select sum(extents) from dba_segments is too slow
for my purposes.
Ethan
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Post, Ethan
INET: [EMAIL PROTECTED]
Fat City
Ethan.Post@pTo: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
s.net cc:
Sent by: Subject: Total Extents
.
It is Oracle 8.1.7.2 on Tru64 Unix
From this I have a couple of questions.
1. I want to test different extent sizes for performance, what tool should I
use, tkprof or statspack
2. How big should the datafiles be, 500m, 1Gb etc.
3. As for the extents, I am leaning towards a locally managed tablespace
prefer so called DBA intuition. The DBA intuition tells me that for
pictures
you want the block to be as big as possible. Thus, - 32k.
2. How big should the datafiles be, 500m, 1Gb etc.
2GB datafiles are usually the most reliable ones.
3. As for the extents, I am leaning towards a locally
and there will be
upto 200 images.
It is Oracle 8.1.7.2 on Tru64 Unix
From this I have a couple of questions.
1. I want to test different extent sizes for performance, what tool should I
use, tkprof or statspack
2. How big should the datafiles be, 500m, 1Gb etc.
3. As for the extents, I am
yep that works -- that is the total ALLOCATED extents in the
tablespace.
--- Viraj Luthra [EMAIL PROTECTED] wrote:
Could I use the following query to get the total used extents :-
SELECT t.tablespace_name, t.initial_extent,
t.next_extent, t.min_extents, t.max_extents
if you use count, then you need to replace dba_segments with
dba_extents
each row in dba_segments has a column called extents which is the
total number of extents allocated to that segment.
there is one row in dba_extents for each allocated extent for a
segment.
either way will work
Fax : +44 (0) 115 - 957 6018
-Original Message-
Sent: 31 January 2002 13:30
To: Multiple recipients of list ORACLE-L
yep that works -- that is the total ALLOCATED extents in the
tablespace.
--- Viraj Luthra [EMAIL PROTECTED] wrote:
Could I use the following query to get
This query should give u the total number of used extents in a tablespace as
well
as the number of used blocks and bytes :
select a.tablespace_name, NVL(count(extent_id),0) Used Extents,
sum(NVL(bytes,0)) Bytes Used,
sum(NVL(blocks,0)) Blocks Used
from dba_tablespaces
Hello All,
Thanks for your responses. I am listing the query, if any one else wants to use the
query :-
SELECT t.tablespace_name, t.initial_extent,
t.next_extent, t.min_extents, t.max_extents,
t.pct_increase, status, contents, nvl(sum(extents),0)
FROM
Could I use the following query to get the total used extents :-
SELECT t.tablespace_name, t.initial_extent,
t.next_extent, t.min_extents, t.max_extents,
t.pct_increase, status, contents, sum(extents)
FROMsys.dba_tablespaces t, sys.dba_segments s
where
Hi,
I'd use count i.s.o. sum if you want the number of extents
Jack
Viraj Luthra [EMAIL PROTECTED]@fatcity.com on 31-01-2002 08:05:19
Please respond to [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:(bcc: Jack van
On Tue, 22 Jan 2002, Wiegand, Kurt wrote:
sort of on the subject.I once had a table with ~88000 extents
(most 1 block!) it took 8 hours to delete and a subsequent coalesce
ran for 2 hours before failing as it ran out of shared
memory(8.1.5).
Kurt,
What component of the SGA
recipients of list ORACLE-L
On Tue, 22 Jan 2002, Wiegand, Kurt wrote:
sort of on the subject.I once had a table with
~88000 extents
(most 1 block!) it took 8 hours to delete and a
subsequent coalesce
ran for 2 hours before failing as it ran out of
shared
memory(8.1.5).
Kurt
On the topic, I once had a tablespace with 300,000+ free extents and 0 used
extents. We executed a drop tablespace command, and looking at fet$ and the
rate at which it was dropping extents from the table, we estimated it would
take us 64 hours. This was on a 7.3.4 db, and we thought it better
, 22 Jan 2002, Wiegand, Kurt wrote:
sort of on the subject.I once had a table with
~88000 extents
(most 1 block!) it took 8 hours to delete and a
subsequent coalesce
ran for 2 hours before failing as it ran out of
shared
memory(8.1.5).
Kurt,
What component of the SGA
[EMAIL PROTECTED]
cc: (bcc: CHITALE Hemant Krishnarao/Prin DBA/CSM/ST Group)
Subject: Re: multiple extents are OK, dagnabbit
the
extent
info, I never bother with the internals of the data dictionary) having
problems while being restructured. Once the tables were changed from 40K
to 500M
extents the upgrade took less than 2 hours.
One of the suggestions I did not use was to edit sql.bsq to provide much
larger
extents
sort of on the subject.I once had a table with ~88000 extents (most 1
block!)
it took 8 hours to delete and a subsequent coalesce ran for 2 hours before
failing as it ran out of shared memory(8.1.5).
-Original Message-
Sent: Tuesday, January 22, 2002 11:55 AM
To: Multiple
Hi All,
Actually, in extreme cases ( 87000 in my case, and I had 12
tables
like that) it can cause problems with upgrading. Not sure what, but we
had to do CTAS into new tables with much larger extents to do the
upgrade from 8.1.5 to 8.1.6 here. Had Oracle support and consultants
baffled
And I was worried about 20 to 60 extents. :)
But I do have one question, if a table has multiple extents, 20 extents at
1Mb each, and they are
contiguous, is that equal to 1 extent of 20Mb big ?? Does oracle have to
work harder to get
those 20 extents ? (okay two questions)
Darren
Can you elaborate on exactly what happened? 8.1.5 to 8.1.6 is just a
catalog script and a binary change. What error did you encounter, and
at which step in the upgrade? Extents should not matter in an
upgrade.
--
Jeremiah Wilton
http://www.speakeasy.net/~jwilton
On Mon, 21 Jan 2002, Dave
Hey Jeremiah, add in something to the last paragraph about how using
LMTs will obviate the problem in truncating tables with lots of extents
:)
There really is NO reason to worry about large numbers of extents these
days. I mean, I wouldn't want to really test the unlimited ability
but other
Jerry,
Tell the client that you will be HAPPY to reorg the tables and indexes
over 10 extents. It will cost X dollars and take Y hours of
downtime/slowdown. Insert inappropriately huge numbers into X and Y.
It's amazing how quickly people will change their minds when you talk
hours and dollars
Jerry - You could approach the issue a little more subtly. Here is an Oracle
paper where Oracle recommends locally managed tablespaces and uniform
extents. If you can point out to them that you are a modern DBA that is
keeping up with new Oracle features, I think that would be persuasive.
http
1 - 100 of 157 matches
Mail list logo
