This discussion is archived
14 Replies Latest reply: Nov 15, 2012 8:06 AM by Dom Brooks RSS

Index Fast Full Scan vs Table Scan operations

864988 Newbie
Currently Being Moderated
Hello,
My database is 11.2.0.3 ; I have very complicated query, high resource consumer taking more than 10 hours running (developers don't want to rewrite the query). When monitoring the query execution with V$SESSION_LONGOPS I see the table scan of TAB_JOB table (76780 of Total work) termintaing fast and quicky in about 2093 seconds (34 minutes) where the Index Fast Full Scan operation on the same table (TAB_JOB)(12859 of Total Work) is estimating to terminate in about 17787 seconds (About 5 hours) ... I am wodnering why Index Fast Full Scan is taking some much time, Between, I have reuild all the indexes ...
  • 1. Re: Index Fast Full Scan vs Table Scan operations
    Dom Brooks Guru
    Currently Being Moderated
    Approach as sql tuning request:
    HOW TO: Post a SQL statement tuning request - template posting

    If licensed for diagnostic pack, monitor query execution using DBMS_SQLTUNE.REPORT_SQL_MONITOR.
    Post back execution plan for more assistance.
    Unfortunately predicates section is missing from RTSM output, so supplement output with predicates from DBMS_XPLAN.DISPLAY_CURSOR.

    Depending on how compilcated the query/execution plan is you may run into the RTSM limit from sqlmonmax_planlines
    I have reuild all the indexes ...
    Why?
    Is this preferable to SQL tuning?
    developers don't want to rewrite the query
    Tough...
  • 2. Re: Index Fast Full Scan vs Table Scan operations
    864988 Newbie
    Currently Being Moderated
    --------------------------------------------------------------------

    Edited by: DBA on 15-nov-2012 7:56
  • 3. Re: Index Fast Full Scan vs Table Scan operations
    Dom Brooks Guru
    Currently Being Moderated
    If the estimates are in any way accurate, then it will take 632 hours - only a few more days to go...
  • 4. Re: Index Fast Full Scan vs Table Scan operations
    864988 Newbie
    Currently Being Moderated
    Dom Brooks wrote:
    If the estimates are in any way accurate, then it will take 632 hours - only a few more days to go...
    very Helpful :)
  • 5. Re: Index Fast Full Scan vs Table Scan operations
    Dom Brooks Guru
    Currently Being Moderated
    The output isn't too large.

    The filter operations are probably killing you.

    The operations will be long running not least because as the execution plan produces some rows from the upper level rowsources, you have to run some filter operations on the rows being produced.

    Can you get the real time sql time sql monitoring report? (subject to diagnostic pack license)

    The real time sql monitoring gives you information about currently running sql, starts of operations, time active, sampled waits, etc, etc

    And the SQL statement would be useful.
  • 6. Re: Index Fast Full Scan vs Table Scan operations
    864988 Newbie
    Currently Being Moderated
    Thanks ... here is the SQL statement, for SQL monitoring I am running it now ....
    NA
    Edited by: DBA on 15-nov-2012 7:56
  • 7. Re: Index Fast Full Scan vs Table Scan operations
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    DBA wrote:
    My database is 11.2.0.3 ; I have very complicated query, high resource consumer taking more than 10 hours running (developers don't want to rewrite the query). When monitoring the query execution with V$SESSION_LONGOPS I see the table scan of TAB_JOB table (76780 of Total work) termintaing fast and quicky in about 2093 seconds (34 minutes) where the Index Fast Full Scan operation on the same table (TAB_JOB)(12859 of Total Work) is estimating to terminate in about 17787 seconds (About 5 hours) ... I am wodnering why Index Fast Full Scan is taking some much time, Between, I have reuild all the indexes ...
    You've named a table which doesn't exist in the plan, and not told us WHICH index on that table is reported in v$session_longops. You haven't told us what you mean by "estimating to terminate"; and a tablescan time of 2,093 seconds is pretty extraordinary for "just" a tablescan when Oracle thinks it can do this in about 7 minutes (assuming your TAB_JOB is actually PS_JOB_PART.

    Note that Oracle shows the tablescan with total work = 76780, while the index fast full scan is 12,859 - so Oracle's prediction is that the fast full scan should be about 6 times as fast as the tablescan in v$session_longops (although that's not consistent with the cost/time figures in the plan)

    My guess: you're estimating the time to completion by checking off the change in "so_far" against the change in clock time. The reason why you can end up with contradictory indications is that Oracle may be reporting total_work for a simple scan, but that scan is feeding into a hash join, and the hash join is taking a lot of time because it keeps spilling to disc. Your clock time includes the hash join time, Oracle's fast full scan "total work" doesn't.

    P.S. I'm guessing that the index you're watching in v$session_longops is INX_PSJOB_7.

    Regards
    Jonathan Lewis
  • 8. Re: Index Fast Full Scan vs Table Scan operations
    864988 Newbie
    Currently Being Moderated
    Hi Jonathan
    I am sorry, the table TAB_JOB is PS_JOB actually, I wrote it by mistake. And the Index Fast Full Scan shown in V$SESSION_LONGOPS belongs to the table BRIDGE_RO.PS_JOB (the table V$SESSION_LONGOPS didn't showed me tha index name). I am assuming the execution time by looking into the TIME_REMAINING column in V$SESSION_LONGOPS , ESLAPSED_SECONDS , SOFAR and TOTALWORK all in V$SESSION_LONGOPS view.

    Thank you much
  • 9. Re: Index Fast Full Scan vs Table Scan operations
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    DBA wrote:
    Hi Jonathan
    I am sorry, the table TAB_JOB is PS_JOB actually, I wrote it by mistake. And the Index Fast Full Scan shown in V$SESSION_LONGOPS belongs to the table BRIDGE_RO.PS_JOB (the table V$SESSION_LONGOPS didn't showed me tha index name). I am assuming the execution time by looking into the TIME_REMAINING column in V$SESSION_LONGOPS , ESLAPSED_SECONDS , SOFAR and TOTALWORK all in V$SESSION_LONGOPS view.
    I should have noticed the reference to PS_JOB - but I was searching for TABLE ACCESS FULL. The arithmetic makes much better sense with PS_JOB - the explaination is still the same, of course, Oracle doesn't know where the data is going when is does the fast full scan, so all it reports in v$session_longops is the work it has to do for the index fast full scan without necessarily giving any indication of how long it will take to get to the end of that scan.

    You need to find a better query plan (if one exists), and if the developers won't change the code you need to associate an SQL Baseline with the query.

    Regards
    Jonathan Lewis
  • 10. Re: Index Fast Full Scan vs Table Scan operations
    Dom Brooks Guru
    Currently Being Moderated
    for SQL monitoring I am running it now ....
    You don't have to wait for the statement to complete before feedback on the sql monitoring.
    That's part of it's beauty.
  • 11. Re: Index Fast Full Scan vs Table Scan operations
    864988 Newbie
    Currently Being Moderated
    Dom Brooks wrote:
    for SQL monitoring I am running it now ....
    You don't have to wait for the statement to complete before feedback on the sql monitoring.
    That's part of it's beauty.
    Yeah, except I need to understand how to interpret the output as it is my first time :)
  • 12. Re: Index Fast Full Scan vs Table Scan operations
    Dom Brooks Guru
    Currently Being Moderated
    If the code tags preserve the width and readability, can you not post it?
  • 13. Re: Index Fast Full Scan vs Table Scan operations
    864988 Newbie
    Currently Being Moderated
    if it is not a problem for you, Could you give me your email address, I will email it to you
  • 14. Re: Index Fast Full Scan vs Table Scan operations
    Dom Brooks Guru
    Currently Being Moderated
    I'd push for a rewrite anyway.
    Not that we know that that would make much difference without more information.

    To do away with some of the multiple subqueries, you could do something like?
    SELECT *
    FROM   BRIDGE_RO.PS_CM_EVALUATIONS CM
    ,      BRIDGE_RO.PS_JOB            JOB
    WHERE  JOB.EMPLID  = CM.EMPLID
    AND    (JOB.EFFDT,JOB.EMPL_RCD,JOB.EFFSEQ) = (SELECT EFFDT, EMPL_RCD, EFFSEQ
                                                  FROM   (SELECT Z.EFFDT, Z.EMPL_RCD, Z.EFFSEQ
                                                          FROM   PS_JOB_PART Z
                                                          WHERE  Z.EMPLID = JOB.EMPLID
                                                          AND    Z.EFFDT <= CM.REVIEW_DT
                                                          ORDER BY Z.EFFDT DESC, Z.EMPL_RCD DESC, Z.EFFSEQ DESC)
                                                  WHERE ROWNUM = 1)
    Alternatives include using an analytic like ROW_NUMBER() or MAX() KEEP (DENSE RANK FIRST...).

    For example:
    WITH subq_evals AS
    (          
    SELECT CM.EMPLID
    ,      CM.EVALUATION_ID
    ,      CM.REVIEW_DT
    ,      CM.REVIEWER_APPRVD
    ,      max(z1.effdt)    keep (dense_rank first order z1.effdt desc)  eeffdt
    ,      max(z1.empl_rcd) keep (dense_rank first order z1.effdt desc, z1.empl_rcd desc)  ercd
    ,      max(z1.effseq)   keep (dense_rank first order z1.effdt desc, z1.empl_rcd desc, z1.effseq desc) eseq
    ,      max(z2.effdt)    keep (dense_rank first order z2.effdt desc)  ceffdt
    ,      max(z2.empl_rcd) keep (dense_rank first order z2.effdt desc, z2.empl_rcd desc) crcd
    ,      max(z2.effseq)   keep (dense_rank first order z2.effdt desc, z2.empl_rcd desc, z2.effseq desc) cseq
    FROM   BRIDGE_RO.PS_CM_EVALUATIONS CM
    ,      PS_JOB_PART                 Z1
    ,      PS_JOB_PART                 Z2
    WHERE  JOB.EMPLID  = CM.EMPLID
    AND    CM.EVALUATION_TYPE IN ('K', 'Y')
    AND  ((CM.WF_STATUS = 'A') 
    OR   ((SELECT COUNT(M_ROW$$)
           FROM   BRIDGE_RO.PS_HR_CM_EVAL_STA
           WHERE  EMPLID = CM.EMPLID
           AND    EVALUATION_ID = CM.EVALUATION_ID) = 3))
    AND    Z1.EMPLID    = JOB.EMPLID
    AND    Z1.EFFDT    <= CM.REVIEW_DT
    AND    Z2.EMPLID    = JOB.EMPLID
    AND    Z2.EFFDT    <= SYSDATE
    GROUP BY 
           CM.EMPLID
    ,      CM.EVALUATION_ID
    ,      CM.REVIEW_DT
    ,      CM.REVIEWER_APPRVD)
    SELECT *
    FROM   subq_evals                  EVL
    ,      BRIDGE_RO.PS_JOB            JOB,
    ,      BRIDGE_RO.PS_JOB            JOB2
    WHERE  JOB.EMPLID    = EVL.EMPLID
    AND    JOB.EFFDT     = EVL.EEFFDT
    AND    JOB.EMPL_RCD  = EVL.ERCD
    AND    JOB.EFFSEQ    = EVL.ESEQ
    AND    JOB2.EMPLID   = EVL.EMPLID
    AND    JOB2.EFFDT    = EVL.CEFFDT
    AND    JOB2.EMPL_RCD = EVL.CRCD
    AND    JOB2.EFFSEQ   = EVL.CSEQ
    .... etc... etc...
    Completely untested ....

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points