Re: Performance disparity between Oracle 8 and 8i

On Mon, 02 Sep 2002 17:20:18 +0200, Pascal Byrne <nospam-byrne_at_icada.net> wrote:

>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
>

The obvious route, before you post 'It doesn't work' and ask to group to take their crystal balls out of their sleeves, would have been monitoring the sqlldr session on 8.1.7 and identify *what* it is waiting for.
You state the two databases are identical, but are they really. How about the file and disk distribution? How about the chance that the 8.1.7 database has it's datafiles on a different edge of the disk? The info you posted above is completely useless, you need to monitor v$waitstat and v$session_event

Regards

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Mon Sep 02 2002 - 12:05:30 CDT