Increase the number of distinct values for the field PROCESS in the index BDCP2~001 from 2 to 100

[Former Member]

Hi,

SAP has just concluded Go-Live Checks service on our ECC system.  They found out that there was an access on BDCP2 causing high database load:

SQL Statement

SELECT

"MANDT", "MESTYPE", "CPIDENT", "PROCESS", "TABNAME", "TABKEY", "FLDNAME", "CRETIME", "ACTTIME", "USRNAME", "CDOBJCL", "CDOBJID", "CDCHGNO", "CDCHGID"

FROM

"BDCP2"

WHERE

"MANDT"=:A0 AND "MESTYPE"=:A1 AND "PROCESS"=:A2

ORDER BY

"MANDT", "CPIDENT"

For this, their Recommendation: Increase the number of distinct values for the field PROCESS in the index BDCP2~001 from 2 to 100. Please test this recommendation and review performance.

How to implement their recommendations (i.e., increase the number of distinct values for the field PROCESS) ?

Any clues would be helpful.

Thanks,

Abdul

[Former Member]

There's a much simpler way to go about this - follow SAP note 1020260. Run the script, create new stats on BDCP2, done.

That script raises the distinct count to 10, which should be plenty, it's published by SAP and kept uptodate and won't be forgotten in one year when the same problem will resurface because new stats were created on BDCP2 by some other method...

[stefan_koehler]

Hi Abdul,

it is pretty amusing to read all of that Early Watch reports or SAP Go Live checks. What a nice (useless) cash print machine.

However lets continue with your question. You can not manipulate the distinct value statistic from a column in an index (exception the column PROCESS is the only one in the index). The distinct values are stored in the column statistics area and not in the index statistics (excluding the exception). I will disregard histograms and different version behaviors right now to keep int simple.

shell> sqlplus / as sysdba
SQL> exec DBMS_STATS.SET_COLUMN_STATS('<SCHEMA_OWNER>','BDCP2','PROCESS', distcnt => 100);
SQL> exec DBMS_STATS.UNLOCK_TABLE_STATS('<SCHEMA_OWNER>','BDCP2');

However this should not be a solution - just a work around (if it works).

Regards

Stefan

[Former Member]

Thank you Stefan.

How do I check if the values are correctly updated in the table?  Is it a simple sql select or something else?

Thanks,

Abdul

[stefan_koehler]

Hi Abdul,

shell> sqlplus / as sysdba
SQL> select COLUMN_NAME, NUM_DISTINCT from ALL_TAB_COL_STATISTICS 
            where OWNER = '<SAP_SCHEMA>' and TABLE_NAME = 'BDCP2' 
            order by COLUMN_NAME;

Regards

Stefan

[volker_borowski2]

Best viewable:

ST05 -> EXPLAIN a single statement

Enter as statement

SELECT * FROM BDCP2

press explain

The plan will surely be a full table scan on the table, never mind.

Doubleclick the table name in the EXPLAIN to get the stats details.

Better: put your above statement into ST05 and check before and after your stats change,

how this will affect the execution plan.

Volker

[Former Member]

SQL> exec DBMS_STATS.UNLOCK_TABLE_STATS('<SCHEMA_OWNER>','BDCP2');  

I don't get it, a LOCK_TABLE_STATS would make sense here.

SQL> exec DBMS_STATS.LOCK_TABLE_STATS('<SCHEMA_OWNER>','BDCP2');  

Cheers Michael

[stefan_koehler]

Hi Michael,

you are right of course. This was a copy & paste error of mine from the official documentation.

Thanks for revision.

Regards

Stefan