Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Help! 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 - 15:36:53 CDT