This discussion is archived
7 Replies Latest reply: Nov 25, 2012 6:43 AM by Solomon Yakobson RSS

ARRAYSIZE

Oracle Maniac Explorer
Currently Being Moderated
When we say the sqlplus arraysize is 100 this means every fetch from the server yields 100 rows to the client . so how is the result divided into slots ? is this has something to do with the cursor fetch ? does setting the arraysize determine the fetches from the cursor at a time . These kind of settings are implicitly set for gui based client tools like toad,sqldev etc.... am i right ?
  • 1. Re: ARRAYSIZE
    rp0428 Guru
    Currently Being Moderated
    >
    When we say the sqlplus arraysize is 100 this means every fetch from the server yields 100 rows to the client
    . so how is the result divided into slots ? is this has something to do with the cursor fetch ? does setting the arraysize determine the fetches from the cursor at a time . These kind of settings are implicitly set for gui based client tools like toad,sqldev etc.... am i right ?
    >
    Yes - it means that when the 'first' row is wanted sql*plus actually retrieves 100 rows into its buffer and then uses the first row. When the 'second' row is wanted sql*plus gets it from the buffer. When the 101st row is wanted sql*plus retrieves another 100 rows into its buffer and then uses the 101st row (the first in the buffer). This minimizes the number of round trips over the network; 100 rows are always sent from the server to sql*plus unless there are fewer than 100 rows left.

    It might be easier to understand with a Java example. If you create Java code that sets the fetchsize to 100 the Oracle driver will create a buffer than can hold 100 rows.

    When you execute a SELECT query it returns a resultset and 100 rows will be returned to the Oracle driver's buffer. When you execute the ResultSet NEXT method the driver will return the row from the buffer. When you ask for row 101 (you execute NEXT for the 101th time) the Oracle driver will retrieve the next 100 rows from the server and give you the first one (row 101).
  • 2. Re: ARRAYSIZE
    Oracle Maniac Explorer
    Currently Being Moderated
    Thanks for the reply ....Yes you made my doubts clear but there is still one doubt in my mind.. So when we say that the client fetches 100 rows into its buffer.So does this mean that the the cursor which is opened in the server for my query is advanced to fetch another 100 rows. I am trying to correlate the behaviour of cursors inside plsql and sql. Just as we can loop around explicitly with the cursor inside a plsql block , the sql*plus arraysize does the same thing ???
  • 3. Re: ARRAYSIZE
    Stew Ashton Expert
    Currently Being Moderated
    Rahul  K wrote:
    Just as we can loop around explicitly with the cursor inside a plsql block , the sql*plus arraysize does the same thing ???
    SQL*Plus and PL/SQL are both "clients" as far as the SQL engine is concerned. The SQL engine doesn't see any difference between clients. It was asked to parse a SELECT, then to execute it, then to fetch so many rows.

    If SQL*Plus has an arraysize of 100, the first fetch will cause the server to return the first 100 rows.

    If you fetch from an explicit cursor in PL/SQL using BULK COLLECT and a LIMIT of 100, the server will return the first 100 rows.

    Whatever the client code does with rows 1 through 100, the server will not be contacted.

    When row 101 is asked for, the server will be called and it will return rows 101 through 200.
  • 4. Re: ARRAYSIZE
    Solomon Yakobson Guru
    Currently Being Moderated
    Stew Ashton wrote:
    If SQL*Plus has an arraysize of 100, the first fetch will cause the server to return the first 100 rows.
    Common misperception. There is array fetch and there is prefetch. So first fetch will not cause server to return first 100 rows. It will cause server to return 100 rows starting with second row. SQL*PLus is written in OCI and in OCI prefetch defaults to 1. Prefetch can occur on EXECUTE and on FETCH. However, prefetch on FETCH occurs only when we are not performing an array fetch, or in SQL*Plus terms, when ARRAYSIZE is 1. So when ARRAYSIZE is 1, which means scalar fetch Oracle prefetches 1 row on EXECUTE and one row on FETCH so number of rows fetched into SQL*Plus buffer will be 1,2,2,... per trip. If we set arraysize to 100, number of rows fetched into SQL*Plus buffer will be 1,100,100,... per trip. For example:
    SQL> alter session set sql_trace=true
      2  /
    
    Session altered.
    
    SQL> select * from tbl;
    
             L
    ----------
             1
    .
    .
    .
           100
    
    100 rows selected.
    
    SQL> alter session set sql_trace=false
      2  /
    
    Session altered.
    
    SQL> 
    And trace file:
    =====================
    PARSING IN CURSOR #3 len=17 dep=0 uid=84 oct=3 lid=84 tim=264105652251 hv=576154433 ad='1fec75d4' sqlid='aj6bazhj5fuu1'
    select * from tbl
    END OF STMT
    PARSE #3:c=0,e=121,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2144214008,tim=264105652246
    EXEC #3:c=0,e=62,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2144214008,tim=264105652824
    FETCH #3:c=0,e=2369,p=0,cr=3,cu=0,mis=0,<font size=3 color=red>r=1</font>,dep=0,og=1,plh=2144214008,tim=264105655386
    FETCH #3:c=0,e=159,p=0,cr=5,cu=0,mis=0,<font size=3 color=red>r=99</font>,dep=0,og=1,plh=2144214008,tim=264105657564
    STAT #3 id=1 cnt=100 pid=0 pos=1 obj=81926 op='TABLE ACCESS FULL TBL (cr=8 pr=0 pw=0 time=0 us cost=4 size=1300 card=100)'

    *** 2012-11-25 07:25:23.984
    CLOSE #3:c=0,e=44,dep=0,type=0,tim=264108659107
    =====================
    Now look what happens when we insert one more row:
    =====================
    PARSING IN CURSOR #3 len=17 dep=0 uid=84 oct=3 lid=84 tim=264501257987 hv=576154433 ad='1fec75d4' sqlid='aj6bazhj5fuu1'
    select * from tbl
    END OF STMT
    PARSE #3:c=0,e=91,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2144214008,tim=264501257982
    EXEC #3:c=0,e=64,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2144214008,tim=264501258424
    FETCH #3:c=0,e=18755,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=1,plh=2144214008,tim=264501277373
    FETCH #3:c=0,e=145,p=0,cr=3,cu=0,mis=0,r=100,dep=0,og=1,plh=2144214008,tim=264501279108
    FETCH #3:c=0,e=46,p=0,cr=2,cu=0,mis=0,r=0,dep=0,og=1,plh=2144214008,tim=264501660843
    STAT #3 id=1 cnt=101 pid=0 pos=1 obj=81926 op='TABLE ACCESS FULL TBL (cr=8 pr=0 pw=0 time=0 us cost=4 size=1300 card=100)'

    *** 2012-11-25 07:32:19.500
    CLOSE #3:c=0,e=31,dep=0,type=1,tim=264524179826
    =====================
    First fetch fetches 1 row. Second 100 rows and third 0 rows. So why do we have third fetch? Because there is no prefetch with array fetch. In previous case second fetch returned 99 rows which is less than ARRAYSIZE of 100. Therefore we know for sure there is nothing more to fetch. Here second fetch returned ARRAYSIAZE rows so we do not know if there are more rows or not. As a result we are forced to issue third fetch.
    Hope it clears the confusion.

    SY.
  • 5. Re: ARRAYSIZE
    Oracle Maniac Explorer
    Currently Being Moderated
    Thanks solomon for drilling in the concepts with data validations (sql trace).. Where can i learn more about sqlplus (oci perspective). Coz the prefetches,fetches etc are bit confusing
  • 6. Re: ARRAYSIZE
    Stew Ashton Expert
    Currently Being Moderated
    Thanks, Solomon. I was not aware of the specific prefetch behavior of OCI.
  • 7. Re: ARRAYSIZE
    Solomon Yakobson Guru
    Currently Being Moderated
    Stew Ashton wrote:
    I was not aware of the specific prefetch behavior of OCI.
    If I am not mistaken, prefetch was introduced in 8i.

    SY.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points