would have hundreds
of extents, and each of those new extents took some time to extend that
would have been avoided if you had started with 100MB first and 25MB next.
Indexes take a far worse performance hit. You also expose yourself to other
issues (fragmentation, full table scans (yuck) run slower
Paul,
With LMT's. uniform extents sizes and properly place objects I think you
avoid most of the situations you described. Cary's paper at hotsos.com
shows that in a system with a lot of activity your disk head is never going
to fulfill the request for a full tablescan in a single operation
Title: Message
Hi there -
I'm trying to
convince a client that multiple extents for a table will not hurt their
performance. It's a PeopleSoft app, and PeopleSoft is telling them that they
need to reorg any object with greater than 10 extents (even indexes). This
Oracle 8.1.6.
I've
PMTo: Multiple recipients of list
ORACLE-LSubject: multiple extents are OK,
dagnabbit!
Hi there -
I'm trying to
convince a client that multiple extents for a table will not hurt their
performance. It's a PeopleSoft app, and PeopleSoft is telling them that they
need to reorg any object
Here's my swing at it:
http://www.speakeasy.org/~jwilton/oracle/lots-of-extents.html
--
Jeremiah Wilton
http://www.speakeasy.net/~jwilton
On Thu, 17 Jan 2002, Cunningham, Gerald wrote:
I'm trying to convince a client that multiple extents for a table will not
hurt their performance. It's
Title: Message
Jerry,
If
they want to pay you to reduce their extents, then let 'em!
;-) "A fool and his money are soon
parted."
If
they employ youand want you to work weekends on this, then it's worth the
effort to educate them. I'm surprised an official Oracle white pap
see that sometimes we
computer folk are our own worst enemy. There is such a thing as being
technically right but losing the client anyway. By the way, I totally agree
with you on the multiple extents issue, but since Oracle was nice enough to
post the paper Stop Defragmenting . . on their web site
- Forwarded by CHITALE Hemant Krishnarao/Prin DBA/CSM/ST Group on
18/01/2002 11:36 AM -
CHITALE Hemant Krishnarao/Prin DBA/CSM/ST Group18/01/2002 11:21 AM
Jerry,
Multiple extents is not a problem, true.
But you could put in some consulting effort to resize the extents ---
recreate
segment of say 100M in
size. Do you think that doing so in either of the following 2 ways would be
more advantageous than the other, or would it not make any difference:
(1) Creating 10 - 10M extents for the RBS
(2) Creating 100 - 1M extents for the RBS
As always, TIA for the historically
Gene,
It was a few weeks ago that I was made aware of this paper. I must have
simply downloaded it from the site that was mentioned in the original E-mail
and printed it for future (i.e. now) reference. All I can tell you is that
there is an About the Author section on the last page which
From what I've heard, temporary tablespaces based on temporary files locally
managed have the following behavior..
They don't de-allocate extents necessarily, otherwise there would be overhead in
using them again, (just a bitmap after all) (I am using 8.1.7 on AIX 4.3.3)
So questions:
1) How
it. Thanks. Ivan
-Original Message-
Sent: Tuesday, October 02, 2001 6:14 PM
To: Multiple recipients of list ORACLE-L
As far as DDL is concerned ,Yes. I have seen
Dataware House application(not
a good design) that dropping/truncating tables with
lot of extents takes
longer time
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: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: Tue, 02 Oct 2001 07:55:28 -0800
That is completely a myth
LMT's have their own problems as well though.
Using LMT's, the bitmap on the tablespace is only used to manage free space,
used space is in the segment header of the segment which represents the
extents. Therefore, to do a query of DBA_EXTENTS you can hit all segment
headers (of all tables
Christopher,
Is the guideline 505 extents for the tablespace or each table in the tablespace?
Tom
Tom Terrian
Oracle DBA
WPAFB - DAASC
[EMAIL PROTECTED]
937-656-3844
-Original Message-
Sent: Wednesday, October 03, 2001 10:05 AM
To: Multiple recipients of list ORACLE-L
LMT's have
Does the 505 extent limit apply to the whole of a partitioned table
or to the number of extents per partition?
For example if I had a table wth 371 partitions (53 weeks per year *
7 years) to hold invoice data for tax purposes, do the number of
extents per partition need to be kept at 1 to avoid
-5744
Fax:(707) 885-2275
Fuelspot
73 Princeton Street
North, Chelmsford 01863
-Original Message-
Sent: Wednesday, October 03, 2001 11:25 AM
To: Multiple recipients of list ORACLE-L
Christopher,
Is the guideline 505 extents for the tablespace or each table in the
tablespace?
Tom
Tom
) 322-5744
Fax:(707) 885-2275
Fuelspot
73 Princeton Street
North, Chelmsford 01863
-Original Message-
Sent: Wednesday, October 03, 2001 11:30 AM
To: Multiple recipients of list ORACLE-L
Does the 505 extent limit apply to the whole of a partitioned table
or to the number of extents per
-
Sent: Wednesday, October 03, 2001 8:25 AM
To: Multiple recipients of list ORACLE-L
Christopher,
Is the guideline 505 extents for the tablespace or each table in the tablespace?
Tom
Tom Terrian
Oracle DBA
WPAFB - DAASC
[EMAIL PROTECTED]
937-656-3844
-Original Message-
Sent
!!!
Ramon E. Estevez
[EMAIL PROTECTED] mailto:[EMAIL PROTECTED]
809-565-3121
-Mensaje original-
De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de Thanh-truc
Nguyen
Enviado el: Monday, 01 October, 2001 5:35 PM
Para: Multiple recipients of list ORACLE-L
Asunto: Extents size
PROTECTED]]En nombre de Thanh-truc
Nguyen
Enviado el: Monday, 01 October, 2001 5:35 PM
Para: Multiple recipients of list ORACLE-L
Asunto: Extents size.
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
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
well, only when you are deleting massive amounts of data or truncating
with drop storage.. then there is an impact because of the hits on the
dictionary tables.
but basically yes... I've been told by various Oracle employees that up
to 4096 extents cause no problem whatsoever.
--- Christopher
Back in the V6 days it was a desired characteristic to have every thing in the
first extent of an object for performance reasons. Thankfully those days are
gone and it really does not matter how many extents there are. Rachel has a
presentation on Oracle Myths where she actually portrays having
Yea - I keep hearing and seeing tests that show that the number of extents
had no bearing on performance (up to a point). It just 'bothers' me to see
a 500 or 1200 or 2000 extents on a table.. grin
Here is a question - is there any situation that having only 1 big extent
would reduce
Yes. If you need to do parallel query, multiple
extents may help you out a bit.
Jared
Steve Smith
something along those lines...
as with everything there is no real black or white on this... but
transactional systems that do a lot of small (non-sequential) reads you
are better off with lots of extents while data warehouses are often
better off with fewer extents as the reads tend
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: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: Tue
-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
Title: RE: Extents size.
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
... I've been told by various
Oracle employees that up
to 4096 extents cause no problem whatsoever.
--- Christopher Spence [EMAIL PROTECTED] wrote:
That is completely a myth. There is no notable
performance different
with a
table with 10,000 extents and one with 1.
The only
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: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: Tue, 02 Oct 2001 07:55:28
You could probably mount a argument about more than
505 extents (for an 8k block) not being able to fit in
the segment header block - but unless you're
clobbering dba_extents and the like with queries, I
doubt you'd ever see a difference.
hth
connor
--- Steve Smith [EMAIL PROTECTED] wrote
I would content that the moment the extent size is
greater than SSTIOMAX (or whatever the OS can
service), then you won't see any benefit from fewer
extents even on data warehouse - the only exception I
can think is if you were lucky enough to be the only
user on a particular disk/volume during
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
Think of something like a temporary load table. If you have a large load
process that is generating thousands of extents the clearing of the temp
table before the loads will kill you. During the truncate or delete SMON
clears all the extent info out of SYS.UET$ and adds them to SYS.FET
-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
As far as DDL is concerned ,Yes. I have seen Dataware House application(not
a good design) that dropping/truncating tables with lot of extents takes
longer time because of extent management. Such code must take into account
no of extents of such objects. If those objects are created
Title: RE: Extents size.
This thread has been very interesting and causing me to learn and go read some more. Which leads me to my next question. Does anyone have any good papers or urls that will discuss and explain all of the base data dictionary tables: fet$, uet$, etc.? Or anyone have
Multiple extents a good thing? YES!
I'm *depending* on many multiple extents of an interMedia index segment (the
DR$$I segment) to distribute I/O for full text indexing and queries. I
plan to distribute the datafiles of the tablespace holding the DR$$I
segment across multiple drives and set
Title: RE: Extents size.
There might be a document somewhere that explains them, but the way I've learned about those tables is to look at the source code for the dba_ views. Go through those views one by one, and look at the tables behind the views.
-Original Message-
From: [EMAIL
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
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
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
). 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
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: [EMAIL PROTECTED]
Fat City Network
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
To: Multiple recipients of list
ORACLE-L [EMAIL PROTECTED]
Micro.com cc:
Sent by:Subject: RE: Extents size
Oracle 8.0.5.1.1
I am getting the ORA-01630 max number of extents (505) for a temp segment in
my temporary tablespace
message when I do a select count(*)
I have two tables,
1. has 300,000+ rows
2. has 1,200,000 rows
I retrieved the numbers by performing a select count(*) from xx.
I
if there is
enough free memory left on the server and it will accomodate new memory
demand by all the sorts that may take place. But resizing temp extents to
larger size would help.
HTH,
Regards,
- Kirti Deshpande
Verizon Information Services
http://www.superpages.com
-Original
greater than the first datafile. But I still keep getting the
ORA-01631 Max extents reached for table "A"
error.
What do I do resolve this error?
I am using 8K as the DB block size on SOlaris with
Oracle 816. It is not a prod environment instead a dev environment.
Thanks
Sanjay
Check the maxextents value assigned to the table:
select max_extents from dba_tables where table_name = 'your_table_name';
Next, see how many extents the table is actually using:
select count(*) from dba_extents where table_name = 'your_table_name';
I'll bet the numbers are equal. If so
Hi Sanjay,
Issue this command as the table owner:
alter table A maxextents unlimited;
Check the max extents for the table and use an appropriate value for your
application. Unlimited will allow the table to grow to the size of free
space in the tablespace.
HTH,
Rocky
--- Sanjay Kumar [EMAIL
Title: RE: Max Extents Error
-Original Message-
From: Sanjay Kumar [mailto:[EMAIL PROTECTED]]
Sent: jeudi, 8. mars 2001 11:01
I have a table which consists of Long Raw column in addition to other columns of Number and Varchar2 datatypes.
I have the datafile size set to 2GB
assigned to the table:
select max_extents from dba_tables where table_name = 'your_table_name';
Next, see how many extents the table is actually using:
select count(*) from dba_extents where table_name = 'your_table_name';
I'll bet the numbers are equal. If so:
Alter your_table_name storage
Apparently, it's a widely held myth that a large # of extents (let's
say "BETWEEN 5 AND 1000") per table segment is bad for performance. Yet
the same sources who label the belief mistaken persist in pushing for
fitting all of a table in the INITIAL extent.
And that confuses the heck
This really depends on the type of access. If it's random using indexes, the extents
don't matter much. If you do a lot of full table scans, and the extents are scattered
all over, there may be a performance degradation. YMMV as always.
[EMAIL PROTECTED] 02/27/01 01:06PM
Apparently, it's
101 - 157 of 157 matches
Mail list logo
