What if we need Extent SIZES Greater than 20 MB to Check Fragmentation ?
-Original Message-
Sent: Thursday, September 26, 2002 2:33 PM
To: Multiple recipients of list ORACLE-L
We tend to use multiples of 1Gb and add 1 Mb to the file so that we get
2001, 10001 Mb etc
Solaris 2.8
LMT
with evenly sized extents, there is no such thing as fragmentation
anymore
and Oracle can deal with objects with numbers of extents up to about
4000 before it starts to slow down a bit.
--- VIVEK_SHARMA [EMAIL PROTECTED] wrote:
What if we need Extent SIZES Greater than 20 MB to Check
Title: RE: datafile sizing question
Rachel,
Are there any studies or papers that test and explain this new magic 4000 extents number? My manager is excited about LMT, but no so excited about number of extents. So, if there is a good paper, I can make him feel happy about this ...
Thanks
Title: RE: datafile sizing question
Do your own testing. Don't rely on
papers. Prove it yourself. It's easy.
There are two types of "performance" implied in
this discussion about extent allocation and deallocation:
performance of SQL statements like SELECT, INSERT,
UPDATE, D
Fragmentation or tablespace fragmentation does not simply mean more
than one extent, as it appears you are assuming. Also, it is an obsolete
concept where LMTs are involved, in all but a few difficult-to-imagine
situations.
Please read one or more of the following: Craig Shallahamer's All
Raj
Print http://otn.oracle.com/deploy/availability/pdf/defrag.pdf
http://otn.oracle.com/deploy/availability/pdf/defrag.pdf - very
well-written, direct from Oracle's site, so he will accept it as official.
BTW - In this paper, for Oracle 8 and above, the correct extent sizes are
120-k, 4-m,
I haven't seen any papers.. I was told this (4096 is the exact number)
in an Internals class.
However, there are lots of papers out there saying multiple extents are
not a problem, and you should be able to find them on the web.
--- Jamadagni, Rajendra [EMAIL PROTECTED] wrote:
Rachel,
Are
Title: RE: datafile sizing question
Thanks you Dennis, Rachel, Tim for the pointers.
Raj
__
Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN
Title: RE: datafile sizing question
If Next Extent Sizing is NON-Uniform for an LMT , will
the Larger Number of Extents cause Fragmentation Performance Degradation ?
If so What Number of Extents may be Considered as a
Candidate for DE-Fragmentation ?
NOTE - We have been Manually
Title: RE: datafile sizing question
I don't know if you intended to "shout", but using
color for your reply certainly does
so...
Can you prove any benefit from the
extraordinaryactions of overriding LMT extent control and using EXP/IMP,
ALTER TABLE ... MOVE, ALTER INDEX .
PROTECTED]
cc:
Subject:RE: datafile sizing question
I also have a just curious question. Do most interchange the values 1000K
for 1M or 1000M for 1G? I try to be precise in my usage, but I guess
that's
just the AR size of my personality. OK, I'll go find my pills now
PROTECTED]
09/26/2002 08:33 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
cc:
Subject:RE: datafile sizing question
I also have a just curious question. Do most interchange the values
1000K
for 1M
We tend to use multiples of 1Gb and add 1 Mb to the file so that we get
2001, 10001 Mb etc
Solaris 2.8
LMT uniform extents range from 64K to 20Mb
John
-Original Message-
Sent: 25 September 2002 19:44
To: Multiple recipients of list ORACLE-L
We created two datafiles of 16GB+64K all
10001Mb?
the uniform extent sizes map to what I'll be using as well. good to
know I'm not way off track
--- [EMAIL PROTECTED] wrote:
We tend to use multiples of 1Gb and add 1 Mb to the file so that we
get
2001, 10001 Mb etc
Solaris 2.8
LMT uniform extents range from 64K to 20Mb
John
Yes, 10Gb datafiles.
I think we have a 35Gb datafile somewhere but I have not looked at that
database myself
John
-Original Message-
Sent: 26 September 2002 12:03
To: Multiple recipients of list ORACLE-L
10001Mb?
the uniform extent sizes map to what I'll be using as well. good to
Datafile sizing affects the speed of backup and restore, since each datafile
can only be backed up or restored by one process at a time. As a result, I
try to keep datafiles at uniform sizes of 2-4 Gb max. How do such large and
variable-sized datafiles impact your backups and restores? Just
Rachel - In the whitepaper How to Stop Defragmenting and Start Living,
they make a big deal under LMT about selecting specific extent sizes, and
those optimum sizes are different for Oracle 8i and Oracle 8. For Oracle 8i
the sizes are 128k, 4m, 128m. I couldn't follow the complete logic of why
Tim,
I cannot speak for the 35Gb datafile which is a one-off but for the systems
where we have
datafiles files 4Gb we invariably use EDM to break a mirror and then
RMAN proxy against that.
The mirror is then kept off-line until we are ready to do the next backup.
At that point the mirror is
I also have a just curious question. Do most interchange the values 1000K
for 1M or 1000M for 1G? I try to be precise in my usage, but I guess that's
just the AR size of my personality. OK, I'll go find my pills now. ;) By
the way, we use a maximum size of 4Gb+8k file size but, I've never has
Darren - Let me try to clarify my answer.
1. Yes, 800-meg is not really large with today's disk systems, so that
consolidation should be no problem.
2. If you are doing this type of consolidation, I think it would be a good
time for you to take as step back for broader perspective. Specifically,
Our SAN has been setup as RAID5, so the use of single large
datafiles is probably the method I will go with, using the added
option of resizing once I read up on it some more :)
Darren
-Original Message-
Sent: Wednesday, September 25, 2002 8:13 PM
To: Multiple recipients of list
that is something I didn't consider, I will look into it further.
Thanks
Darren
-Original Message-
Sent: Wednesday, September 25, 2002 5:48 PM
To: Multiple recipients of list ORACLE-L
Is there a reason you can't just resize the existing file?
-Original Message-
Sent:
Michael - I've done it both ways, and yes, it depends on how AR you wish to
be. You can convert everything to K and get pretty precise. Or you can
simply create the tablespace and datafile, then query the free space and
create your table accordingly. Of course LMT with uniform extents changes
the
Thanks Dennis
I was going to use LMT's, I didn't mention it in my email as I thought
it would be irrelevant to the question I was asking.
The first database in question to move is oracle financials 11.5.7 which
requires you to move to LMT's
Darren
-Original Message-
Sent: Thursday,
Subject:RE: datafile sizing question
I think it's automatic on Solaris 2.8 but I don't know for sure so I'd
rather not rely on it.
I'm going with 2001M and creating about a year's worth of
tablespaces/partitions. 101 datafiles and tablespaces. Just to
START.
my aching typing fingers
Well as long as we are being AR...Don't you mean DEC/Compaq/HP
:-)
-Original Message-
Sent: Thursday, September 26, 2002 11:34 AM
To: Multiple recipients of list ORACLE-L
I also have a just curious question. Do most interchange the values 1000K
for 1M or 1000M for 1G? I try to be
:
@city.coquitlSubject: Another datafile sizing question
am.bc.ca
Okay, I'm about to create some locally managed tablespaces for some
large partitioned tables. I plan on making each datafile 2GB, with an
extent of 20M.
What I've done other times when I use LMTs is add 64K to the file size,
for the bitmap header, so that I don't waste most of an extent. But in
Hi Rachel,
I know the guy who creates the warehouses here uses 4Gb datafiles (I believe
he creates them at 4050Mb).
We are on Tru64 Unix
He is on his honeymoon at the moment so I cannot ask him his reasoning.
Regards
Lee
-Original Message-
Sent: 25 September 2002 17:33
To: Multiple
and can handle
large files.
-Original Message-
From: Rachel Carmichael [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, September 25, 2002 12:33 PM
To: Multiple recipients of list ORACLE-L
Subject: datafile sizing question
Okay, I'm about to create some locally managed tablespaces for some
Are you going with 2048M or the traditional 2000m?
A quick dirty way to not waste the space is to use 2001m or 2041m. You
'waste' a little space, but not much.
Dan Fink
-Original Message-
Sent: Wednesday, September 25, 2002 10:33 AM
To: Multiple recipients of list ORACLE-L
Okay, I'm
Title: RE: datafile sizing question
We created two datafiles of 16GB+64K all LMT autoallocate ... never gave a problem. A basic testing concluded that fixed size allocation of 128M caused unnecessary delays whereas autoallocate was much faster. I don't know the full details yet, but I'll know
AHA!I was going to go with 2000M so that's beautiful, 2001M would work
perfectly without going over. I don't mind wasting less than a meg.
I love the logic everyone here at work has. disk is cheap, don't
worry about it. Except every time I ask for more disk, I hear it's too
expensive
Rachel
---
To: Multiple recipients of list ORACLE-L
Subject: datafile sizing question
Okay, I'm about to create some locally managed tablespaces for some
large partitioned tables. I plan on making each datafile 2GB, with
an
extent of 20M.
What I've done other times when I use LMTs is add
I believe that the OS filesystem has to be configured for large file
support (on HP-UX, it's a kernel parameter) to allow files 2 GB.
I usually just add 1 MB to the file size to allow for the header.
Personally, I'd play it safe and go with however many 2001 MB files you
need to accommodate
Vogons. Let me know
if you'd like me to recite some poetry.
-Original Message-
From: Rachel Carmichael [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, September 25, 2002 3:18 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: datafile sizing question
AHA!I was going to go
]]
Sent: Wednesday, September 25, 2002 12:33 PM
To: Multiple recipients of list ORACLE-L
Subject: datafile sizing question
Okay, I'm about to create some locally managed
tablespaces for some
large partitioned tables. I plan on making each datafile 2GB, with
an
extent of 20M
I think it's automatic on Solaris 2.8 but I don't know for sure so I'd
rather not rely on it.
I'm going with 2001M and creating about a year's worth of
tablespaces/partitions. 101 datafiles and tablespaces. Just to
START.
my aching typing fingers!
--- Paul Baumgartel [EMAIL PROTECTED]
I am in the process of upgrading our databases from 8.0.5 to 8.1.7,
possibly 9i depending on application
certifications.
I currently have a tablespace that is made up of 4 - 200mb datafiles, my
first thought would be to
create a 800mb datafile and move all the data into it,
The growth of this
Is there a reason you can't just resize the existing file?
-Original Message-
Sent: Wednesday, September 25, 2002 7:43 PM
To: Multiple recipients of list ORACLE-L
I am in the process of upgrading our databases from 8.0.5 to 8.1.7,
possibly 9i depending on application
certifications.
Darren - My advice would be to read up on Locally Managed Tablespaces (LMT)
and uniform extents. This is a new feature that will ease your management
work.
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
-Original Message-
Sent: Wednesday, September 25, 2002 6:43 PM
To: Multiple
It's late at night maybe that's why I do not
understand your answer but I do not see the link
between LMT and the number/size of datafiles.
One reason of multiple datafiles id to spread IO but
since nowadays a majority of sites goes on huge disk
box using raid 5 (that's what we have, the unix
.
-Original Message-
From: Rachel Carmichael [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, September 25, 2002 3:18 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: datafile sizing question
AHA!I was going to go with 2000M so that's beautiful, 2001M would
work
, 2002 3:24 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: datafile sizing question
I'm betting that it's not 64-bit Oracle. See, I am the
development DBA.
As an employee, I get to create the scripts, but I don't get to run
them or even get access as oracle to the servers
44 matches
Mail list logo
