on 10-19-2010 11:35 AM
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
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)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
25 | |
12 | |
9 | |
6 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.