cancel
Showing results for 
Search instead for 
Did you mean: 

Oracle CBO doesnu00B4t work as aspected

Former Member
0 Kudos

Hi,

we do have some problems with the performance (oracle). We are working with SAP ECC 6.0 and Oracle 10.2.0.2.0.

One of the tables is CUOB (f.e.) with a data class APPL0, a size category of 0 (up to 8.700 records) and buffering not allowed. The table has 4.740.000 rows! and 2.139.801 of allocated blocks. The statistics of the table and indices are uptodate - but the analyze took only about 160.000 rows.

The primary Key CUOB0 Nonunique CUOBU

MANDT 1 MANDT 1

KNTAB 7

KNOBJ 3.287.218

KNNUM 5.930 KNNUM 5.930

ADZHL 2

Levels of B-Tree 3 3

Number of leaf blocks 49.533 169.633

Number of distinct keys 5.342.667 5.930

Average leaf blocks per key 1 155

Average data blocks per key 1 1.540

Clustering factor 286.167 1.676.100

To check the problem we wrote a simple testprogram and analyzed it with transaction ST05:

REPORT ywktest02.

DATA: tb_knobj TYPE curto_knobj_t.

SELECT knobj FROM cuob INTO TABLE tb_knobj

WHERE knnum = '000000000230062923'

AND kntab = 'STPO'.

  • %_HINTS ORACLE 'RULE'.

CHECK sy-subrc = 0.

The CBO decided to make:

SELECT STATEMENT ( Estimated Costs = 1.388 , Estimated #Rows = 114 )

1 INDEX RANGE SCAN CUOB~0

( Estim. Costs = 1.387 , Estim. #Rows = 114 )

Search Columns: 3

Estim. CPU-Costs = 43.569.203 Estim. IO-Costs = 1.385

This has a duration of 655.162.395!!!!

Activating the %_hints in the program leads to

SELECT STATEMENT

2 TABE ACCESS BY INDEX ROWID CUOB

1 INDEX RANGEW SCAn CUOB~U

Search Columns: 2

This has a duration of 292!!!

Of course creating a new index (KNNUM, KNTAB, + KNOBJ) solves the problem too.

But in our oppion it´s no good idea to create lots of new indices if the real problem is somewhere else.

SO THE QUESTION IS:

Why the CBO creates the first way and doesn´t take the index CUOB~U???

Are the statistics not well done?

Is the clustering factor too high (and therefore the astimated block I/O)?

Is the size category wrong?

Or what else is going wrong?

Thank you for your help.

JSammy

Edited by: JSammy on Oct 19, 2010 12:38 PM

Edited by: JSammy on Oct 19, 2010 12:38 PM

Accepted Solutions (1)

Accepted Solutions (1)

0 Kudos

Hi,

Lets take a look at the query.

Probably, the query is sent to the DB as:

SELECT knobj

FROM cuob I

WHERE

MANDT = :A01 AND

knnum = :A02 AND

kntab = :A03

there are 3 conditions and only one field is selected.

Index CUOB~U only has two fields MANDT and KNNUM.

That means that Oracle has to select several rows from the index, then go to the table and then eliminate several of this rows because do not fulfill the condition kntab.

Point to keep in mind

1.- Index is "Big" Number of leaf blocks is 169 thousand blocks, I think it may be highly fragmented.

2.- the selectivity of this index is very low (Number of distinct keys 5.930) Oracle assumes it will have to read a high portion of those blocks

3.- The clustering factor of the index is high (1.676.100 compared to the number of rows), it would be good to know how many blocks has the table.

All those things give a "bad" cost for this index (it is not in your data, but it could be roughly calculated if needed)

If you take a look at the primary index

1.- it contains the 3 condition fields (but there is a non specified field in the middle)

2.- index size is smaller than the other index (1/4) probably the other index is fragmented

3.- The selected field (knobj) is in the index, it does NOT have to access the table

All that gives a lower cost that the other index.

Oracle "looks" at the numbers and, based on those numbers the primary index does look "better"

It is a "normal" issue of the CBO when the statistics are not representative.

Note that you should not be in 10.2.0.2 anymore as a lot of issues with the CBO are corrected in new versions.

Before creating a new index you can modify the existing U index adding the mising fields at the end (KNTAB, KNOBJ)

You can also try to reorganize AND compress the index CUOB~U as this may solve the issue:

Alter index "CUOB~U" REBUILD COMPRESS 2 PCTFREE 1;

Use the online option only if you have the patch that solves an issue with online reorganizations (do not know the note now)

Former Member
0 Kudos

Hi,

we will rebuild and reculculate the table and the indexes. Then we will try again.

Thank you

Answers (2)

Answers (2)

volker_borowski2
Active Contributor
0 Kudos

Hi,

at least this is somewhat strange:

> Number of leaf blocks 49.533 169.633

So with just two of the fields, that the PK has as well, your U Index has more

than three times the number of leaf blocks that your PK. -- scratching head --

I think this can't be realistic.

I'd start to rebuild (not compress, not coalesce) both indexes and recalc new stats,

to see if somethings is strange there.

Check PCT_FREE before the rebuild, may be somthing has been tried in that area before

to fight against insert performance.

Rebuild, recalc, re-explain

Volker

former_member204746
Active Contributor
0 Kudos

recalc your DB stats for table CUOB

you can do this through brconnect or through DB20.

then, try again.

it still does not choose proper index? refer to SAP note 830576 and make sure that all parameters are set properly.

you might also need to patch your oracle installation with latest patches.