Have you ever faced a problem which made you feel like in the nightmare you cannot wake up from? Recently I take over some legacy PL/SQL code to load large amount of data from one table to another with a little bit of transformations. This was simple case: take subset of data from source table and direct load it into another. This operation was executed in loop iterating on few thousand of rows in defined cursor:

DECLARE
  CURSOR c_cur IS
    SELECT id FROM table_t;

  r_cur c_cur%ROWTYPE;
BEGIN
  FOR r_cur IN c_cur LOOP

    INSERT INTO destination_t
    SELECT some_fields
      FROM source_t s
     WHERE s.id = r_cur.id;

  END LOOP;
END;

The SOURCE_T table contained apprx. 20 million of rows but index was created on ID column, so it should be executed in fair time. I run the procedure according to manual and waited for results. A big surprise was that next day, after over 30 hours of processing, there was only 40% of work done! Something was definitely wrong there. I checked execution plan for running query and found that there was full table scan performed every iteration! I analyzed table and index but with no change. Moreover, the same query extracted from PL/SQL and checked in SQL Developer, shows execution plan using table access by index ROWID! Why within PL/SQL package it is run with full table scan?!

I made some code refactoring, optimized procedure (there was actually 2 scans on SOURCE_T for every loop iteration, one shown above and second with SELECT DISTINCT). It was running faster but estimated time wasn’t still optimistic and full table scan was still there… I was going really crazy… Outside procedure index was used, inside – not! What the heck?! Fortunately, I recall that I’m not alone in the universe so I asked Holy Spirit for help. And, finally, I found it!

The reason was type difference in WHERE clause. I found out, that r_cur.id is actually a number but SOURCE_T.id was a string! And index was created for strings. Unfortunately, an optimizer didn’t handle it. So small change:

WHERE s.id = TO_CHAR(r_cur.id)

was the solution.

Why execution plan was different outside PL/SQL? Explain plan utility treated r_cur.id as a constant and assumed it’s type match with SOURCE_T.id. But in real life Oracle optimizer wasn’t able to repeat this…

I don’t remember a time when I was so happy seeing folowin execution plan:

INSERT STATEMENT 	
+ TABLE ACCESS BY INDEX ROWID	SOURCE_T
++  INDEX RANGE SCAN	IND_SRC_ID

So, a lesson for today:

---

ALWAYS USE EXPLICIT TYPE CAST IN SQL!

---

Finishing, some usefull snipets usefull to receive running query information.

If you need to get current running query in session:

SELECT SQ.SQL_TEXT, S.*
  FROM V$SESSION S, V$SQL SQ
 WHERE S.SQL_ID = SQ.SQL_ID
   AND LOWER(S.USERNAME) = 'username'
   AND s.SID= <session ID>;

To get information about long running ops, i.e. performed table scan:

SELECT *
  FROM V$SESSION_LONGOPS L
 WHERE L.SID = <sid>
   AND SOFAR <> TOTALWORK -- to get only in progress ops
 ORDER BY LAST_UPDATE_TIME DESC;

To get execution plan of currently running query:

SELECT ID,
       PARENT_ID,
       LPAD(' ', LEVEL - 1) || OPERATION || ' ' || OPTIONS OPERATION,
       OBJECT_NAME
  FROM (SELECT ID, PARENT_ID, OPERATION, OPTIONS, OBJECT_NAME
          FROM V$SQL_PLAN SP, V$SESSION A, V$SQL B
         WHERE SP.ADDRESS = B.ADDRESS
           AND SP.HASH_VALUE = B.HASH_VALUE
           AND SP.CHILD_NUMBER = B.CHILD_NUMBER
           AND A.SQL_ADDRESS = B.ADDRESS
           AND a.SID= <sid>
           )
 START WITH ID = 0
CONNECT BY PRIOR ID = PARENT_ID;

To get list of last running queries:

SELECT sq.sql_id,
       sq.sql_fulltext,
       sq.sql_text,
       se.username,
       sq.last_active_time
  FROM v$sql sq, v$session se, v$open_cursor oc
 WHERE sq.sql_id = oc.sql_id
   AND se.saddr = oc.saddr
   AND se.sid = oc.sid
   AND LOWER(se.username) = 'username'
   AND se.sid = <sid>
 ORDER BY sq.last_active_time DESC;