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=arraysize_top_{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='SQL*Net 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='SQL*Net 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='SQL*Net 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='SQL*Net 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='SQL*Net 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='SQL*Net 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='SQL*Net 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='SQL*Net 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='SQL*Net 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='SQL*Net 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='SQL*Net 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='SQL*Net 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.