Home » AIX » Oracle 11g Extended Statistics for SAP Tables

Oracle 11g Extended Statistics for SAP Tables

RSS Link

  • An error has occurred; the feed is probably down. Try again later.

Blog Stats

  • 690,237 hits


Categories

Advertisements

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’);

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

My twitter post @devratt

November 2013
M T W T F S S
« Oct   Jun »
 123
45678910
11121314151617
18192021222324
252627282930  

Popularity


PageRank

Bookmark and Share

Top Clicks

  • None
%d bloggers like this: