Oracle8 performance: export and D2K upgrade

Oracle Gurus:

I have been browsing this newsgroup on a regular basis for the past half a year (I am a relative new DBA). I am amzaed by the varieties of questions being asked and I am impressed by the depth and details of some of the answers. This is the first time that I post a question here. Thank you in advance for your advices and suggestions.

In late July, I installed Oracle8 to co-exist with Oracle7 on a Digital Unix server. Soon after
the installation of Oracle8, our immediate tasks were to install the Designer 2.1 to a new Oracle8 database, and to upgrade several D2K 1.3.2 applications (under Oracle7) to 2.1. The size of the DMP file for the first application that I upgraded is 27,842KB. We encountered many bugs during the upgrade. Disregard the time that I spent getting assistance from Oracle Support, the upgrade itself, if without interruption, did not seem to take too long (could be done between 5 to 8 hours). A few days ago, I started to upgrade the second application (DMP file size is 44,750KB) and I was struck by the "slowness" of the process.

I have tried forcing the upgrade to run on RULE base by deleting the statistics. However, some further readings make it clear that the upgrade process will compute statistics. I have also stopped the upgrade process several times to adjust several parameters. My last attemp was interrpted by an unexpected "shutdown immediate" command issued by a Unix operator to perform cold backup. At the time the process was stopped, the process had been running for 60 hours (from Friday afternoon to following Monday morning) and was at the beginnig of the stage to convert modules. I estimate that it would take at least another 24 to 48 hours to complete the remaining stages.

I shudder to think that it will take about 100 hours to upgrade our second application if I try again under my current environment. In additon, I have noticed that export with D2K user under Oracle7 is faster than export with D2K user under Oracle8, while Oracle8 Repository is only a small subset of the Oracle7 Repository. Currently, it takes 2 and half an hours to create a 197M dump file under Oracle7 (3 months ago, it took about 20-30 minutes to create a 70-80M dump file), 3 hours for 100M under Oracle8. How to improve the performance for export?

I do not think my system is optimal. It probably needs tuning in many places. Any advice and suggestions are welcome and greatly appreciated.

MY SYSTEM: OS: Digital Unix 4.0a
Oracle DBMS on Unix: Oracle7 (7.3.2.3.0) with D2K 1.3.2 repository, Oracle8 (8.0.4.0) with D2K 2.1 repository Oracle Tools: Designer 2000 (both 1.3.2 and 2.1) on NT

						Oralce7			Oracle8
processes					200			200
shared_pool_size				250M			400M
db_block_checkpoint_batch			10			10
db_block_buffers				???			20000
log_buffer					327680			532480
log_checkpoint_interval				20500			10000
db_block_multiblock_read_count			32			32
open_cursors					255			400
sort_area_size					98304			1M
sort_area_retained_size				98304			1M
optimizer_mode					CHOOSE			CHOOSE
db_name						CPDEV			CMDEV
rollback_segments				r01,r02,r03,r04		d2k_rbs

LOGFILE (3 groups with 1 member)		10M			20M
Rollback tablespace				650M			550M	
Temporary tablespace				250M			400M
Temporary segment (inital/next extent)		196608			10M

The D2K_RBS on Oracle8 was created with storage (initial 10M next 10M minexents 4 optimal 50M). I have other smaller rollback segments. However, for the purpose of doing the D2K upgrade, I start the CMDEV database with only D2K_RBS on line.

Thanks,
Ying-fen Received on Tue Sep 22 1998 - 12:57:46 CDT