FAQ
I have an application on Oracle 9i. The database (designed by the vendor)
was set with optimizer_mode=CHOOSE. A week after we went live with the
application, performance started degrading. I discovered that statistics
were missing and built them. Immediately the application problems were
resolved.

The vendor claims that with the statistics, it would not use the right
indexes and some data would not be pulled up in the application. Is that
possible?

Thanks,

Manjula

Search Discussions

  • Riyaj Shamsudeen at May 19, 2006 at 3:32 pm
    Quoting Manjula:
    and some data would not be pulled up in the application. Is that
    possible?

    Huh ? Generally not possible. But, I know of one exception. I think
    Kirti Deshpande encountered this: One of the application was relying
    upon an index order to return the rows in a specific order. Optimizer
    selected a different index, after collecting statistics, affecting the
    functionality. Specifically, if the code is also interested in top N
    rows with this strategy, then incorrect (from the application point of
    view) will be pulled in to the application, causing user irritation.

    But, the problem here is that incorrect and potentially treacherous
    strategy. Unless 'order by' clause is used, RDBMS will return rows
    anyway it sees fit. So, you might want to ask them to provide more
    details as to exactly why collecting statistics will not work and ask
    for code/strategy that will break.

    --

    Thanks

    Riyaj "Re-yas" Shamsudeen
    Certified Oracle DBA (ver 7.0 - 9i)
    Allocation & Assortment planning systems
    JCPenney

    Manjula Krishnan wrote:
    I have an application on Oracle 9i. The database (designed by the
    vendor) was set with optimizer_mode=CHOOSE. A week after we went live
    with the application, performance started degrading. I discovered that
    statistics were missing and built them. Immediately the application
    problems were resolved.

    The vendor claims that with the statistics, it would not use the right
    indexes and some data would not be pulled up in the application. Is
    that possible?

    Thanks,

    Manjula
    The information transmitted is intended only for the person or entity to
    which it is addressed and may contain confidential and/or privileged
    material. If the reader of this message is not the intended recipient,
    you are hereby notified that your access is unauthorized, and any review,
    dissemination, distribution or copying of this message including any
    attachments is strictly prohibited. If you are not the intended
    recipient, please contact the sender and delete the material from any
    computer.
  • Manjula Krishnan at May 19, 2006 at 3:47 pm
    I quote the vendor "the potential for displaced data (missing data on forms
    and reports) where statistics have been gathered. Missing data has been
    observed within the application under a strict RULE based environment where
    objects have been analyzed to gather statistics."
    What Riyaj said makes sense that without the order by clause, it will
    "appear" like data is missing.

    Riyaj: What did you mean by "code/strategy that will break" ?

    Thanks yall.

    Manjula
    On 5/19/06, Riyaj Shamsudeen wrote:

    Quoting Manjula:
    and some data would not be pulled up in the application. Is that
    possible?

    Huh ? Generally not possible. But, I know of one exception. I think
    Kirti Deshpande encountered this: One of the application was relying
    upon an index order to return the rows in a specific order. Optimizer
    selected a different index, after collecting statistics, affecting the
    functionality. Specifically, if the code is also interested in top N
    rows with this strategy, then incorrect (from the application point of
    view) will be pulled in to the application, causing user irritation.

    But, the problem here is that incorrect and potentially treacherous
    strategy. Unless 'order by' clause is used, RDBMS will return rows
    anyway it sees fit. So, you might want to ask them to provide more
    details as to exactly why collecting statistics will not work and ask
    for code/strategy that will break.

    --

    Thanks

    Riyaj "Re-yas" Shamsudeen
    Certified Oracle DBA (ver 7.0 - 9i)
    Allocation & Assortment planning systems
    JCPenney

    Manjula Krishnan wrote:
    I have an application on Oracle 9i. The database (designed by the
    vendor) was set with optimizer_mode=CHOOSE. A week after we went live
    with the application, performance started degrading. I discovered that
    statistics were missing and built them. Immediately the application
    problems were resolved.

    The vendor claims that with the statistics, it would not use the right
    indexes and some data would not be pulled up in the application. Is
    that possible?

    Thanks,

    Manjula


    The information transmitted is intended only for the person or entity to
    which it is addressed and may contain confidential and/or privileged
    material. If the reader of this message is not the intended recipient,
    you are hereby notified that your access is unauthorized, and any review,
    dissemination, distribution or copying of this message including any
    attachments is strictly prohibited. If you are not the intended
    recipient, please contact the sender and delete the material from any
    computer.

    --
    http://www.freelists.org/webpage/oracle-l
  • Riyaj Shamsudeen at May 19, 2006 at 5:29 pm
    ('binary' encoding is not supported, stored as-is)

    Hi

    Ask the vendor, for the code that breaks in CBO environment. Ask them
    what design do they have that will break in CBO environment. What plans
    do they have for migrating to higher versions, such as 10g in which rule
    based optimizer is desupported (not suggesting that RBO will not work in
    10g) .

    If you are in 10g, then RBO is already de-supported and refer them to
    docid:*189702.1.*

    Few lines from the above document:

    " The RBO is Oracle's legacy optimizer originating from Oracle Version 6
    and earlier. The RBO is being desupported in Oracle10g. The RBO will
    still exist in Oracle10g, but will be an unsupported feature and will be
    removed in a future release. No code changes will be made to the RBO
    code and no bug fixes will be provided. The RBO was superseded in
    Oracle7 by the CBO and has continued to be available for backwards
    compatibility. Oracle Oracle10g will support only one optimizer, and all
    applications running on that release should use that optimizer."


    Thanks
    Riyaj

    Manjula Krishnan wrote:
    I quote the vendor "the potential for displaced data (missing data on
    forms and reports) where statistics have been gathered. Missing data
    has been observed within the application under a strict RULE based
    environment where objects have been analyzed to gather statistics."
    What Riyaj said makes sense that without the order by clause, it will
    "appear" like data is missing.

    Riyaj: What did you mean by "code/strategy that will break" ?

    Thanks yall.

    Manjula



    On 5/19/06, *Riyaj Shamsudeen*
    wrote:
    Quoting Manjula:
    and some data would not be pulled up in the application. Is that
    possible?

    Huh ? Generally not possible. But, I know of one exception. I think
    Kirti Deshpande encountered this: One of the application was relying
    upon an index order to return the rows in a specific order. Optimizer
    selected a different index, after collecting statistics, affecting the
    functionality. Specifically, if the code is also interested in top N
    rows with this strategy, then incorrect (from the application point of
    view) will be pulled in to the application, causing user irritation.

    But, the problem here is that incorrect and potentially treacherous
    strategy. Unless 'order by' clause is used, RDBMS will return rows
    anyway it sees fit. So, you might want to ask them to provide more
    details as to exactly why collecting statistics will not work and ask
    for code/strategy that will break.

    --

    Thanks

    Riyaj "Re-yas" Shamsudeen
    Certified Oracle DBA (ver 7.0 - 9i)
    Allocation & Assortment planning systems
    JCPenney

    Manjula Krishnan wrote:
    I have an application on Oracle 9i. The database (designed by the
    vendor) was set with optimizer_mode=CHOOSE. A week after we went live
    with the application, performance started degrading. I
    discovered that
    statistics were missing and built them. Immediately the application
    problems were resolved.

    The vendor claims that with the statistics, it would not use the right
    indexes and some data would not be pulled up in the application. Is
    that possible?

    Thanks,

    Manjula


    The information transmitted is intended only for the person or
    entity to
    which it is addressed and may contain confidential and/or privileged
    material. If the reader of this message is not the intended
    recipient,
    you are hereby notified that your access is unauthorized, and any
    review,
    dissemination, distribution or copying of this message including any
    attachments is strictly prohibited. If you are not the intended
    recipient, please contact the sender and delete the material from any
    computer.

    The information transmitted is intended only for the person or entity to
    which it is addressed and may contain confidential and/or privileged
    material. If the reader of this message is not the intended recipient,
    you are hereby notified that your access is unauthorized, and any review,
    dissemination, distribution or copying of this message including any
    attachments is strictly prohibited. If you are not the intended
    recipient, please contact the sender and delete the material from any
    computer.
  • Kirtikumar Deshpande at May 19, 2006 at 11:46 pm
    Hi Riyaj,

    The case we encountered was not due to gathering statistics.

    Vendor used index_desc hint to "derive" a result set.

    There was a block corruption on the index and it was recreated with a different name
    while the DBA worked with Oracle Support to analyze the problem. Of course, the index
    hint was useless and the SQL produced unwanted results.

    Regards,

    Kirti

    Riyaj Shamsudeen wrote:
    Quoting Manjula:
    and some data would not be pulled up in the application. Is that
    possible?

    Huh ? Generally not possible. But, I know of one exception. I think
    Kirti Deshpande encountered this: One of the application was relying
    upon an index order to return the rows in a specific order. Optimizer
    selected a different index, after collecting statistics, affecting the
    functionality. Specifically, if the code is also interested in top N
    rows with this strategy, then incorrect (from the application point of
    view) will be pulled in to the application, causing user irritation.

    But, the problem here is that incorrect and potentially treacherous
    strategy. Unless 'order by' clause is used, RDBMS will return rows
    anyway it sees fit. So, you might want to ask them to provide more
    details as to exactly why collecting statistics will not work and ask
    for code/strategy that will break.

    --

    Thanks

    Riyaj "Re-yas" Shamsudeen
    Certified Oracle DBA (ver 7.0 - 9i)
    Allocation & Assortment planning systems
    JCPenney

    Manjula Krishnan wrote:
    I have an application on Oracle 9i. The database (designed by the
    vendor) was set with optimizer_mode=CHOOSE. A week after we went live
    with the application, performance started degrading. I discovered that
    statistics were missing and built them. Immediately the application
    problems were resolved.

    The vendor claims that with the statistics, it would not use the right
    indexes and some data would not be pulled up in the application. Is
    that possible?

    Thanks,

    Manjula
    The information transmitted is intended only for the person or entity to
    which it is addressed and may contain confidential and/or privileged
    material. If the reader of this message is not the intended recipient,
    you are hereby notified that your access is unauthorized, and any review,
    dissemination, distribution or copying of this message including any
    attachments is strictly prohibited. If you are not the intended
    recipient, please contact the sender and delete the material from any
    computer.
    Do You Yahoo!?
    Tired of spam? Yahoo! Mail has the best spam protection around
    http://mail.yahoo.com

    --
    http://www.freelists.org/webpage/oracle-l
  • Ghassan Salem at May 20, 2006 at 7:54 am
    Well,
    'Missing data' can come from 2 sources:

    A bug, this can happen
    A different ORDER that makes the data missing from the first page.
    If the SQL does not an ORDER BY, but relies on a plan using a given index to
    have the data ordered as the app wishes, using another index will make the
    order different, and hence the data that used to be on the first pages go
    the other ones, and so seem missing.

    rgds
    On 5/20/06, Kirtikumar Deshpande wrote:

    Hi Riyaj,

    The case we encountered was not due to gathering statistics.

    Vendor used index_desc hint to "derive" a result set.

    There was a block corruption on the index and it was recreated with a
    different name
    while the DBA worked with Oracle Support to analyze the problem. Of
    course, the index
    hint was useless and the SQL produced unwanted results.

    Regards,

    - Kirti




    --- Riyaj Shamsudeen wrote:
    Quoting Manjula:
    and some data would not be pulled up in the application. Is that
    possible?

    Huh ? Generally not possible. But, I know of one exception. I think
    Kirti Deshpande encountered this: One of the application was relying
    upon an index order to return the rows in a specific order. Optimizer
    selected a different index, after collecting statistics, affecting the
    functionality. Specifically, if the code is also interested in top N
    rows with this strategy, then incorrect (from the application point of
    view) will be pulled in to the application, causing user irritation.

    But, the problem here is that incorrect and potentially treacherous
    strategy. Unless 'order by' clause is used, RDBMS will return rows
    anyway it sees fit. So, you might want to ask them to provide more
    details as to exactly why collecting statistics will not work and ask
    for code/strategy that will break.

    --

    Thanks

    Riyaj "Re-yas" Shamsudeen
    Certified Oracle DBA (ver 7.0 - 9i)
    Allocation & Assortment planning systems
    JCPenney

    Manjula Krishnan wrote:
    I have an application on Oracle 9i. The database (designed by the
    vendor) was set with optimizer_mode=CHOOSE. A week after we went live
    with the application, performance started degrading. I discovered that
    statistics were missing and built them. Immediately the application
    problems were resolved.

    The vendor claims that with the statistics, it would not use the right
    indexes and some data would not be pulled up in the application. Is
    that possible?

    Thanks,

    Manjula
    The information transmitted is intended only for the person or entity
    to
    which it is addressed and may contain confidential and/or privileged
    material. If the reader of this message is not the intended recipient,
    you are hereby notified that your access is unauthorized, and any review,
    dissemination, distribution or copying of this message including any
    attachments is strictly prohibited. If you are not the intended
    recipient, please contact the sender and delete the material from any
    computer.

    __________________________________________________
    Do You Yahoo!?
    Tired of spam? Yahoo! Mail has the best spam protection around
    http://mail.yahoo.com
    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Powell, Mark D at May 19, 2006 at 3:37 pm
    If is possible that for specific queries that the CBO could make an
    incorrect choice for which index to use. There are some bugs especially
    related to ANSI join syntax where incorrect results can be produced so
    depending on your Oracle database version and the manner in which the
    vendor application is written there exists the possibility. However, as
    you posted the CBO seems to have gotten quite a bit of the application
    SQL correct.


    HTH -- Mark D Powell --


    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Manjula Krishnan

    Sent: Friday, May 19, 2006 11:05 AM
    To: oracle-l_at_freelists.org
    Subject: Optimizer issue

    I have an application on Oracle 9i. The database (designed by

    the vendor) was set with optimizer_mode=CHOOSE. A week after we went
    live with the application, performance started degrading. I discovered
    that statistics were missing and built them. Immediately the application
    problems were resolved.


    The vendor claims that with the statistics, it would not use the
    right indexes and some data would not be pulled up in the application.
    Is that possible?


    Thanks,


    Manjula

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedMay 19, '06 at 3:05p
activeMay 20, '06 at 7:54a
posts7
users5
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase