12 Replies Latest reply on May 3, 2016 6:48 PM by Jonathan Lewis

    Query performance INDEX_FFS compared with FTS

    SP-DBA

      I have been working on a query and i have noticed the below behavior. As per Oracle CBO FTS is cheaper, but despite that, i get quicker results (i.e. with less elapsed time) using INDEX_FFS no matter how many times i execute it.

       

      SQL> SELECT /* INDEX(D XPKCLIENT_ACCOUNT) */ E.ECID,A.acct_nb

      FROM client_account d, client e, account a

      where

      1. A.acct_nb ='00000000000000722616216'

      AND D.CLNT_ID = E.CLNT_ID

      AND D.ACCT_ID=A.ACCT_ID;  2    3 4    5    6

       

       

      Elapsed: 00:00:01.38

       

      Execution Plan

      ----------------------------------------------------------

      Plan hash value: 345524667

       

      -------------------------------------------------------------------------------------------------

      | Id  | Operation | Name           | Rows | Bytes | Cost (%CPU)| Time     |

      -------------------------------------------------------------------------------------------------

      | 0 | SELECT STATEMENT | |     1 |    59 |   808 (14)| 00:00:10 |

      | 1 |  NESTED LOOPS | |     1 |    59 |   808 (14)| 00:00:10 |

      | 2 |   NESTED LOOPS | |     1 |    59 |   808 (14)| 00:00:10 |

      |* 3 |    HASH JOIN | |     1 |    42 |   806 (14)| 00:00:10 |

      | 4 |     TABLE ACCESS BY INDEX ROWID| ACCOUNT        |     1 |    30 |     5   (0)| 00:00:01 |

      |* 5 |      INDEX RANGE SCAN          | XAK1ACCOUNT    |     1 |       |     4 (0)| 00:00:01 |

      | 6 |     TABLE ACCESS FULL          | CLIENT_ACCOUNT |  9479K|   108M|   763  (10)| 00:00:09 |

      |* 7 |    INDEX UNIQUE SCAN           | XPKCLIENT      |     1 |       |     1 (0)| 00:00:01 |

      | 8 |   TABLE ACCESS BY INDEX ROWID  | CLIENT |     1 |    17 | 2   (0)| 00:00:01 |

      -------------------------------------------------------------------------------------------------

       

      Predicate Information (identified by operation id):

      ---------------------------------------------------

       

      3 - access("D"."ACCT_ID"="A"."ACCT_ID")

      5 - access("A"."ACCT_NB"='00000000000000722616216')

      7 - access("D"."CLNT_ID"="E"."CLNT_ID")

       

       

      Statistics

      ----------------------------------------------------------

      0  recursive calls

      0  db block gets

      17955  consistent gets

      0  physical reads

      0  redo size

      623  bytes sent via SQL*Net to client

      524  bytes received via SQL*Net from client

      2  SQL*Net roundtrips to/from client

      0  sorts (memory)

      0  sorts (disk)

      1  rows processed

       

       

       

       

      But where as for INDEX_FFS

       

       

      SQL> SELECT /*+ INDEX_FFS(D XPKCLIENT_ACCOUNT) */ E.ECID,A.acct_nb

      FROM client_account d, client e, account a

      where

      1. A.acct_nb ='00000000000000722616216'

      AND D.CLNT_ID = E.CLNT_ID

      AND D.ACCT_ID=A.ACCT_ID;  2    3 4    5    6

       

      Elapsed: 00:00:00.86

       

      Execution Plan

      ----------------------------------------------------------

      Plan hash value: 3273057186

       

      ----------------------------------------------------------------------------------------------------

      | Id  | Operation | Name | Rows  | Bytes | Cost (%CPU)| Time     |

      ----------------------------------------------------------------------------------------------------

      | 0 | SELECT STATEMENT | |     1 |    59 |  1190 (8)| 00:00:14 |

      | 1 |  NESTED LOOPS | |     1 |    59 |  1190 (8)| 00:00:14 |

      | 2 |   NESTED LOOPS | |     1 |    59 |  1190 (8)| 00:00:14 |

      |* 3 |    HASH JOIN | |     1 |    42 |  1188 (8)| 00:00:14 |

      | 4 |     TABLE ACCESS BY INDEX ROWID| ACCOUNT |     1 |    30 | 5   (0)| 00:00:01 |

      |* 5 |      INDEX RANGE SCAN          | XAK1ACCOUNT       |     1 |       |     4 (0)| 00:00:01 |

      | 6 |     INDEX FAST FULL SCAN       | XPKCLIENT_ACCOUNT | 9479K|   108M|  1145   (5)| 00:00:13 |

      |* 7 |    INDEX UNIQUE SCAN           | XPKCLIENT |     1 | |     1   (0)| 00:00:01 |

      | 8 |   TABLE ACCESS BY INDEX ROWID  | CLIENT |     1 |    17 | 2   (0)| 00:00:01 |

      ----------------------------------------------------------------------------------------------------

       

      Predicate Information (identified by operation id):

      ---------------------------------------------------

       

      3 - access("D"."ACCT_ID"="A"."ACCT_ID")

      5 - access("A"."ACCT_NB"='00000000000000722616216')

      7 - access("D"."CLNT_ID"="E"."CLNT_ID")

       

       

      Statistics

      ----------------------------------------------------------

      0  recursive calls

      0  db block gets

      28696  consistent gets

      0  physical reads

      0  redo size

      623  bytes sent via SQL*Net to client

      524  bytes received via SQL*Net from client

      2  SQL*Net roundtrips to/from client

      0  sorts (memory)

      0  sorts (disk)

      1  rows processed

       

       

       

      Any ideas how this can be optimized and why this is happening like this.

       

      Regards

      Sai

        • 1. Re: Query performance INDEX_FFS compared with FTS
          SP-DBA

          Hi,

           

          Below is the snippet from 10053.

           

          Table Stats::

            Table: CLIENT_ACCOUNT  Alias:  D

              #Rows: 9479811  #Blks:  18110  AvgRowLen:  71.00  ChainCnt:  0.00

            Column (#1): CLNT_ID(

              AvgLen: 6 NDV: 1261035 Nulls: 0 Density: 0.000001 Min: 0 Max: 4244786

              Histogram: HtBal  #Bkts: 254  UncompBkts: 254  EndPtVals: 239

            Column (#2): ACCT_ID(

              AvgLen: 6 NDV: 9479811 Nulls: 0 Density: 0.000000 Min: 1 Max: 22028568

              Histogram: HtBal  #Bkts: 254  UncompBkts: 254  EndPtVals: 255

          Index Stats::

            Index: XPKCLIENT_ACCOUNT  Col#: 1 2

              LVLS: 2  #LB: 28543  #DK: 9479811  LB/K: 1.00  DB/K: 1.00  CLUF: 1809449.00

          Access path analysis for CLIENT_ACCOUNT

          ***************************************

          SINGLE TABLE ACCESS PATH

            Single Table Cardinality Estimation for CLIENT_ACCOUNT[D]

            Table: CLIENT_ACCOUNT  Alias: D

              Card: Original: 9479811.000000  Rounded: 9479811  Computed: 9479811.00  Non Adjusted: 9479811.00

            Access Path: TableScan

              Cost:  763.12  Resp: 763.12  Degree: 0

                Cost_io: 694.00  Cost_cpu: 1740537148

                Resp_io: 694.00  Resp_cpu: 1740537148

            Access Path: index (index (FFS))

              Index: XPKCLIENT_ACCOUNT

              resc_io: 1092.00  resc_cpu: 1340844582

              ix_sel: 0.000000  ix_sel_with_filters: 1.000000

            Access Path: index (FFS)

              Cost:  1145.25  Resp: 1145.25  Degree: 1

                Cost_io: 1092.00  Cost_cpu: 1340844582

                Resp_io: 1092.00  Resp_cpu: 1340844582

            Access Path: index (FullScan)

              Index: XPKCLIENT_ACCOUNT

              resc_io: 28545.00  resc_cpu: 2099243705

              ix_sel: 1.000000  ix_sel_with_filters: 1.000000

              Cost: 28628.37  Resp: 28628.37  Degree: 1

            Best:: AccessPath: TableScan

                   Cost: 763.12  Degree: 1  Resp: 763.12  Card: 9479811.00  Bytes: 0

           

           

          Regards

          Sai

          • 2. Re: Query performance INDEX_FFS compared with FTS
            AndrewSayer

            Execution times look very similar.

            When were stats last gathered on table? Where were stats last gathered on index?

             

            select table_name, blocks, last_analyzed from all_tables where table_name ='CLIENT_ACCOUNT';

            select index_name,LEAF_BLOCKS , blevel,last_analyzed from all_indexes where table_name = 'CLIENT_ACCOUNT';

             

            It could be to do with the ordering of rows from line operation 3 when nesting loop joining to CLIENT.

             

            What does it look like when showing row source level statistics? (https://blogs.oracle.com/optimizer/entry/how_do_i_know_if)

            • 3. Re: Query performance INDEX_FFS compared with FTS
              SP-DBA

              statistics were analyzed recently i.e. on 29th April, 2016 for the two queries that you have posted.

              • 4. Re: Query performance INDEX_FFS compared with FTS
                SP-DBA

                before executing the query i have altered this hidden parameter at my session level alter session set "_rowsource_statistics_sampfreq"=1;

                 

                Regards

                Sai

                • 5. Re: Query performance INDEX_FFS compared with FTS
                  SP-DBA

                  TABLE_NAME = CLIENT_ACCOUNT

                  BLOCKS = 18110

                  LAST_ANALYZED = 29-!PR-16

                   

                  INDEX_NAME=XPKCLIENT_ACCOUNT

                  LEAF_BLOCKS = 28543

                  BLEVEL = 2

                  LAST_ANALYZED = 29-!PR-16

                  • 6. Re: Query performance INDEX_FFS compared with FTS
                    SP-DBA

                    I'm unable to copy paste the contents here, hence writing the o/p manually

                    • 7. Re: Query performance INDEX_FFS compared with FTS
                      Jonathan Lewis

                      It looks like you're either using a 32KB block size for the table, or you've implemented table compression, or both. (With an 8KB block size for the index, without using index compression).

                       

                      Mixing block sizes inevitably confuses the optimizer; but if you've implemented table compression then the difference could easily be the CPU requirement to decompress the blocks before extracting and comparing values.

                       

                      Regards

                      Jonathan Lewis

                      1 person found this helpful
                      • 8. Re: Query performance INDEX_FFS compared with FTS
                        SP-DBA

                        Thanks Jonathan for your reply.

                         

                        Yes, you are correct we are using table compression only without any compression on index.

                         

                        I have got the answer.

                         

                        Thanks a lot.

                         

                        Regards

                        Sai

                        • 9. Re: Query performance INDEX_FFS compared with FTS
                          AJ

                          Jonathan Lewis wrote:

                           

                          It looks like you're either using a 32KB block size for the table, or you've implemented table compression, or both. (With an 8KB block size for the index, without using index compression).

                           

                          Mixing block sizes inevitably confuses the optimizer; but if you've implemented table compression then the difference could easily be the CPU requirement to decompress the blocks before extracting and comparing values.

                           

                          Regards

                          Jonathan Lewis

                          Hi Jonathan

                           

                          Just curious and eager to learn..How did you spot that? Just past experience or was it something in the trace provided by the OP?

                           

                          Thank you.

                           

                          Regards,

                          AJ

                          • 10. Re: Query performance INDEX_FFS compared with FTS
                            Jonathan Lewis

                            The first step was experience: ideas on how an index fast full scan could have a higher cost than a tablescan (which means the index seems to be bigger than the table) and require more block visits (which suggests it really is bigger) while using less CPU (which, for the same type of work, suggests it's smaller).

                             

                            Second step was to check the statistics for the table and index:

                             

                            Table:  num_rows * avg_row_len is about 4 times blocks * 8K (but possibly a bit bigger even than that)

                              So the table is in 32KB blocks, or it's compressed.

                             

                            Index:  picking up the index column length, add on the rowid, multiply by the number of index entries and the result is close to leaf_blocks * 8K

                              So the index is probably in 8KB blocks and not compresed.

                             

                            Various assumptions in the arithmetic include the observation that if it's the primary key index the columns are not null and the number of rows matches the table number of rows (and the number of distinct keys).

                             

                            Regards

                            Jonathan Lewis

                            • 11. Re: Query performance INDEX_FFS compared with FTS
                              AJ

                              Thank you very much for a thorough explanation and analyze of the problem.

                               

                               

                              AJ

                              • 12. Re: Query performance INDEX_FFS compared with FTS
                                Jonathan Lewis

                                I've just written this up on my blog: http://jonathanlewis.wordpress.com/2016/05/03/debugging-3/

                                 

                                 

                                 

                                Regards

                                Jonathan Lewis