Re: Forms 3.0 slower in ORACLE7 on DG/UX?

Bill McMillan (uj569_at_freenet.Victoria.BC.CA) wrote:

: I am migrating Forms 3.0 applications from Oracle 6 to ORACLE7
: and finding the ORACLE7 forms to consistently be about 20%
: slower. I've tweaked every tweakable parameter I can find but
: still get the same results. Oracle Tech Support has been helping
: me with this but has yet to come up with an answer. Sqlplus is
: ok. Data Query and ReportWriter (Forms based) have the same
: problem. I've talked to another DG/UX site that is having the
: same type of problem, but other platforms have no complaints.
: Has anyone else experienced poor performance with Forms 3.0 and
: ORACLE7?
: I'm using DG/UX 5.4.2 and migrating 3.0.16 applications from
: 6.0.37 to 7.0.16.
: Any ideas are appreciated.
 

: Bill McMillan
: BC Ministry of Attorney General
: --

Hi,

The problem you have hit is of 'bad' programming. In Oracle 7 the thing to look out for is the shared pool and the UNIQUE pieces of sql being generated. If do a sql dump of the shared pool I bet you will find loads of 'supposedly' identical sql statements. The key lies in modifying the forms ,especially, the base table queries, so that it uses the bind variables which means that in the shared pool the shared pool you should see sql of the following kind

 select * from emp where emp_id= :emp_id

rather

 select * from emp where emp_id=1234

The reason is , as Oracle 7 , the second sql stmt is a new statement and hence it is reparsed. ANY kind of parsing in 7 is VERY VERY expensive.

Hope this helps. ( If Oracle support is unable to help , mention our name and they will recall !!! ). Also if your problems continue, we would be happy to come and assist you. We are ,probably, the heaviest Oracle OLTP site in the world and have very extentsive experience in the above and related issues.

Amit

--
                                  _____________
    Amit Gupta                        churchill        Tel: +44 81 313 5688
    Sr. Database Administrator    =============        Fax: +44 81 313 5699
    Churchill Management
    15-17 London Rd, Bromley BR1 1DE, UK.    Email: amit_at_churchill.co.uk