Home » RDBMS Server » Performance Tuning » slower execution of pl/sql code
slower execution of pl/sql code [message #440997] Thu, 28 January 2010 04:41 Go to next message
mandark
Messages: 23
Registered: October 2009
Junior Member
Hi,
I am having issue with performance of following PL/SQL code
in Oracle iDS 10g with oracle 11i database

PROCEDURE load_credit_soc IS

in_file           TEXT_IO.FILE_TYPE ;
out_file          TEXT_IO.FILE_TYPE ;
exception_raised  VARCHAR2(1)       ; 
linebuf           VARCHAR2(32767)      ;
TXT_TEMP          VARCHAR2(100);
V_TEMP 	TEXT_IO.FILE_TYPE ;
temp_emp      varchar2(6);
temp_area      varchar2(20);
temp_area_emp      varchar2(100);
temp_line     number  :=  0;
CNT  NUMBER;
SAMMY VARCHAR2(10);


BEGIN

     
                       in_file  := TEXT_IO.FOPEN(:filename , 'r');
--
                       :module := 'TEXT FILE SPLITTER';
--
                         SAMMY := 'TEST';
--
                         CNT := 1;
           --
                         V_TEMP :=   TEXT_IO.FOPEN('C:\SPLITTER\'  || SAMMY || TO_CHAR(CNT) || '.'  || 'TXT','W' );
--
                  IF TEXT_IO.IS_OPEN(in_file) THEN
                  BEGIN
                  loop
          --
                       --  message('Inside Loop'); pause;
  
                         temp_line  :=  temp_line  +  1;
                         :NBT_MODULE  :=   'Processing Line #  ' ||  to_char(temp_line) ;
              --
                        :NBT_EMP  :=   temp_emp;
                        :NBt_CNT  :=   to_char(CNT);
            --
                         synchronize;
               --
  --
                         TEXT_IO.GET_LINE(in_file, linebuf);

--
                   IF INSTR(UPPER(LINEBUF), :IDENTIFIER) >  0 THEN
--
                             temp_emp  :=  LTRIM(RTRIM(SUBSTR(LINEBUF, :START_POS, :SIZER)));
--
                   END IF;
--
--
                   IF INSTR(UPPER(LINEBUF),'AREA :') >  0 THEN
--
                             temp_area  :=  SUBSTR(LINEBUF, 113, 5);
--
                   END IF;
--
                   IF INSTR(UPPER(LINEBUF),  :TERMINATOR) >  0 THEN
                                        --

                                                TEXT_IO.PUT_LINE(V_TEMP,linebuf);
            
                                        --
                                                   TEXT_IO.FCLOSE(V_TEMP); 

                                                   TXT_TEMP  :=  'C:\SPLITTER\'|| SAMMY  || TO_CHAR(CNT) || '.'||'TXT';--
                                                   --
--                 temp_area_emp  :=    LTRIM(RTRIM(TEMP_AREA))    ||   temp_emp;
                   temp_area_emp  :=    temp_emp;
                                            --
                                                   FILE_XFER(TXT_TEMP, temp_area_emp); 
                                                                                            
--

                                                   CNT :=         CNT   +     1;
                                       --
                                               V_TEMP := TEXT_IO.FOPEN('C:\SPLITTER\'  || SAMMY   || TO_CHAR(CNT)  || '.'   || 'TXT','W' );
--                                              message('STARTING  NEW  ' );
--                                              message('STARTING  NEW  ' );
                          --
                            
                               

                      --
                     ELSE
                   --
                                     TEXT_IO.PUT_LINE( V_TEMP ,linebuf  );


                  END IF;


--





END LOOP;




            --TEXT_IO.FCLOSE(V_TEMP);
  

 EXCEPTION
          WHEN no_data_found THEN
              TEXT_IO.FCLOSE(in_file)  ;
              TEXT_IO.FCLOSE(out_file) ;
 END ;
  
            ELSE 
            message('FPS0111:file not loaded'); pause ;
             END IF ;
--              message('COMPLETEDD First  ROUND   ') ; pause ;
END;



when I am using above code in D2K forms 4.5 with oracle 8i as database execution of code which is generating text files takes around 10 mins for 7000 files to be generated.
But with oracle iDS 10g and oracle 11i as database it is taking around 70 mins for same no of files to be geneated.
As it is not accessing any of the tables, what might be the problem with execution of the code?
Re: slower execution of pl/sql code [message #441235 is a reply to message #440997] Fri, 29 January 2010 10:56 Go to previous message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
You need to do some basic code tuning.

put some timing statements in your code and see if you can figure out if one particular piece is taking up most of your time.

Kevin
Previous Topic: Bitmap Join Index for DW
Next Topic: Heap size exceeds warning notification in alert.log
Goto Forum:
  


Current Time: Tue Sep 27 04:14:10 CDT 2016

Total time taken to generate the page: 0.11057 seconds