Issues: DB word-size changes, version migration, tablespace migration to locally managed

Any tips on diagnosing the root cause of predictable system slowdowns when performing large inserts, selecting from a separate table?

Our database was initially set up on HP/UX 11.00 64-bit with Oracle 8.1.6.0.0 64-bit using dictionary-managed tablspaces. We then patched the database to 8.1.6.2 32-bit and performed all the requisite steps for the patch and word-size change. Later we upgraded to 8.1.7.0.0 32-bit and then migrated all bu the SYSTEM tablespace to be locally-managed.

We are currently on 8.1.7.3.0 32-bit and I'm noticing a serious performance issue when performing bulk loads via insert /*+ append */ into t select col1, col2, coln from s; where table t is in one tablespace and s is in a second tablespace, each tablespace using datafiles on different mountpoints. The filesystem is set up with RAID 0+1 for all mountpoints for oracle datafiles; each logical volume spans multiple drives. The most noticeable "pause" in database performance occurs as extents are allocated in the destination table, which seems odd since the purpose of locally-managed tablespaces was to avoid serialized space allocations in the data dictionary.

Richard Smith
richard.smith25_at_attbi.com Received on Sat Jan 11 2003 - 15:45:59 CST