14 Replies Latest reply: Nov 15, 2012 10:06 AM by Dom Brooks RSS

    Index Fast Full Scan vs Table Scan operations

    864988
      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
          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
            --------------------------------------------------------------------

            Edited by: DBA on 15-nov-2012 7:56
            • 3. Re: Index Fast Full Scan vs Table Scan operations
              Dom Brooks
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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 ....