Skip to Content
avatar image
Former Member

create new CBO statistics for the tables

Dear All,

I am facing bad performance in server.In SM50 I see that the read and delete process at table D010LINC takes

a longer time.how to create new CBO statistics for the tables D010TAB and D010INC. Please suggest.

Regards,

Kumar

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    avatar image
    Former Member
    Sep 23, 2010 at 01:10 PM

    Does your explain show the CBO using the wrong index or no index? If so, check this oracle parameter. OPTIMIZER_INDEX_COST_ADJ. In 10g by default it is set to 100. It should be betwen 10 and 50. What this parm does is change the weighting of indexes versus table scans. At 100, it says the index is the same as a table scan. Sometimes it seems to just pick any available index.

    Alter system set OPTIMIZER_INDEX_COST_ADJ=u201950u2019

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Kumar,

      as a general task go to the db13 and invoke "Check and update optimizer statistics". We let do this every night.

      For particular problems you can go to the st04, but st04old is more usable in my eyes. There you can look at the Shared cursor cache (Performance - SQL Statement Analysis) or at the st04old: button "Detail analysis menu" and then butten "SQL request".

      In the screen you can arrange and sort the columns to your requirements. There you can call the explain plan for every statement. And additional you can analyze tables or only indexes of this statements.

      I prefer to sort on "Elapsed Time/Exec" and then check the "Proc. rows" and the ratio between "Disks reads" and "Buffer gets".

      But this is an everlasting task...

      best regards

      Kay

  • avatar image
    Former Member
    Sep 23, 2010 at 05:21 AM

    Hi,

    You can execute the update statistics for the entire DB using the TA DB13. You can update the statistics for a particular index by following the below listed steps.

    SE14 - Table Name (Eg: EKET)
    Choose index (Index Button)
    Activate and adjust database (Button)
    To run the update statistics for the particular index (exceute db20)
    enter the table name
    goto statistics->create statistics
    logs can be viewed in DB24

    Regards,

    Varadharajan M

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi,

      If you have indexes then u need the first three steps else u can simply execute TA DB20 and then create the statistics for a particular table.

      To run the update statistics for the particular table (exceute db20)

      enter the table name

      goto statistics->create statistics

      Mark this thread as answered if ur issue is fixed.

      Regards,

      Varadharajan M

  • Sep 23, 2010 at 05:51 AM

    Hi Kumar,

    have you recognize a false execution plan in the st04 / st04old cursor cache or open sessions ? New CBO statistics will help you only if the optimizer choose a better execution plan afterwards.

    regards

    Kay

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Varadharajan ,

      Thanks for you suggestion , I am using ecc6 and oracle 10g on windows and I need to create new CBO statistics for the tables D010TAB and D010INC

      As suggest by you in SE14 - Table Name (D010LINC)

      Choose index (Index Button)

      But there is no index button in that are following radio buttons

      Dictionary object

      Table

      View

      Matchcode

      Pools/clusters

      What to choouse

      In ( db20)

      enter the table name

      goto statistics->create statistics

      there when I am going to goto statistics->

      Update

      Update( DBSTATC)

      Create Missing Statistics

      Delete Harmful Statistics

      Oracle Dictionary

      System Statistics

      To  create new CBO statistics for the tables eg  D010INC
      I have to run tx DB20 only give the Table Name (D010LINC) and *goto statistic*s --->& select Create Missing Statistics*

      Please suggest

      Regards,

      Kumar