This discussion is archived
5 Replies Latest reply: Jul 17, 2012 4:11 PM by rp0428 RSS

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

proussin Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    .

    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 Guru
    Currently Being Moderated
    >
    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.

Legend

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