5 Replies Latest reply: Jul 17, 2012 6:11 PM by rp0428 RSS

    Cost of fetching rows incredibly high after migration from 9i to 11g

    proussin
      Hello,
      I have an issue on a particular table on a 11.2.0.2 database.

      This is and example query:
      select
      acc.acc_dw_id
      FROM
      FBNDWH.tb_acc_account acc;

      on 9.2.0.8:
      I get this plan:
      --------------------------------------------------------------------
      ------------------------------------------------------------------------
      | Id | Operation | Name | Rows | Bytes | Cost |
      ------------------------------------------------------------------------
      | 0 | SELECT STATEMENT | | 29M| 169M| 36813 |
      | 1 | TABLE ACCESS FULL | TB_ACC_ACCOUNT | 29M| 169M| 36813 |
      ------------------------------------------------------------------------

      On 11.2.0.2:
      I get this plan:
      ------------------------------------------------------------------------------------
      | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
      ------------------------------------------------------------------------------------
      | 0 | SELECT STATEMENT | | 29M| 169M| 231K (1)| 00:54:02 |
      | 1 | TABLE ACCESS FULL| TB_ACC_ACCOUNT | 29M| 169M| 231K (1)| 00:54:02 |
      ------------------------------------------------------------------------------------


      What is happening, this is the only table where I get this issue, all indexes are usable, analyzed with histograms.
      This issue is also present when accessing by rowid, the cost is inordinately high.
      I've dropped and rebuilded the tables and indexes. Stats and histograms are present on both versions (9i and 11g).

      Any ideas ?

      Pierre
        • 1. Re: Cost of fetching rows incredibly high after migration from 9i to 11g
          Tubby
          proussin wrote:
          Hello,
          I have an issue on a particular table on a 11.2.0.2 database.

          This is and example query:
          select
          acc.acc_dw_id
          FROM
          FBNDWH.tb_acc_account acc;

          on 9.2.0.8:
          I get this plan:
          --------------------------------------------------------------------
          ------------------------------------------------------------------------
          | Id | Operation | Name | Rows | Bytes | Cost |
          ------------------------------------------------------------------------
          | 0 | SELECT STATEMENT | | 29M| 169M| 36813 |
          | 1 | TABLE ACCESS FULL | TB_ACC_ACCOUNT | 29M| 169M| 36813 |
          ------------------------------------------------------------------------

          On 11.2.0.2:
          I get this plan:
          ------------------------------------------------------------------------------------
          | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
          ------------------------------------------------------------------------------------
          | 0 | SELECT STATEMENT | | 29M| 169M| 231K (1)| 00:54:02 |
          | 1 | TABLE ACCESS FULL| TB_ACC_ACCOUNT | 29M| 169M| 231K (1)| 00:54:02 |
          ------------------------------------------------------------------------------------


          What is happening, this is the only table where I get this issue, all indexes are usable, analyzed with histograms.
          This issue is also present when accessing by rowid, the cost is inordinately high.
          I've dropped and rebuilded the tables and indexes. Stats and histograms are present on both versions (9i and 11g).

          Any ideas ?

          Pierre
          Sorry, but what's the exact issue? You had a full table scan before the upgrade .. you're getting a full table scan after the upgrade.

          You mention there are indexes that are usable, but why would you expect that to make any difference for the query you posted? Unless you're expecting a FAST FULL INDEX scan or something (which i'm doubting since you had a full table scan before you upgraded, just like you're getting now).

          If you're concerned about why the COST is different, then you'll have to check out the variables that affect the calculation (system statistics, multiblock read count).

          http://richardfoote.wordpress.com/2009/11/25/the-cbo-cpu-costing-model-indexes-vs-full-table-scans/

          Should give you an idea of where to look.

          Cheers,
          • 2. Re: Cost of fetching rows incredibly high after migration from 9i to 11g
            Srini Chavali-Oracle
            If you are comparing "36813" and "231K", then the comparison is meaningless and inaccurate, as the calculation for these numbers vary from release to release

            HTH
            Srini
            • 3. Re: Cost of fetching rows incredibly high after migration from 9i to 11g
              proussin
              Hello,
              After somes testing it seems the cost cannot be compared between the 2 versions, we have 5x better performance on 11g although the costs is much higher.
              However I noticed that for count(*) the PK is used on 9i and provides a much faster result than on 11g where a full table scan is done instead. Anyone knows about this ?

              Pierre
              • 4. Re: Cost of fetching rows incredibly high after migration from 9i to 11g
                Dom Brooks
                .

                Edited by: Dom Brooks on Jul 18, 2012 9:59 AM
                Misread question
                • 5. Re: Cost of fetching rows incredibly high after migration from 9i to 11g
                  rp0428
                  >
                  Hello,
                  After somes testing it seems the cost cannot be compared between the 2 versions, we have 5x better performance on 11g although the costs is much higher.
                  >
                  Well isn't that what srini just told you? Mark your question ANSWERED and give HELPFUL or ANSWERED credit as appropriate.
                  >
                  However I noticed that for count(*) the PK is used on 9i and provides a much faster result than on 11g where a full table scan is done instead. Anyone knows about this ?
                  >
                  What I know is that you should post a new question for this as it isn't related to the current one. When you post the question provide the DDL for any available indexes and the plans that are being used.

                  One possibility is that the PK on 11g was created using an existing nullable index.