rebuild certain tables instead of complete re-import dump

[blom0344]

I'm posting this on the ORACLE 8 forum because of minimal activity on the 7 forum, but I suspect situation is comparable.

To improve performance on the database we do time-consuming exports and imports of database dumps. Performance issue can only be related to a few large facttables , cause there are only a few. I want to try and do a rebuild on them to increase dropping performance (chained row perc is also quite high) on the individual tables instead of spending hours with the dump file. The Vendor of the application suggests that this is not usefull. 95% of the DB tables are stationary supporting tables, so going for the individual big ones seems a faster approach. Am I right? T. Blom
Information analyst
tbl@shimano-eu.com

 

[alexhu]

If 95% are staic then you are wasting your time exporting /importing them.

In my systems I have seperate tablspaces for static and dynamic tables. You can then concentrate your effort where it is required

Alex

 

[lfoata]

you are right. Too bad I.T. is not cash business

Luc Foata
Unix sysadmin, Oracle DBA

 

[AOLEURO]

Hi blom0344

Oracle version 7.0 does indeed have a performance problem then you got chained rows, extent fragmentation or if you have not uniform extent size.

And you got a lot of performance degree not seen in newer versions.

So guidelines for tuning an Oracle 7.0 and 8i are not the same.

Now it is possible for you just to make export and import on these fat tables, but where are several considerations.

Normally the application developer knows limited for the application, but I would indeed make tablespaces after a model called SAFE – Simple Algorithm for Fragmentation Elimination. SAFE consists of a set of rules, many of which have been internalized and implemented as part of the locally managed tablespace feature been introduced in Oracle 8i.

If you want to continue the thread, then give a PIP, and I will explain SAFE.
Regards
Allan
Icq: 346225948
! If you can't prove it - don't bother try to teach me !