7 Replies Latest reply: Nov 25, 2012 8:43 AM by Solomon Yakobson RSS

    ARRAYSIZE

    Oracle Maniac
      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
          >
          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
            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
              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
                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
                  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
                    Thanks, Solomon. I was not aware of the specific prefetch behavior of OCI.
                    • 7. Re: ARRAYSIZE
                      Solomon Yakobson
                      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.