different thin/thick behaviour for arraysize and prefetchrows

[alberto-dellera]

1. What versions are you using?

platform.platform: Linux-6.2.0-26-generic-x86_64-with-glibc2.37 sys.maxsize > 2**32: True platform.python_version: 3.10.11 oracledb.version: 1.4.0

2. Is it an error or a hang or a crash?

It is a different behaviour

3. What error(s) or behavior you are seeing?

Plaese check the belowtest cases, all using cursor.prefetchrows = 7 cursor.arraysize = 50

When in thin mode:

• first "V8 Bundled Exec" OPI call (that bundles PARSE+EXEC+FETCH) fetches 'prefetchrows' rows,
• the next "FETCH" calls all fetch 'arraysize' rows

When in thick mode:

• first "V8 Bundled Exec" OPI call: as above
• second "FETCH" call fetches 'arraysize'-'prefetchrows' (i.e. 43) rows
• the next "FETCH" calls all fetch 'arraysize' rows, as above

Side note: I personally prefer the thin mode behaviour, much more intuitive to understand and explain: "prefetchrows is the number of rows returned by the first roundtrip, arraysize is the same for the next roundtrips"

4. Does your application call init_oracle_client()?

Yes to activate the Thick mode, no to activate the Thin mode

5. Include a runnable Python script that shows the problem.

   
   import oracledb 

driver = "THICK"

initialize Thick connection in oracledb if requested

if driver == "THICK": oracledb.init_oracle_client()

connect

conn = oracledb.connect(user="DELLERA", password="dellera", dsn="ora19c_hostonly")

check thickness

driver_check = "THIN" if conn.thin else "THICK" if driver != driver_check: raise Exception(f"different thickness: wanted {driver}, actual {driver_check}")

print(f"oracledb driver version: {oracledb.version}") print(f"oracle version: {conn.version}")

activate sql trace

with conn.cursor() as cursor: cursor.execute(f"""alter session set tracefile_identifier=arraysizetop{driver}""") cursor.execute("""alter session set events '10046 trace name context forever, level 12'""") cursor.execute("""alter session set events '10051 trace name context forever, level 1'""")

fetch rows

with conn.cursor() as cursor: cursor.prefetchrows = 7 cursor.arraysize = 50 cursor.execute("""select rownum from dual connect by level <= 200""") rows = cursor.fetchall() print(f"fetched {len(rows)} rows")

deactivate sql trace

with conn.cursor() as cursor: cursor.execute("""alter session set events '10051 trace name context off'""") cursor.execute("""alter session set events '10046 trace name context off'""")

Thin (excerpt):

OPI CALL: type=94 argc=38 cursor= 0 name=V8 Bundled Exec

PARSING IN CURSOR #1846235332808 len=47 dep=0 uid=197 oct=3 lid=197 tim=9924173389 hv=1384384163 ad='7ffa4ef43b40' sqlid='3uj2ggp9881p3' select rownum from dual connect by level <= 200 END OF STMT PARSE #1846235332808:c=0,e=25,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1731520519,tim=9924173388 EXEC #1846235332808:c=0,e=23,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1731520519,tim=9924173468 WAIT #1846235332808: nam='SQLNet message to client' ela= 1 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=9924173494 FETCH #1846235332808:c=0,e=19,p=0,cr=0,cu=0,mis=0,r=7,dep=0,og=1,plh=1731520519,tim=9924173533 WAIT #1846235332808: nam='SQLNet message from client' ela= 410 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=9924173965 OPI CALL: type= 5 argc= 2 cursor= 1 name=FETCH WAIT #1846235332808: nam='SQLNet message to client' ela= 0 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=9924174011 FETCH #1846235332808:c=0,e=34,p=0,cr=0,cu=0,mis=0,r=50,dep=0,og=1,plh=1731520519,tim=9924174041 WAIT #1846235332808: nam='SQLNet message from client' ela= 379 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=9924174440 OPI CALL: type= 5 argc= 2 cursor= 1 name=FETCH WAIT #1846235332808: nam='SQLNet message to client' ela= 0 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=9924174484 FETCH #1846235332808:c=0,e=34,p=0,cr=0,cu=0,mis=0,r=50,dep=0,og=1,plh=1731520519,tim=9924174514 WAIT #1846235332808: nam='SQLNet message from client' ela= 360 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=9924174894

Thick (excerpt):

OPI CALL: type=94 argc=38 cursor= 0 name=V8 Bundled Exec

PARSING IN CURSOR #1846235328864 len=47 dep=0 uid=197 oct=3 lid=197 tim=9940385533 hv=1384384163 ad='7ffa4ef43b40' sqlid='3uj2ggp9881p3' select rownum from dual connect by level <= 200 END OF STMT PARSE #1846235328864:c=0,e=26,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1731520519,tim=9940385533 EXEC #1846235328864:c=0,e=23,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1731520519,tim=9940385612 WAIT #1846235328864: nam='SQLNet message to client' ela= 1 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=9940385639 FETCH #1846235328864:c=0,e=18,p=0,cr=0,cu=0,mis=0,r=7,dep=0,og=1,plh=1731520519,tim=9940385676 WAIT #1846235328864: nam='SQLNet message from client' ela= 396 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=9940386092 OPI CALL: type= 5 argc= 2 cursor= 1 name=FETCH WAIT #1846235328864: nam='SQLNet message to client' ela= 1 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=9940386137 FETCH #1846235328864:c=0,e=33,p=0,cr=0,cu=0,mis=0,r=43,dep=0,og=1,plh=1731520519,tim=9940386166 WAIT #1846235328864: nam='SQLNet message from client' ela= 290 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=9940386475 OPI CALL: type= 5 argc= 2 cursor= 1 name=FETCH WAIT #1846235328864: nam='SQLNet message to client' ela= 1 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=9940386518 FETCH #1846235328864:c=0,e=32,p=0,cr=0,cu=0,mis=0,r=50,dep=0,og=1,plh=1731520519,tim=9940386546 WAIT #1846235328864: nam='SQLNet message from client' ela= 365 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=9940386931

[cjbj]

Thin mode gave us more control over the behavior and @anthony-tuininga was able to simplify. I don't think changing Oracle Client is feasible.

Bottom line: tune the value for your data, network and driver mode.