create new CBO statistics for the tables

[Former Member]

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

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

Hi,

I am facing problem in when save/activating any problem ,so sap has told me to create new CBO statistics for the tables D010TAB and D010INC

Now as you have suggest when tx db20

Table D010LINC

there error comes Table D010LINC does not exist in the ABAP Dictionary

Table D010TAB

Statistics are current (|Changes| < 50 %)

New Method C

New Sample Size

Old Method C Date 10.03.2010

Old Sample Size Time 07:39:37

Old Number 51,104,357 Deviation Old -> New 0 %

New Number 51,168,679 Deviation New -> Old 0 %

Inserted Rows 160,770 Percentage Too Old 0 %

Changed Rows 0 Percentage Too Old 0 %

Deleted Rows 96,448 Percentage Too New 0 %

Use O

Active Flag P

Analysis Method C

Sample Size

Please suggest

Regards,

Kumar

[KayKa · Accepted solution]

Hi Kumar,

i think D010LINC is only a typo, the table is D010INC without the L.

Ok, you call tx db20, insert the table name and then refresch the screen. Then you the actual values of the statistics.

In the menu bar "Statistics" -> "Create Statistics" -> "Yes" at the start operation window.

Then you come back to the db20 with "operation started" in the status line. Ok, there is no message when the operation is ended.

So try to refresh the screen and after a while you see the new statistic values.

regards

Kay

[Former Member · Accepted solution]

Hi,

Thanks, there i one query GOTO >global statistics or Statistics" -> "Create Statistics" both are same ?

in goto global statistics->

Update

Update( DBSTATC)

Create Missing Statistics

Delete Harmful Statistics

Oracle Dictionary

System Statistics

Please suggest what option to choose .

Regards,

Kumar

[KayKa · Accepted solution]

Hi Kumar,

choose the "Statistics" -> "Create statistics" in the menu bar. Not the "Global" button.

Ok, there is the icon with the empty sheet, that does the same: "Create statistics (Ctrl+F2)".

regards

Kay

[Former Member · Accepted solution]

Dear Kay,

Thanks a lot.I will do and update

regards,

Kumar

[Former Member · Accepted solution]

Dear Kay,

Thanks Its done know can u please tell me how to check other table that need new CBO statistics.

Regards,

Kumar

[KayKa · Accepted solution]

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

[KayKa]

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

[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

[Former Member]

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

[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