I've one sql which is executing inside a procedure. The particular sql while executing for a worst case in SQLPLUS it takes max 1 minute.
But when this procedure gets executed inside the procedure it takes a long time even more than 30 minutes to execute. I'm confirming this as i saw the trace file.

This's the trace file sample of particular sql while exceuted isnide the procedure. Any idea why is the big time differnece.


SELECT CASE WHEN EVENT = 1 THEN SESSIONCREATIONTIME ELSE
SESSIONINVALIDATIONTIME END AS EVENTTIME , PRODUCTCODE , EVENT
FROM
(SELECT /*+ INDEX(USERSESSIONTRANSACTION SESSIONSESSIONTRANSACTION_IDX) */
TO_TIMESTAMP(TO_CHAR(SESSIONCREATIONTIME, 'YYYY-MM-DD HH24:MI:SS'),
'YYYY-MM-DD HH24:MI:SS') + NUMTODSINTERVAL(SESSIONCREATIONTIMEMS / 1000,
'SECOND') AS SESSIONCREATIONTIME ,
TO_TIMESTAMP(TO_CHAR(SESSIONINVALIDATIONTIME, 'YYYY-MM-DD HH24:MI:SS'),
'YYYY-MM-DD HH24:MI:SS') + NUMTODSINTERVAL(SESSIONINVALIDATIONTIMEMS / 1000,
'SECOND') AS SESSIONINVALIDATIONTIME , PRODUCTCODE FROM
SE510.USERSESSIONTRANSACTION WHERE SESSIONINVALIDATIONTIME >=
trunc(sysdate-2) AND SESSIONINVALIDATIONTIME < trunc(sysdate-2) + 0.25 AND
ZONENAME = 'hp' UNION ALL SELECT /*+ INDEX(USERSESSIONTRANSACTION
SESSIONSESSIONTRANSACTION_IDX) */ TO_TIMESTAMP(TO_CHAR(SESSIONCREATIONTIME,
'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') +
NUMTODSINTERVAL(SESSIONCREATIONTIMEMS / 1000, 'SECOND') AS
SESSIONCREATIONTIME , TO_TIMESTAMP(TO_CHAR(SESSIONINVALIDATIONTIME,
'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') +
NUMTODSINTERVAL(SESSIONINVALIDATIONTIMEMS / 1000, 'SECOND') AS
SESSIONINVALIDATIONTIME , PRODUCTCODE FROM SE600.USERSESSIONTRANSACTION
WHERE SESSIONINVALIDATIONTIME >= trunc(sysdate-2) AND
SESSIONINVALIDATIONTIME < trunc(sysdate-2) + 0.25 AND ZONENAME = 'hp') ,
(SELECT 1 AS EVENT FROM DUAL UNION ALL SELECT -1 AS EVENT FROM DUAL) ORDER
BY EVENTTIME


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.01 1 6 0 0
Execute 6 0.01 0.01 0 2 0 0
Fetch 40894 4966.82 16733.60 6568009 6568864 0 40893
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 40901 4966.85 16733.63 6568010 6568872 0 40893

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 83 (STATENGINE) (recursive depth: 1)

Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 SORT (ORDER BY)
0 MERGE JOIN (CARTESIAN)
0 VIEW
0 UNION-ALL
0 FILTER
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'USERSESSIONTRANSACTION'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'USERSESSIONTRANSACTION_IDX' (NON-UNIQUE)
0 FILTER
0 PARTITION RANGE (ALL) PARTITION: START=1 STOP=5
0 TABLE ACCESS GOAL: ANALYZED (BY LOCAL INDEX ROWID)
OF 'USERSESSIONTRANSACTION' PARTITION: START=1 STOP=5
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'USERSESSIONTRANSACTION_IDX' (NON-UNIQUE) PARTITION:
START=1 STOP=5
0 BUFFER (SORT)
0 VIEW
0 UNION-ALL