Performance disparity between Oracle 8 and 8i

From: Pascal Byrne <nospam-byrne_at_icada.net>
Date: Mon, 02 Sep 2002 17:20:18 +0200
Message-ID: <akvvjf$1m5c09$1@ID-147008.news.dfncis.de>

I have created two (almost) identical databases, one 8.0.5.1 and the other 8.1.7. Uploading a million rows with sqlldr on the Oracle 8 db takes 47 seconds but on 8i it takes 11 minutes.

Both databases are on the same server (SuSE 7.3) have almost identical init.ora files and tablespace sizings. Tables on both databases were created from the same SQL. The known differences are as follows: 1. The init.ora file for 8i has 'compatable=8.1.7' and 'java_pool_size' parameters.
2. The charset used for 8i is 'UTF8' and for 8 is 'WE8ISO8859P1' 3. I used locally managed tablespaces for 8i.

initRSM.ora for 8i
db_name = RSM
control_files = (
/ora00/8.1.7/oradata/RSM/control01.ctl,
/ora00/8.1.7/oradata/RSM/control02.ctl,
/ora00/8.1.7/oradata/RSM/control03.ctl)

db_block_size			= 4096
rollback_segments		= (r01,r02,r03,r04)

db_files = 10
open_cursors = 200
processes = 100
dml_locks = 500
db_file_multiblock_read_count = 8
db_block_buffers = 12800
shared_pool_size = 52428800
shared_pool_reserved_size = 0
java_pool_size = 20971520
log_checkpoint_interval = 0
sort_area_size=2097152
sort_area_retained_size=262144
log_buffer = 524288
log_archive_start = false
global_names = TRUE
optimizer_mode = choose
compatible = 8.1.7

Could the locally managed tablespaces be to blame?

Thanks,
pascal Received on Mon Sep 02 2002 - 10:20:18 CDT