Hi Tanel,
I think there's a logical reason why ASSM is designed to behave as you
describe below.
There's a bit of a balancing act going on here between nice, efficient
performance of inserts vs. nice compact, efficient use of storage within a
segment. The issue that Oracle has is that it has no r
I invalidated the buffer cache for my tablespace, and saw all the scattered
reads from start to end, but no sequential reads whatsoever. (9.2.0.4 on
W2k)
Tanel.
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Thursday, November 13, 2003 12:04 AM
I think it will behave the same in serial scans but we may not see any waits
since there is good chance the blocks are cached in buffer cache.
Waleed
-Original Message-
Sent: Wednesday, November 12, 2003 4:45 PM
To: Multiple recipients of list ORACLE-L
Yep, that's exactly what I thought
Yep, that's exactly what I thought, that due migration the row might go to a
PX granule read by another slave... but I still don't see enough reason why
migrated rows are handled different from serial scan. During serial scan you
also might have rows in beginning of segment migrating to end of it,
Maybe, the process that read the block use the same logic to pass the rows
out, no matter if it FTS or direct block read. So this logic resolved
migrated rows in the block that was passed to him.
Yechiel Adar
Mehish
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PR
Using PQ, the segment is split into multiple sub-segments using rowid range
scan.
Since there is no guarantee that the migrated row will be in the same
sub-segment that has the pointer to the migrated row, the PQ slave might
need to resolve the issue real time.
I mean the migrated row might exist
Tanel,
Maybe a PQ FTS needs to resolve migrated rows immediately as it's possible
that the migrated row is located in a block that's allocated to a different
PQ slave.
Chris
-Original Message-
Sent: 12 November 2003 15:49
To: Multiple recipients of list ORACLE-L
As a strange thing, fro
As a strange thing, from 10046 trace I saw that normal table scanning was
done using direct reads, this was expected behaviour, but the lookups of
migrated rows were reflected as 'db file sequential reads'. And even more,
there were 3 subsequent sequential read waits for the same datablock in a
row
I believe it's direct read from files in parallel execution, nothing gets
read from cache.
Cached blocks for the table get flushed to files before the direct read.
Regards,
Waleed
-Original Message-
Sent: Wednesday, November 12, 2003 7:10 AM
To: Multiple recipients of list ORACLE-L
Ye
Yep, the situation can get bad for parallel execution, especially if blocks
read aren't cached...
But for serial FTS I haven't seen such a problem, I did even a test to
verify it on 9.2.0.4, and did see behaviour as I expected - all blocks were
scanned using multiblock reads and rows were returned
Hi Mladen,
Don't desert me now, I thought we've come a long way !!
Although, I'm not a listed Oracle guru, let me try and explain further ;)
PCTFREE works in almost exactly the same way as it does for non-ASSM
objects. PCTFREE determines how much of the block we want to reserve for
subsequent upd
Actually row migration is a big problem for FTS also(whether serially or
using PQ).
You end up waiting for too many "db file sequential read" single block reads
instead of
MBRC in (direct path read, db file scattered read)
Regards,
Waleed
-Original Message-
Sent: Tuesday, November 11, 2
> From that, it would follow that free lists as such are gone in ASSM
tablespaces
> and are replaced by bitmaps. As Richard Foote has shown, PCTFREE is not
ignored,
> but without free lists, it doesn't make much sense. Does any of the gurus
(Cary, Steve,
> Jonathan, Wolfgang, Pete Sharman) have any
Int the note 247752.1 named "Oracle9i Space Management Demystified" oracle says
the following:
---
AUTOMATIC SEGMENT SPACE MANAGEMENT
ARCHITECTURE Oracle9i introduces a new way of managing free space within a
segment us
build any indicies.
>>
>>HTH,
>>--
>>Alan Davey
>>[EMAIL PROTECTED]
>>
>>
>>
>>On 9/25/2002 11:38 AM, Hemant K Chitale <[EMAIL PROTECTED]>
>wrote:
>> >
>> >Let me clarify my original question.
>> >
>> >
s in existing
>extents immediately behave such that they allow only one row
>per block ? Or would only new blocks in new extents take
>the PCTFREE 99 and PCTUSED 1 attributes ?
> >>
>
>I would guess the following Hemant,
>After changing the PCTFREE/PCTUSED values I do not
;
>
>On 9/25/2002 11:38 AM, Hemant K Chitale <[EMAIL PROTECTED]> wrote:
> >
> >Let me clarify my original question.
> >
> >I do not expect the FreeList for a table to get updated instantaneously
> >after I change the PCTFREE/PCTUSED.
> >What
tion.
> >
> >I do not expect the FreeList for a table to get updated instantaneously
> >after I change the PCTFREE/PCTUSED.
> >What I meant by "is the effect ... immediate" is that do the
> >new values come into play immediately -- even for existing blocks.
>
ew blocks in new extents take
the PCTFREE 99 and PCTUSED 1 attributes ?
>>
I would guess the following Hemant,
After changing the PCTFREE/PCTUSED values I do not think existing blocks
will be changed until sufficient rows are deleted so that a block will be
available on the freelist.
If as y
<[EMAIL PROTECTED]> wrote:
>
>Let me clarify my original question.
>
>I do not expect the FreeList for a table to get updated instantaneously
>after I change the PCTFREE/PCTUSED.
>What I meant by "is the effect ... immediate" is that do the
>new values c
Let me clarify my original question.
I do not expect the FreeList for a table to get updated instantaneously
after I change the PCTFREE/PCTUSED.
What I meant by "is the effect ... immediate" is that do the
new values come into play immediately -- even for existing blocks.
Suppos
RE: Is the effect of modifying PCTFREE/PCTUSED immediate ?
Well I was sure about it until you had the temerity to question me :)
I think we agree on extents sizes not being changed after the event so it
is
now a discussion on whether changes to a pctfree/pctused are
retrospective.
I contend tha
temerity
> to question me :)
> I think we agree on extents sizes not being changed
> after the event so it is
> now a discussion on whether changes to a
> pctfree/pctused are retrospective.
>
> I contend that if a table is fully loaded upto its
> pctfree/pctused limits
>
This was on 8.1.7 on Linux.
>
> It's in the archives if you care to look for it.
>
> Jared
>
>
>
>
>
>
> [EMAIL PROTECTED]
> Sent by: [EMAIL PROTECTED]
> 09/24/2002 09:08 AM
> Please respond to ORACLE-L
>
>
> To: Multiple
[EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
09/24/2002 09:08 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject:RE: Is the effect of modifying PCTFREE/PCTUSED immediate ?
Well I was sure about i
.
> >
> > HTH
> >
> > John
> >
> > -Original Message-
> > Sent: 24 September 2002 10:58
> > To: Multiple recipients of list ORACLE-L
> >
> >
> >
> > Is the effect of modifying PCTFREE/PCTUSED
> immediate ?
> &
Well I was sure about it until you had the temerity to question me :)
I think we agree on extents sizes not being changed after the event so it is
now a discussion on whether changes to a pctfree/pctused are retrospective.
I contend that if a table is fully loaded upto its pctfree/pctused limits
ve only in new Extents ? In that case,
> existing blocks in existing Extents still use the old
> PCTFREE/PCTUSED parameters and keep re-entering the
> FreeList.
>
> Hemant K Chitale
> http://hkchital.tripod.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
the next one
that is created.
HTH
John
-Original Message-
Sent: 24 September 2002 10:58
To: Multiple recipients of list ORACLE-L
Is the effect of modifying PCTFREE/PCTUSED immediate ?
If I do an "ALTER TABLE PCTFREE 99 PCTUSED1",
does this take effect immediately
Is the effect of modifying PCTFREE/PCTUSED immediate ?
If I do an "ALTER TABLE PCTFREE 99 PCTUSED1",
does this take effect immediately, even for existing blocks.
[If so, existing blocks would not get new rows inserted].
Or is it effective only in new Extents ? In that case,
why does this number not surprise me ??? lol
-Original Message-
Sent: Thursday, September 05, 2002 1:18 PM
To: Multiple recipients of list ORACLE-L
42 ;>)
-Original Message-
Sent: Thursday, September 05, 2002 3:05 PM
To: Multiple recipients of list ORACLE-L
Hi
Can some on
42 ;>)
-Original Message-
Sent: Thursday, September 05, 2002 3:05 PM
To: Multiple recipients of list ORACLE-L
Hi
Can some one suggest what would be normal PCTFREE and PCTUSED for following
type of tables?
TYPE A: High rate of insert/delete but less update
TYPE B: High rate of updat
Hi
Can some one suggest what would be normal PCTFREE and PCTUSED for following
type of tables?
TYPE A: High rate of insert/delete but less update
TYPE B: High rate of update but less insert/delete
TYPE C: Large objects used for read mostly less DML operations
Type D: High rate of DML operations
I don't think this is true anymore. At one time, PCTUSED had to be set to
"1", but as of 8i, at least, it will be put back on the freelist when empty
if PCTUSED=0. (I haven't tested it myself, but I believe Dave Ensor.)
-Don Granaman
[OraSaurus]
- Original Message -
To: "Multiple recip
uter Services
[EMAIL PROTECTED]
> -Original Message-
> From: Sinard Xing [SMTP:[EMAIL PROTECTED]]
> Sent: Mon, February 18, 2002 11:08 AM
> To: Multiple recipients of list ORACLE-L
> Subject: 8i Concept PCTFREE & PCTUSED are 0
>
> Hi all,
>
> Oracle 8i (81600)
>
ge-
> From: Sinard Xing [SMTP:[EMAIL PROTECTED]]
> Sent: Mon, February 18, 2002 11:08 AM
> To: Multiple recipients of list ORACLE-L
> Subject: 8i Concept PCTFREE & PCTUSED are 0
>
> Hi all,
>
> Oracle 8i (81600)
>
> I create user Panadol and give
[EMAIL PROTECTED]
> -Original Message-
> From: Sinard Xing [SMTP:[EMAIL PROTECTED]]
> Sent: Mon, February 18, 2002 11:08 AM
> To: Multiple recipients of list ORACLE-L
> Subject: 8i Concept PCTFREE & PCTUSED are 0
>
> Hi all,
>
> Oracle 8i (81600)
Hi all,
Oracle 8i (81600)
I create user Panadol and give unlimited quota on TBS.
as Panadol I create 1 table like this:
SQL> CREATE TABLE mytable(
2 col1 char(3),
3 col2 char(4),
4 col4 varchar2(4)
5 )
6 PCTFREE 0
7 PCTUSED 0
8 STORAGE (
9INITIAL 20K
10
sert is processed it
> goes thorugh the freelist
> and checks for space in the block. The performance
> hit comes when too many
> blocks have to be checked for adequate space. Does
> that make sense?
>
> Now some people on this list have debated that
> PCTFREE/PCTUSED is so l
horugh the freelist
and checks for space in the block. The performance hit comes when too many
blocks have to be checked for adequate space. Does that make sense?
Now some people on this list have debated that PCTFREE/PCTUSED is so low
level and that performance really is not affected that mu
Title: RE: Setting PCTFREE & PCTUSED in a Datawarehouse
Hi Vikas,
The docs should have discussed the reason why. High pctused can lead to a block going on and off the freelist frequently as deletes/inserts are processed, and also can leave the amount of free space inadequate for a
I read in the Oracle docs that setting a high PCTUSED ( such as 60/70) would
increase the cost of INSERTS to the table. Can someone explain this , pleas
e!
thanks
vikas
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Vikas Kawatra
INET: [EMAIL PROTECTED]
Fat City N
MY DB_BLOCK_SIZE = 8K
- Original Message -
From:
Mark
Leith
To: Multiple recipients of list ORACLE-L
Sent: Friday, May 18, 2001 12:30 PM
Subject: RE: PCTFREE/PCTUSED
What
is your DB_BLOCK_SIZE?
-Original Message-From: [EMAIL PROTECTED] [mailto
What
is your DB_BLOCK_SIZE?
-Original Message-From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]On Behalf Of Bunyamin K.
KaradenizSent: Thursday, May 17, 2001 04:51To: Multiple
recipients of list ORACLE-LSubject:
PCTFREE/PCTUSED
PCT_USED = PCT_FREE + 1 row sizePCT_USED
PCT_USED = PCT_FREE + 1 row sizePCT_USED +
PCT_FREE < 100 Are the rules .
But what does 1 row size mean here?
For ex . My pctfree=30
1 row
size (avg.) = 150 bytes .
Then
what is PCT_USED ?
TIA
45 matches
Mail list logo
