Need help in understanding query execution time

Question

I previously posted a question about joining two tables based on certain criteria over here How to join two tables based on a timestamp (with variance of a few seconds)? (link doesn't have to be read)

I found that after creating indexes it works really fast. The snippet of my current code is

CREATE INDEX INDEXNAME1 ON TABLEA (CALL_DATE+5/86400);
CREATE INDEX INDEXNAME2 ON TABLEA (CALL_DATE+6/86400);
CREATE INDEX INDEXNAME3 ON TABLEB (NUMBER1,NUMBER2);
CREATE INDEX INDEXNAME4 ON TABLEA (NUMBER1,NUMBER2);
----
INSERT INTO AB_RECON (
SELECT A.*,B.* FROM TABLEB B FULL OUTER JOIN TABLEA A
ON B.NUMBER1=A.NUMBER1 AND B.NUMBER2=A.NUMBER2 AND
B.CALL_DATE-A.CALL_DATE IN (5/86400,6/86400);
----
DROP INDEX INDEXNAME1;
DROP INDEX INDEXNAME2;
DROP INDEX INDEXNAME3;
DROP INDEX INDEXNAME4;

Don't bother about the correctness of the code, it works. But the problem I'm facing is that the execution time is quite random. 90% of the time, the execution time is really quick (2-5 minutes) but sometimes (like right now its running for more than 20 minutes). I know it might seem like "depends on the size of the tables" but on an average TABLEA has 1.4 million records, and TABLEB has .9 million records. + or - a few ten thousands, not more.

I've run the following code (ran as SYS) to identify the queries currently running on the database along with elapsed time

select sess.sid, sess.serial#, sess.sql_id, sess.last_call_et as 
EXECUTION_TIME,sq.sql_text from v$session sess,v$sql sq
where status = 'ACTIVE' and last_call_et > sysdate - (sysdate - (3/86400))
and username is not null and sess.sql_id=sq.sql_id;

And I get the following output

SID  ||  SERIAL#    ||  SQL_ID         ||  EXECUTION_TIME  ||  SQL_TEXT
246  ||  51291      ||  dxa2sz103vt0g  ||  1256            || <my recon query pasted above>

I don't understand what's taking it so long cause from the looks of it, its the only active query. I'm not a DBA so I don't fully understand if there's something I'm missing.

Would appreciate it if some light could be shed into possible reasons/ solutions so that I can be point myself in the correct direction.

Additional information if required

Explain Plan

| Id  | Operation                | Name            | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |                 |  2386K|   530M|       |  2395M  (1)|999:59:59 |
|   1 |  LOAD TABLE CONVENTIONAL | AB_RECON        |       |       |       |            |          |
|   2 |   VIEW                   |                 |  2386K|   530M|       |  2395M  (1)|999:59:59 |
|   3 |    UNION-ALL             |                 |       |       |       |            |          |
|*  4 |     HASH JOIN RIGHT OUTER|                 |  1417K|   109M|    49M| 10143   (1)| 00:02:02 |
|   5 |      TABLE ACCESS FULL   | TABLEA          |   968K|    38M|       |  1753   (1)| 00:00:22 |
|   6 |      TABLE ACCESS FULL   | TABLEB          |  1417K|    52M|       |  2479   (1)| 00:00:30 |
|*  7 |     FILTER               |                 |       |       |       |            |          |
|   8 |      TABLE ACCESS FULL   | TABLEA          |   968K|    38M|       |  1754   (1)| 00:00:22 |
|*  9 |      TABLE ACCESS FULL   | TABLEB          |     1 |    29 |       |  2479   (1)| 00:00:30 |

Oracle Edition Oracle Database 11g Enterprise Edition 11.2.0.3.0 64-bit Production

score 0 · Accepted Answer · answered Dec 04 '13 at 13:09

Ok, I didn't really find an answer to my question but I did find a work around. I've broken down what I wanted to do (basically a reconciliation between two sources of information) into three parts.

Matched in both source A and B
Missing in source B
Missing in source A

The queries I've used is given below. Overall it runs much faster. Will have to monitor its performance over several executions.

INSERT INTO AB_RECON (
SELECT M.*,I.* FROM TABLEA M, TABLEB I
WHERE M.ANUMBER=I.ANUMBER AND M.BNUMBER=I.BNUMBER
AND M.CALL_DATE-I.CALL_DATE B (5/86400,6/86400));

COMMIT;


INSERT INTO AB_RECON
(SELECT ANUMBER,BNUMBER,CALL_DATE,CALL_DURATION,REF_NO,NULL,NULL,NULL,NULL,NULL FROM
   (SELECT * FROM TABLEA M WHERE NOT EXISTS
      (SELECT ANUMBER,BNUMBER,CALL_DATE,CALL_DURATION,REF_NO FROM AB_RECON I
       WHERE M.ANUMBER=I.ANUMBER AND M.BNUMBER=I.BNUMBER AND M.CALL_DATE=I.CALL_DATE
      )
   )
);

COMMIT;

INSERT INTO AB_RECON
(SELECT NULL,NULL,NULL,NULL,NULL,ANUMBER,BNUMBER,CALL_DATE,CALL_DURATION,REF_NO FROM
   (SELECT * FROM TABLEB M WHERE NOT EXISTS
      (SELECT ANUMBER,BNUMBER,CALL_DATE,CALL_DURATION,REF_NO FROM AB_RECON I
       WHERE M.ANUMBER=I.ANUMBER AND M.BNUMBER=I.BNUMBER AND M.CALL_DATE=I.CALL_DATE
      )
   )
);

Honestly, I have no idea about the theory behind why this works faster. So my main question remains unresolved.

Need help in understanding query execution time

1 Answers1