Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Fat Pig Replication

Fat Pig Replication

From: <Stephen.Lee_at_DTAG.Com>
Date: Thu, 15 Apr 2004 11:12:54 -0500
Message-ID: <>

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

To unsubscribe send email to: put 'unsubscribe' in the subject line.

Archives are at FAQ is at
Received on Thu Apr 15 2004 - 12:58:46 CDT

Original text of this message