7 Replies Latest reply: Jul 24, 2014 4:35 PM by rp0428 RSS

    FTS v/s Index full scan?

    Sid_ Z.

      Hi All,

      Below are the two plan with stats. One is using the index hints and other is FTS.

      This plan is generated from the developement environment as the data is less in the table (168 records only in both table) . Before generating plan tables were analysed.

      but in Production these tables have

      atbilltransactiondet -1139855 and atbilltransactionmst -1094804 records.

       

      Looking at the the volume of data in production which plan gives me the best performance?

       

      Plan 1.

       

       

       

      SELECT m.ID mst_id,
          m.dealdate,
          m.productcode,
          m.currencycode ccy1code,
          d.currencycode ccy2code,
          d.margin
        FROM atbilltransactionmst m,
          atbilltransactiondet d
        WHERE d.transactionid = m.ID
        AND d.ratetype       != 'SPLIT'
        AND d.ratetype !      = 'FWDUTILIZATION'
        AND m.status         <> 'TERMINATE';
      
       
      
      Plan hash value: 205452775
       
      -------------------------------------------------------------------------------------------
      | Id  | Operation          | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
      -------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT   |                      |   166 |  9794 |    10  (10)| 00:00:01 |
      |*  1 |  HASH JOIN         |                      |   166 |  9794 |    10  (10)| 00:00:01 |
      |*  2 |   TABLE ACCESS FULL| ATBILLTRANSACTIONDET |   167 |  3173 |     4   (0)| 00:00:01 |
      |*  3 |   TABLE ACCESS FULL| ATBILLTRANSACTIONMST |   167 |  6680 |     5   (0)| 00:00:01 |
      -------------------------------------------------------------------------------------------
       
      Predicate Information (identified by operation id):
      ---------------------------------------------------
       
         1 - access("D"."TRANSACTIONID"="M"."ID")
         2 - filter("D"."RATETYPE"<>'SPLIT' AND "D"."RATETYPE"<>'FWDUTILIZATION')
         3 - filter("M"."STATUS"<>'TERMINATE')
         Statistics
      -----------------------------------------------------------
                     6  user calls
                278528  physical read total bytes
                     0  physical write total bytes
                     0  spare statistic 3
                     0  commit cleanout failures: cannot pin
                     0  TBS Extension: bytes extended
                     0  total number of times SMON posted
                     0  SMON posted for undo segment recovery
                     0  SMON posted for dropping temp segment
                     0  segment prealloc tasks
      
       
      

      And then I issues following two commands to release data.

      ALTER SYSTEM FLUSH BUFFER_CACHE;

      ALTER SYSTEM FLUSH SHARED_POOL;

       

      Plan 2

       

       

       

      SELECT /*+ INDEX (D ATBILLTRANSDET_RATETYPE) index (m STATUS_IDX)  */
          m.ID mst_id,
          m.dealdate,
          m.productcode,
          m.currencycode ccy1code,
          d.currencycode ccy2code,
          d.margin
        FROM atbilltransactionmst m,
          atbilltransactiondet d
        WHERE d.transactionid = m.ID
        AND d.ratetype       != 'SPLIT'
        AND d.ratetype !      = 'FWDUTILIZATION'
        AND m.status         <> 'TERMINATE';
      
       
      Plan hash value: 625695020
       
      --------------------------------------------------------------------------------------------------------
      | Id  | Operation                    | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
      --------------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT             |                         |   166 |  9794 |    53   (2)| 00:00:01 |
      |*  1 |  HASH JOIN                   |                         |   166 |  9794 |    53   (2)| 00:00:01 |
      |   2 |   TABLE ACCESS BY INDEX ROWID| ATBILLTRANSACTIONDET    |   167 |  3173 |    24   (0)| 00:00:01 |
      |*  3 |    INDEX FULL SCAN           | ATBILLTRANSDET_RATETYPE |   167 |       |     1   (0)| 00:00:01 |
      |   4 |   TABLE ACCESS BY INDEX ROWID| ATBILLTRANSACTIONMST    |   167 |  6680 |    28   (0)| 00:00:01 |
      |*  5 |    INDEX FULL SCAN           | STATUS_IDX              |   167 |       |     1   (0)| 00:00:01 |
      --------------------------------------------------------------------------------------------------------
       
      Predicate Information (identified by operation id):
      ---------------------------------------------------
       
         1 - access("D"."TRANSACTIONID"="M"."ID")
         3 - filter("D"."RATETYPE"<>'SPLIT' AND "D"."RATETYPE"<>'FWDUTILIZATION')
         5 - filter("M"."STATUS"<>'TERMINATE')
         Statistics
      -----------------------------------------------------------
                     6  user calls
                294912  physical read total bytes
                     0  physical write total bytes
                     0  spare statistic 3
                     0  commit cleanout failures: cannot pin
                     0  TBS Extension: bytes extended
                     0  total number of times SMON posted
                     0  SMON posted for undo segment recovery
                     0  SMON posted for dropping temp segment
                     0  segment prealloc tasks
      
      

       

      Your suggestions please...

        • 1. Re: FTS v/s Index full scan?
          Karthick_Arp

          I will not use HINT unless its appropriate. In your case its very inappropriate. Look at the cost. When you hint the SQL it goes up. Basically you are forcing CBO to use INDEX when its totally unnecessary. The number of rows returned by the SELECT is almost the same as number of rows in the table. A FTS looks like the best bet. But still i would hate to make assumptions. If i where you, i will be making all the effort to get the execution plan from the production.

          • 2. Re: FTS v/s Index full scan?
            Manik

            Well you see in DEV the number of rows is small... so optimizer thought FTS is enough to get the data.. I am sure in PROD with that amount of data (that you have mentioned), optimizer would surely come up with some other plan and if all stats are available, it will consider indexes as well in that new plan.. so my suggestion would be that refrain from using index hints.. as hints sometimes can be evil and have negative impact.

             

            Cheers,

            Manik.

            • 3. Re: FTS v/s Index full scan?
              KayK

              Hi,

              i think the optimizer did a good job. In your where-clause you have only the join condition (d.transactionid = m.ID ) and then three unindexable conditions ( <> or !=) .

              So you have to read one table complete and then you have the choice between nested loop or hash join.

              And the hash join seems to be the better way.

              In the second statement there is an index hint and the optimizer does the best he can. A hash join based on two index full scans.

               

              Without any other restriction in where clause that's seems to be the only way to handle this statement.

               

              regards
              Kay

              • 4. Re: FTS v/s Index full scan?
                Aryacool

                Hi Sid_z,

                 

                  This is mainly depends upon your data how much you are fetching from table. If data is more then it will do the FTS if data is less then if index is there then it will do the index scan.

                Its better done by optimizer

                you can refer below link

                 

                Index Scan or Full Table Scan: The “Magic” Number (Magic Dance) | Richard Foote's Oracle Blog

                 

                Regards,

                Arya

                • 5. Re: FTS v/s Index full scan?
                  John Spencer

                  As others havesaid, my bet would be that the first version would be more performant in your production environment.  Looking at the statistics, the full table scan version shows:

                       278528  physical read total bytes

                  While the index scan version shows:

                       294912  physical read total bytes

                   

                  The fact that they are all physical is due to your flushng the buffer, and I suspect that the overall total is somewhat higher than actually required due to the hard parsing from flushing the shared pool.  I would run both several times, without flushing things between, to see what the actual work is in terms of I/O.  In general, the plan with the lowest amount of I/O should be most performant.

                   

                  John

                  • 6. Re: FTS v/s Index full scan?
                    Martin Preiss

                    an INDEX FULL SCAN is generally a good idea only if you need a sorted result in a following step (or in the query result). As source for a HASH JOIN the INDEX FULL SCAN is quite useless. A FULL TABLE SCAN reads all the table's blocks with multiblock I/O - and for large reads that's in many cases the best approach. An INDEX FULL SCAN is a sorted read of index entries using single block I/O - sometimes Oracle uses this operation to avoid an expensive sort but for a following HASH JOIN that's not relevant. So the first plan is more efficient.

                     

                    With 168 rows the difference is not that big - but with > 1M rows the INDEX FULL SCAN could result in massive problems.

                    • 7. Re: FTS v/s Index full scan?
                      rp0428

                      And then I issues following two commands to release data.

                      ALTER SYSTEM FLUSH BUFFER_CACHE;

                      ALTER SYSTEM FLUSH SHARED_POOL;

                      Well - that isn't a very good idea.

                       

                      Why did you do that? You certainly don't plan to do that in production do you?

                       

                      And even if you thought that somehow gives you a better/cleaner/ more accurate second plan then why didn't you do it before the first query also?