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
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
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
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
I know that wuth 64 bit HP-UX and 64 bit Oracle RDBMS,
I have no problems with 1 8GB file. I don't know much
about Solaris, but I suppose the following will work:
nm $ORACLE_HOME/lib/libclntsh.so|grep lseek64
Results should be nonempty and look something like
this:
__lseek64 |
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
---
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.
In other words, all the grunt work, all the responsibility for problems
(production is managed by a
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
Rachel, if you can execute sqlplus, you can do the nm thing.
You don't need to log in as oracle.
-Original Message-
From: Rachel Carmichael [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, September 25, 2002 3:24 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: datafile sizing
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]
.
-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
34 matches
Mail list logo
