Re: Oracle 8.1.7 huge performance problem : please help !

From: kouchba <kouchba_at_yahoo.com>
Date: 14 Jan 2004 23:53:26 -0800
Message-ID: <f55a8ec8.0401142353.4ced6d1e@posting.google.com>

First I would like to thank everyone for your help. Here is some more info.
The disk on which everything is stored is a netapp F810 filesystem. Is ia a NAS (Network accessed Storage) disk accessed through NFS and recommanded by Oracle.
Both Instances 7.3.4 and 8.1.7 run on a quadri pro on Solaris 7 and following are the initXXX.ora files. No object has ever been analyzed in both instances so I changed Oracle817 optimizer from choose to rule without any success.

The tkprof output looks like this and is not linked to any particular query :
SELECT * FROM TEST (which produces the same execution plan on both machines ) I also checked that TEST is not fragmented on both extents (one single extent)

on Oracle 7.3.4

        elapsed       disk   rows
        100.10        65000  200000

on Oracle 8.1.7
        elapsed       disk   rows
        153.70        43000  180000

which means it takes less time on Oracle 7.3.4 to make a full scan which takes more IO than on Oracle 8.1.7..! That problem is essentially visible on reads as don't make that many disk writes(INSERT/UPDATES). So I suspect that may be Oracle 7.3.4 can read asynchronously whereas Oracle 8.1.7 does not..? May be I am wrong but can you please help ?

Thanks Philippe

initO817.ora

control_files =
(/netapp/app/oracle/oradata/SCPE8/controlSCPE801.ctl,

/netapp/app/oracle/oradata/SCPE8/controlSCPE802.ctl,

/mnt/app/oracle/oradata/SCPE8/controlSCPE803.ctl)

background_dump_dest = /netapp/app/oracle/admin/SCPE8/bdump

core_dump_dest                  = /netapp/app/oracle/admin/SCPE8/cdump
user_dump_dest                  = /netapp/app/oracle/admin/SCPE8/udump

db_name                 = SCPE8
db_block_size           = 4096

rollback_segments = (r01,r02,r03,r04,r05,r06,r07,r08,r09,r10,r11,r12,r13,r14,r15 ,r16,r17,r18,r19,r20)

#---- tuning parameters

db_files = 25            
db_file_multiblock_read_count =16
db_block_buffers = 35000

shared_pool_size = 120000000
java_pool_size=0
buffer_pool_keep=1000
log_checkpoint_interval = 99999
log_checkpoint_timeout=0
disk_asynch_io=TRUE
#_filesystemio_options=directIO
processes = 1200
log_buffer = 204800
timed_statistics = true
max_dump_file_size = 102400

#--- mode archive ---

log_archive_start = false
log_archive_dest=/mnt/app/oracle/oradata/SCPE8/arch/archSCPE8
log_archive_format=%S.dbf

open_cursors = 1500

#--- pour snapshot ---

job_queue_processes = 1
job_queue_interval = 60
sort_area_size = 520192
optimizer_mode=rule

pre_page_sga=true
compatible =8.1.0.0.0

InitO734.ora

control_files =
(/netapp/app/oracle/oradata/SCPEMOL/controlSCPE01.ctl,

/netapp/app/oracle/oradata/SCPEMOL/controlSCPE02.ctl)

background_dump_dest = /netapp/app/oracle/admin/SCPEMOL/bdump

core_dump_dest                  =


/netapp/app/oracle/admin/SCPEMOL/cdump

user_dump_dest                  =

/netapp/app/oracle/admin/SCPEMOL/udump

db_name                 = SCPEMOL
db_block_size           = 4096

rollback_segments = (r01,r02,r03,r04,r05,r06,r07,r08,r09,r10,r11,r12,r13,r14,r15 ,r16,r17,r18,r19,r20)

#---- tuning parameters

db_files = 20
db_file_multiblock_read_count = 16
db_block_buffers = 20000

shared_pool_size = 100000000
log_checkpoint_interval = 99999
processes = 1200
log_buffer = 163840
sequence_cache_entries = 32000
sequence_cache_hash_buckets = 32000
timed_statistics = true
max_dump_file_size = 102400

#--- mode archive ---

log_archive_start = false
log_archive_dest=/mnt/app/oracle/oradata/SCPEMOL/arch/archSCPEMOL
log_archive_format=%S.dbf

#---- Nombre de curseur max ----
open_cursors = 1500

#--- pour snapshot ---
job_queue_processes = 1
job_queue_interval = 60

#--- pour les tris ---
#sort_area_size = 2000000
#sort_area_size = 8000000

sort_area_size = 520192

optimizer_mode=choose
compatible=7.3.4.0

unlimited_rollback_segments = true
#sql_trace = true Received on Thu Jan 15 2004 - 01:53:26 CST