Fat Pig Replication
- From: Stephen.Lee@xxxxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 15 Apr 2004 11:12:54 -0500
I was doing some testing on building read-only, simple snapshots on prebuilt
tables. The table prompting this post has AVG_ROW_LEN = 87, number of rows
= 8484544, sum(blocks) = 114997 with database block size = 8192. There are
four indexes on the table which include 6 of the table's 19 columns. The
indexes overlap. One index includes all 6 of the columns; two of them
include 5 of the 6 columns; another includes 2 of the 6 columns. Don't look
at me, I didn't do it. What I think happened was the app was written on
Oracle 7, and column order in the indexes had to match column order in the
SQL. The company that wrote the app is gone now, and nobody wants to touch
it. Now that I've covered the preliminaries (I think), here's the deal.
Create a duplicate, empty table in database #2. Drop all constraints other
than the primary key. DB #2 tablespace is locally managed with uniform size
256K. The indexes go in another tablespace that is locally managed with
uniform size 256K.
The files for temp and rollback tablespaces are started at 1GB each and set
to autoextend next 500M.
create snapshot xyz on prebuilt table refresh force with primary key as
select * from bubba.xyz@db#1;
An empty snapshot gets created.
exec dbms_snapshot.refresh('XYZ','C') -- (Recall, the target table starts
empty, so there are no existing rows to be deleted.)
During this whole process, there is NO other activity in either database.
NOW, what happens is that this ends up using 1.7 GB of TEMP tablespace and 4
GB on the rollback tablespace. Which makes one ask the oft asked, and
possibly the patron saint of all IT questions: What the hell?! I expected
that the sort usage would be related to index builds. But no, the SQL
associated with V$SORT_USAGE appeared to be updates to X$ tables (maybe
something to do with block or space allocation?). The complete refresh
apparently keeps track of a few things so that you either get your all nice
and proper snapshot, or you get nothing. I can see that maybe some
additional rollback gets used to keep track of things, but I would have to
say that 4 Gig of rollback is seriously extreme; we're talking 5.7 Gig of
temp and rollback in order to run a complete refresh on the previously
described table which isn't all that big (even if we include the indexes).
The only thing I can come up with is that the 256K uniform size might
require Oracle to keep track of additional slots. But 5.7 Gig of temp and
rollback?! The reason for the small extent sizes is that all the other app
tables are small tables with small indexes, and we maximize the return on
Oracle per-CPU licensing by cramming those development databases onto 4-CPU
boxes with 16 Gig of RAM until the memory has been sucked dry and the box
starts swapping. We get about 30 - 35 databases per box.
One option, in a case where both databases are quiet, is to copy the rows
from db#1 to db#2 using a non-replication technique, then create the
snapshot. But in the real world, you can't expect your source database to
remain unchanged while copying the data, so the snapshot must be created and
refreshed through the snapshot mechanism.
So the question here (at long last) is: Is doing this on a prebuilt table
necessarily the fat pig operation I experienced with my test, or there a
slicker way of doing this? Or did I miss some important step? One idea is
to try pre-allocating space for the table and indexes. But, it seems
unreasonable for Oracle to require this to prevent hog entrails from being
smeared all over the place.
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
