| 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: Move table online and update the indexes at the same time.
Just found DBMS_REPAIR.REBUILD_FREELISTS - seems that it could fill new freelists with free blocks.
You are right about updates - I didn't make distinctions between update
and insert. In addition there is BLOBs that can be extended, but it's rare
and we have hardly any migrated/chained rows.
Regards,
Alex
From: "Mark W. Farnham" <mwf_at_rsiz.com>@freelists.org on 27-07-2004
08:07 AST
Please respond to oracle-l_at_freelists.org
Sent by: oracle-l-bounce_at_freelists.org
To:
<oracle-l_at_freelists.org>
cc:
Subject:
RE: Move table online and update the indexes at the same time.
Please explain what you believe happens when you change the freelists for
an
existing table.
I do not believe information regarding freelists is stored in individual blocks, but rather freelists are lists of blocks queued up for new inserts.
Are you thinking of initial transactions and maximum transactions?
Further, I'm a bit confused that you're having freelist troubles due to
heavy update (unless you include insert in the generic "update" as opposed
to making a distinction between insert, update, and delete.) I suppose
that
if the updates are something like a huge expansion of a row by putting a
giant lob into a column, then freelists might be called for in updates by
row migration and/or row chaining. If you routinely expand rows greatly,
you
like have a different design consideration to consider re-tooling before
you
spend a lot of time reorganizing.
good luck!
mwf
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Alexandre Gorbatchev
Sent: Tuesday, July 27, 2004 6:09 AM
To: oracle-l_at_freelists.org
Subject: Re: Move table online and update the indexes at the same time.
Tanel,
Yes, PCTUSED and PCTFREE needs to be corrected as well.
Yes, sure, we do have RAC. But even without RAC we experienced some problems with low FREELISTS on heavily updated tables/indexes. Changing it without reorg. would affect only new blocks. We need to change FREELIST GROUPS and I was planning to set it higher than number of nodes we have now in anticipation of additional nodes. Thanks for warning of the "side effect" for setting several freelist groups - we have to research on it. We can't easily partition some of the tables. I wonder what other options are besides ASSM.
You've correctly pointed out the problem with IO distribution. That's another reason for reorganization.
Thanks for your points,
Alex
From: Tanel Põder <tanel.poder.003_at_mail.ee>@freelists.org on 27-07-2004
12:44 ZE3
Please respond to oracle-l_at_freelists.org
Sent by: oracle-l-bounce_at_freelists.org
To:
<oracle-l_at_freelists.org>
cc:
Subject:
Re: Move table online and update the indexes at the same time.
> By the way, when is the lock required - in the beginning or in the end?
I've not tested it, but probably in the end, when you switch your newly created table instead of the old one.
> I move the table for several reasons:
> 1.) CREATE TABLE ... AS SELECT produces the table which is much smaller
> (sometimes 2-3 times). We are trying to reduce the space usage with it.
So
> it seems the tables are space.
This seems to be an issue of incorrectly configured PCTFREE and PCTUSED.
If
you analyzed the table over time and checked the average free space in
blocks (and rowcounts+rowlens) you might get closer whats the real issue
here. In default configuration the tables PCTUSED is 40 for example,
meaning
that up to 60% of the block contents may remain empty and completely
unused
if the space usage doesn't fall below PCTUSED...
> 2.) Change storage clause - FREELIST, FREELIST GROUP or move to ASSM
(not
> sure, because it seems there are several bugs that we might hit in our
> environment)
You can change FREELISTS online without reorg. You shouldn't normally use FREELIST GROUPS if you're not in OPS or RAC evnironment. Freelist groups have the problem, that the processes which PID's map to another freelist group, don't see free blocks in other freelist groups, thus potentially wasting space in DML intensive environment. So if you're not seeing heavy segment header block contention due freelist updates in your database, you shouldn't consider freelist groups (and even if you see, then there are other alternatives to consider, like partitioning, etc.)
ASSM - again don't move to it if you don't have a RAC environment (with
continuously changing number of nodes) or you don't have special
conditions
like having rows with extremely varying sizes inserted to your table etc..
> 3.) Physical layout reorganization. Some tables are in wrong
tablespaces.
This doesn't seem like a serious problem, given that your databases availability is more important (unless you don't have serious bottlenecks due improperly balanced IO)
Tanel.
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Wed Jul 28 2004 - 02:04:53 CDT
 |
 |