Oracle 7.0.15 ReportWriter prformance problem on Solaris 2.2

Our site is encountering a strange performance problem which I hope you can kindly advise us or perhaps suggest some recommendations:  

Whenever our particular inhouse app runs using Oracle 7 ReportWriter, the turnaround time is terribly slow - ranging from more than 20 minutes to hours! iostat says there is virtually no physical disk access and the idle % of our 6-CPU SC2000 ranges from 60-90% free. vmstat says that there are virtually not much paging and no swapping at all. sar says about the same. However, Oracle's sqldba monitor reveals that there is tremendeous I/O (logical I/O) associated with these particular user sessions. Moreover, trussing the app's shadow process (for dbwriter) indicates all I/O are performed in terms of 2K (equivalent to ora.init block size parameter setting). Two task & ISM are turned off.  

[1] I suspect the I/O are mainly intensive memory moves within sections of
memory reserved by Oracle. Will this be Oracle's SGA or PGA?

[2] If indeed I/O are contained within memory, then why so extremely slow?

[2] Do you think such problems can be attributed to the database block size not
tally with the default SunOS 5.2 block size of 8K?  

[3] Our DBA says that the database instance has to be shutdown & restart in
order for the parameter changes in init.ora to take effect. Is this true?  

Please kindly help me to answer at least some of the above questions, since our organisational wide MIS is solely dependent on this Oracle platform.

Sincere thanks & best regards.  

Lionel Lee
System Manager
EMAIL: lionell_at_solomon.technet.sg Received on Wed Jun 15 1994 - 05:38:21 CEST