4 Replies Latest reply: Jul 9, 2009 2:36 AM by Timur Akhmadeev RSS

    Selecting CLOB column makes fetching one by one

    640834
      Hi,

      I paste my test case (10.2.0.3.0 - 64bi Solaris):
      drop table test purge;
       CREATE TABLE test
        (    x number,
             val CLOB       
        ) PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
       TABLESPACE "GESAFIN"
      LOB ("VAL") STORE AS (
       TABLESPACE "GESAFIN" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
       CACHE ) ;
        
      insert into test select rownum, rpad('X',1000,'X') from dual connect by level<=100;
      commit;
      
      set arraysize 13
      select x from test;
      select val from test;
      Checking how many fetches did the querys
      43.DESA.sys> select EXECUTIONS, FETCHES from v$sql where sql_text='select x from test';
      EXECUTIONS    FETCHES
      ---------- ----------
               1          9
      
      1 fila seleccionada.
      
      43.DESA.sys> select EXECUTIONS, FETCHES from v$sql where sql_text='select val from test';
      EXECUTIONS    FETCHES
      ---------- ----------
               1        101
      Checking the number of fetches with event 10046:

      PARSING IN CURSOR #31 len=18 dep=0 uid=71 oct=3 lid=71 tim=9369917735690 hv=498814776 ad='4e4fa338'
      select x from test
      END OF STMT
      PARSE #31:c=10000,e=38959,p=0,cr=24,cu=0,mis=1,r=0,dep=0,og=1,tim=9369917735679
      EXEC #31:c=0,e=90,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=9369917735955
      FETCH #31:c=0,e=135,p=0,cr=4,cu=0,mis=0,r=1,dep=0,og=1,tim=9369917736270
      FETCH #31:c=0,e=101,p=0,cr=3,cu=0,mis=0,r=13,dep=0,og=1,tim=9369917737395
      FETCH #31:c=0,e=129,p=0,cr=3,cu=0,mis=0,r=13,dep=0,og=1,tim=9369917778455
      FETCH #31:c=0,e=118,p=0,cr=3,cu=0,mis=0,r=13,dep=0,og=1,tim=9369917814198
      FETCH #31:c=0,e=128,p=0,cr=3,cu=0,mis=0,r=13,dep=0,og=1,tim=9369917844345
      FETCH #31:c=0,e=115,p=0,cr=3,cu=0,mis=0,r=13,dep=0,og=1,tim=9369917866263
      FETCH #31:c=0,e=103,p=0,cr=3,cu=0,mis=0,r=13,dep=0,og=1,tim=9369917887138
      FETCH #31:c=0,e=118,p=0,cr=6,cu=0,mis=0,r=13,dep=0,og=1,tim=9369917903654
      FETCH #31:c=0,e=61,p=0,cr=2,cu=0,mis=0,r=8,dep=0,og=1,tim=9369917909345
      STAT #31 id=1 cnt=100 pid=0 pos=1 obj=123063 op='TABLE ACCESS FULL TEST (cr=30 pr=0 pw=0 time=620 us)'


      PARSING IN CURSOR #32 len=20 dep=0 uid=71 oct=3 lid=71 tim=9369948129295 hv=3426193149 ad='c045b1d8'
      select val from test
      END OF STMT
      PARSE #32:c=20000,e=5290,p=0,cr=24,cu=0,mis=1,r=0,dep=0,og=1,tim=9369948129284
      EXEC #32:c=0,e=153,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=9369948129616
      FETCH #32:c=0,e=184,p=0,cr=4,cu=0,mis=0,r=1,dep=0,og=1,tim=9369948130970
      FETCH #32:c=0,e=72,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=9369948140029
      FETCH #32:c=0,e=198,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=9369948145704
      FETCH #32:c=0,e=59,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=9369948151327
      FETCH #32:c=0,e=79,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=9369948156580
      FETCH #32:c=0,e=41,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=9369948161792
      FETCH #32:c=0,e=50,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=9369948167029
      FETCH #32:c=0,e=60,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=9369948172606
      FETCH #32:c=0,e=196,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=9369948178297
      FETCH #32:c=0,e=77,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=9369948183919
      FETCH #32:c=0,e=57,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=9369948189275
      FETCH #32:c=0,e=209,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=9369948194867
      FETCH #32:c=0,e=61,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=9369948200375
      FETCH #32:c=0,e=85,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=9369948205640
      FETCH #32:c=0,e=44,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=9369948211135
      FETCH #32:c=0,e=44,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=9369948216247
      FETCH #32:c=0,e=56,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=9369948221506
      FETCH #32:c=0,e=106,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=9369948227277
      FETCH #32:c=0,e=51,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=9369948232450
      FETCH #32:c=0,e=73,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=9369948237632
      FETCH #32:c=0,e=42,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=9369948242821
      FETCH #32:c=0,e=41,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=9369948247990
      FETCH #32:c=0,e=116,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=9369948253276
      FETCH #32:c=0,e=42,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=9369948258580
      FETCH #32:c=0,e=48,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=9369948264021
      FETCH #32:c=0,e=54,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=9369948269173
      FETCH #32:c=0,e=51,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=9369948274401
      ... continues...


      Why when selecting the clob column it fetches rows one by one?

      Thanks,

      Joaquin Gonzalez
        • 1. Re: Selecting CLOB column makes fetching one by one
          Jonathan Lewis
          In reply to your prompt on the other thread (Value for FETCH in TKPROF is 2, though I executed the SQL only once ): I don't know why this happens.

          My best guess is that it is a defensive measure. A LOB can be very large, and may require multiple message to get the whole thing back from the database to the front-end. So it's possible that SQL*plus is coded to handle every lob in two steps, namely "get the lob locator plus the first piece of lob; then get the rest of the lob in pieces".

          If this guess is correct, you could understand that it's going to be easier to code it by fetching one row at a time in all cases - as there would be less to do tracking memory allocations.

          Regards
          Jonathan Lewis
          http://jonathanlewis.wordpress.com
          http://www.jlcomp.demon.co.uk

          "For every expert there is an equal and opposite expert."
          Arthur C. Clarke
          • 2. Re: Selecting CLOB column makes fetching one by one
            Timur Akhmadeev
            Not reproduced on a
            SQL> select * from v$version;
             
            BANNER
            ----------------------------------------------------------------
            Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
            select val 
            from
             test
            
            
            call     count       cpu    elapsed       disk      query    current        rows
            ------- ------  -------- ---------- ---------- ---------- ----------  ----------
            Parse        1      0.00       0.00          0          1          0           0
            Execute      1      0.00       0.00          0          0          0           0
            Fetch        3      0.00       0.00          0         25          0         100
            ------- ------  -------- ---------- ---------- ---------- ----------  ----------
            total        5      0.00       0.00          0         26          0         100
            I've used 40 as an arraysize.
            • 3. Re: Selecting CLOB column makes fetching one by one
              26741
              Is "val" a LOB column ?
              • 4. Re: Selecting CLOB column makes fetching one by one
                Timur Akhmadeev
                Sure.

                Edited by: Timur Akhmadeev on Jul 9, 2009 11:35 AM

                Got it: I've tested with PL/SQL Developer the first time. In SQL*Plus the results I have are the same as OP's:
                select val 
                from
                 test
                
                
                call     count       cpu    elapsed       disk      query    current        rows
                ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                Parse        1      0.00       0.00          0         24          0           0
                Execute      1      0.00       0.00          0          0          0           0
                Fetch      101      0.00       0.00          0        106          0         100
                ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                total      103      0.00       0.00          0        130          0         100
                So, this is again a SQL*Plus issue.