Transcript Reorganizations Agenda • The Goal • Mythology and other interesting anti-facts and facts… • Reorganizing Tables – – Offline Online • Entire table • Shrinking Space • Purging • Reorganizing Indexes – – Offline Online •

Reorganizations
Agenda
• The Goal
• Mythology and other interesting anti-facts and facts…
• Reorganizing Tables
–
–
Offline
Online
• Entire table
• Shrinking Space
• Purging
• Reorganizing Indexes
–
–
Offline
Online
• Entire rebuild
• Coalesce
Goal
The goal
• The Goal is to not have to reorganize
• Do not get into “ground hog day” mode
• There are long term solutions for many of the
common issues
• The trick is understanding why something happens,
so you can develop a corrective action
• EG: we purge old data, this leaves table 30%
empty, we want to reclaim this space
–
–
You could rebuild table
You could use partitioning to age old data out instead
Mythology
And other interesting
anti-facts and facts
(found in that inter-web thingy)
Indexes
If this
allphysically
indexesdeleted
wouldwhen
grow,
Oracle
indexwere
nodestrue,
are not
table
and grow
and
grow.
rows are deleted,
nor are
thegrow
entriesand
removed
from the
index. Rather, Oracle "logically" deletes the index entry and
leaves "dead" nodes in the index tree where that may be reIf index
space
were
notis reused,
used
if another
adjacent
entry
required. all indexes
would
always
to ofbeadjacent
rebuiltrows
at some
However,
when
large need
numbers
are deleted,
point
inwill
time.
it is highly unlikely that
Oracle
have an opportunity to reuse the deleted leaf rows, and these represent wasted
space in the index. In addition to wasting space, large
was
never
volumes of deletedItleaf
nodes
will true.
make index fast-full scans
run for longer periods.
Myth1.sql
Indexes
If this
were true,
all indexes
sequence
Hence,
an Oracle
index may
have four on
levels,
but only in
those areas
of the index
tree where
themost
massive
populated
columns
and
oninserts
have occurred. Oracle indexes can support many millions of
dates/timestamps
be index
unbalanced.
entries
in three levels, and would
any Oracle
that has four or
more levels would benefit from rebuilding.
It is physically impossible to have an
…
unbalanced
index. will
It is
mosttoprobable
that
Note
that Oracle indexes
“spawn”
a fourth level
only
in areas
of the index
massive
insertof
has4 occurred,
rebuilding
anwhere
indexa with
height
will
such that 99% of the index has three levels, but the index is
result
infour
an levels.
index of height….. 4
reported as
having
Indexes
Add to this the various node splitting algorithms Oracle uses
forThe
non-sequential
insertsis
and
can easily
cluster factor
a updates
metric and
thatyou
describes
seehow
why sorted
clusteringthe
factor
increases
and
can respect
become out
table
data is
with
to of
sync with reality. An index rebuild coalesces nodes and
thewith
index
key in the
given
aligns them
the underlying
table.
Now,index.
in many cases
this reduces the clustering factor, …
Rebuilding an index can never have any
However, I effect
may have
things unclearly,
the goal in
onstated
the clustering
factor
index rebuilding is not to reduce clustering factor, that is
actually a desired by-product, … Clustering factor ratios are
requires
a table
just one ofItseveral
indicators
thatreorganization
can tell you an index
needs to be investigated.
Cf.sql
Myth2.sql
Indexes
DEL_LF_ROWS is very unreliable as a
method to detect an index that needs to be
rebuilt.
The second
ruleto
of thumb
is that
the deleted
leaf rows
Contrary
popular
belief
–
deleted
rows
should be less than 20% of the total number of leaf rows. An
are
fact leaf
cleaned
out
excessive number
of in
deleted
rows indicates
that a high
number
deletes
or updates
occurred
the index
An of
index
that
is mosthave
in need
of to
a rebuild
column(s).
The to
index
should
rebuilt
to better
from time
time
willbe
not
make
itselfbalance
knownthe
tree. The INDEX_STATS table can be queried to determine
usingdeleted
this ‘technique’.
if there are excessive
leaf rows in relation to the
An index
that
will be perfectly fine in a
total number
of leaf
rows.
couple of minutes will be flagged
erroneously
Myth3.sql
Indexes
If the indexed data arrives randomly
(last_name for example) this is utter nonsense. The index might end up 50%
utilized
and a rebuild
could
make
it 90%
Indexes
that–undergo
frequent
insert,
update
and
utilized
– for need
the next
couple
minutes!
delete
operations
to be
rebuiltof
regularly
to
prevent ‘fragmentation’
Think “sweeper” index from prior slide.
They are candidates for period coalesce or
rebuilds.
Indexes
This is why atomic_refresh => false on a
materialized view might be relevant.
This is why you want to consider
partitioning
other physical
structures –
Index
space is and
not reused
within a transaction.
if you
have two indexwill
segments,
and flipthe
Hence
DELETE+INSERT
tend to increase
flop
this won’t be an issue.
size of
thepartitions,
index greatly
Knowing that this is true is the first step to
solving the problem. Or at least identifying
when you might want to coalesce/rebuild
fact1.sql
Indexes
If you leave a few stragglers behind –
delete most but not all old entries – then
the left hand side of the index might
Sweeper
indexes
are candidates
forare
periodic
rebuilds
become
“brown”.
Since you
inserting
inmonotonically
order to either values – only the right hand
side gets hit
1. Reclaim space
You could
rebuild or coalesce
2. Improve
the performance
of “selectforever.
id from tOr
order
by you
id” style
queries
could
fix it with a reverse key index
(but only if the only goal was to reclaim
space! Range scan issue)
Indexes
Well, rebuilding the table over and over
could
be anrange
optionscans,
(especially
with
If you
do large
you should
dbms_redefinition
ORDERBY_COLS)
reorganize
the table to
be sorted by the
index key
But so could B*Tree clustering, Hash
clustering, Index organized tables,
Partitioning – anything that forces
“location” on data
Indexes
corollary
This is–false
in most all general cases. It is
true in one special case – when you access
only the index and not the table and you do
so during a RANGE SCAN (not a fast full
scan)
Rebuild indexes that you range scan in a
tablespace
withIO’s
a larger
than
default
Most logical
will blocksize
be against
thethe
table,
blocksize. This will
logical IO’s by 50%.
notreduce
the index
And the detrimental side effects?
Increased contention. Special memory set
aside that cannot be used for other stuff.
More management for you. Myth4.sql
How To
For
Tables
How do I reorg a table?
• Suppose you get the question “how do I reorg a table’
• You answer quickly “Alter table T move, rebuild indexes”…
–
And say they did that, what did they get?
• Took 2 hours of downtime (big table, lots of indexes)
• Cost $$$, they could have been doing something truly
productive
• Did they achieve their goal? Not in this case, their perceived
problem was chained rows (say 1,000 of them)
• After the reorg, they still had 1,000 chained rows!
• We need to ask “WHY”, what is the goal.
• Before you do any reorganization you want to know why and
understand how the operation will solve that problem
How do I reorg a 50gig database
At a given customer site, I must reorganize a 50 Gigs 8.0.5 Prod DB in a
single day.
Note that I do not know the environment yet (I am replacing somebody
leaving on vacation without providing any analysis report. This guy only
told this DB is bad, we must re-organize it and left on vacation...).
I only know the DB is running on a Windows (NT) server, the 50 Gigs DB
is mission-critical (hosting the PeopleSoft Financials suite), the DB is
said to be highly-fragmented at the tablespace-levels and highly chained
at the table-levels but no bad-perf issue has been apparently reported.
I do not have disk space to create a new db aside the current one, I only
have One Day to successfully carry out the whole thing. What would be
the right approach ? Keeping in mind, not to loose anything on the way
... (i.e., low risk). What would be the best strategy ?
How do I reorg a 50gig database
• I asked “why”, “what is the goal”
• Answer:
–
–
Tablespace fragmented as reported by Toad
Chained rows, must get rid of chained rows
• Turns out peoplesoft uses lots of longs, won’t matter how
many times you rebuild will it.
• Fragmented tablespace – so what? 1 extent or 500 extents.
So what? They don’t drop/truncate, so “so what”
• Only answer is “don’t even think about doing this”. What
would have happened had I just answered the question!
How to for Tables
• Reorganizing Tables
–
–
–
Offline
Online – EE only
• Entire table
• Shrinking Space
• Purging
Mostly online
• Materialized view trick, all editions
How to for Tables - offline
• Two basic approaches
–
ALTER TABLE T MOVE
• Very offline
• Queries can proceed while the alter table executes
• Immediately after completion, all indexes go invalid
• That likely kills all queries till they are rebuilt
• Downtime for modifications = time to move + time to
rebuild all indexes
• Downtime for reads = time to rebuild all indexes
How to for Tables - offline
• Two basic approaches
–
Make read only + Create table as select + index +
constrain + drop old + rename new
• About same amount of downtime for writes
• Less downtime for reads (just time to drop and
rename)
• Requires more space – 2 tables, 2 of each index.
ALTER MOVE just requires 2 tables for a moment
plus 1 of each index. And then 1 table and 1 copy of
1 index plus individual ones as you rebuild them
• Much more work
How to for Tables - offline
• Two basic approaches
–
–
–
Never ever use
• exp/imp (CLM)
• Expdp/impdp
• Dump and load
Given a choice, I would always choose alter move
• Simplicity
• No loss of anything
Given need for continuous reads, CTAS
How to for Tables - online
•
Enhanced Online Table Redefinition
–
Easy cloning of indexes, grants, constraints, etc.
•
–
–
Down to 4 easy steps
1. Create new, empty table
2. Start redef (initial copy)
3. Copy_Table_Dependents, instead of manually
indexing, altering, etc
4. Finish redef
Convert from long to LOB online
Allow unique index instead of primary key
Redef.sql
How to for Tables - online
•
DBMS_REDEFINITION
–
–
–
–
–
Ranks high on the safety scale
Direct path load – can use nologging
Dependent objects can be
• Copied automatically
• Done by hand
• Combination of above
You want primary key (to avoid extra rowid column at
end of redefed table that will be dropped)
High update tables will require frequent ‘syncs’
Online Segment Shrink
•
•
•
Table fills over time
You delete rows
Lots of whitespace
–
–
•
In the past
–
–
–
•
You want to get it back
You full scan and want it
smaller
Alter table move, Alter index
rebuild
Export/Import
Offline
Not any more…
shrink.sql
How To
For
Indexes
Here is an email I got once:
.... HELP!!!! Riddle me this batman...
Why does an rebuilding an index cause increased redolog
generation AFTER the index has been built?
I have a table 35 million rows and an index (nothing is
partitioned) Transactions against this table are constant.
It's always 500,000 rows per day. This generally creates 10
logs a day
Once a month the indexes are rebuilt. (Alter index rebuild)
On the day following the indexes rebuild 50 logs are created
On the following days 45...40...35...30....25....down to 10
at 10 logs this remains constant at 10 Mining the logs we
see that we have increase INTERNAL INDEX UPDATES
Why does this happen?? Is this always the case??
How To For Indexes
• Remember – one of three things will
happen as a result of a reorganization
–
–
–
It’ll go better
It’ll not change at all
It’ll be much worse than it was before
Have metrics and live up to them
• Keep metrics
–
–
Statspack/AWR for example, see if LIO’s go down
Application level statistics
• Evaluate against them
–
–
–
–
–
–
Do an index rebuild
Come back tomorrow and verify you did more good then harm
Rebuilding can be good
Coalescing – even better (online, without the overhead)
Most of the time, it is not even needed and can do more harm
then good
Does not mean I’ve said “you never have to rebuld an index”
• Bitmaps
• Secondary indexes on IOT’s
• Text indexes for example…
How To For Indexes
• Reorganizing Indexes
–
–
Offline
Online
• Entire rebuild
• Coalesce
How to for Indexes - offline
• Two basic approaches
–
–
Drop and create
• Entirely offline
Create and swap
• Almost online in SE
• Read only during index create
swap.sql
How to for Indexes - online
• Two basic approaches
–
–
Rebuild
• Optionally online
• Need approximately 2 times the storage
• Use existing index to copy from
o Skip sort
o Less physical IO
Coalesce
• Need only current storage
• Online
• Combines logically adjacent index blocks as much
as possible
compare.sql
How to for Indexes - online
• Two basic approaches
–
–
–
–
–
If I had my way – it would be coalesce
Always online
Least space needs
No locking issues
Does not “skinny up” the interior (riddle me this
batman…)
<Insert Picture Here>
Q&A