SQL Developer for VS Code

(SQL Dev for VSCode 23.4.1; Windows 11)

Unless SET ARRAYSIZE is used to set the size of the fetch array explicitly in the script runner, a suboptimal fetch size could be used in scripts.

Proof: the following test script is opened in a SQL worksheet, then run as a script (F5):

show arraysize
help set arraysize

alter session set events = 'sql_trace plan_stat=all_executions,bind=true,wait=false';

set termout off

select /* sample qry #1 */
    object_id
from
    all_objects
fetch first 250 rows only;

set termout on

alter session set events = 'sql_trace off';

select /* __v_sql_scan__ */
    a.sql_id, a.child_number, a.executions,
    a.fetches, a.end_of_fetch_count, a.rows_processed,
    substr(a.sql_text, 1, 60) as sql_text
from
    v$sql a,
    v$sqlcommand b
where
    instr(a.sql_fulltext, '__v_sql_scan__') = 0
    and a.command_type = b.command_type
    and b.command_name = 'SELECT'
    and regexp_like(a.sql_fulltext, 'sample qry #1', 'i')
order by 
    a.last_active_time desc;

set arraysize 50

alter session set events = 'sql_trace plan_stat=all_executions,bind=true,wait=false';

set termout off

select /* sample qry #2 */
    object_id
from
    all_objects
fetch first 250 rows only;

set termout on

alter session set events = 'sql_trace off';

select /* __v_sql_scan__ */
    a.sql_id, a.child_number, a.executions,
    a.fetches, a.end_of_fetch_count, a.rows_processed,
    substr(a.sql_text, 1, 60) as sql_text
from
    v$sql a,
    v$sqlcommand b
where
    instr(a.sql_fulltext, '__v_sql_scan__') = 0
    and a.command_type = b.command_type
    and b.command_name = 'SELECT'
    and regexp_like(a.sql_fulltext, 'sample qry #2', 'i')
order by 
    a.last_active_time desc;

Readout:

Apologies for using a bitmap here, but not having a proper way of copying & pasting from the output panel makes it very painful to share the readout otherwise. (Use right-click, then “Open Image in New Tab” if it's too small to read here.)

From the above, we read that the ARRAYSIZE is set as “default” in the script runner. However, the online help of the SET ARRAYSIZE command reminds us that the default is 15.

So why do we need 102 fetch calls to fetch 250 rows???

The trace file tells the full story:

PARSING IN CURSOR #140203016759904 len=87 dep=0 uid=154 oct=3 lid=154 tim=62754308002 hv=1207126926 ad='6a8f4dd0' sqlid='0mjxtaj3z6kwf'
select /* sample qry #1 */
    object_id
from
    all_objects
fetch first 250 rows only
END OF STMT
PARSE #140203016759904:c=46061,e=48522,p=0,cr=66,cu=0,mis=1,r=0,dep=0,og=1,plh=50804201,tim=62754308001
EXEC #140203016759904:c=192,e=192,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=50804201,tim=62754308244
...
FETCH #140203016759904:c=4324,e=3126,p=0,cr=217,cu=0,mis=0,r=50,dep=0,og=1,plh=50804201,tim=62754311445
FETCH #140203016759904:c=42,e=42,p=0,cr=10,cu=0,mis=0,r=2,dep=0,og=1,plh=50804201,tim=62754313225
FETCH #140203016759904:c=39,e=39,p=0,cr=12,cu=0,mis=0,r=2,dep=0,og=1,plh=50804201,tim=62754313584
FETCH #140203016759904:c=48,e=48,p=0,cr=12,cu=0,mis=0,r=2,dep=0,og=1,plh=50804201,tim=62754313975
...

That's right: the first fetch returned 50 rows (same arraysize as set in the extension's settings), and all subsequent fetch calls returned only 2 rows :-(

Once we set the arraysize explicitly to 50 rows (SET ARRAYSIZE 50) the fetch behaviour is as expected, and rows from the 2nd queries are retrieved in 6 fetches (5 x 50 rows + 1 extra fetch returning 0 row).

Regards,