Oracle 7.0.15 ReportWriter prformance problem on Solaris 2.2

Oracle 7.0.15 ReportWriter prformance problem on Solaris 2.2

Post by Lionel Lee Yu » Thu, 16 Jun 1994 13:00:05

 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

[1] I suspect the I/O are mainly intensive memory moves within the
Oracle's SGA in RAM but the question is why so slow?
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?

[2] Will increasing the block size of 2KB to 8KB accelerate performance in this
context in terms of multiple read/write I/O performed by Oracle within memory?
If so, will performance increase by say 2-4 times faster?

[3] What is the default page size in Solaris 2.x?  Is the page size
variable?  If so, is page size dynamically determined by the kernel?

[4] Is the disk block size variably determined by the kernel as well during
I/O access?

[5] What is the inter relationship between the page size and block size in
Solaris 2.x?  Must they be the same?

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

Sincere thanks & best regards.

Lionel Lee
System Manager