Re: Performance disparity between Oracle 8 and 8i

From: Pascal Byrne <byrne_at_nospam.net>
Date: Tue, 03 Sep 2002 15:12:07 +0200
Message-ID: <al2cf6$1moj2o$1@ID-147008.news.dfncis.de>

Thanks for the answer. I did as you asked and monitored v$session_event during the upload and found that excessive waiting for log file switches was to blame.

rgrds,
pascal

Sybrand Bakker wrote:

> 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 Tue Sep 03 2002 - 08:12:07 CDT