Mogens
Mark J. Bobak wrote:
Hmm...not sure if this is what's biting you, but if you're out of ITL
slots on the block, Oracle will move to the next block on the free
list. If all the blocks on the free list have filled ITLs, Oracle will
add an extent to the free list. I don't suppose you have MAXTRANS set
to 1 or some other very low number? If so, and with concurrent inserts
happening, it's possible that you could be allocating more blocks to the
free list, even if there are tons of blocks already on the free list,
simply due to the ITL shortage. (Note that this is why you'll never see
mode 4 TX enqueue waits on insert to a table.)
If MAXTRANS isn't really low, and if you're not doing direct-load
(APPEND hint) inserts, then I'm stumped.
-Mark
On Tue, 2002-12-03 at 17:09, Fink, Dan wrote:
Hmm...Why didn't they ask for your buffer cache hit ratio?
Seriously, I've pondered this and it comes down to a question. What would
cause a transaction not to use blocks on the freelist? If a transaction
cannot use these blocks, then it must allocate new space. If the transaction
is set up so as to allocate space above the HWM, we have the same scenario.
Are there transactions that are allocating blocks off the freelist? How deep
will a transaction read the freelist to find an open block before giving up
and allocating space? Are the inserts of such size that they would not fit
into the space in the blocks on the freelist?
I don't know the answers, but it seems that the questions may offer some
clues. I can't wait to find out the real answer!
-----Original Message-----
Sent: Tuesday, December 03, 2002 1:50 PM
To: Multiple recipients of list ORACLE-L
Just for grins, here's the level of support I'm getting on my Oracle TAR:
----------------------------------
You had stated earlier:
1/ After reanalyzing the table I saw the following stats in DBA_TABLES:
num_freelist_blocks: 2266966
avg_space_freelist_blocks: 3895
Unless I'm misreading this I should have had over 8Gig available for
inserts.
2/ I've had to add another data file and it has already grown to 600 Meg.
If the table is only 600 mb, then there is no way that it can have 8 gb of
free space. Since you have a lot of blocks with some free space, you may
want to export and import the table back to re-org the table... ----------------------------------
Someone should inform these people that a table can consist of more than one
datafile...
-----Original Message-----
Sent: Tuesday, December 03, 2002 11:54 AM
To: Multiple recipients of list ORACLE-L
I had one thought.
The Freelist parameter for this table is only set to 1. Is it possible that
if it gets tied up with contention for the freelist that it grabs a new
extent?
I see that some of these blocks are being written to, the
num_freelist_blocks is now down to 2095705. But the new data file has grown
to 600 Meg.
I've opened a TAR to see what Oracle says but I'm not encouraged by the
first question they sent me (which was asking to query dba_free_space).
Jay
-----Original Message-----
Sent: Monday, December 02, 2002 6:14 PM
To: Multiple recipients of list ORACLE-L
One thing I haven't seen mentioned yet is what degree of parallelism is
defined for the table?
What is the next extent size set to?
If the table is paralleled, EACH parallel worker will grab a next extent
sized segment. (Been bit by
this a few times...)
How many indexes and are they in the same tablespace?
Ron Thomas
Hypercom, Inc
[EMAIL PROTECTED]
Each new user of a new system uncovers a new class of bugs. -- Kernighan
JayMiller@TDWater
house.com To:
[EMAIL PROTECTED]
Sent by: cc:
[EMAIL PROTECTED] Subject: RE: ORA-1653:
unable to extend table - Why?
12/02/2002 02:04
PM
Please respond to
ORACLE-L
Yep, I agree that coalescing is irrelevant in my current situation. In any
event there was no free space until I added the additional datafile but
there was the 8gig of space on the freelists.
Jay
-----Original Message-----
Sent: Friday, November 29, 2002 8:54 PM
To: Multiple recipients of list ORACLE-L
Richard,
if pctincrease is zero, and there are a large number of contiguous
smaller extents, SMON will not automatically coalesce the tablespace.
However, whether or not SMON does an automatic coalesce, if you need an
extent that is larger than any of the small ones, Oracle will coalesce
those smaller extents to make the one you need. so Jay would not have
needed to add a datafile no matter what, if he was not doing a direct
path insert.
As for meeting in person.... there is a user group meeting on Dec 12
(check www.nyoug.org for details). You can meet me, and more
importantly you can meet Tim Gorman, Dan Fink, Arup Nanda and Anita
Bardeen, also of this list. They are all presenting :)
I saw Priscilla about a month ago, haven't talked with her since.
Rachel
--- Richard Ji <[EMAIL PROTECTED]> wrote:
Rachel,__________________________________________________
What I mean to say is when there are a lot of contiguous smaller free
extents.
Then coalesce will produce a larger free extent so Jay wouldn't have
to
add a datafile for his table to grow.
On the automatically coalescing part, I believe SMON will only
coalesce
when pctincrease != 0, or has that changed? My understand could be
outdated.
With LMT one doesn't have to worry about it.
Have a Happy Thanksgiving.
PS, I am in New York too, would love to meet you in person some time.
Have
you
talked to Priscilla lately?
Richard Ji
-----Original Message-----
Sent: Friday, November 29, 2002 5:29 PM
To: Multiple recipients of list ORACLE-L
how would coalescing help even if there were a lot of smaller free
extents? Oracle would do the coalesce automatically, there would be
no
difference between manually coalescing or allowing Oracle to do it
when
a new extent was needed.
--- Richard Ji <[EMAIL PROTECTED]> wrote:
Coalescing might help if there are many smaller free extentsjust
that can be coalesced. But that still doesn't solve Jay's problem.
Because he doesn't want the table to extent at all since he just
deleted
2 million rows so there are plenty of space within the segment
itself.
Those free blocks should be used, unless he is doing a direct path
insert
which will only use space above the HWM.
Richard Ji
-----Original Message-----
Sent: Friday, November 29, 2002 2:05 PM
To: Multiple recipients of list ORACLE-L
did u coalesced the tablespaces?
-----Original Message-----
Sent: sexta-feira, 29 de Novembro de 2002 17:59
To: Multiple recipients of list ORACLE-L
Okay, I can't figure this one out. Earlier this week I got an
ORA-1653:
unable to extend table on a really big table. However this was
after IDBA_TABLES:
had deleted over 2 million rows in the table and we were only
inserting
30,000.
After reanalyzing the table I saw the following stats in
num_freelist_blocks: 2266966for
avg_space_freelist_blocks: 3895
Unless I'm misreading this I should have had over 8Gig available
inserts.services
We tried the insert again and got the same error so I added a
datafile and
it went through (using about 40Meg of space in the new datafile).
Why isn't it making use of the existing blocks on the freelist?
Oracle 8.1.7.2
Solaris 2.6
PCTFREE = 10
PCTUSED = 75
Block Size = 4K
Jay Miller
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Miller, Jay
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail messagesubscribing).
to: [EMAIL PROTECTED] (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
--services
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Paulo Gomes
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail messagesubscribing).
to: [EMAIL PROTECTED] (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
--services
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Richard Ji
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail messagesubscribing).
to: [EMAIL PROTECTED] (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
__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rachel Carmichael
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Richard Ji
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rachel Carmichael
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Miller, Jay
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?= INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
