Home » SQL & PL/SQL » SQL & PL/SQL » For loop taking more than 24 hours to complete (merged 4)
For loop taking more than 24 hours to complete (merged 4) [message #413006] Mon, 13 July 2009 23:54 Go to next message
apprentice
Messages: 21
Registered: June 2009
Location: Malaysia
Junior Member
Hi Frens,

This for loop took close to 24 hours to complete.I cant seem to detect the problem.Can someone help to advice?

vpath := CS_UTILS.GET_UTL_PATH ;


For Rec in Lot_Ship_Assy_Filename  Loop

       v_sysdate    := to_char(sysdate-1,'YYYYMMDD');
       Filename:= Rec.shipfrom||'_'||Rec.shipto||'_'||substr(v_sysdate,1,8)||'_'||Rec.invoiceno||'.txt';

        edi_hat_data := utl_file.fopen(vpath,filename,'W');

        var1 := to_char(sysdate,'YYYYMMDD HH24MISS');
        dbms_output.put_line('Start      '||var1);

        Hdr_Sql := 'H'||to_char(sysdate-1,'YYMMDD');

        utl_file.put_line(edi_hat_data,Hdr_sql);
        nRecord := '0';

    For Lotcur in Lot_Ship_Assy (Rec.InvoiceNo) Loop
      vLotCode := ' ';


        If    instr(vLotCode,'RR') > 0 or instr(vLotCode,'CR') > 0 or
              instr(vLotCode,'RT') > 0 or instr(vLotCode,'RE') > 0 or
              instr(vLotCode,'CE') > 0 or instr(vLotCode,'RW') > 0 Then
              vLotCode := 'REWORK';
        Elsif (instr(vLotCode,'E') > 0 and instr(vLotCode,'IE') = 0 and instr(vLotCode,'PPE') = 0) or instr(vLotCode,'Q') > 0 Then
              vLotCode := 'ENGINEERING';
        Else
              vLotCode := 'PRODUCTION';
        End if;


      Unix_Sql := rpad(LotCur.Record_Id,1,' ')||rpad(Lotcur.InvoiceNo,20,' ')||rpad(Lotcur.MAWB,35,' ')||rpad(NVL(Lotcur.HAWB,' '),35,' ')||
                  rpad(Lotcur.Ship_Out_Date,6,' ')||rpad(Lotcur.Ship_Out_Time,6,' ')||
                  rpad(Lotcur.From_Org_Code,3,' ')||
                  rpad(Lotcur.To_Org_Code,3,' ')||
                  rpad(Lotcur.Intersil_Lot_No,20,' ')||rpad(Lotcur.Dept,8,' ')||rpad(Lotcur.WaferLotNumber,20,' ')||
                  rpad(Lotcur.Part_Name,25,' ')||
                  rpad(Lotcur.PD_Part_Name,25,' ')||lpad(Lotcur.Die_Qty,10,'0')||
                  rpad(Lotcur.Wafer_Quantity,2,'0')||
                  rpad(NVL(Lotcur.Tracecode,' '),10,' ')||rpad(NVL(Lotcur.Datecode,' '),4,' ')||rpad(vLotCode,20,' ')||
                  rpad(' ',30,' ');

      utl_file.put_line(edi_hat_data,Unix_sql);
      nRecord := nRecord + 1;
End Loop;
      
      Hdr_Sql := 'T'||lpad(nRecord,5,'0');
      utl_file.put_line(edi_hat_data,Hdr_sql);

      v_msg := 'Close Unix File';
      utl_file.fclose(edi_hat_data);

End loop;
Re: For loop taking more than 24 hours to complete [message #413010 is a reply to message #413006] Tue, 14 July 2009 00:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://www.orafaq.com/forum/m/382787/102589/

Regards
Michel
Re: For loop taking more than 24 hours to complete [message #413017 is a reply to message #413006] Tue, 14 July 2009 00:20 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member

ALTER SESSION SET SQL_TRACE=TRUE;
-- invoke the problem SQL here
ALTER SESSION SET SQL_TRACE=FALSE;

now find the trace file within ./udump folder
tkprof <trace_file.trc> trace_results.txt
post the contents of trace_results.txt back here
Re: For loop taking more than 24 hours to complete (merged 4) [message #413018 is a reply to message #413006] Tue, 14 July 2009 00:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
STOP posting the same question over and over.

Regards
Michel
Re: For loop taking more than 24 hours to complete (merged 4) [message #413021 is a reply to message #413018] Tue, 14 July 2009 00:45 Go to previous messageGo to next message
apprentice
Messages: 21
Registered: June 2009
Location: Malaysia
Junior Member
Sorry! I didnt meant to.The page hanged when I submitted the post.So I re-submitted causing two duplicate posts.
Re: For loop taking more than 24 hours to complete [message #413023 is a reply to message #413017] Tue, 14 July 2009 00:56 Go to previous messageGo to next message
apprentice
Messages: 21
Registered: June 2009
Location: Malaysia
Junior Member
Hi Black Swan,

Is this applicable to SQL Nav 4.5? I dont quite know how to use the statements you have suggested.

Can you guide?
Re: For loop taking more than 24 hours to complete (merged 4) [message #413024 is a reply to message #413006] Tue, 14 July 2009 01:02 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Re: For loop taking more than 24 hours to complete (merged 4) [message #413034 is a reply to message #413021] Tue, 14 July 2009 01:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
apprentice wrote on Tue, 14 July 2009 07:45
Sorry! I didnt meant to.The page hanged when I submitted the post.So I re-submitted causing two duplicate posts.

2 duplicates? Not 3.
And I don't trust you the last one was posted AFTER you reported my first answer.

Regards
Michel
Re: For loop taking more than 24 hours to complete (merged 4) [message #413050 is a reply to message #413024] Tue, 14 July 2009 03:03 Go to previous messageGo to next message
apprentice
Messages: 21
Registered: June 2009
Location: Malaysia
Junior Member
Hi Black Swan,

I did read up but I dont know what am I doing wrong
Re: For loop taking more than 24 hours to complete (merged 4) [message #413054 is a reply to message #413006] Tue, 14 July 2009 03:39 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
First off you should have continued in the other thread.

Secondly you need to follow Blackswan's instructions for tracing the session, we need that to see where all the time is being spent - use sqlplus and jsut type the commands as stated.

Thirdly, as I pointed out in the other thread this:
vLotCode := ' ';


        If    instr(vLotCode,'RR') > 0 or instr(vLotCode,'CR') > 0 or
              instr(vLotCode,'RT') > 0 or instr(vLotCode,'RE') > 0 or
              instr(vLotCode,'CE') > 0 or instr(vLotCode,'RW') > 0 Then
              vLotCode := 'REWORK';
        Elsif (instr(vLotCode,'E') > 0 and instr(vLotCode,'IE') = 0 and instr(vLotCode,'PPE') = 0) or instr(vLotCode,'Q') > 0 Then
              vLotCode := 'ENGINEERING';
        Else
              vLotCode := 'PRODUCTION';
        End if;


Does exactly the the same as this:
vLotCode := 'PRODUCTION';


I assume it should be something like this:
If instr(Lotcur.<some column from the cursor>,'RR') > 0 or .....
  vLotCode := 'REWORK';
Elsif ......

etc

Re: For loop taking more than 24 hours to complete (merged 4) [message #413074 is a reply to message #413054] Tue, 14 July 2009 05:10 Go to previous messageGo to next message
apprentice
Messages: 21
Registered: June 2009
Location: Malaysia
Junior Member
Noted on that one cookie.

Ive done the changes but the results are still the same,very slow!

Maybe I can try to break the sql into two portion.Any idea how this can be done?

Cursor Lot_Ship_Assy (pInvoiceNo varchar2) Is
Select  'D' Record_Id,'TESTBANK' Dept,
         pr.invoiceno InvoiceNo,
         decode(instr(max(dies.device),'/'),'0',max(dies.device),substr(max(dies.device),instr(max(dies.device),'/')+1,length(max(dies.device)))) Part_Name,
         nvl(substr(max(dies.device),1,instr(max(dies.device),'/')-1), max(dietype)) PD_Part_Name,
         dies.customerlotnumber Intersil_Lot_No,
         NVL(max(dies.waferlotnumber),max(dies.originalcustomerlotnumber)) WaferLotNumber,
         sum(fw.componentqty) Die_Qty, nvl(max(dies.waferquantity),0) Wafer_Quantity,
         max(MAWB) MAWB , max(HAWB) HAWB, max(shipfrom) From_Org_Code, max(shipto) To_Org_Code,
         substr(max(pr.invoicedate),3,6) Ship_Out_Date,
         substr(max(pr.invoicedate),10,6) Ship_Out_Time ,
         decode(max(dies.customercode||dies.package),'HUSTBA','E',max(fw.lottype)) Owner_Code,
         max(dies.labeldatecode) DateCode,
         max('F'||labeldatecode||tracecode) tracecode
From     fwlot fw, fwcatns_lotorder lto, fwcatns_diesinventory dies, proman_invoice_info pr
where    fw.sysid = dies.lotobject and dies.lotorderid = lto.lotorderid and (dies.asyinvoiceno = pr.invoiceno or dies.wpinvoiceno = pr.invoiceno) and
         lto.lotorderline = '300' and (instr(fw.appid,'-') > 0 and instr(fw.appid,'-C') = 0 ) and
         fw.processingstatus in ('Active','Finished') and
         dies.lotorderstate  = 'Finished' and
         ltrim(dies.customercode) in ('HAT','HUS','ISL','ELS','ELX')  and
         pr.invoicedate > '20090712 235959' and
         pr.invoicedate <= '20090713 235959'
Group by dies.customerlotnumber,invoiceno;




Cursor Lot_Ship_Assy_Filename Is
select max(shipfrom) ShipFrom,max(shipto)ShipTo,max(invoiceno)InvoiceNo from
fwcatns_diesinventory d, proman_invoice_info p, fwcatns_lotorder l, fwlot fw
where (d.asyinvoiceno = p.invoiceno or d.wpinvoiceno = p.invoiceno)
AND d.lotorderid = l.lotorderid
and d.lotobject = fw.sysid
and l.lotorderline = '300'
and d.lotorderstate = 'Finished'
and fw.processingstatus in ('Active','Finished')
AND LTRIM (d.customercode) IN ('HAT', 'HUS', 'ISL', 'ELS', 'ELX')
AND (Invoicedate >  '20090712 235959'
AND invoicedate <=  '20090713 235959')
group by invoiceno;
Re: For loop taking more than 24 hours to complete (merged 4) [message #413077 is a reply to message #413006] Tue, 14 July 2009 05:30 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well I didn't expect that fix to speed anything up it was just obviously wrong.
You need to trace the session, there is no point trying to fix the performance until you know what is taking all the time.
Re: For loop taking more than 24 hours to complete (merged 4) [message #413093 is a reply to message #413077] Tue, 14 July 2009 06:18 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You could use DBMS_PROFILER to see where the time gets spent.
Re: For loop taking more than 24 hours to complete (merged 4) [message #413139 is a reply to message #413006] Tue, 14 July 2009 09:51 Go to previous message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
Post Operating System (OS) name & version for DB server system.
Post Oracle version to 4 decimal place of database s/w from v$version.

SELECT   Max(shipfrom)  shipfrom,
         Max(shipto)    shipto,
         Max(invoiceno) invoiceno
FROM     fwcatns_diesinventory d,
         proman_invoice_info p,
         fwcatns_lotorder l,
         fwlot fw
WHERE    (d.asyinvoiceno = p.invoiceno
           OR d.wpinvoiceno = p.invoiceno)
         AND d.lotorderid = l.lotorderid
         AND d.lotobject = fw.sysid
         AND l.lotorderline = '300'
         AND d.lotorderstate = 'Finished'
         AND fw.processingstatus IN ('Active','Finished')
         AND Ltrim(d.customercode) IN ('HAT','HUS','ISL','ELS',
                                       'ELX')
         AND (invoicedate > '20090712 235959'
              AND invoicedate <= '20090713 235959')
GROUP BY invoiceno; 


We don't know table definitions so don't know which columns come from which tables.

Don't compare DATE with strings.

Are all columns in WHERE clause indexed?

Do all tables & indexes have current statistics?
Previous Topic: join two tables with no common coloumn
Next Topic: How to get the name of the column of the table that caused exception while loading data
Goto Forum:
  


Current Time: Mon Dec 05 18:48:01 CST 2016

Total time taken to generate the page: 0.20721 seconds