| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: LMT monitoring
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.
I don't know if any tests have been done with CASE WHEN initial_extent > 8m or 64M either.
me I go for UNIFORM....
Niall
-----Original Message-----
Rajendra
Sent: 11 March 2003 16:29
To: Multiple recipients of list ORACLE-L
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 initial_extent >= 1m THEN
CASE WHEN EXTENTS < 64 THEN NEXT = 1m,
WHEN EXTENTS < 184 THEN NEXT = 8m,
ELSE NEXT = 64m )
WITH main_qry AS (
SELECT a.owner owner , a.segment_name seg, b.init init, a.extent_id
ext#, TO_CHAR(a.bytes) bytes
FROM DBa_EXTENTS a,
(SELECT owner, segment_name, bytes init FROM DBa_EXTENTS WHERE
extent_id = 0) b
WHERE b.owner = a.owner
AND b.segment_name = a.segment_name
ORDER BY a.owner, a.segment_name, a.extent_id)
SELECT owner, seg, ext#,
CASE WHEN init < (1*1024*1024) THEN
CASE WHEN ext# = 0 THEN bytes ELSE
CASE WHEN (ext# BETWEEN 1 AND 15) AND (bytes =
(64*1024)) THEN bytes ELSE
CASE WHEN (ext# BETWEEN 16 AND 80) AND (bytes =
(1024*1024)) THEN bytes ELSE
CASE WHEN (ext# BETWEEN 81 AND 200) AND (bytes =
(8*1024*1024)) THEN bytes ELSE
CASE WHEN (ext# > 200) AND (bytes = (64*1024*1024)) THEN
bytes ELSE bytes || '#' END END END END END
ELSE
CASE WHEN ext# = 0 THEN bytes ELSE
CASE WHEN (ext# BETWEEN 1 AND 64) AND (bytes =
(1024*1024)) THEN bytes ELSE
CASE WHEN (ext# BETWEEN 65 AND 184) AND (bytes =
(8*1024*1024)) THEN bytes ELSE
CASE WHEN (ext# > 184) AND (bytes = (64*1024*1024)) THEN
bytes ELSE bytes || '#' END END END END
END verify
FROM main_qry
ORDER BY owner, seg, ext#
I know it is not optimal, but it gives me what I need. But here it the thing, even this is not right ...
SELECT owner, segment_name, bytes, stragg(extent_id)
FROM DB$EXTENTS
WHERE owner = 'TCS'
AND segment_name LIKE 'ACTUAL_IMP%'
GROUP BY owner, segment_name, bytes
ORDER BY owner,segment_name, bytes
/
OWNER SEGMENT_NAME BYTES STRAGG(EXTENT_ID)
----- ----------------- ------ ------------------TCS ACTUAL_IMPRESSIONS 1048576
TCS ACTUAL_IMPRESSIONS 8388608
0,55,57,74,73,72,71,70,69,68,67,66,82,81,80,79,78,77,76,75,65,64,63,62,6
1,60,59,58,56,54,50,51,53,52
Last columns is just a comma delimited list of extent numbers (works like sum() for varchar2 strings, I can't get them sorted !!)
Raj
-----Original Message-----
Sent: Tuesday, March 11, 2003 11:14 AM
To: Jamadagni, Rajendra
Raj,
I know for a fact that Carnival does... as I was online during my cruise in February!
Rachel
--- "Jamadagni, Rajendra" <Rajendra.Jamadagni_at_espn.com> wrote:
> 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 > ------------------------------------------------------------- > Rajendra dot Jamadagni at espn dot com > Any views expressed here are strictly personal. > QOTD: Any clod can have facts, having an opinion is an art !! > > > -----Original Message----- > Sent: Tuesday, March 11, 2003 9:24 AM > To: Multiple recipients of list ORACLE-L > > > > 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 = 8m, > else next = 64m > when initial_extent >= 1m then > case when extents < 64 then next = 1m, > when extents < 184 then next = 8m, > else next = 64m ) > > and a large thank you to all those who passed on best > wishes for my wedding. It was a great day enjoyed by > all. > > Cheers > Connor > > --- "Daniel W. Fink" <optimaldba_at_yahoo.com> wrote: > > From 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 extents of 1m, the next 120 extents of > > 8m and all additional > > extents at 64m. I have tested this with segments in > > excess of 100 > > gigabytes and I did not find a new extent size. The > > first 3 sizes are > > documented by Oracle, the last one I found by > > testing and have verified > > from other research, though the author/website > > escapes me at the current > > time. > > > > -- > > Daniel W. Fink > > http://www.optimaldba.com > > > > IOUG-A Live! April 27 - May 1, 2003 Orlando, 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: > > > > > 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 > > (or I haven't seen > > > one agreed upon by Oracle ... the answer from > > Oracle is always fuzzy > > > about this). > > > > > > That's why, I don't know if the next extent of my > > table will be 64K or > > > 1M ... if someone knows a formula, I can write a > > quick script and > > > things would be easy ... but due to lack of > > formula, everything is a > > > hypothesis .. > > > > > > In case of dictionary managed, you have next > > extent size and pct > > > increase and you can predict what the next extent > > would be. This is > > > also true if you use uniformed extents in LMT. But > > it isn't easy in > > > LMT and auto allocate. It is probably as > > predictable as expecting a > > > straight like from a drunken monkey with a crayon. > > > > > > Raj > > > > > > ------------------------------------------------------------- > > > Rajendra dot Jamadagni at espn dot com > > > Any views expressed here are strictly personal. > > > QOTD: Any clod can have facts, having an opinion > > is an art !! > > > > > > >------------------------------------------------------------------------
> > > > > > >********************************************************************This
> > e-mail message is confidential, intended only for > > the named recipient(s) above and may contain > > information that is privileged, attorney work > > product or exempt from disclosure under applicable > > law. If you have received this message in error, or > > are not the named recipient(s), please immediately > > notify corporate MIS at (860) 766-2000 and delete > > this e-mail message from your computer, Thank > > > you.*********************************************************************2
> > > > > > > > > > > > > > ===== > Connor McDonald > web: http://www.oracledba.co.uk > web: http://www.oaktable.net > email: connor_mcdonald_at_yahoo.com > > "GIVE a man a fish and he will eat for a day. But TEACH him how to > fish, > and...he will sit in a boat and drink beer all day" > > __________________________________________________ > Do You Yahoo!? > Everything you'll ever need on one web page > from News and Sport to Email and Music Charts > http://uk.my.yahoo.com > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: =?iso-8859-1?q?Connor=20McDonald?= > INET: hamcdc_at_yahoo.co.uk > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > San Diego, California -- Mailing list and web hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > > > *********************************************************************This
> e-mail message is confidential, intended only for the named > recipient(s) above and may contain information that is privileged, > attorney work product or exempt from disclosure under applicable law. > If you have received this message in error, or are not the named > recipient(s), please immediately notify corporate MIS at (860) > 766-2000 and delete this e-mail message from your computer, Thank > you.*********************************************************************1
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Niall Litchfield INET: niall.litchfield_at_dial.pipex.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-LReceived on Wed Mar 12 2003 - 15:34:37 CST
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
 |
 |