Home » RDBMS Server » Performance Tuning » code taking long amount of time to execute (ORACLE 11G)
icon10.gif  code taking long amount of time to execute [message #625258] Fri, 03 October 2014 08:40 Go to next message
manoj12
Messages: 210
Registered: March 2008
Location: India
Senior Member
Hi Team,

Ihave a code which is taking lot of time to execute
DECLARE
CURSOR C1 IS select e.empno,
       e.ename,
       e.sal,
       d.deptno 
   from emp e,
        dept d 
   where e.deptno = d.deptno
   and not exists(select 1 from emp4 s where 
                         s.empno = e.empno
                         and s.ename = e.ename
                         and s.deptno = d.deptno);
                         
  
       
    TYPE empno_type is table of EMP.EMPNO%TYPE;
    TYPE ename_type is table of emp.ename%TYPE;
    TYPE sal_type is table of emp.sal%type;
    TYPE deptno_type is table of emp.deptno%type;
    
    empnos    empno_type;
    enames   ename_type;
    sals     sal_type;
    deptnos  deptno_type;
    
      v_version number; 
    v_cnt number := 0;
 begin
       OPEN C1;
      LOOP
       
      FETCH C1 BULK COLLECT INTO empnos,enames,sals,deptnos LIMIT 2;
      
      for i in 1..empnos.count
      loop
      BEGIN
      SELECT COUNT(1) 
      into v_cnt
      from emps_tab 
      where empno = empnos(i)
       and ename = enames(i)
       and deptno = deptnos(i);
       
       select  max(version_number)+1 into v_version
       from durchlauf;
       IF v_cnt >= 1 then
      FORALL i in 1..empnos.COUNT
      INSERT INTO EMPS_TAB(empno,ename,sal,deptno,Version_Number,invalid_number)  values(EMPNOS(i),enames(i),sals(i),deptnos(i),v_version,NULL);
      
      UPDATE EMPS_TAB SET INVALID_NUMBER = v_version
      WHERE empno = empnos(i)
       and ename = enames(i)
       and deptno = deptnos(i)
       AND ROWID NOT IN(SELECT MAX(ROWID) FROM emps_tab WHERE empno = empnos(i)
       and ename = enames(i)
       and deptno = deptnos(i));
    ELSE
      FORALL i in 1..empnos.COUNT
      INSERT INTO EMPS_TAB(empno,ename,sal,deptno,Version_Number,invalid_number)  values(EMPNOS(i),enames(i),sals(i),deptnos(i),v_version,NULL);
     END IF;
      EXIT WHEN C1%NOTFOUND;
      EXCEPTION
      WHEN DUP_VAL_ON_INDEX THEN
       dbms_output.put_line(empnos(i)||' '||enames(i)||' '||deptnos(i)||' are duplicate in emps_tab table');
     END;
      END LOOP; 
       end loop;
     close c1;
  commit;
    EXCEPTION
      WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-200001,'Exception from Parent Block');
    END;
 


Can you please suggest what I am doing wrong in the code.
The functioanlity is it will ignore all same records which are coming from cursor and again check for duplicate record in emps_tab table. If cnt >=1 then insert and invalid the invalid number in emps_tab .

Please suggest why it is taking lot of time to execute
Re: code taking long amount of time to execute [message #625260 is a reply to message #625258] Fri, 03 October 2014 08:44 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Do not do in PL/SQL that which can be done in plain SQL.

Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: code taking long amount of time to execute [message #625267 is a reply to message #625260] Fri, 03 October 2014 09:06 Go to previous message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well you appear to be doing more work than necessary, but also I doubt that code is doing what you think it's doing.
For instance:
AND ROWID NOT IN(SELECT MAX(ROWID) FROM emps_tab WHERE empno = empnos(i)

Rowid has no implicit order so that bit of the where clause is excluding a random row for the empno.

And then you do this:
1) Get two rows from the cursor.
2) For each row check if there's a match in emps_tab.
3) If there is insert both rows.
4) Then do an update for each empno.
5) Repeat steps 2-4

If you're checking each row individually then you shouldn't be subsequently using FORALL inside the loop.

I suspect the whole thing can be done with one insert/select statement and one update statement, but you'll have to describe the process in a lot more detail.


I'd also say that if you're using a limit of 2 on a bulk collect then you might as well use a cursor for loop, it'll be just as fast and easier to read.
Previous Topic: Access Remote Data Slow
Next Topic: improve performance for the query
Goto Forum:
  


Current Time: Thu Mar 28 06:07:37 CDT 2024