DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: sql taking long time

  1. #1
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349

    Arrow 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
    http://www.perf-engg.com
    A performance engineering forum

  2. #2
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    159
    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

  3. #3
    Join Date
    Feb 2005
    Posts
    158
    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
  •  


Click Here to Expand Forum to Full Width