Help: Poor IMP Performance 8.1.7.2, Win 2000

From: EE <silSPverAMback_at_photSPoAMbooks.com>
Date: Mon, 10 Jun 2002 20:41:51 GMT
Message-ID: <3d05090b.621472937@news.service.emory.edu>

Hello experts,

I am seeing what I consider poor performance/pauses on a schema import. We are seeing 1 hour for an import of a 450 MB total DMP file
(by schema). Total database space involved in the import is under 2 GB
(data + index).

I am used to the UNIX world, so I am not sure what is reasonable for M$. Please let me know what you think, and any tuning hints.

No, I can not change OS or shoot the vendor. Sad.

thanks,

Evan (Remove both SPAM's to email me)
s i l v e r b a c k AT p h o t o b o o k s DOT c o m

---

I am running an Oracle 8.1.7.2 database on a 4 CPU machine with 1 GB of ram. We are running Windows version 5.0 (Win2K, build 2195, SP2). The task manager shows CPU running at near zero for all processors with spikes of 25% on one processor at a time. Memory usage is 656,740K (out of 1,048,092K). Disks are EMC, 3x channels, max I/O of 5 (Other boxes have broken 30 without performance hits).

The import paused with DBA Studio showing inactive until I hit "Enter" in the import window. Logs stopped being written from 4:03 to 4:15, then resumed. (No other users to cause logging while this happened).

I have to assume that the process was sitting as idle as DBA Studio showed during that time.

Most of the larger (for this database) tables seems to cause it to freeze until I hit enter. Large is any table over 100000 rows.

Note that this is a test database, so I can do anything (within reason) that seems valid, but I will need good performance by next week for the production system.

• Here is the pfile minus identifying names --- db_files = 512

control_files = ("e:\oracle\oradata\TMST\control01.ctl", "e:\oracle\oradata\TMST\control02.ctl",
"e:\oracle\oradata\TMST\control03.ctl")

open_cursors = 300
max_enabled_roles = 30
db_file_multiblock_read_count = 8

db_block_buffers = 13070

shared_pool_size = 36341785

large_pool_size = 614400
java_pool_size = 20971520

log_checkpoint_interval = 10000
log_checkpoint_timeout = 1800

processes = 150

parallel_max_servers = 5

log_buffer = 32768

max_dump_file_size = 10240 # limit trace file size to 5M each

log_archive_start = true
log_archive_dest_1 = "location=F:\oracle\admin\TMST\arch"
log_archive_format = %%ORACLE_SID%%T%TS%S.ARC

rollback_segments = ( RBS0, RBS1, RBS2, RBS3, RBS4, RBS5, RBS6, RBS7, RBS8, RBS9, RBS10, RBS11 ) global_names = true

oracle_trace_collection_name = ""

background_dump_dest = e:\oracle\admin\TMST\bdump resource_manager_plan = system_plan
user_dump_dest = e:\oracle\admin\TMST\udump

db_block_size = 8192

remote_login_passwordfile = exclusive

os_authent_prefix = ""

distributed_transactions = 10
mts_dispatchers =
"(PROTOCOL=TCP)(PRE=oracle.aurora.server.SGiopServer)"

compatible = 8.1.0
sort_area_size = 65536
sort_area_retained_size = 65536 Received on Mon Jun 10 2002 - 15:41:51 CDT