Oracle 11g Extended Statistics for SAP Tables

Extended Statistics is an attempt to fix one of the flaws in CBO--values of different columns are not correlated.

Let us take an example of two columns in a table. One of the column contain department code and the other contains employee name. Let us assume that there are 10 departments and 3000 employees in our example. In a real life scenario, all the employees do not belong to all the departments, but CBO assumes that is the case and hence it assumes that there are 3000*10 = 30000 combinations of employee name and department code that exist. In reality, it can be between 3000 and 30000 (assuming employee belongs to at least one department and can clock for multiple departments).

The CBO is not intelligent to know these relations and this assumption can have serious performance impact on join operations.

In order to calculate better statistics, we can use extended statistics from Oracle 11g onwards. SAP has provided these statistics for AUSP, BKPF, MSEG and HRP1001 tables as part of SAP note 1020260.

You can run the following commands to define the extended statistics on the above listed tables on SAP ERP application.

SELECT DBMS_STATS.CREATE_EXTENDED_STATS('SAPR3', 'AUSP', '(MANDT, KLART, ATINN)') FROM DUAL;
SELECT DBMS_STATS.CREATE_EXTENDED_STATS('SAPR3', 'BKPF', '(MANDT, BUKRS, BSTAT)') FROM DUAL;
SELECT DBMS_STATS.CREATE_EXTENDED_STATS('SAPR3', 'HRP1001', '(RELAT, SCLAS, OTYPE, PLVAR)') FROM DUAL;
SELECT DBMS_STATS.CREATE_EXTENDED_STATS('SAPR3', 'MSEG', '(MANDT, MATNR, WERKS, LGORT)') FROM DUAL;
SELECT DBMS_STATS.CREATE_EXTENDED_STATS('SAPR3', 'MSEG', '(MANDT, MBLNR, MJAHR)') FROM DUAL;
SELECT DBMS_STATS.CREATE_EXTENDED_STATS('SAPR3', 'MSEG', '(MANDT, WERKS, BWART)') FROM DUAL;
SELECT DBMS_STATS.CREATE_EXTENDED_STATS('SAPR3', 'MSEG', '(MANDT, WERKS, BWART, LGORT)') FROM DUAL;
SELECT DBMS_STATS.CREATE_EXTENDED_STATS('SAPR3', 'MSEG', '(MANDT, WERKS, LGORT)') FROM DUAL;

If you are aware of similar relationships, you can use the following syntax.

To define extended statistics:
SELECT DBMS_STATS.CREATE_EXTENDED_STATS ('<owner>', '<table_name>', ' (<col1>, ..., <colN>)') FROM DUAL;

To define and create extended statistics:
EXEC DBMS_STATS.GATHER_TABLE_STATS('<owner>', '<table_name>', METHOD_OPT => 'FOR COLUMNS (<col1>, ..., <colN>) SIZE 1');