-
sql taking long time
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
-
Do either of the 'USERSESSIONTRANSACTION_IDX' indexes include the column SESSIONINVALIDATIONTIME? The plan output suggests it is using it as hinted. Is the index helping? Are the hints necessary?
My first thought would always be to remove all hints, analyze the tables, and see if the optimizer comes up with a better plan. If the index is needed it should be able to figure that out for itself. If the index is not needed, the hint is counterproductive. How are you analyzing the tables? How are you generating the tkprof output? Also, can you see where the plan is different to the SQL*Plus plan?
Is one part of that query (e.g. one half of the main UNION ALL view) particularly slow, or is it the combination that seems to be the problem? If I've got this right, SE600.USERSESSIONTRANSACTION is partitioned, and SE510.USERSESSIONTRANSACTION is not.
btw the results might be easier to read if the code wasn't all in uppercase and preformatted text was enclosed in [code] tags.
Also you can CAST a date directly to a TIMESTAMP, which can be simpler than TO_TIMESTAMP(TO_CHAR(dt,'fmt'),'fmt'), e.g:
SELECT CAST(SYSDATE AS TIMESTAMP) FROM dual;
insead of
SELECT TO_TIMESTAMP(TO_CHAR(sysdate,'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') FROM dual;
That gives me this:
Code:
SELECT CASE
WHEN event = 1 THEN sessioncreationtime
ELSE sessioninvalidationtime
END AS eventtime
, productcode
, event
FROM ( SELECT /*+ INDEX(usersessiontransaction sessionsessiontransaction_idx) */
CAST(sessioncreationtime AS TIMESTAMP) +
NUMTODSINTERVAL(sessioncreationtimems / 1000, 'SECOND') AS sessioncreationtime
, CAST(sessioninvalidationtime AS TIMESTAMP) +
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) */
CAST(sessioncreationtime AS TIMESTAMP) +
NUMTODSINTERVAL(sessioncreationtimems/1000,'SECOND') AS sessioncreationtime
, CAST(sessioninvalidationtime AS TIMESTAMP) +
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
-
Depending on how it is used, SQL in PL/SQL tends to go with the ALL_ROWS aim (sensible because it is inside a non-interactive procedure).
Try the plan after an
alter session set optimizer_goal=all_rows;
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|