You Asked

Hi Chris/Connor,
Can you please have a look at below scenario:
I have a sql : select * from t1 where status = ‘NEW’; — this has 100 rows.

There is a mule soft application which calls this sql as is by setting parameter at mule as 10 rows each time.
For oracle it’s a simple sql it should return 100 rows.
However mulesoft claims that it is getting the sql response by in chunks of 10.

I have monitored this by using v$sql and found that:
Executions remains 1
Fetches becomes 10
Rows processed equals 100
And have checked session as well remains the same.
I am bit confused here because for my database it is normal select, right?
Is there any parameter getting set at oracle session level due to which oracle is provide data in 10 chunks wise?

and Connor said...

Sorry - I'm not entirely sure what you are asking here, but the number of rows fetched at a time is a decision made by the *client*.

For example:


SQL>
SQL> create table t as select * from dba_objects where rownum <= 100;

Table created.

SQL> set arraysize 10
SQL> select owner, object_name from t;

OWNER                          OBJECT_NAME
------------------------------ ----------------------------------------
SYS                            I_FILE#_BLOCK#
SYS                            I_OBJ3
SYS                            I_TS1
SYS                            I_CON1
SYS                            IND$
SYS                            CDEF$
SYS                            C_TS#
SYS                            I_CCOL2
SYS                            I_PROXY_DATA$
SYS                            I_CDEF4
SYS                            I_TAB1
SYS                            CLU$
SYS                            I_PROXY_ROLE_DATA$_1
SYS                            I_OBJ1
SYS                            UNDO$
SYS                            I_UNDO2
SYS                            I_TS#
SYS                            I_FILE1
SYS                            I_COL2
SYS                            I_OBJ#
SYS                            C_OBJ#
SYS                            I_CDEF3
SYS                            C_COBJ#
SYS                            CCOL$
SYS                            I_OBJ5
SYS                            PROXY_ROLE_DATA$
SYS                            I_CDEF1
SYS                            C_USER#
SYS                            C_FILE#_BLOCK#
SYS                            FET$
SYS                            I_CON2
SYS                            I_OBJ4
SYS                            CON$
SYS                            I_CDEF2
SYS                            ICOL$
SYS                            I_COL3
SYS                            I_CCOL1
SYS                            COL$
SYS                            I_ICOL1
SYS                            UET$
SYS                            PROXY_DATA$
SYS                            USER$
SYS                            I_PROXY_ROLE_DATA$_2
SYS                            I_OBJ2
SYS                            TAB$
SYS                            I_COBJ#
SYS                            I_USER#
SYS                            FILE$
SYS                            OBJ$
SYS                            TS$
SYS                            I_UNDO1
SYS                            BOOTSTRAP$
SYS                            I_COL1
SYS                            I_FILE2
SYS                            I_IND1
SYS                            I_USER2
SYS                            I_USER1
SYS                            SEG$
SYS                            OBJERROR$
SYS                            OBJAUTH$
SYS                            I_OBJAUTH1
SYS                            I_OBJAUTH2
SYS                            C_OBJ#_INTCOL#
SYS                            I_OBJ#_INTCOL#
SYS                            HISTGRM$
SYS                            I_H_OBJ#_COL#
SYS                            HIST_HEAD$
SYS                            I_HH_OBJ#_COL#
SYS                            I_HH_OBJ#_INTCOL#
SYS                            FIXED_OBJ$
SYS                            I_FIXED_OBJ$_OBJ#
SYS                            TAB_STATS$
SYS                            I_TAB_STATS$_OBJ#
SYS                            IND_STATS$
SYS                            I_IND_STATS$_OBJ#
SYS                            OBJECT_USAGE
SYS                            I_STATS_OBJ#
SYS                            PARTOBJ$
SYS                            I_PARTOBJ$
SYS                            DEFERRED_STG$
SYS                            I_DEFERRED_STG1
SYS                            DEPENDENCY$
SYS                            ACCESS$
SYS                            I_DEPENDENCY1
SYS                            I_DEPENDENCY2
SYS                            I_ACCESS1
SYS                            USERAUTH$
SYS                            I_USERAUTH1
SYS                            UGROUP$
SYS                            I_UGROUP1
SYS                            I_UGROUP2
SYS                            TSQ$
SYS                            SYN$
SYS                            VIEW$
SYS                            TYPED_VIEW$
SYS                            SUPEROBJ$
SYS                            I_SUPEROBJ1
SYS                            I_SUPEROBJ2
SYS                            SEQ$
SYS                            I_VIEW1

100 rows selected.

SQL> select
  2     fetches
  3    ,executions
  4    ,rows_processed
  5  from v$sql
  6  where sql_text like 'select owner, object_name from t%';

   FETCHES EXECUTIONS ROWS_PROCESSED
---------- ---------- --------------
        11          1            100

SQL>
SQL> set arraysize 50
SQL> select owner, object_name from t;

OWNER                          OBJECT_NAME
------------------------------ ----------------------------------------
SYS                            I_FILE#_BLOCK#
SYS                            I_OBJ3
SYS                            I_TS1
SYS                            I_CON1
SYS                            IND$
SYS                            CDEF$
SYS                            C_TS#
SYS                            I_CCOL2
SYS                            I_PROXY_DATA$
SYS                            I_CDEF4
SYS                            I_TAB1
SYS                            CLU$
SYS                            I_PROXY_ROLE_DATA$_1
SYS                            I_OBJ1
SYS                            UNDO$
SYS                            I_UNDO2
SYS                            I_TS#
SYS                            I_FILE1
SYS                            I_COL2
SYS                            I_OBJ#
SYS                            C_OBJ#
SYS                            I_CDEF3
SYS                            C_COBJ#
SYS                            CCOL$
SYS                            I_OBJ5
SYS                            PROXY_ROLE_DATA$
SYS                            I_CDEF1
SYS                            C_USER#
SYS                            C_FILE#_BLOCK#
SYS                            FET$
SYS                            I_CON2
SYS                            I_OBJ4
SYS                            CON$
SYS                            I_CDEF2
SYS                            ICOL$
SYS                            I_COL3
SYS                            I_CCOL1
SYS                            COL$
SYS                            I_ICOL1
SYS                            UET$
SYS                            PROXY_DATA$
SYS                            USER$
SYS                            I_PROXY_ROLE_DATA$_2
SYS                            I_OBJ2
SYS                            TAB$
SYS                            I_COBJ#
SYS                            I_USER#
SYS                            FILE$
SYS                            OBJ$
SYS                            TS$
SYS                            I_UNDO1
SYS                            BOOTSTRAP$
SYS                            I_COL1
SYS                            I_FILE2
SYS                            I_IND1
SYS                            I_USER2
SYS                            I_USER1
SYS                            SEG$
SYS                            OBJERROR$
SYS                            OBJAUTH$
SYS                            I_OBJAUTH1
SYS                            I_OBJAUTH2
SYS                            C_OBJ#_INTCOL#
SYS                            I_OBJ#_INTCOL#
SYS                            HISTGRM$
SYS                            I_H_OBJ#_COL#
SYS                            HIST_HEAD$
SYS                            I_HH_OBJ#_COL#
SYS                            I_HH_OBJ#_INTCOL#
SYS                            FIXED_OBJ$
SYS                            I_FIXED_OBJ$_OBJ#
SYS                            TAB_STATS$
SYS                            I_TAB_STATS$_OBJ#
SYS                            IND_STATS$
SYS                            I_IND_STATS$_OBJ#
SYS                            OBJECT_USAGE
SYS                            I_STATS_OBJ#
SYS                            PARTOBJ$
SYS                            I_PARTOBJ$
SYS                            DEFERRED_STG$
SYS                            I_DEFERRED_STG1
SYS                            DEPENDENCY$
SYS                            ACCESS$
SYS                            I_DEPENDENCY1
SYS                            I_DEPENDENCY2
SYS                            I_ACCESS1
SYS                            USERAUTH$
SYS                            I_USERAUTH1
SYS                            UGROUP$
SYS                            I_UGROUP1
SYS                            I_UGROUP2
SYS                            TSQ$
SYS                            SYN$
SYS                            VIEW$
SYS                            TYPED_VIEW$
SYS                            SUPEROBJ$
SYS                            I_SUPEROBJ1
SYS                            I_SUPEROBJ2
SYS                            SEQ$
SYS                            I_VIEW1

100 rows selected.

SQL> select
  2     fetches
  3    ,executions
  4    ,rows_processed
  5  from v$sql
  6  where sql_text like 'select owner, object_name from t%';

   FETCHES EXECUTIONS ROWS_PROCESSED
---------- ---------- --------------
        14          2            200

SQL>

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

without arraysize

A reader, May 18, 2020 - 4:57 am UTC

Hi Connor,
The issue we are facing with the client application unable to process the result set of for example 10k rows.
Hence, before client application fires SELECT it sets some parameter like 1k and SELECT gives the output in chunk of 1k and at the at the end of SQL processing FETCH & ROWS_PROCESSED shows values 10 & 10k respectively.

My doubt is : Setting arraysize may solve client application (like Java/MuleSoft) issue of getting bulk of 10k rows.
But for database perspective it will still execute the select in 10 times (i.e. considering arraysize of 1k) -- Could you please correct if I am wrong?

So, in oracle perspective it is worth running SQL having e.g. 10k rows without arraysize setting instead of running it in chunks right?

May 19, 2020 - 11:05 am UTC

Unless network latency is a key factor, once you get above getting data in batches of a few hundred the performance benefits are small. For example

SQL> set feedback only
SQL> set timing on
SQL> set arraysize 5
SQL> select * from asktom.t;

10000 rows selected.

Elapsed: 00:00:00.35
SQL> set arraysize 50
SQL> select * from asktom.t;

10000 rows selected.

Elapsed: 00:00:00.17
SQL> set arraysize 200
SQL> select * from asktom.t;

10000 rows selected.

Elapsed: 00:00:00.11
SQL> set arraysize 500
SQL> select * from asktom.t;

10000 rows selected.

Elapsed: 00:00:00.10
SQL> set arraysize 1000
SQL> select * from asktom.t;

10000 rows selected.

Elapsed: 00:00:00.09

In all cases, the database will only execute the query ONCE.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

without arraysize

More to Explore

PL/SQL demos

PL/SQL docs

V$SQL field information for array fetch

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

without arraysize

More to Explore

PL/SQL demos

PL/SQL docs