My Oracle Support Banner

Performance Degrade Because Prefetch Doesn't Work for Host Array After Upgrading to Pro*c/Pro*COBOL 12.2 (Doc ID 2281191.1)

Last updated on JULY 04, 2017

Applies to:

Precompilers - Version 12.2 BETA1 to 12.2 BETA1 [Release 12.2]
Information in this document applies to any platform.

Symptoms

After upgrading a Pro*C/Pro*COBOL application to 12.2, performance degrade occurs on during the fetch operations using a host array variable of SELECT statement like below:

char sqlstr[] = "SELECT OBJECT_NAME FROM DBA_OBJECTS";
VARCHAR dataarr[100][120+1];
int i;

...

for (;;)
{
    memset((void *)dataarr, '\0', sizeof(dataarr));
    EXEC SQL FOR 100 FETCH dbaobj_cur INTO :dataarr;
    if (sqlca.sqlcode == 1403) break;
    for (i = 0; i < 100; i++)
    {
        printf("%s\n", dataarr[i].arr);
    }
}

With observing the EVENT 10046 trace files, prefetch doesn't work.

12.2.0.1 -- "r=100" (it means 100 rows are fetched in one FETCH operation) is observed at the second FETCH only:


PARSING IN CURSOR #140638719112416 len=35 dep=0 uid=9 oct=3 lid=9
tim=5356401349874 hv=1793804545 ad='7b5a7ef0' sqlid='1ty3wyjpfqk81'
SELECT OBJECT_NAME FROM DBA_OBJECTS
END OF STMT
PARSE
#140638719112416:c=162975,e=281188,p=68,cr=546,cu=0,mis=1,r=0,dep=0,og=1,plh=3485858184,tim=5356401349873
EXEC
#140638719112416:c=1000,e=110,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3485858184,tim=5356401486091
FETCH
#140638719112416:c=1000,e=34779,p=0,cr=9,cu=0,mis=0,r=1,dep=0,og=1,plh=3485858184,tim=5356401521013
FETCH
#140638719112416:c=1000,e=779,p=0,cr=291,cu=0,mis=0,r=100,dep=0,og=1,plh=3485858184,tim=5356401657242
FETCH
#140638719112416:c=0,e=40,p=0,cr=9,cu=0,mis=0,r=2,dep=0,og=1,plh=3485858184,tim=5356401793808
FETCH
#140638719112416:c=0,e=91,p=0,cr=7,cu=0,mis=0,r=2,dep=0,og=1,plh=3485858184,tim=5356401929217
FETCH
#140638719112416:c=0,e=55,p=0,cr=7,cu=0,mis=0,r=2,dep=0,og=1,plh=3485858184,tim=5356402064506
FETCH
#140638719112416:c=0,e=42,p=0,cr=9,cu=0,mis=0,r=2,dep=0,og=1,plh=3485858184,tim=5356402199646
...

12.1.0.2 or older version -- "r=100" is observed at the all FETCHes from the second one:


PARSING IN CURSOR #140010112707904 len=35 dep=0 uid=9 oct=3 lid=9
tim=5357548434069 hv=1793804545 ad='7b5a7ef0' sqlid='1ty3wyjpfqk81'
SELECT OBJECT_NAME FROM DBA_OBJECTS
END OF STMT
PARSE
#140010112707904:c=0,e=183,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3485858184,tim=5357548434068
EXEC
#140010112707904:c=0,e=226,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3485858184,tim=5357548569880
FETCH
#140010112707904:c=0,e=759,p=0,cr=9,cu=0,mis=0,r=1,dep=0,og=1,plh=3485858184,tim=5357548570779
FETCH
#140010112707904:c=0,e=812,p=0,cr=291,cu=0,mis=0,r=100,dep=0,og=1,plh=3485858184,tim=5357548707116
FETCH
#140010112707904:c=0,e=733,p=0,cr=263,cu=0,mis=0,r=100,dep=0,og=1,plh=3485858184,tim=5357548844833
FETCH
#140010112707904:c=0,e=711,p=0,cr=239,cu=0,mis=0,r=100,dep=0,og=1,plh=3485858184,tim=5357548982416
FETCH
#140010112707904:c=1000,e=686,p=0,cr=267,cu=0,mis=0,r=100,dep=0,og=1,plh=3485858184,tim=5357549120044
FETCH
#140010112707904:c=1000,e=618,p=0,cr=239,cu=0,mis=0,r=100,dep=0,og=1,plh=3485858184,tim=5357549257700
...

 

Changes

Upgraded a Precompiler application to 12.2.

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.