need help with table creation, nologging, and backup

From: Ryan Gaffuri <rgaffuri_at_cox.net>
Date: 12 May 2003 10:49:33 -0700
Message-ID: <1efdad5b.0305120949.55d24ccc@posting.google.com>

This is going to be long and alot is really basic. I apologize for the length of this post. If Im unclear or leave out any important information please let me know. Im at work and posting from dejanews so I wont see any responses until I get home.

This all runs together. Im attempting improve performing on a batch update,insert,delete process we are doing.

We are on 8.1.7.3 on Sun v5.8 Our production servers are on newer versions of Sun. I cant do anything about the deprecated operating system.

Essentially we have 2 schemas. Staging and Master. Both are on the development side and the user never sees them. We rebuild our tables on the staging table every time we get a new data file in related to that table. All data is new. The old table is dropped. We do direct path inserts with SQLLDR.

We then update,insert,and delete tables in our master schema based on the new data and some business rules. We then use the transportable tablespace model to transport the master schema to our production server.

I have been unable to improve the query that performs the updates. So now Im looking to other methods. Here some things that Im considering.

Turn on nologging. I dont see any reason to log these transactions. We have backups of the datafiles that are made as soon the batch process is done. I believe we can just import the tablespace back to the database if we have a media problem. Am I correct here? Any other possibilities?
I ran a table density report I got off of Ixora and noticed that several of our tables are using way too many blocks. One was only using 9.5% of the allocated blocks. I did an

ALTER TABLE <TABLE_NAME> MOVE; This eliminated up to 3.5GB of space from one of the tables.

Now How do I size my table creation settings? Im concerned about ROW MIGRATION with all the updates. I can monitor for this with reports and then re-create the tables if I see a problem, but would prefer to keep this at a minimum. We are not using Locally Managed tablespaces. I would prefer that we do, however, I am currently not in a position to change this. I have to deal with the situation at hand. Here is our current settings for table creation. I think its mainly defaults. If you require more information on this please let me know.

USING INDEX

     TABLESPACE MASTER PCTFREE 10
     STORAGE ( INITIAL 199024640 NEXT 19906560 PCTINCREASE 10 ))

   TABLESPACE BO_OWNER_MASTER
   PCTFREE 10
   PCTUSED 40
   INITRANS 1
   MAXTRANS 255
  STORAGE (
   INITIAL 447651840
   NEXT 44769280
   PCTINCREASE 10
   MINEXTENTS 1
   MAXEXTENTS 505
   FREELISTS 1 FREELIST GROUPS 1 )
   NOCACHE; 3. Im going to implement insert /*+APPEND */ any pitfalls I have to watch out for?

4. Anyone work with discrete transactions?

dbms_transaction.begin_discrete_transaction? Any pitfalls?

Any other ideas? Any other info required? Received on Mon May 12 2003 - 12:49:33 CDT