loop cursor in plsql

pjsiong-JavaNet · Sep 15, 2011 3:40AM

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

Karthick2003 · Sep 15, 2011 3:44AM

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 · Sep 15, 2011 5:01AM

Hi, Karthick
Thanks for the reply but I tried but still the same ...any suggestion?

Thanks

Vincent

Hoek · Sep 15, 2011 5:09AM

Why are you using a loop in a loop instead of a single SQL statement?
Why don't you use a join?

odie_63 · Sep 15, 2011 5:20AM

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 · Sep 15, 2011 5:24AM

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 · Sep 15, 2011 5:46AM

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 · Sep 15, 2011 6:10AM

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 · Sep 15, 2011 8:52AM

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 · Sep 15, 2011 9:08AM

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 · Sep 15, 2011 9:33AM

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

Forum Stats

Quick Links

Categories

loop cursor in plsql

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

loop cursor in plsql

Answers