Re: Oracle 7.0.15 ReportWriter prformance problem on Solaris 2.2

In article <2tlutc$pmr_at_raffles.technet.sg>, lionell_at_solomon.technet.sg (Lionel Lee Yuin) writes:
>
> Lionel Lee Yuin (lionell_at_solomon.technet.sg) wrote:
> : 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

The process will only use one CPU (of your 6) at a time so it can only use up to 16.6666% of your total machine capacity.

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

Sounds like SQL*ReportWriter is doing all its work in the database buffers (ie in the SGA).

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

You need to profile the sql scripts that are running to find out if they are performing efficiently. Maybe they are NOT using indexes and are simply churning cpu doing multiple full table scans in buffers.

>
> : [3] 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?

No.

> :
> : [4] 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?

Yes.

>
> [5] Our DBA also says the entire database files need to be reorganised in
> order for the new 8K block size to take effect. She says to the effect that
> the existing 2K database files has to be wiped out & recreated as 8K database
> files by Oracle. Is this true? Why can't we simply just alter the init.ora
> block size parameter value & retain the existing database files?

Imagine that each Database file that Oracle uses is a logical disk. It's not but just imagine for a moment. Oracle formats each database file using a specific block size. If you want to change the block size you must remove your data, reformat the database file, and then put your data back.

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

Turn on sql trace for your database, run your ReportWriter program, run the resulting trace file through the TKPROF utility and have a look at exactly what way the database is being accessed.

Other problems could be the cost based optimiser going burko. Try it with only the rule based optimiser.

-- 

Bruce...        pihlab_at_cbr.hhcs.gov.au

*******************************************************************
* Bruce Pihlamae  --  Database Administration                     *
* Commonwealth Department of Human Services and Health            *
* Canberra, ACT, Australia                        (W) 06-289-7056 *
*=================================================================*
* These are my own thoughts and opinions, few that I have.        *
*******************************************************************

"The more complex the argument gets, the easier it is to refute."
"Killing is wrong!"  -- Trent 'The Uncatchable' Castanaveras