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

"Sybrand Bakker" <gooiditweg_at_nospam.demon.nl> wrote in message news:98pvbvo8kq9g85gvabsl0o65p49kg293nm_at_4ax.com...
> 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.

anyway to do a direct path update? I tried everything I can think of to speed up my update statement. Tried updates from asktom, jonathan lewis's page, metalink, and others.
>
> >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!!!
>

I didnt put in pctincrease. It was like that when I got there. Just looking at it now. How do you size your initial extent? Im finding that if I use

ALTER TABLE DEALLOCATE UNUSED; Im still having a large number of empty blocks. Would it be prudent to shrink my initial extent to approximately the amount of data in the table?

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

I ran a report off of ixora that said 95% or so. Ive been told to consider 99%. is that getting too high?

any accuracy to Burleson's article on the subject? Its different than other advice Ive gotten.

http://www.dba-oracle.com/art_pctfree.htm

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

Its not the process its the amount of data involved. So I have to squeeze.
>
>
> Sybrand Bakker, Senior Oracle DBA
>
> To reply remove -verwijderdit from my e-mail address
Received on Mon May 12 2003 - 15:53:39 CDT