Forum Stats

  • 3,817,218 Users
  • 2,259,290 Discussions
  • 7,893,701 Comments

Discussions

loop cursor in plsql

pjsiong-JavaNet
pjsiong-JavaNet Member Posts: 398 Green Ribbon
edited Sep 15, 2011 10:12AM in SQL & PL/SQL
Hi,
I create a procedure which is something like below:
create or replace procedure INSERT_DATA is
cursor cursor1(vfield varchar2) is
(
select field1,field2,field3,field4
 from tableb z, xmltable( .....) x
       where z.field1=vfield
       );  

begin
  for c in (select fieldname from tablea)
  loop
    for t in cursor1(c.fieldname)
    loop
     insert into tablec 
      (field1,field2,field3,field4)
      values
      (t.field1,t.field2,t.field3,t.field4);
    end loop;  
    commit;
  end loop;

   
end INSERT_DATA;
when I run this procedure, I found that the memory usage of Oracle process in the server will keep increasing,
I want to run this procedure against some huge table, if the memory usage keep increase, I am afraid that it will exhaust the memory.
Is there any way to improve this?

Thanks

Vincent

Edited by: pj**** on 15-Sep-2011 00:42
«1

Answers

  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge
    Dont use Cursor and Loop just do it in SQL
    insert into tablec (field1,field2,field3,field4)
    select field1,field2,field3,field4
      from tableb z, 
           tablea a,
           xmltable( .....) x
     where z.field1=a.fieldname
  • pjsiong-JavaNet
    pjsiong-JavaNet Member Posts: 398 Green Ribbon
    Hi, Karthick
    Thanks for the reply but I tried but still the same ...any suggestion?

    Thanks

    Vincent
  • Hoek
    Hoek Member Posts: 16,087 Gold Crown
    Why are you using a loop in a loop instead of a single SQL statement?
    Why don't you use a join?
  • odie_63
    odie_63 Member Posts: 8,466 Silver Trophy
    Hi,

    If I recall correctly from your posts in the XML DB forum, you're using XML data stored as CLOB.
    That's the worst-case scenario for large volumes of data.

    Could you post the real query and an explain plan? (with up-to-date statistics)
  • Hoek
    Hoek Member Posts: 16,087 Gold Crown
    Here's an explanation regarding a similar construction (loop in loop):
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1288401763279#tom59921197472382
    Try to join all tables rightaway., use an APPEND hint to speed things up even more.
    Something like:
    insert /*+ APPEND */
    into  ...
    select ...
    from ...
    where ...
  • pjsiong-JavaNet
    pjsiong-JavaNet Member Posts: 398 Green Ribbon
    Hi, Odie
    Thanks for remember me, the actual query is as below
    select testname,StepName,Status,Comp,Data,TESTSTATUS,SingleTest
     from cust_vw_test_log_clob z, xmltable(
           '
           declare function local:getChildren($p as element()) as element()*
            {
             for $i in $p
             return 
             (
             if ($i/Prop[5]/@Name="Measurement") 
             then (
                 for $t in $i/Prop[@Name="Measurement"]/Value
                 return
                 element r {
                         element StepName1 {$t/Prop/@Name} 
                        ,element Status1 {$t/Prop/Prop[@Name="Status"]/Value/text()}
                        ,element Comp1 {$t/Prop/Prop[@Name="Comp"]/Value/text()}
                        ,element Data1 {$t/Prop/Prop[@Name="Data"]/Value/text()}
                        ,element Testname1 {$i/Prop[@Name="TS"]/Prop[@Name="StepName"]/Value/text()}
                        ,element TEST_STATUS {$i/Prop[@Name="Status"]/Value/text()}
                        
                       }             
                  )
             else 
               (
    		   for $t in $i
               return
               element r {
                     element SingleTest {$t/Prop[@Name="TS"]/Prop[@Name="StepName"]/Value/text()}
                    ,element TEST_STATUS {$t/Prop[@Name="Status"]/Value/text()}
                    ,element Data1 {$t/Prop[@Name="Numeric"]/Value/text()} 
                        }
               ,
    		   for $a in $i/Prop[@Name="TS"]/Prop[@Name="SequenceCall"]/Prop[@Name="ResultList"]/Value
    		   return
    		   local:getChildren($a/Prop[@Type="TEResult"])
               )           
             )
            }; (: :)
            local:getChildren(/Reports/Report/Prop[@Type="TEResult"])'
           passing xmltype(z.test_log_text)
           columns 
           StepName varchar2(500) path 'StepName1/@Name',
           Status varchar2(500) path 'Status1',
           Comp varchar2(500) path 'Comp1',
           Data varchar2(500) path 'Data1',
           TestName varchar2(100) path 'Testname1',       
           TestStatus varchar2(100) path 'TEST_STATUS',
           SingleTest varchar2(100) path 'SingleTest'       
          ) x
           where z.ticket=vTicket
    vincent
  • odie_63
    odie_63 Member Posts: 8,466 Silver Trophy
    the actual query is as below
    Oh, that one... ;)

    It's using a recursive XQuery function, no wonder you hit performance and memory issues on big documents.
    Furthermore, if you're still on a 10.2 database, the XQuery engine used in this case is Java-based.

    Any chance you could at least use an XMLType column for XML storage?
  • pjsiong-JavaNet
    pjsiong-JavaNet Member Posts: 398 Green Ribbon
    Hi, Odie
    sign...it's a big document so I have to zip it and keep in a blob field, otherwise the db grow 32GB per month.
    For the query, I actually unzip the blob field into clob in a view. will it be help if I unzip and cast it into xmltype?

    Thanks
    Vincent
  • odie_63
    odie_63 Member Posts: 8,466 Silver Trophy
    edited Sep 15, 2011 9:13AM
    For the query, I actually unzip the blob field into clob in a view.
    Using UTL_COMPRESS?
    will it be help if I unzip and cast it into xmltype?
    I'm afraid not.
    The data should at least be materialized (physically stored) as an XMLType column at some point, but even then I guess you won't see that much improvement if the underlying storage model is still CLOB.

    Do you have an XML schema describing the document?

    Another solution that sometimes performs better than XQuery is XSL transformation.
    See this example which is close to your situation : {message:id=9753074}

    Edited by: odie_63 on 15 sept. 2011 15:08
  • pjsiong-JavaNet
    pjsiong-JavaNet Member Posts: 398 Green Ribbon
    Yes...using utl_compress
    Do you have an XML schema describing the document?
    Do you mean XSL? I have the xsl which can be downloaded from http://124.82.216.245:81/test.xsl
    what can I do with the xsl?

    Thanks

    Vincent
This discussion has been closed.