Extra Implicit Parsing in Forms 6.0

From: Mike Smith <mikesmith_at_certis.co.uk>
Date: Mon, 1 Nov 1999 16:06:21 -0000
Message-ID: <381dbb3a.0@nnrp1.news.uk.psi.net>

Hi All,

I form part of a performance and scalability team and during our move to forms 6.0 we noticed a new and significant overhead that is now built into the product.

[Oracle Bug 1019023 and TAR 2281376.1]

The extra parses occur in every form for every base block and they repeatedly occur every time you open the form. Having tuned our 4.5 installation until the 'pips squeaked' we were surprised when our converted forms 6.0 forms failed to perform too.

Having isolated the problem we contacted Oracle, who have eventually informed us that, as suspected, this is to permit the use of some of the new features in 8i, see below:

Oracle's response to the extra processing in Forms 6 (Bug 1019023 on the Oracle Support Site)
This is not a bug. Implicit describe has been added in 6.0. Presently, there is no way to toggle this on/off. You may want to consider a enhancement request to do this. The following are the reasons, a Describe is required: 1) Oracle 8, supports CLOBS. Forms 6.0 supports items based on CLOBS. If you have noticed, the builder does not have a CLOB as a data type for a item. So CHAR item can have a CHAR/VARCHR2/BLOB as the corresponding database column. So, how does the Forms Runtime know whether the underlying column is a CHAR/VARCHR2/BLOB. A 'Describe' is necessary for that. 2) Oracle 8 and Forms 6.0 supports ADT's. A block can be based on a ADT. The Forms Runtime has no way of knowing this as the user does not specify any ADT information in the Builder. For DML on a ADT, we need to know the column types. So a 'Describe' is done.

Our company for one believes this to be unacceptable. We cannot tell our customers that they will need to upgrade their servers just to provide the same level of service as they already had.

Far from this release enhancing scalability as Oracle have claimed, it has, in fact added a huge overhead to multiple form OLTP type systems.

I am wondering if other people have also noticed this and whether or not they deem this to be a problem.

Mike Smith
Senior Consultant
Certis Ltd

Extra info, from a supplementary email:

Hi,

It's quite a severe degradation in service, from that we obtained from forms 4.5. Included below is a portion of the trace file that started the row with Oracle.

Note the parses of 'Select *' these have been added by Oracle - they simply did not exist before. They were a result of just 7 form openings. For each base block you get one of these every time you open the form. As you can imaging this extra load would not be acceptable in an OLTP database with 2000 + users, especially when the parse timings actually exceed the time the form took to actually do it's work.

Regards Again,

Mike

select *
from
WIP_POLICY_CONTRACTS call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

Parse 7 0.00 0.05 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ------

total 7 0.00 0.05 0 0 0 0

Misses in library cache during parse: 0 Optimizer goal: CHOOSE
Parsing user id: 56 (SUSER)

Rows Execution Plan
------- ---------------------------------------------------

      0  SELECT STATEMENT   GOAL: CHOOSE
      0   TABLE ACCESS (FULL) OF 'WIP_POLICY_CONTRACTS'

****************************************************************************

SELECT ROWID,CONTRACT_ID,END_OF_TERM_ACTION,CONTRACT_LANGUAGE, CONTRACT_CURRENCY,CONTRACT_STATUS
FROM
WIP_POLICY_CONTRACTS WHERE contract_id = :1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

Parse 7 0.02 0.06 0 0 0 0
Execute 7 0.00 0.00 0 0 0 0
Fetch 7 0.00 0.00 0 14 0 7
------- ------ -------- ---------- ---------- ---------- ---------- ------

total 21 0.02 0.06 0 14 0 7

Misses in library cache during parse: 0 Optimizer goal: CHOOSE
Parsing user id: 56 (SUSER)

Rows Execution Plan
------- ---------------------------------------------------

      0  SELECT STATEMENT   GOAL: CHOOSE
      1   TABLE ACCESS (BY ROWID) OF 'WIP_POLICY_CONTRACTS'
      1    INDEX (UNIQUE SCAN) OF 'WIP_POL_CONT_PK' (UNIQUE)

****************************************************************************

select *
from
WIP_POLICY_VERSIONS call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

Parse 9 0.04 0.27 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ------

total 9 0.04 0.27 0 0 0 0

Misses in library cache during parse: 0 Optimizer goal: CHOOSE
Parsing user id: 56 (SUSER)

Rows Execution Plan
------- ---------------------------------------------------

      0  SELECT STATEMENT   GOAL: CHOOSE
      0   TABLE ACCESS (FULL) OF 'WIP_POLICY_VERSIONS'

****************************************************************************

SELECT ROWID,PRODUCT_ID,PROD_VERSION,CONTRACT_ID,CONTRACT_STATUS FROM
WIP_POLICY_VERSIONS WHERE (CONTRACT_ID=:1) call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

Parse 7 0.02 0.06 0 0 0 0
Execute 7 0.00 0.00 0 0 0 0
Fetch 7 0.00 0.00 0 14 0 7
------- ------ -------- ---------- ---------- ---------- ---------- ------

total 21 0.02 0.06 0 14 0 7

Misses in library cache during parse: 0 Optimizer goal: CHOOSE
Parsing user id: 56 (SUSER)

Rows Execution Plan
------- ---------------------------------------------------

      0  SELECT STATEMENT   GOAL: CHOOSE
      1   TABLE ACCESS (BY ROWID) OF 'WIP_POLICY_VERSIONS'
      1    INDEX (UNIQUE SCAN) OF 'WIP_POL_VERS_PK' (UNIQUE)

****************************************************************************

select *
from
WIP_POLICY_BASES call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

Parse 9 0.03 0.18 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ------

total 9 0.03 0.18 0 0 0 0

Misses in library cache during parse: 0 Optimizer goal: CHOOSE
Parsing user id: 56 (SUSER)

Rows Execution Plan
------- ---------------------------------------------------

      0  SELECT STATEMENT   GOAL: CHOOSE
      0   TABLE ACCESS (FULL) OF 'WIP_POLICY_BASES'

****************************************************************************

SELECT ROWID,TERM_START_DATE,TERM_END_DATE,AGENT_ROLE,GROUP_ID,COMPANY_ID, COMPANY_ORG_UNIT,CONTRACT_ID,ACTION_CODE FROM
WIP_POLICY_BASES WHERE action_code != 'D' and (CONTRACT_ID=:1)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

Parse 7 0.01 0.04 0 0 0 0
Execute 7 0.00 0.00 0 0 0 0
Fetch 7 0.00 0.00 0 14 0 7
------- ------ -------- ---------- ---------- ---------- ---------- ------

total 21 0.01 0.04 0 14 0 7

Misses in library cache during parse: 0 Optimizer goal: CHOOSE
Parsing user id: 56 (SUSER)

Rows Execution Plan
------- ---------------------------------------------------

      0  SELECT STATEMENT   GOAL: CHOOSE
      1   TABLE ACCESS (BY ROWID) OF 'WIP_POLICY_BASES'
      1    INDEX (UNIQUE SCAN) OF 'WIP_POL_BASE_PK' (UNIQUE)

****************************************************************************

Received on Mon Nov 01 1999 - 10:06:21 CST

Extra Implicit Parsing in Forms 6.0 - Performance Problem