Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Move table online and update the indexes at the same time.

RE: Move table online and update the indexes at the same time.

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Wed, 28 Jul 2004 10:54:07 -0400
Message-ID: <KNEIIDHFLNJDHOOCFCDKCEMGFBAA.mwf@rsiz.com> 





You should only need to use rebuild freelists if your freelists are in some
way "broken."



Freelist information does not reside in individual blocks.



If your primary problem is insert rate, when you add the freelists the
database engine should start using them without further intervention on your
part.



I don't know the exact algorithm and it is probably a trade secret, but the
coarse overview is this:



    
  
  1.   You've got blocks allocated to some segment, (for example,  a table).
This is all the space you see for the segment in dba_segments, for example.
If
   you run out of blocks that can be put on freelists and the freelists get
empty, the database engine tries to give you more extents. In the meantime,
there is a high water mark, above which the segment owns the blocks, but
they are not allocated for real use yet, just reserved for this segment.
  
  2.   There for each segment there is a master freelist, in which are
registered some subset of your table containing a mix of zero or more blocks
that are either zero to partially full and can still accept new rows or
formerly too full but shrunk enough to be returned to the freelist.
  
  3.   One or more freelist groups, each containing freelists.
  
  4.   So an inserter wants a place to insert. Look on a freelist in a freelist
group allocated to you, and you get a block. If your freelist runs out of
space, it gets more (how much? too complicated for this email) it petitions
the master freelist for more (not consulting other freelist groups, which is
why it is helpful to have multiple freelist groups for OPS and RAC, but you
burn some amount of space earlier than if you only have one freelist group).
Some sort of round robin/collision avoidance has multiple concurrent
inserters look at different freelists, so they don't queue up. So when you
add freelists, I'm not sure whether they start empty and get a chunk of
blocks the first time you get two concurrent "needers of free blocks"
(inserters, mostly), or whether it allocates blocks when you alter the
table. Probably they start empty (that's what I'd do, because why hold up
the dictionary when you already have a process to refill freelists when they
run low on demand), but it's not worth the time to figure that out unless
you're managing many many small segments so the pre-burned space costs
enough to concern you.






So in review:



segment allocation is from the tablespace (relatively big overhead)
master freelist keeps some free headroom in reserve and gets back blocks
that get "empty enough." If the master freelist runs low on demand, bump
goes the high water mark, and if needed gets some segment allocation.
(relatively medium overhead, unless it drives segment allocation in turn)
freelist groups protect a number of freelists so that a given instance
doesn't have to consult with other instances to use blocks.
individual freelists have blocks they can point needers of blocks at pretty
quickly. they get blocks from the master free list if they run low (zero?).
(This is pretty cheap.)



Thus, if your need for concurrency is what I believe it is, you just need to
add freelists. How many? How many inserters run concurrently (within
reason - and you supply the reason). Probably you don't get significant
extra wait time on freelist collisions until you have several hardworking
inserters per freelist, but since I've never exceeded the number of
concurrent inserters that would make me worry about the pre-burned space
before hitting some other hard bottleneck, such as redo log throughput, my
thumbrule is bump it up so each insert thread has a freelist. I'm not aware
of any overhead to this beyond what I've been referring to as "pre-burned"
space.



I once wondered if there could be a magic bad synchronicity between number
of freelists and number of concurrent inserters such that they always ran
low/out at the same time so they queued on each other time after time
getting space from the master freelist, but I've never seen it, and I figure
that someone has to win and get started burning free space first, so the
race condition should be sorted out on the first collision.



I hope my coarse liberties with the overall process don't inspire too many
flames, but if I've managed to misrepresent something in a major way and you
really know and are allowed to correct this, fire away! Note in particular I
made no attempt whatsoever to describe the freelist group rules, or what
happens when push comes to shove on failing segment allocations.



Now, if you're concerned using space from less than full blocks that have
fallen off the freelist for one reason or another, that is an entirely
different question.



mwf



-----Original Message-----


From: oracle-l-bounce_at_freelists.org


[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Alexandre Gorbatchev
Sent: Wednesday, July 28, 2004 3:08 AM


To: oracle-l_at_freelists.org


Subject: 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.






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
-----------------------------------------------------------------




----------------------------------------------------------------
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 - 09:52:08 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US