PCTUSED comes into play when rows are deleted from the block. If enough
data is deleted from a block to cause the block to fall below 60% used
(PCTUSED), the block goes back on the freelist for subsequent
inserts/updates.
-Original Message-
Sent: Tuesday, November 04, 2003 3:09 PM
To: Mul
Your example block can be 60% full and not be on the free list.
Once your block reaches 90% full, it is removed from the free list.
It will not be put on the free list again until used space in the block
falls below PCTUSED, which is 40% in your example.
So, a block fills up, it is removed from
To use your numbers, the block can fill to 90% (100-PCTFREE) at which
time it comes off the freelist.
If you delete rows until the block falls below 40% used (PCTUSED), the
block will go back on the freelist.
-Original Message-
Sent: Tuesday, November 04, 2003 3:09 PM
To: Multiple recipie
OK, I used to teach DBA courses and that was one of my favorite topics so let
me give it a shot here:
1) If the free space percentage in the block falls below PCTFREE, the block is
taken off the free list. Heuristically speaking, we can say that oracle does
its best to keep the block PCTFREE
Hi!
Note that a block can come off freelist also when it's usage is under
PCTFREE but above PCTUSED *and* an insert is attempted, but rejected for
this block because it would have filled the block above PCTFREE.
Tanel.
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EM
--- Mladen Gogala <[EMAIL PROTECTED]> wrote:
[snip]
>
> 2) When the block is taken off the free list and records are deleted,
> the block is not returned to the free list until the percentage of
used
> space doesn't fall below PCTUSED.
Mladen--
You did mean "percentage of used space falls below
> Your example block can be 60% full and not be on the free list.
> Once your block reaches 90% full, it is removed from the free list.
So, you are implying that it can be removed from the free list
based on the PCTFREE value too, right? So far I was under the
impression that it can be removed fr
At 06:34 PM 11/4/2003, you wrote:
> 1) If the free space percentage in the block falls below PCTFREE, the
block is
> taken off the free list. Heuristically speaking, we can say that oracle
does
> its best to keep the block PCTFREE free.
So, if pctfree is 10%, oracle does its best to keep it full
At 06:34 PM 11/4/2003, you wrote:
> So, PCTFREE and PCTUSED are kind of like boundary values, or kind-of-like
> FREE SPACE IS BETWEEN PCTUSED and PCTFREE values, right?
I think I should have said :
> USED SPACE IS BETWEEN PCTUSED and PCTFREE values, right?
maa
--
Please see the official ORAC
Ummm ... Mladen?
MG> 1) If the free space percentage in the block falls
MG> below PCTFREE, , the block is
MG> taken off the free list.
Don't you mean if the free space percentage goes
*above* the value in PCTFREE?
Unless I'm reading you incorrectly, don't you mean
that, say, if the PCTFREE val
No, I don't mean that. If the free space percentage goes above PCTFREE, that
means that there is more then PCTFREE % free space in the block. Block is
thus eligible for free list. If the percentage of free space falls below
PCTFREE, that means that there is less then PCTFREE % of free space.
I did. Thanks.
On 2003.11.04 19:29, Paul Baumgartel wrote:
--- Mladen Gogala <[EMAIL PROTECTED]> wrote:
[snip]
>
> 2) When the block is taken off the free list and records are deleted,
> the block is not returned to the free list until the percentage of
used
> space doesn't fall below PCTUSED.
Mlad
On 2003.11.04 19:54, Maryann Atkinson wrote:
At 06:34 PM 11/4/2003, you wrote:
> 1) If the free space percentage in the block falls below PCTFREE, the
block is
> taken off the free list. Heuristically speaking, we can say that oracle
does
> its best to keep the block PCTFREE free.
So, if pctfre
Or due to lack of free ITL slots during inserts
--
Denny Koovakattu
Quoting Tanel Poder <[EMAIL PROTECTED]>:
> Hi!
>
> Note that a block can come off freelist also when it's usage is under
> PCTFREE but above PCTUSED *and* an insert is attempted, but rejected for
> this block because it w
OK.
I think we're essentially saying the same thing. But
in two different ways.
--- Mladen Gogala <[EMAIL PROTECTED]> wrote:
> No, I don't mean that. If the free space percentage
> goes above PCTFREE, that
> means that there is more then PCTFREE % free space
> in the block. Block is
> thus
Uh, oh. No wonder I confused you with what I wrote
Mladen.
The below should read if the PCTFREE value is 10, NOT
90.
See what happens when you compose emails when taking
antihistamines. Don't worry. I'm not doing any
storage tuning at the present moment ... :)
Melanie
--- Melanie Caffrey <[E
On Tue, 2003-11-04 at 15:34, Mladen Gogala wrote:
> You can test it by setting up a table
> with PCTFREE+PCTUSED=100. In other word, the answer to your question is that
> two parameters are needed to reduce the overhead of the free list maintenance.
Well, geez Mladen, I was trying to make her
The answer is that
free list handling is overhead, which means that the database is working on
its own structures and not working on the user data. It's easy to conceive a
busy transaction table to which records are frequently added and from which
they're frequently removed. Having only one parame
On the other hand, you might have overallocated the space, which would
leave plenty of blocks on the free list, thus minimizing the impact.
These things are best seen on "almost full" tables with things like
row chaining, row migration, waits on ITL entries and other lovely
things. Looks like you'
CREATE TABLESPACE DATA01
DATAFILE '\data01.dbf' size 8M reuse
AUTOEXTEND ON NEXT 4096M MAXSIZE 32M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO
Our prod sys is using 8i on solaris, but I quickly tried that on 9i
on my own pc running on XP, before I go try it anywhere else,
an
I have a hunch that it will fail on Oracle 8i and with the message like
"SQL command not properly terminated", with the asterisk below the word "SEGMENT".
It's just my intuition.
On 11/07/2003 03:04:31 PM, Maryann Atkinson wrote:
>
> >CREATE TABLESPACE DATA01
> >DATAFILE '\data01.dbf' size 8M re
Hi (again) Mladen,
I'm sure I mentioned this previously but ASSM only deals with FREELISTS,
FREELIST GROUPS and PCTUSED (with possibly significant overheads).
You still need to set *PCTFREE*, which means you can still have over
allocation of space if you set it too high, you can still have row mi
Richard, here is what the concepts manual says (quoted):
"Segment Space Management in Locally Managed Tablespaces
When you create a locally managed tablespace using the CREATE TABLESPACE
statement, the SEGMENT SPACE MANAGEMENT clause lets you specify how free and
used space within a segment is
Hi Mladen,
Yes, I can offer some additional information.
Firstly, let me extend your quote from the Concepts manual where immediately
afterwards it says (quote) :
"Free lists have been the traditional method of managing free space within
segments. Bitmaps, however, provide a simpler and more eff
> We haven't even touched the subject of ITL entries which is also
unaffected
> by ASSM.
By the way, in 9i there is one interesting issue with ITLs, that every table
datablock gets 2 ITL slots by default, even if INITRANS and MAXTRANS are set
to 1. And blocks formatted due direct path inserts will
Hi!
> I'm not entirely in agreement with the quote (that's a different story)
but
> notice there is no mention of PCTFREE.
Yes, this is a deficiency of documentation. PCTFREE is still used and needed
in ASSM segments. As you said, value for it can not be tuned automatically,
since Oracle doesn't
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Monday, November 10, 2003 9:54 AM
> Note that ASSM bitmaps track "freeness" not "fullness", to be correct in
> terminology.
Hi Tanel,
At restaurants, I always say to the waiter that my glass of wine
Thanks, Richard. My PC died this weekend (it's getting CPR right now)
and I couldn't respond. I believe you now, but I still want to know
what Metalink has to say about it. The documentation didn't do a
very good job of explaining this thing.
On 11/10/2003 04:19:29 AM, Richard Foote wrote:
>
I have some PL/SQL code which looks for chained rows
once a week and unchains them when the chained rows
count exceeds 5% of the total number of rows. Inside
this code for each table where the unchaining occurs
I increase PCTFREE by 5 and decrease PCT used by 5.
The goal is to achieve equilibrium
I see, Charlie.
Thanks for answering.
I'm not trying to avoid chained rows, I think that
Buffer busy waits may be ocurring in the header of
this segment because the gap between the PCTUSED and
PCTFREE might be too small.
What I want is to check this and all tables as well
and set this GAP corre
Bad things will happen, it just a question of how expensive these waits
are. I have a feeling that they don't contribute much to the overall
performance problem.
Besides that pcfree/pctused will determine when blocks are put on the
freelist and when not. So probably what you want is multiple freel
Thanks for the answer ANJO,
"Besides that pcfree/pctused will determine when
blocks are put on the
freelist and when not. So probably what you want is
multiple freelists to
take care of single freelist contention."
Let me ask you something about this.
The problem of having a small gap between
P
Richard, I asked the question on the Metalink. The mighty Metalink will,
hopefully, resolve the doubt once and for all. Tom Kyte is busy so
he doesn't accept new questions right now.
--
Mladen Gogala
Oracle DBA
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mladen Gogal
Hi Mladen,
Oh, I have no doubts ;)
Hurt you still don't trust me but considering our little wager (2 tickets to
the Bowie world tour) I guess it's only fair you go to a neutral referee.
Bet when they confirm what I say they don't give as detailed an explanation
;)
Cheers
Richard
- Origina
34 matches
Mail list logo
