Re: need help with table creation, nologging, and backup

On 12 May 2003 10:49:33 -0700, rgaffuri_at_cox.net (Ryan Gaffuri) wrote:

>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.
>
>1. 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?
>

nologging is not going to help a bit, as it applies to special operations only, direct load being one of them. Usually people complaining about this never tuned their redolog process and are switching way too often.

>2. 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;
Using pctincrease 10 is definitely a nobrainer. As this has been discussed over and over and over again, I am a bit surprised seeing you using this. Change it NOW into the only sensible value : 0!!!

Also as your table is apparently very volatile you should consider increasing pctused to 60 or 80 percent. This will result in blocks getting sooner on the freelist, hence your table will be more densely packed.
>
>3. Im going to implement insert /*+APPEND */ any pitfalls I have to
>watch out for?
>

Sure, APPEND doesn't check constraints, and your indexes are going to end-up in unusable state.

Very much looks like you are trying to address your problems by using some quick fixes ending up in a bigger mess than you were before.

>4. Anyone work with discrete transactions?
>

No, why?

>dbms_transaction.begin_discrete_transaction? Any pitfalls?
>
>
>Any other ideas? Any other info required?

Time to redesign the process I guess. You are going to sacrifice stability in order to get a few measly percents of speed increase, and you are likely to end up in hell.

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Mon May 12 2003 - 13:23:03 CDT