Niall,
I played around with autoallocate on 8.1.7 a while back and came to the
same conclusions as yourself.
Chris
-Original Message-
Sent: 13 October 2003 21:54
To: Multiple recipients of list ORACLE-L
A week or so ago Jesse (I think) suggested a test to see whether
auto-allocate
None whatsoever that we've seen. We've been running this way (until we can
convert our remaining TSs to LMT) for over a year now on 8.1.7.4 on HP/UX
11.0 32-bit.
Rich
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI
Title: RE: LMT
Nope. but my experience is strictly on 9i related to LMT
Raj
Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts
I use the A) strategy for medium BD's with good
performance behavior, for the fragmentation issue a
good guide is the document:
How to stop fragmentation and start living.
you can find it in metalink..
regards,
Gabriel
--- AK [EMAIL PROTECTED] wrote:
At present we have one tablespace
ak,
I have located my indexes in different tablespaces according to size
of the index and catagory. Small indexes are in one tablespace with 4K
extents and medium in 1M extents with the large in 20M extends. The
tables that are partitioned also have the indexes partitioned. Some
tables span
Hi!
Make sure your extents are in size or multiples of
your block_size*db_file_multiblock_read_count. This might help in performance
when doing index fast full scans. Also, if you use striped disks, you might want
to match it with stripe width.
But number of extents doesn't cause any
AK
The link for How to Stop Defragmenting and Start Living is here:
http://metalink.oracle.com/cgi-bin/cr/getfile_cr.cgi?239049
http://metalink.oracle.com/cgi-bin/cr/getfile_cr.cgi?239049
This is a classic paper and I consider it essential to carefully study this
paper before you embark on your
AK
None that I'm aware of. That is how I moved my tables into LMT, a few
at a time. You may want to check Metalink for any bugs related to your
specific Oracle version.
Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
-Original Message-
Sent: Monday,
Jonathan wrote
I'd test what happens if the algorithm says 'I need a 64m extent'
and
there is (say) 24m free space left. IIRC you do get an 'unable to
allocate extent blah' error but I can't swear to it, and it wouldn't
at
all surprise me if the behaviour changed in a later release.
ED]
03/17/2003 12:28 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
cc:
Subject:Re: Autoallocate (was Re:
LMT monitoring)
No.. that should be:
With great power comes great responsibility.
Regards
Jonathan
Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
cc:
Subject:Re: Autoallocate (was Re:
LMT monitoring)
No.. that should be:
With great power comes great responsibility.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Now available One-day tutorials:
Alex
If you do not specify the UNDO TABLESPACE when creating the database then
AUTOEXTEND is set to ON. I was able to alter that to OFF.
The point of the UNDO is that it is automatically managed by Oracle. If
you can't cope with that, or you decide that doesn't work well for you, then
you
Yet another reason for avoiding Automatic Undo -
one little accident can haunt you for ages.
It's also a major pain to find out exactly what does
go on in extreme cases because of the massive
delay between UNDO becoming redundant and
smon dropping it.
Regards
Jonathan Lewis
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:RE: Autoallocate (was Re: LMT monitoring)
Alex
If you do not specify the UNDO TABLESPACE when creating the database
then
AUTOEXTEND is set to ON. I was able to alter that to OFF.
The point of the UNDO
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:RE: Autoallocate (was Re: LMT monitoring)
Alex
If you do not specify the UNDO TABLESPACE when creating the database
then
AUTOEXTEND is set to ON. I was able to alter that to OFF
respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:RE: Autoallocate (was Re: LMT monitoring)
Alex
If you do not specify the UNDO TABLESPACE when creating the database
then
AUTOEXTEND is set to ON. I was able to alter
[EMAIL PROTECTED]
cc:
Subject:RE: Autoallocate (was Re: LMT monitoring)
Jared - When it comes to comparing databases, a lot comes down to
perception. Oracle would like to market itself to small sites that don't
even have a DBA, otherwise if forfeits those accounts
(was Re: LMT monitoring)
Jared - When it comes to comparing databases, a lot comes down to
perception. Oracle would like to market itself to small sites that don't
even have a DBA, otherwise if forfeits those accounts to Microsoft. Now,
when the MS salesperson says Oracle takes a lot more maintenance
No.. that should be:
With great power comes great responsibility.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Now available One-day tutorials:
Cost Based Optimisation
Trouble-shooting and Tuning
Indexing Strategies
(see http://www.jlcomp.demon.co.uk/tutorial.html )
]
Sent by: cc:
[EMAIL PROTECTED] Subject: Re: Autoallocate (was Re:
LMT monitoring
yes, well, that too. :)
Jonathan Lewis [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
03/17/2003 12:28 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:Re: Autoallocate (was Re: LMT monitoring
PROTECTED] wrote: yes, well, that
too. :)
Jonathan Lewis [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
03/17/2003 12:28 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
cc:
Subject:Re: Autoallocate (was Re
How about UNDO tablespace in 9.2?
It gets created with autoallocate, and there is no way to change it or
specify any parameters for undo segments.
Each segment extended as needed, and when shrinked deallocated some extents
not necessary the last, than allocate new extent.
Alex.
- Original
Based on these two points:
The version is 8.1.7.1.0
The table is populated by sqlldr direct path.
I have a bit of gossip (i.e. someone I know
told me that someone he knows told him
that he'd heard that ...) there have been
cases where parallel execution slaves have
applied extent trimming in
ORACLE-LSubject: RE: LMT monitoring
Here is my interpretation of algorithm suggested by
Conner, (I'll get to others too)
/* CASE WHEN
initial_extent 1m THEN CASE WHEN EXTENTS 16 THEN
NEXT = 64k, WHEN
EXTENTS 80 THEN NEXT = 1m, WHEN
EXTENTS 200 THEN NEXT = 8m, ELSE
NEXT
Notes inline
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Now available One-day tutorials:
Cost Based Optimisation
Trouble-shooting and Tuning
Indexing Strategies
(see http://www.jlcomp.demon.co.uk/tutorial.html )
UK___April 8th
UK___April 22nd
Denmark May
] Subject: RE: LMT monitoring
03/10/2003 02:02
PM
Please respond to
ORACLE-L
As mydata load continues, the saga continues. The simplistic algorithm does
not hold
Can anyone explain
The version is 8.1.7.1.0
The report is via this query:
SELECT partition_name, extent_id, bytes/1024, bytes/1024/1024
FROM dba_extents
WHERE segment_name = 'FORMATTER_DATA_HISTORY'
ANDowner = 'KEVIN'
ORDER BY 1, 2
The file is not autoextent.
The table is populated by
Title: RE: LMT monitoring
Connor,
What on earth you are doing on this list immediately after your Wedding? Which cruise liner has internet access?? I think Disney has ...
ps: Thanks for the algorithm, let me implement and see how good my data dictionary holds up.
Raj
Not that this helps Raj much, but the algorithm does
vary if the initial size of the segment is large,
along the lines of:
case
when initial_extent 1m then
case when extents 16 then next = 64k,
when extents 80 then next = 1m,
when extents 200 then next
Title: RE: LMT monitoring
Here is my interpretation of algorithm suggested by Conner,
(I'll get to others too)
/*
CASE WHEN initial_extent 1m THEN
CASE WHEN EXTENTS 16 THEN NEXT = 64k,
WHEN EXTENTS 80 THEN NEXT = 1m,
WHEN EXTENTS 200 THEN NEXT = 8m,
ELSE NEXT = 64m
WHEN
Sounds like you are using LMTs with system allocation -
wait until you get to LMTs with system allocation and
ASS management.
Your problem is one of my favourite reasons for
sticking to LMTs with uniform extent management.
(see www.dbazine.com for an article wrote
on LMTs).
There is something
Raj
Are you using autoallocate or uniform extents. If you are using
autoallocate, wouldn't uniform extents make your task easier?
Dennis Williams
DBA, 40%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
-Original Message-
Sent: Monday, March 10, 2003 8:29 AM
To: Multiple
I admit to being sleep-deprived but I don't see how there is a
difference between monitoring dictionary managed and locally managed
tablespaces when you are talking about the inability to allocate
another extent.
It seems relatively simple to me: check the size of the next extent
that will be
Title: RE: LMT monitoring
Rachel,
in case of auto allocate, oracle used 4 or 5 (experts don't even agree on if it is 4 or 5) fixed sizes (64k ...) and based on number of existing extents it will choose when an extent of next size should be allocated. The problem is there is no set formula
Title: RE: LMT monitoring
-Original Message-
From: Rachel Carmichael [mailto:[EMAIL PROTECTED]]
It seems relatively simple to me: check the size of the next extent
that will be allocated (this can be calculated, regardless of auto
allocate, uniform or dictionary managed next
There are three (3) types of LMTs (yes, three!)
UNIFORM Extent sizes
Every extent created in the tablespace will be the same size, no matter the
storage parameters specified.
AUTOALLOCATE (System managed)
The system will decide the next extent size at creation. This is based on a
large number of
I thought (never having used autoallocate) that there was a set
formula for the allocation (I did mention I was sleep-deprived, didn't
I?)
Raj explained there is not and so I must apologize!
--- Jacques Kilchoer [EMAIL PROTECTED] wrote:
-Original Message-
From: Rachel Carmichael
ndo, FL
Sunday, April 27 8:30am - 4:30pm - Problem Solving with Oracle 9i SQL
Wednesday, May 1 1:00pm - 2:00pm - Automatic Undo Internals
Jamadagni, Rajendra wrote:
RE: LMT monitoring
Rachel,
in case of auto allocate, oracle used 4 or 5 (experts
don't even ag
Kevin
For Raj's purposes, is it possible to estimate a range? I'm thinking he
really just needs an estimate to see if he is getting close.
Dennis Williams
DBA, 40%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
-Original Message-
Sent: Monday, March 10, 2003 12:40 PM
To: Multiple
Title: RE: LMT monitoring
I was hoping someone would have the definitive answer!
A rough formula (derived from experimental observation) seems to be:
current object next extent
size (x) size
-- ---
0M x = 1M 64K
1M x = 64M 1M
64M x = 1G 8M
1G x 64M
e.g
I *knew* I had talked to someone about a formula for this thanks!
I'm only half as crazy as I thought I was.
--- Daniel W. Fink [EMAIL PROTECTED] wrote:
From my testing, I have found the following autoallocate alogrithm.
The
first 16 extents are 64k in size. The subsequent allocation
[mailto:[EMAIL PROTECTED]Sent: Monday, March 10, 2003 1:26
PMTo: Multiple recipients of list ORACLE-LSubject: Re:
LMT monitoringFrom my testing, I have found the following
autoallocate alogrithm. The first 16 extents are 64k in size. The subsequent
allocation method is the next 63
According to a good email from Dan Fink (which I've since checked to 83
extents), the size of the extents is based soley on extent counts
#extentsnext extent size
1-1564k
16-79 1m
80-199 8m
200-
As mydata load continues, the saga continues. The simplistic algorithm does
not hold
Can anyone explain these results?
PARTITION_NAME EXTENT_ID BYTES/1024 BYTES/1024/1024
-- -- -- ---
FINS_FM_DATA_CLOSED_200207
]
Sent by: cc:
[EMAIL PROTECTED] Subject: RE: LMT monitoring
Title: RE: LMT monitoring
Kevin, Dennis, JK and DF
Thanks for your input ... I am working on something to map out the extent list ... to find a pattern
Raj
-
Rajendra dot Jamadagni at espn dot com
Any views expressed here
Which version of Oracle ?
How are you getting the report ?
Is the file autoextent - if so at what unit ?
How are you filling the table ?
Is the tablespace ASS Managed ?
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Now available One-day tutorials:
Cost Based Optimisation
The paper by Juan Loaiza (How to stop ... and start...) is not really
relevant or useful for LMT's. The 160 thing should really have been 128,
but was rounded due to the allocation algorithm. With LMTs that
algorithm is not a problem anymore, so more sensible extent sizes
should be used.
IIRC, a 160m table would be in an LMT with 4m extents.
The 3 extent sizes recommended in the paper are 128k,
4m and 128m.
1) Create LMT Tablespaces with an extent size of 160k ? ( This is
ignored by the import, tables will be one extent big)
Not so. If you create an LMT of the correct
Rajesh.Rao£¬
hi, you can dump the file header and look at its contents, it can be easily found
out.
2002-08-08 10:36:00 You wrote:
I have been struggling to find the right answer to the question: What is
the bitmap header size for a uniform extents LMT? 64K, 1 block, 2
blocks
I
recipients of list ORACLE-L
Subject: RE: LMT to DMT
Why bother with WinZip?
www.gzip.org
[EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
02/14/02 10:56 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
cc
Message-
From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]]
Sent: Thu, February 14, 2002 9:54 PM
To: Multiple recipients of list ORACLE-L
Subject:RE: LMT to DMT
Why bother with WinZip?
www.gzip.org
àãø éçéàì [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED
, Mehish Computer Services
[EMAIL PROTECTED]
-Original Message-
From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]]
Sent: Thu, February 14, 2002 9:54 PM
To: Multiple recipients of list ORACLE-L
Subject:RE: LMT to DMT
Why bother with WinZip?
www.gzip.org
sys@cust9 desc dbms_space_admin
...
PROCEDURE TABLESPACE_MIGRATE_FROM_LOCAL
Argument Name Type
In/Out Default?
--
--- --
TABLESPACE_NAMEVARCHAR2
IN
the obvious
Thanks a lot. I know it is very easy. Just can't remember how to do it.
Joan
Connor McDonald wrote:
sys@cust9 desc dbms_space_admin
...
PROCEDURE TABLESPACE_MIGRATE_FROM_LOCAL
Argument Name Type
In/Out Default?
--
---
Thanks, I don't have pkzip, just winzip. Don't worry, I got it from
http://www.winzip.com/xcmdline.htm
Joan
àãø éçéàì wrote:
Hello Joan
Try : pkzip -add zipfilename *.txt
Yechiel Adar, Mehish Computer Services
[EMAIL PROTECTED]
-Original Message-
From: Joan Hsieh
reserved.
Yechiel Adar, Mehish Computer Services
[EMAIL PROTECTED]
-Original Message-
From: Joan Hsieh [SMTP:[EMAIL PROTECTED]]
Sent: Thu, February 14, 2002 4:34 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: LMT to DMT
Thanks, I don't have pkzip, just winzip
Why bother with WinZip?
www.gzip.org
àãø éçéàì [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
02/14/02 10:56 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:RE: LMT to DMT
Sorry, I can't answer you're question, but I'm curious - why do you want
to revert back to DMT? Just for my own edification... since I'm in the
process of converting to LMT myself.
-Original Message-
Sent: Wednesday, February 13, 2002 3:32 PM
To: Multiple recipients of list ORACLE-L
Well, that's my habit. if I upgrade database, I will have to know how to
downgrade. (scripts ready) if I go one way, find something wrong or I
don't like it. I always can find easy way to come back without get burn
to much. Just kidding. I am curious to know how to do it.
Joan
Seefelt, Beth
Beth/Joan,
Pretty simple actually !
Just run dbms_space_admin.tablepsace_migrate_from_local
('tablespace_name') as system.
Does not take long to run !
HTH
Srini Chavali
Oracle DBA
Cummins Inc
Seefelt, Beth [EMAIL PROTECTED]@fatcity.com on 02/13/2002
03:53:36 PM
Please respond to [EMAIL
Raj,
The AUTOALLOCATE option causes the tablespace to b created with a bitmap
containing one bit for each data block. Oracle controls the extent size.
When we create an object in the tablespace, Oracle will start out by
allocating 64KB extent to that object. As the object grows, more 64KB
(I think) you get 16 extents of 64k, 16 extents of 1m,
16 extents of 16m, etc
hth
connor
--- Jamadagni, Rajendra
[EMAIL PROTECTED] wrote: Hi all,
I tried to read through, but couldn't find the exact
answer I am looking
for. If AUTOALLOCATE is specified for LMT, how does
oracle determine
Thanks Samir,
I found this a bit confusing ... why because for one of the indexes, this is
from dba_extents ...
select segment_name, bytes, count(extent_id)
from dba_extents
where segment_name = 'STAT_TOTALS_PK'
group by segment_name, bytes
order by bytes
Segment NameBytes No Of
Rachel Carmichael wrote:
Justin,
You can't make the SYSTEM tablespace a locally managed tablespace. Oracle
won't allow it. Not even in 9i. :) And they do set up the other tablespaces
as LMTs in the demo install.
Rachel
Their 'there are rules for you and rules for us' has always left
The only issue I know of - is that Oracle won't let you do it:)
HTH
Mark
-Original Message-
Coleman
Sent: Monday, July 02, 2001 11:45
To: Multiple recipients of list ORACLE-L
Dear All,
A quick question.
Is there any issues with setting up the System Tablespace as a Local Managed
Isn't SYSTEM the original locally managed tablespace? (lower case anyways)
%^)
Jim
-Original Message-
Carmichael
Sent: Monday, July 02, 2001 4:10 AM
To: Multiple recipients of list ORACLE-L
Justin,
You can't make the SYSTEM tablespace a locally managed tablespace. Oracle
won't allow
Another alternative - as each partition has its own datafile, how about
giving each partition its own tablespace? all you need then is a simple
alter tablespace tablespace read only;
Not sure if this is applicable to your situation, but I would consider it..
Mark
-Original Message-
You can put all partitions that you would like to be read only in the read
only tablespaces.
Alex Hillman
-Original Message-
Sent: Thursday, June 07, 2001 5:14 PM
To: Multiple recipients of list ORACLE-L
List,
I have implemented Local Managed Tablespaces and Partitioned my data and
Mark,
Appology accepted ;-)
Some people flame me because they did not read my post
carefully. I said that for optimal performance the
number of extents with an 8K blocksize was 505. People
thought that was a maximum.
2147483645 is a theoritical limit. It's like the
maximum Oracle database
stephane Reply-To: [EMAIL PROTECTED] To: Multiple
recipients of list ORACLE-L Subject: Re: LMT MAXEXTENTS for 8K Blocksize
Date: Thu, 15 Feb 2001 08:06:06 -0800
Mark,
Appology accepted ;-)
Some people flame me because they did not read my post carefully. I said
that for optimal performance
hhm, I agree to an "extent":). Any good DBA should check for all objects
in a database reaching MAXEXTENTS, on a fairly regular basis. I DIDNT say
that MAXEXTENTS should have a default of 2000 for a LMT just because I'm too
lazy too get of my slim a$$ to check every now and then, I said
73 matches
Mail list logo
