cancel
Showing results for 
Search instead for 
Did you mean: 

sap system slow after upgrade

Former Member
0 Kudos

Hi All,

recently we have done oracle upgrade from 9.2.0.8 to 10.2.0.4.i have done the postupgrade scripts and application was fine.But,few days back, the table ARFCRSTATE is occupying more dialog work process and users are facing slowness in the SAP application.I have ran the updates stats daily and still am facing this issue.Can anyone provide me a help on this

i have checked st02,st04 and hardware related and i am not having any problem related to above these.Kindly provide me your valuable solutions

Sunos5.9/oracle10.2.0.4/ecc5

Regards

Ram

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi,

we had similar problems, please check following notes and create 'special' statistics:

Note 932975 - Oracle statistics for RFC tables

Note 1020260 - Delivery of Oracle statistics (Oracle 10g, 11g)

BR,

Peter

Former Member
0 Kudos

Hi PETER,

can you guide in short what has to be done using the note 1020260.i am having brtools 7.0 version(patch 52).i have to download the script and what has to be done.......

i did not get it clearly from that note.Please help me ...

Regards

Ram

Former Member
0 Kudos

Hi Bharatram,

- firstly you will need to update your brtools to version at least 7.10, patch 25 (see SAP Note 12741).

- secondly we adjusted the script to create stats only for RFC tables (attached), then

     - rename attached RFC_STAT.txt to RFC_STAT.SQL

     - logon to OS as ora<sid>

     - run: sqlplus /nolog @RFC_STAT.SQL <SCHEMA_OWNER>

     - check created file RFC_STAT.out

Use at your own risk.

BR,

Peter

Answers (4)

Answers (4)

Former Member
0 Kudos

Hi Peter, thanks for your timely help.I have ran the scripts in Quality system firstly .I created one statistics.txt1file as below and updated the brtools to 7.10 and ran the sql scripts

Rem ###########################################################################################

Rem

Rem The following commands provide pre-defined CBO statistics for tables where the standard

Rem statistic creation is often not sufficient (e.g. because of the dynamic table content)

Rem

Rem This script is an attachment to SAP note 1020260 and is intended for Oracle databases

Rem 10g and 11g. It may also be used for database <= 9i (PLS-00302 errors have to be ignored then).

Rem

Rem Script name: statistics.txt

Rem

Rem Copyright: (c) 2007 - 2012

Rem

Rem  XXXXXX     XX    XXXXXX            XXXXX     XXXX    XXXXXX

Rem XX    X    XXXX    XX  XX          XX    X   XX  XX   XX 

Rem XX        XX  XX   XX  XX          XX       XX    XX  XX

Rem  XXXXX    XX  XX   XXXXX           XX       XX    XX  XXXXX

Rem      XX   XXXXXX   XX              XX       XX    XX  XX   

Rem X    XX   XX  XX   XX              XX    X   XX  XX   XX 

Rem XXXXXX    XX  XX  XXXX              XXXXX     XXXX    XXXXXX

Rem

Rem  XXXXX                               XXX

Rem XX   XX                               XX

Rem XX   XX  XX XXX    XXXXX    XXXXX     XX     XXXXX

Rem XX   XX   XXX XX       X   XX   XX    XX    XX    X

Rem XX   XX   XX      XXXXXX   XX         XX    XXXXXXX

Rem XX   XX   XX      X   XX   XX   XX    XX    XX

Rem  XXXXX   XXXX     XXXXX X   XXXXX    XXXX    XXXXX

Rem

Rem Author: Martin Frauendorfer

Rem Mail:   martin.frauendorfer@sap.com

Rem

Rem Content history:

Rem Date           | Change

Rem ---------------+----------------------------------------------------------------------------

Rem Jan. 23rd 2007 | PAYR.PERNR

Rem Mar. 15th 2007 | TRBAT2, TRBAT, TATAF, DDXTT, DDXTF

Rem Apr.  3rd 2007 | ARFCRSTATE, ARFCSDATA, ARFCSSTATE, QREFTID,

Rem                | TRFCQDATA, TRFCQIN, TRFCQOUT, TRFCQSTATE

Rem Apr. 13th 2007 | UPSITX.DELNUM

Rem Apr. 30th 2007 | SXMSPMAST, SXMSPEMAS, SXMSPVERS, SXMSPERROR,

Rem                | SXMSCLUP, SXMSCLUR

Rem May   3rd 2007 | SXMSPMAST2, SXMSPEMAS2, SXMSPVERS2, SXMSPERRO2,

Rem                | SXMSCLUP2, SXMSCLUR2

Rem May  11th 2007 | TBTCO.SDLSTRTDT, TBTCO.STATUS

Rem Jun. 26th 2007 | /SAPAPO/MATLOC.LOCID, /SAPAPO/MATLOC.SATID

Rem Aug. 23rd 2007 | SXMSPMAST~TID, SXMSPMAST2~TID

Rem Sep. 17th 2007 | LTAK.KQUIT

Rem Sep. 21st 2007 | MLST.AUFPL, AFKO.AUFNT

Rem Oct.  8th 2007 | TBTCO.EVENTID, TBTCO.EVENTPARM

Rem Nov. 12th 2007 | TRFCQSTATE.HASH

Rem Feb. 15th 2008 | Individual statistic changes only if NUM_ROWS >= 1000

Rem Apr. 16th 2008 | MLST.PLNNR

Rem Jul. 23rd 2008 | SXMSPMAST.PARENTMSG, SXMSPMAST2.PARENTMSG -> NUM_DISTINCT changed from 1 to 10

Rem Aug. 26th 2008 | TRFCQIN~6 -> DISTINCT_KEYS increased from 4 to 400

Rem Dec.  4th 2008 | Set BLEVEL of all TRFCQOUT indexes to 2 (before: values between 2 and 4)

Rem Jan.  2nd 2009 | /SAPAPO/ORDADM_I.ANCHOR

Rem Jan. 16th 2009 | SMOFCDBHD, SMOFCMPDAT, SMOFCMPHD, SMOFCMPOBJ

Rem Feb. 12th 2009 | SMOEJOBID, SMOHJOBQ, SMOHMSGQ, SMOHSITEQ

Rem Feb. 18th 2009 | TRFCQOUT: BLOCKS increased from 13.000 to 100.000 (to avoid full table scans)

Rem Feb. 20th 2009 | TRFCQIN: BLOCKS and LEAF_BLOCKS increased by factor 10 (to avoid segment scans)

Rem June 12th 2009 | SMFCMPDAT.TABNAME: NUM_DISTINCT >= 2

Rem Aug. 24th 2009 | SXMSPHIST, SXMSPHIST2

Rem Dec.  7th 2009 | More flexible execution of script via "/ AS SYSDBA" rather than SAP user

Rem Feb. 12th 2010 | AUFK.PSPEL, AFPO.PROJN, AFVC.PROJN: NUM_DISTINCT >= 10

Rem Mar.  1st 2010 | HOPCOUNT column for SXMSPHIST, SXMSPHIST2

Rem Apr. 19th 2010 | LTAK~Q: DISTINCT_KEYS >= 200

Rem Apr. 28th 2010 | SXMSPMAST.PARENTMSG, SXMSPMAST2.PARENTMSG: NUM_DISTINCT increased from 10 to 1000

Rem                | SXMSPMAST~PAR, SXMSPMAST2~PAR: DISTINCT_KEYS increased from 1 to 1000

Rem May  26th 2010 | /SAPAPO/MATLOC.LOCID, SAPAPO/MATLOC.SATID: NUM_DISTINCT increased from at least 5 to at least 20

Rem                | /SAPAPO/MATLOC~SAT: DISTINCT_KEYS increased from at least 25 to at least 400

Rem                | /SAPAPO/MATLOC~LID: DISTINCT_KEYS increased from at least 5 to at least 20

Rem June 21st 2010 | SXMSPMAST, SXMSPMAST2: NUM_DISTINCT of MSGTYPE increased from 3 to 15

Rem July  6th 2010 | SMOFCMPDAT: AVG_COL_LEN of SEG_DATA reduced from 3103 to 300

Rem                |             AVG_ROW_LEN reduced from 3213 to 400

Rem July  7th 2010 | SMOHSITEQEX, SMOHSITEQRD

Rem                | SMOHMSGQ: NUM_DISTINCT of QUEUENAME increased from 2 to 10

Rem Feb.  2nd 2011 | AFKO: NUM_DISTINCT of PRONR increased to at least 100

Rem                |       DISTINCT_KEYS of AFKO~3 increased to at least 100

Rem Apr.  5th 2011 | PAYR: DISTINCT_KEYS of PAYR~P increased to at least 10

Rem May   5th 2011 | TESTDATRNRPART0

Rem May  12th 2011 | TBTCO: NUM_DISTINCT of STATUS increased from 10 to 20

Rem Jul.  8th 2011 | AFKO: NUM_DISTINCT of MAUFNR increased to at least 100

Rem                |       DISTINCT_KEYS of AFKO~5 increased to at least 100

Rem Jul. 14th 2011 | BDCP2: NUM_DISTINCT of PROCESS increased to at least 10

Rem Jul. 20th 2011 | AUSP: Column group statistics for MANDT, KLART, ATINN

Rem Aug. 17th 2011 | BBP_PDHGP: NUM_DISTINCT of ACTIVE header increased to at least 100

Rem                |            DISTINCT_KEYS of BBP_PDHGP~VER increased to at least 100

Rem Sep.  3rd 2011 | BKPF: Column group statistics for MANDT, BUKRS, BSTAT

Rem Nov. 29th 2011 | MSEG: Column group statistics for MANDT, MBLNR, MJAHR

Rem Dec.  8th 2011 | SMOHMSGQRE

Rem Dec. 29th 2011 | VEPO: NUM_DISTINCT of UNVEL increased to at least 20

Rem                |       DISTINCT_KEYS of VEPO~A increased to at least 20

Rem Jan.  3rd 2012 | CATSDB: NUM_DISTINCT of STATUS increased to at least 20

Rem Jan.  6th 2012 | DRAW: NUM_DISTINCT of PRENR increased to at least 10

Rem                |       DISTINCT_KEYS OF DRAW~1 increased to at least 10

Rem Feb. 7 th 2012 | DFKKOP: NUM_DISTINCT of WHGRP increased to at least 2

Rem Feb. 16th 2012 | SWWWIHEAD: NUM_DISTINCT of CHECK_STAT increased to at least 10

Rem Feb. 27th 2012 | XI_AF_SVC_ID_MAP (JAVA schema)

Rem Mar.  8th 2012 | RSBATCHDATA: NUM_DISTINCT of RELID increased to at least 10

Rem Mar. 23rd 2012 | MSEG: Column group statistics for MANDT, WERKS, LGORT

Rem Mar. 29th 2012 | MSEG: Column group statistics for MANDT, MATNR, WERKS, LGORT

Rem

Rem Installation procedure:

Rem

Rem 1. Implement BRCONNECT with at least version 7.10 (25) - see SAP Note 12741.

Rem

Rem 2. Copy this script statistics.txt to a directory on the database server

Rem

Rem 3. Go to the directory with the script

Rem

Rem 4. Make sure that the indexes of the relevant tables follow the "~"

Rem    naming convention. Replace <sapuser> with the name of the SAP user

Rem    (SAPR3 / SAPSR3 / SAP<sid> / SAP<xyz>):

Rem

Rem    sqlplus / as sysdba

/*

       SELECT

         INDEX_NAME 

       FROM

         DBA_INDEXES

       WHERE

         OWNER = '<sapuser>' AND

         TABLE_NAME IN

         ( 'ARFCRSTATE'

         ) AND

         INDEX_NAME NOT LIKE '%~%' AND

         INDEX_TYPE != 'LOB';

*/

Rem    If indexes are returned (e.g. containing '_' or '^' as separator),

Rem    they have to be renamed to the "~" naming convention, e.g.:

Rem

Rem    ALTER INDEX "<sapuser>"."TATAF__0" RENAME TO "TATAF~0";

Rem    ALTER INDEX "<sapuser>"."SXMSPMAST^0" RENAME TO "SXMSPMAST~0";

Rem

Rem    This change of the index name is transparent to the SAP application.

Rem

Rem    Exit sqlplus via "exit".

Rem

Rem 5. Check if you have already made some customer specific settings for the

Rem    involved tables in DBSTATC. This script here will remove all previously

Rem    existing settings for the tables with delivered CBO statistics and so you

Rem    have to maintain them again appropriately after having run the script.

Rem

Rem 6. Execute the script statistics.txt. Replace <sapuser> with the name of

Rem    the SAP user (SAPR3 / SAPSR3 / SAP<sid> / SAP<xyz>):

Rem

Rem    sqlplus /nolog @statistics_BRCONNECT_710_25.txt <sapuser>

Rem

Rem 7. Check the spool file statistics.out for errors.

Rem

Rem 8. If errors are returned, consider the following:

Rem

Rem    PLS-00302: component 'LOCK_TABLE_STATS' must be declared

Rem    PLS-00302: component 'UNLOCK_TABLE_STATS' must be declared

Rem

Rem    -> These errors are returned if the script is run in an Oracle database with

Rem       release <= 9i because the locking functionality doesn't exist with these

Rem       releases. These errors are not critical, but be aware that this script

Rem       here is mainly intended for databases >= 10g.

Rem

Rem    ORA-00904: "DBMS_STATS"."CREATE_EXTENDED_STATS": invalid identifier

Rem

Rem    -> Extended statistics only available as of Oracle 11g, with earlier releases the

Rem       above ORA-00904 is thrown which can be ignored

Rem

Rem    ORA-00942: table or view does not exist

Rem

Rem    -> This error usually indicates that the script is run with the wrong user.

Rem       Make sure that you connect to SQLPLUS with the SAP schema owner.

Rem 

Rem    ORA-01403: no data found

Rem

Rem    -> This error indicates that the concerned table doesn't exist in the system.

Rem       This happens if a table doesn't exist for a specific SAP release and can be

Rem       ignored.

Rem

Rem    ORA-20000: INDEX "<owner>"."<index_name>" does not exist or insufficient privileges

Rem

Rem    -> This error can occur when the index name doesn't follow the "~" naming

Rem       convention. See step 4 and rename the index if necessary.

Rem    -> It can also happen if the index doesn't exist in the system. In this case it can

Rem       be ignored.

Rem

Rem    ORA-20000: TABLE "<owner>"."<table_name>" does not exist or insufficient privileges

Rem

Rem    -> This error is displayed if the table doesn't exist in the system. This can happen

Rem       because this script is generic while SAP systems often only contain a sub set of

Rem       the tables. In this case the error can be ignored.

Rem

Rem    ORA-20000: Unable to set values for column <column_name>: does not exist or

Rem               insufficient privileges

Rem

Rem    -> If columns are introduced with newer SAP releases, they are included in this

Rem       script. If an older SAP release without the column is used, updating the

Rem       statistics for this column will fail with ORA-20000 because the column doesn't

Rem       exist. This is not critical and can be ignored.

Rem

Rem    ORA-20005: object statistics are locked (stattype = ALL)

Rem

Rem    -> This error appears if the statistics of a table are already locked.

Rem       This situation should not occur because before performing the changes

Rem       the UNLOCK_TABLE_STATS functionality is executed.

Rem 

Rem    ORA-06550: line <line>, column <column>

Rem    ORA-06512: at line 

Rem

Rem    -> These error codes are secondary error codes (note 636475) and can be

Rem       ignored.

Rem

Rem    ORA-20007: extension (...) already exists in the table

Rem

Rem    -> Happens if required extended statistics are already defined and can be ignored

Rem

Rem 9. If you have created customer specific indexes or columns you have to take

Rem    into account that SAP can't deliver good statistics for them and so

Rem    you might have to adapt their statistics on your own in order to synchronize

Rem    them with the delivered statistics. This may only be necessary for tables that

Rem    receive a full set of statistics in this script (->

Rem    DBMS_STATS.SET_TABLE_STATS, DBMS_STATS.SET_INDEX_STATS and

Rem    DBMS_STATS.SET_COLUMN_STATS are used for these tables). In this case you

Rem    can manually execute the DBMS_STATS commands for these indexes and columns.

Rem

Rem 10. If you have identified customer specific DBSTATC settings in step 5 above, you have

Rem     to maintain them now again if required.

Rem

Rem 11. Some of the changes only take effect after the next BRCONNECT statistic run. If

Rem     required, schedule a BRCONNECT statistic run manually:

Rem   

Rem     brconnect -u / -c -f stats -t all

Rem ###########################################################################################

CONNECT / AS SYSDBA

SPOOL statistics.out

VARIABLE OWNER VARCHAR2(30)

EXECUTE :OWNER := '&&1'

ALTER SESSION SET CURRENT_SCHEMA = &&1;

-- ############# COMPLETE STATISTIC DELIVERY ###################

-- ARFCRSTATE --

EXECUTE DBMS_STATS.UNLOCK_TABLE_STATS(:OWNER, '"ARFCRSTATE"');

EXECUTE DBMS_STATS.SET_TABLE_STATS(:OWNER, '"ARFCRSTATE"', NUMROWS=>3390, NUMBLKS=>200, AVGRLEN=>260, NO_INVALIDATE=>FALSE);

EXECUTE DBMS_STATS.SET_INDEX_STATS(:OWNER, '"ARFCRSTATE~1"', NUMROWS=>3390, NUMLBLKS=>30, NUMDIST=>2432, AVGLBLK=>1, AVGDBLK=>1, CLSTFCT=>227, INDLEVEL=>1, NO_INVALIDATE=>FALSE);

EXECUTE DBMS_STATS.SET_INDEX_STATS(:OWNER, '"ARFCRSTATE~0"', NUMROWS=>3390, NUMLBLKS=>60, NUMDIST=>3390, AVGLBLK=>1, AVGDBLK=>1, CLSTFCT=>600, INDLEVEL=>1, NO_INVALIDATE=>FALSE);

EXECUTE DBMS_STATS.SET_COLUMN_STATS(:OWNER, '"ARFCRSTATE"', '"ARFCIPID"', DISTCNT=>4, DENSITY=>.25, NULLCNT=>0, AVGCLEN=>9, NO_INVALIDATE=>FALSE);

EXECUTE DBMS_STATS.SET_COLUMN_STATS(:OWNER, '"ARFCRSTATE"', '"ARFCPID"', DISTCNT=>104, DENSITY=>.009615, NULLCNT=>0, AVGCLEN=>5, NO_INVALIDATE=>FALSE);

EXECUTE DBMS_STATS.SET_COLUMN_STATS(:OWNER, '"ARFCRSTATE"', '"ARFCTIME"', DISTCNT=>3310, DENSITY=>.00030211, NULLCNT=>0, AVGCLEN=>9, NO_INVALIDATE=>FALSE);

EXECUTE DBMS_STATS.SET_COLUMN_STATS(:OWNER, '"ARFCRSTATE"', '"ARFCTIDCNT"', DISTCNT=>1507, DENSITY=>.000667, NULLCNT=>0, AVGCLEN=>5, NO_INVALIDATE=>FALSE);

EXECUTE DBMS_STATS.SET_COLUMN_STATS(:OWNER, '"ARFCRSTATE"', '"ARFCDEST"', DISTCNT=>5, DENSITY=>.2, NULLCNT=>0, AVGCLEN=>16, NO_INVALIDATE=>FALSE);

EXECUTE DBMS_STATS.SET_COLUMN_STATS(:OWNER, '"ARFCRSTATE"', '"ARFCLUWCNT"', DISTCNT=>200, DENSITY=>0.005, NULLCNT=>0, AVGCLEN=>9, NO_INVALIDATE=>FALSE);

EXECUTE DBMS_STATS.SET_COLUMN_STATS(:OWNER, '"ARFCRSTATE"', '"ARFCSTATE"', DISTCNT=>100, DENSITY=>.01, NULLCNT=>0, AVGCLEN=>9, NO_INVALIDATE=>FALSE);

EXECUTE DBMS_STATS.SET_COLUMN_STATS(:OWNER, '"ARFCRSTATE"', '"ARFCFNAM"', DISTCNT=>5, DENSITY=>.2, NULLCNT=>0, AVGCLEN=>18, NO_INVALIDATE=>FALSE);

EXECUTE DBMS_STATS.SET_COLUMN_STATS(:OWNER, '"ARFCRSTATE"', '"ARFCRETURN"', DISTCNT=>4, DENSITY=>.25, NULLCNT=>0, AVGCLEN=>2, NO_INVALIDATE=>FALSE);

EXECUTE DBMS_STATS.SET_COLUMN_STATS(:OWNER, '"ARFCRSTATE"', '"ARFCUZEIT"', DISTCNT=>2000, DENSITY=>.0005, NULLCNT=>0, AVGCLEN=>7, NO_INVALIDATE=>FALSE);

EXECUTE DBMS_STATS.SET_COLUMN_STATS(:OWNER, '"ARFCRSTATE"', '"ARFCDATUM"', DISTCNT=>50, DENSITY=>.02, NULLCNT=>0, AVGCLEN=>9, NO_INVALIDATE=>FALSE);

EXECUTE DBMS_STATS.SET_COLUMN_STATS(:OWNER, '"ARFCRSTATE"', '"ARFCUSER"', DISTCNT=>20, DENSITY=>.05, NULLCNT=>0, AVGCLEN=>10, NO_INVALIDATE=>FALSE);

EXECUTE DBMS_STATS.SET_COLUMN_STATS(:OWNER, '"ARFCRSTATE"', '"ARFCRETRYS"', DISTCNT=>8, DENSITY=>0.125, NULLCNT=>0, AVGCLEN=>5, NO_INVALIDATE=>FALSE);

EXECUTE DBMS_STATS.SET_COLUMN_STATS(:OWNER, '"ARFCRSTATE"', '"ARFCTCODE"', DISTCNT=>12, DENSITY=>.083333333333333, NULLCNT=>0, AVGCLEN=>4, NO_INVALIDATE=>FALSE);

EXECUTE DBMS_STATS.SET_COLUMN_STATS(:OWNER, '"ARFCRSTATE"', '"ARFCRHOST"', DISTCNT=>5, DENSITY=>.2, NULLCNT=>0, AVGCLEN=>9, NO_INVALIDATE=>FALSE);

EXECUTE DBMS_STATS.SET_COLUMN_STATS(:OWNER, '"ARFCRSTATE"', '"ARFCMSG"', DISTCNT=>4, DENSITY=>.25, NULLCNT=>0, AVGCLEN=>3, NO_INVALIDATE=>FALSE);

EXECUTE DBMS_STATS.SET_COLUMN_STATS(:OWNER, '"ARFCRSTATE"', '"ARFCRESERV"', DISTCNT=>9, DENSITY=>.111111111111111, NULLCNT=>0, AVGCLEN=>234, NO_INVALIDATE=>FALSE);

EXECUTE DBMS_STATS.SET_COLUMN_STATS(:OWNER, '"ARFCRSTATE"', '"HASH"', DISTCNT=>5, DENSITY=>.2, NULLCNT=>0, AVGCLEN=>11, NO_INVALIDATE=>FALSE);

DELETE FROM "DBSTATC" WHERE DBOBJ = 'ARFCRSTATE' AND DBTYP IN (' ', 'ORACLE');

INSERT INTO "DBSTATC"

  (DBOBJ, DOTYP, OBJOW, DBTYP, VWTYP, ACTIV, OBJEC, AEDAT, SIGNI, AMETH, OPTIO, TOBDO, HISTO, TDDAT, DURAT, PLAND) VALUES

  ('ARFCRSTATE', '01', ' ', 'ORACLE', 'O', 'I', ' ', ' ', '0', ' ', ' ', ' ', ' ', ' ', '000000', ' ');

EXECUTE DBMS_STATS.LOCK_TABLE_STATS(:OWNER, '"ARFCRSTATE"');

COMMIT;

SPOOL OFF

Thanks a lot .Let me know if m wrong

Regards

Ram

Former Member
0 Kudos

Hi Peter,

owner used:sapr3, and used below  sql command too.

sqlplus / as sysdba

  SELECT INDEX_NAME FROM DBA_INDEXES WHERE OWNER = 'SAPR3' AND TABLE_NAME IN ( 'ARFCRSTATE') AND INDEX_NAME NOT LIKE '%~%' AND INDEX_TYPE != 'LOB';

no rows selected

sqlplus /nolog @statistics1.txt SAPR3

output was few sql procedure was completed and 1 row deleted and 1 row inserted.

I checked in DB 20 and  active flag as I in DBSTATC setting

do i need to run BRCONNECT statistic run for all tables?

Regards

Ram

Former Member
0 Kudos

Hi Ram,

I dont think you need to run collecting of stats at this point as you imported statistics for affected tables manually.

BR,

Peter

Former Member
0 Kudos

Thanks a lot peter and i am sorry as unmistakebly the correct answer i have clicked mine.You have provided the best solution to me

Regards

Ram

Former Member
0 Kudos

Hi Peter, thanks for your timely help.I have ran the scripts in Quality system firstly .I created one statistics.txt1file as below and updated the brtools to 7.10 and ran the sql scripts

Rem ###########################################################################################

Rem

Rem The following commands provide pre-defined CBO statistics for tables where the standard

Rem statistic creation is often not sufficient (e.g. because of the dynamic table content)

Rem

Rem This script is an attachment to SAP note 1020260 and is intended for Oracle databases

Rem 10g and 11g. It may also be used for database <= 9i (PLS-00302 errors have to be ignored then).

Rem

Rem Script name: statistics.txt

Rem

Rem Copyright: (c) 2007 - 2012

Rem

Rem  XXXXXX     XX    XXXXXX            XXXXX     XXXX    XXXXXX

Rem XX    X    XXXX    XX  XX          XX    X   XX  XX   XX 

Rem XX        XX  XX   XX  XX          XX       XX    XX  XX

Rem  XXXXX    XX  XX   XXXXX           XX       XX    XX  XXXXX

Rem      XX   XXXXXX   XX              XX       XX    XX  XX   

Rem X    XX   XX  XX   XX              XX    X   XX  XX   XX 

Rem XXXXXX    XX  XX  XXXX              XXXXX     XXXX    XXXXXX

Rem

Rem  XXXXX                               XXX

Rem XX   XX                               XX

Rem XX   XX  XX XXX    XXXXX    XXXXX     XX     XXXXX

Rem XX   XX   XXX XX       X   XX   XX    XX    XX    X

Rem XX   XX   XX      XXXXXX   XX         XX    XXXXXXX

Rem XX   XX   XX      X   XX   XX   XX    XX    XX

Rem  XXXXX   XXXX     XXXXX X   XXXXX    XXXX    XXXXX

Rem

Rem Author: Martin Frauendorfer

Rem Mail:   martin.frauendorfer@sap.com

Rem

Rem Content history:

Rem Date           | Change

Rem ---------------+----------------------------------------------------------------------------

Rem Jan. 23rd 2007 | PAYR.PERNR

Rem Mar. 15th 2007 | TRBAT2, TRBAT, TATAF, DDXTT, DDXTF

Rem Apr.  3rd 2007 | ARFCRSTATE, ARFCSDATA, ARFCSSTATE, QREFTID,

Rem                | TRFCQDATA, TRFCQIN, TRFCQOUT, TRFCQSTATE

Rem Apr. 13th 2007 | UPSITX.DELNUM

Rem Apr. 30th 2007 | SXMSPMAST, SXMSPEMAS, SXMSPVERS, SXMSPERROR,

Rem                | SXMSCLUP, SXMSCLUR

Rem May   3rd 2007 | SXMSPMAST2, SXMSPEMAS2, SXMSPVERS2, SXMSPERRO2,

Rem                | SXMSCLUP2, SXMSCLUR2

Rem May  11th 2007 | TBTCO.SDLSTRTDT, TBTCO.STATUS

Rem Jun. 26th 2007 | /SAPAPO/MATLOC.LOCID, /SAPAPO/MATLOC.SATID

Rem Aug. 23rd 2007 | SXMSPMAST~TID, SXMSPMAST2~TID

Rem Sep. 17th 2007 | LTAK.KQUIT

Rem Sep. 21st 2007 | MLST.AUFPL, AFKO.AUFNT

Rem Oct.  8th 2007 | TBTCO.EVENTID, TBTCO.EVENTPARM

Rem Nov. 12th 2007 | TRFCQSTATE.HASH

Rem Feb. 15th 2008 | Individual statistic changes only if NUM_ROWS >= 1000

Rem Apr. 16th 2008 | MLST.PLNNR

Rem Jul. 23rd 2008 | SXMSPMAST.PARENTMSG, SXMSPMAST2.PARENTMSG -> NUM_DISTINCT changed from 1 to 10

Rem Aug. 26th 2008 | TRFCQIN~6 -> DISTINCT_KEYS increased from 4 to 400

Rem Dec.  4th 2008 | Set BLEVEL of all TRFCQOUT indexes to 2 (before: values between 2 and 4)

Rem Jan.  2nd 2009 | /SAPAPO/ORDADM_I.ANCHOR

Rem Jan. 16th 2009 | SMOFCDBHD, SMOFCMPDAT, SMOFCMPHD, SMOFCMPOBJ

Rem Feb. 12th 2009 | SMOEJOBID, SMOHJOBQ, SMOHMSGQ, SMOHSITEQ

Rem Feb. 18th 2009 | TRFCQOUT: BLOCKS increased from 13.000 to 100.000 (to avoid full table scans)

Rem Feb. 20th 2009 | TRFCQIN: BLOCKS and LEAF_BLOCKS increased by factor 10 (to avoid segment scans)

Rem June 12th 2009 | SMFCMPDAT.TABNAME: NUM_DISTINCT >= 2

Rem Aug. 24th 2009 | SXMSPHIST, SXMSPHIST2

Rem Dec.  7th 2009 | More flexible execution of script via "/ AS SYSDBA" rather than SAP user

Rem Feb. 12th 2010 | AUFK.PSPEL, AFPO.PROJN, AFVC.PROJN: NUM_DISTINCT >= 10

Rem Mar.  1st 2010 | HOPCOUNT column for SXMSPHIST, SXMSPHIST2

Rem Apr. 19th 2010 | LTAK~Q: DISTINCT_KEYS >= 200

Rem Apr. 28th 2010 | SXMSPMAST.PARENTMSG, SXMSPMAST2.PARENTMSG: NUM_DISTINCT increased from 10 to 1000

Rem                | SXMSPMAST~PAR, SXMSPMAST2~PAR: DISTINCT_KEYS increased from 1 to 1000

Rem May  26th 2010 | /SAPAPO/MATLOC.LOCID, SAPAPO/MATLOC.SATID: NUM_DISTINCT increased from at least 5 to at least 20

Rem                | /SAPAPO/MATLOC~SAT: DISTINCT_KEYS increased from at least 25 to at least 400

Rem                | /SAPAPO/MATLOC~LID: DISTINCT_KEYS increased from at least 5 to at least 20

Rem June 21st 2010 | SXMSPMAST, SXMSPMAST2: NUM_DISTINCT of MSGTYPE increased from 3 to 15

Rem July  6th 2010 | SMOFCMPDAT: AVG_COL_LEN of SEG_DATA reduced from 3103 to 300

Rem                |             AVG_ROW_LEN reduced from 3213 to 400

Rem July  7th 2010 | SMOHSITEQEX, SMOHSITEQRD

Rem                | SMOHMSGQ: NUM_DISTINCT of QUEUENAME increased from 2 to 10

Rem Feb.  2nd 2011 | AFKO: NUM_DISTINCT of PRONR increased to at least 100

Rem                |       DISTINCT_KEYS of AFKO~3 increased to at least 100

Rem Apr.  5th 2011 | PAYR: DISTINCT_KEYS of PAYR~P increased to at least 10

Rem May   5th 2011 | TESTDATRNRPART0

Rem May  12th 2011 | TBTCO: NUM_DISTINCT of STATUS increased from 10 to 20

Rem Jul.  8th 2011 | AFKO: NUM_DISTINCT of MAUFNR increased to at least 100

Rem                |       DISTINCT_KEYS of AFKO~5 increased to at least 100

Rem Jul. 14th 2011 | BDCP2: NUM_DISTINCT of PROCESS increased to at least 10

Rem Jul. 20th 2011 | AUSP: Column group statistics for MANDT, KLART, ATINN

Rem Aug. 17th 2011 | BBP_PDHGP: NUM_DISTINCT of ACTIVE header increased to at least 100

Rem                |            DISTINCT_KEYS of BBP_PDHGP~VER increased to at least 100

Rem Sep.  3rd 2011 | BKPF: Column group statistics for MANDT, BUKRS, BSTAT

Rem Nov. 29th 2011 | MSEG: Column group statistics for MANDT, MBLNR, MJAHR

Rem Dec.  8th 2011 | SMOHMSGQRE

Rem Dec. 29th 2011 | VEPO: NUM_DISTINCT of UNVEL increased to at least 20

Rem                |       DISTINCT_KEYS of VEPO~A increased to at least 20

Rem Jan.  3rd 2012 | CATSDB: NUM_DISTINCT of STATUS increased to at least 20

Rem Jan.  6th 2012 | DRAW: NUM_DISTINCT of PRENR increased to at least 10

Rem                |       DISTINCT_KEYS OF DRAW~1 increased to at least 10

Rem Feb. 7 th 2012 | DFKKOP: NUM_DISTINCT of WHGRP increased to at least 2

Rem Feb. 16th 2012 | SWWWIHEAD: NUM_DISTINCT of CHECK_STAT increased to at least 10

Rem Feb. 27th 2012 | XI_AF_SVC_ID_MAP (JAVA schema)

Rem Mar.  8th 2012 | RSBATCHDATA: NUM_DISTINCT of RELID increased to at least 10

Rem Mar. 23rd 2012 | MSEG: Column group statistics for MANDT, WERKS, LGORT

Rem Mar. 29th 2012 | MSEG: Column group statistics for MANDT, MATNR, WERKS, LGORT

Rem

Rem Installation procedure:

Rem

Rem 1. Implement BRCONNECT with at least version 7.10 (25) - see SAP Note 12741.

Rem

Rem 2. Copy this script statistics.txt to a directory on the database server

Rem

Rem 3. Go to the directory with the script

Rem

Rem 4. Make sure that the indexes of the relevant tables follow the "~"

Rem    naming convention. Replace <sapuser> with the name of the SAP user

Rem    (SAPR3 / SAPSR3 / SAP<sid> / SAP<xyz>):

Rem

Rem    sqlplus / as sysdba

/*

       SELECT

         INDEX_NAME 

       FROM

         DBA_INDEXES

       WHERE

         OWNER = '<sapuser>' AND

         TABLE_NAME IN

         ( 'ARFCRSTATE'

         ) AND

         INDEX_NAME NOT LIKE '%~%' AND

         INDEX_TYPE != 'LOB';

*/

Rem    If indexes are returned (e.g. containing '_' or '^' as separator),

Rem    they have to be renamed to the "~" naming convention, e.g.:

Rem

Rem    ALTER INDEX "<sapuser>"."TATAF__0" RENAME TO "TATAF~0";

Rem    ALTER INDEX "<sapuser>"."SXMSPMAST^0" RENAME TO "SXMSPMAST~0";

Rem

Rem    This change of the index name is transparent to the SAP application.

Rem

Rem    Exit sqlplus via "exit".

Rem

Rem 5. Check if you have already made some customer specific settings for the

Rem    involved tables in DBSTATC. This script here will remove all previously

Rem    existing settings for the tables with delivered CBO statistics and so you

Rem    have to maintain them again appropriately after having run the script.

Rem

Rem 6. Execute the script statistics.txt. Replace <sapuser> with the name of

Rem    the SAP user (SAPR3 / SAPSR3 / SAP<sid> / SAP<xyz>):

Rem

Rem    sqlplus /nolog @statistics_BRCONNECT_710_25.txt <sapuser>

Rem

Rem 7. Check the spool file statistics.out for errors.

Rem

Rem 8. If errors are returned, consider the following:

Rem

Rem    PLS-00302: component 'LOCK_TABLE_STATS' must be declared

Rem    PLS-00302: component 'UNLOCK_TABLE_STATS' must be declared

Rem

Rem    -> These errors are returned if the script is run in an Oracle database with

Rem       release <= 9i because the locking functionality doesn't exist with these

Rem       releases. These errors are not critical, but be aware that this script

Rem       here is mainly intended for databases >= 10g.

Rem

Rem    ORA-00904: "DBMS_STATS"."CREATE_EXTENDED_STATS": invalid identifier

Rem

Rem    -> Extended statistics only available as of Oracle 11g, with earlier releases the

Rem       above ORA-00904 is thrown which can be ignored

Rem

Rem    ORA-00942: table or view does not exist

Rem

Rem    -> This error usually indicates that the script is run with the wrong user.

Rem       Make sure that you connect to SQLPLUS with the SAP schema owner.

Rem 

Rem    ORA-01403: no data found

Rem

Rem    -> This error indicates that the concerned table doesn't exist in the system.

Rem       This happens if a table doesn't exist for a specific SAP release and can be

Rem       ignored.

Rem

Rem    ORA-20000: INDEX "<owner>"."<index_name>" does not exist or insufficient privileges

Rem

Rem    -> This error can occur when the index name doesn't follow the "~" naming

Rem       convention. See step 4 and rename the index if necessary.

Rem    -> It can also happen if the index doesn't exist in the system. In this case it can

Rem       be ignored.

Rem

Rem    ORA-20000: TABLE "<owner>"."<table_name>" does not exist or insufficient privileges

Rem

Rem    -> This error is displayed if the table doesn't exist in the system. This can happen

Rem       because this script is generic while SAP systems often only contain a sub set of

Rem       the tables. In this case the error can be ignored.

Rem

Rem    ORA-20000: Unable to set values for column <column_name>: does not exist or

Rem               insufficient privileges

Rem

Rem    -> If columns are introduced with newer SAP releases, they are included in this

Rem       script. If an older SAP release without the column is used, updating the

Rem       statistics for this column will fail with ORA-20000 because the column doesn't

Rem       exist. This is not critical and can be ignored.

Rem

Rem    ORA-20005: object statistics are locked (stattype = ALL)

Rem

Rem    -> This error appears if the statistics of a table are already locked.

Rem       This situation should not occur because before performing the changes

Rem       the UNLOCK_TABLE_STATS functionality is executed.

Rem 

Rem    ORA-06550: line <line>, column <column>

Rem    ORA-06512: at line 

Rem

Rem    -> These error codes are secondary error codes (note 636475) and can be

Rem       ignored.

Rem

Rem    ORA-20007: extension (...) already exists in the table

Rem

Rem    -> Happens if required extended statistics are already defined and can be ignored

Rem

Rem 9. If you have created customer specific indexes or columns you have to take

Rem    into account that SAP can't deliver good statistics for them and so

Rem    you might have to adapt their statistics on your own in order to synchronize

Rem    them with the delivered statistics. This may only be necessary for tables that

Rem    receive a full set of statistics in this script (->

Rem    DBMS_STATS.SET_TABLE_STATS, DBMS_STATS.SET_INDEX_STATS and

Rem    DBMS_STATS.SET_COLUMN_STATS are used for these tables). In this case you

Rem    can manually execute the DBMS_STATS commands for these indexes and columns.

Rem

Rem 10. If you have identified customer specific DBSTATC settings in step 5 above, you have

Rem     to maintain them now again if required.

Rem

Rem 11. Some of the changes only take effect after the next BRCONNECT statistic run. If

Rem     required, schedule a BRCONNECT statistic run manually:

Rem   

Rem     brconnect -u / -c -f stats -t all

Rem ###########################################################################################

CONNECT / AS SYSDBA

SPOOL statistics.out

VARIABLE OWNER VARCHAR2(30)

EXECUTE :OWNER := '&&1'

ALTER SESSION SET CURRENT_SCHEMA = &&1;

-- ############# COMPLETE STATISTIC DELIVERY ###################

-- ARFCRSTATE --

EXECUTE DBMS_STATS.UNLOCK_TABLE_STATS(:OWNER, '"ARFCRSTATE"');

EXECUTE DBMS_STATS.SET_TABLE_STATS(:OWNER, '"ARFCRSTATE"', NUMROWS=>3390, NUMBLKS=>200, AVGRLEN=>260, NO_INVALIDATE=>FALSE);

EXECUTE DBMS_STATS.SET_INDEX_STATS(:OWNER, '"ARFCRSTATE~1"', NUMROWS=>3390, NUMLBLKS=>30, NUMDIST=>2432, AVGLBLK=>1, AVGDBLK=>1, CLSTFCT=>227, INDLEVEL=>1, NO_INVALIDATE=>FALSE);

EXECUTE DBMS_STATS.SET_INDEX_STATS(:OWNER, '"ARFCRSTATE~0"', NUMROWS=>3390, NUMLBLKS=>60, NUMDIST=>3390, AVGLBLK=>1, AVGDBLK=>1, CLSTFCT=>600, INDLEVEL=>1, NO_INVALIDATE=>FALSE);

EXECUTE DBMS_STATS.SET_COLUMN_STATS(:OWNER, '"ARFCRSTATE"', '"ARFCIPID"', DISTCNT=>4, DENSITY=>.25, NULLCNT=>0, AVGCLEN=>9, NO_INVALIDATE=>FALSE);

EXECUTE DBMS_STATS.SET_COLUMN_STATS(:OWNER, '"ARFCRSTATE"', '"ARFCPID"', DISTCNT=>104, DENSITY=>.009615, NULLCNT=>0, AVGCLEN=>5, NO_INVALIDATE=>FALSE);

EXECUTE DBMS_STATS.SET_COLUMN_STATS(:OWNER, '"ARFCRSTATE"', '"ARFCTIME"', DISTCNT=>3310, DENSITY=>.00030211, NULLCNT=>0, AVGCLEN=>9, NO_INVALIDATE=>FALSE);

EXECUTE DBMS_STATS.SET_COLUMN_STATS(:OWNER, '"ARFCRSTATE"', '"ARFCTIDCNT"', DISTCNT=>1507, DENSITY=>.000667, NULLCNT=>0, AVGCLEN=>5, NO_INVALIDATE=>FALSE);

EXECUTE DBMS_STATS.SET_COLUMN_STATS(:OWNER, '"ARFCRSTATE"', '"ARFCDEST"', DISTCNT=>5, DENSITY=>.2, NULLCNT=>0, AVGCLEN=>16, NO_INVALIDATE=>FALSE);

EXECUTE DBMS_STATS.SET_COLUMN_STATS(:OWNER, '"ARFCRSTATE"', '"ARFCLUWCNT"', DISTCNT=>200, DENSITY=>0.005, NULLCNT=>0, AVGCLEN=>9, NO_INVALIDATE=>FALSE);

EXECUTE DBMS_STATS.SET_COLUMN_STATS(:OWNER, '"ARFCRSTATE"', '"ARFCSTATE"', DISTCNT=>100, DENSITY=>.01, NULLCNT=>0, AVGCLEN=>9, NO_INVALIDATE=>FALSE);

EXECUTE DBMS_STATS.SET_COLUMN_STATS(:OWNER, '"ARFCRSTATE"', '"ARFCFNAM"', DISTCNT=>5, DENSITY=>.2, NULLCNT=>0, AVGCLEN=>18, NO_INVALIDATE=>FALSE);

EXECUTE DBMS_STATS.SET_COLUMN_STATS(:OWNER, '"ARFCRSTATE"', '"ARFCRETURN"', DISTCNT=>4, DENSITY=>.25, NULLCNT=>0, AVGCLEN=>2, NO_INVALIDATE=>FALSE);

EXECUTE DBMS_STATS.SET_COLUMN_STATS(:OWNER, '"ARFCRSTATE"', '"ARFCUZEIT"', DISTCNT=>2000, DENSITY=>.0005, NULLCNT=>0, AVGCLEN=>7, NO_INVALIDATE=>FALSE);

EXECUTE DBMS_STATS.SET_COLUMN_STATS(:OWNER, '"ARFCRSTATE"', '"ARFCDATUM"', DISTCNT=>50, DENSITY=>.02, NULLCNT=>0, AVGCLEN=>9, NO_INVALIDATE=>FALSE);

EXECUTE DBMS_STATS.SET_COLUMN_STATS(:OWNER, '"ARFCRSTATE"', '"ARFCUSER"', DISTCNT=>20, DENSITY=>.05, NULLCNT=>0, AVGCLEN=>10, NO_INVALIDATE=>FALSE);

EXECUTE DBMS_STATS.SET_COLUMN_STATS(:OWNER, '"ARFCRSTATE"', '"ARFCRETRYS"', DISTCNT=>8, DENSITY=>0.125, NULLCNT=>0, AVGCLEN=>5, NO_INVALIDATE=>FALSE);

EXECUTE DBMS_STATS.SET_COLUMN_STATS(:OWNER, '"ARFCRSTATE"', '"ARFCTCODE"', DISTCNT=>12, DENSITY=>.083333333333333, NULLCNT=>0, AVGCLEN=>4, NO_INVALIDATE=>FALSE);

EXECUTE DBMS_STATS.SET_COLUMN_STATS(:OWNER, '"ARFCRSTATE"', '"ARFCRHOST"', DISTCNT=>5, DENSITY=>.2, NULLCNT=>0, AVGCLEN=>9, NO_INVALIDATE=>FALSE);

EXECUTE DBMS_STATS.SET_COLUMN_STATS(:OWNER, '"ARFCRSTATE"', '"ARFCMSG"', DISTCNT=>4, DENSITY=>.25, NULLCNT=>0, AVGCLEN=>3, NO_INVALIDATE=>FALSE);

EXECUTE DBMS_STATS.SET_COLUMN_STATS(:OWNER, '"ARFCRSTATE"', '"ARFCRESERV"', DISTCNT=>9, DENSITY=>.111111111111111, NULLCNT=>0, AVGCLEN=>234, NO_INVALIDATE=>FALSE);

EXECUTE DBMS_STATS.SET_COLUMN_STATS(:OWNER, '"ARFCRSTATE"', '"HASH"', DISTCNT=>5, DENSITY=>.2, NULLCNT=>0, AVGCLEN=>11, NO_INVALIDATE=>FALSE);

DELETE FROM "DBSTATC" WHERE DBOBJ = 'ARFCRSTATE' AND DBTYP IN (' ', 'ORACLE');

INSERT INTO "DBSTATC"

  (DBOBJ, DOTYP, OBJOW, DBTYP, VWTYP, ACTIV, OBJEC, AEDAT, SIGNI, AMETH, OPTIO, TOBDO, HISTO, TDDAT, DURAT, PLAND) VALUES

  ('ARFCRSTATE', '01', ' ', 'ORACLE', 'O', 'I', ' ', ' ', '0', ' ', ' ', ' ', ' ', ' ', '000000', ' ');

EXECUTE DBMS_STATS.LOCK_TABLE_STATS(:OWNER, '"ARFCRSTATE"');

COMMIT;

SPOOL OFF

Thanks a lot .Let me know if m wrong

Regards

Ram

Former Member
0 Kudos

Hi Peter, thanks for your timely help.I have ran the scripts in Quality system firstly .I created one statistics.txt1file as below and updated the brtools to 7.10 and ran the sql scripts

Rem ###########################################################################################

Rem

Rem The following commands provide pre-defined CBO statistics for tables where the standard

Rem statistic creation is often not sufficient (e.g. because of the dynamic table content)

Rem

Rem This script is an attachment to SAP note 1020260 and is intended for Oracle databases

Rem 10g and 11g. It may also be used for database <= 9i (PLS-00302 errors have to be ignored then).

Rem

Rem Script name: statistics.txt

Rem

Rem Copyright: (c) 2007 - 2012

Rem

Rem  XXXXXX     XX    XXXXXX            XXXXX     XXXX    XXXXXX

Rem XX    X    XXXX    XX  XX          XX    X   XX  XX   XX 

Rem XX        XX  XX   XX  XX          XX       XX    XX  XX

Rem  XXXXX    XX  XX   XXXXX           XX       XX    XX  XXXXX

Rem      XX   XXXXXX   XX              XX       XX    XX  XX   

Rem X    XX   XX  XX   XX              XX    X   XX  XX   XX 

Rem XXXXXX    XX  XX  XXXX              XXXXX     XXXX    XXXXXX

Rem

Rem  XXXXX                               XXX

Rem XX   XX                               XX

Rem XX   XX  XX XXX    XXXXX    XXXXX     XX     XXXXX

Rem XX   XX   XXX XX       X   XX   XX    XX    XX    X

Rem XX   XX   XX      XXXXXX   XX         XX    XXXXXXX

Rem XX   XX   XX      X   XX   XX   XX    XX    XX

Rem  XXXXX   XXXX     XXXXX X   XXXXX    XXXX    XXXXX

Rem

Rem Author: Martin Frauendorfer

Rem Mail:   martin.frauendorfer@sap.com

Rem

Rem Content history:

Rem Date           | Change

Rem ---------------+----------------------------------------------------------------------------

Rem Jan. 23rd 2007 | PAYR.PERNR

Rem Mar. 15th 2007 | TRBAT2, TRBAT, TATAF, DDXTT, DDXTF

Rem Apr.  3rd 2007 | ARFCRSTATE, ARFCSDATA, ARFCSSTATE, QREFTID,

Rem                | TRFCQDATA, TRFCQIN, TRFCQOUT, TRFCQSTATE

Rem Apr. 13th 2007 | UPSITX.DELNUM

Rem Apr. 30th 2007 | SXMSPMAST, SXMSPEMAS, SXMSPVERS, SXMSPERROR,

Rem                | SXMSCLUP, SXMSCLUR

Rem May   3rd 2007 | SXMSPMAST2, SXMSPEMAS2, SXMSPVERS2, SXMSPERRO2,

Rem                | SXMSCLUP2, SXMSCLUR2

Rem May  11th 2007 | TBTCO.SDLSTRTDT, TBTCO.STATUS

Rem Jun. 26th 2007 | /SAPAPO/MATLOC.LOCID, /SAPAPO/MATLOC.SATID

Rem Aug. 23rd 2007 | SXMSPMAST~TID, SXMSPMAST2~TID

Rem Sep. 17th 2007 | LTAK.KQUIT

Rem Sep. 21st 2007 | MLST.AUFPL, AFKO.AUFNT

Rem Oct.  8th 2007 | TBTCO.EVENTID, TBTCO.EVENTPARM

Rem Nov. 12th 2007 | TRFCQSTATE.HASH

Rem Feb. 15th 2008 | Individual statistic changes only if NUM_ROWS >= 1000

Rem Apr. 16th 2008 | MLST.PLNNR

Rem Jul. 23rd 2008 | SXMSPMAST.PARENTMSG, SXMSPMAST2.PARENTMSG -> NUM_DISTINCT changed from 1 to 10

Rem Aug. 26th 2008 | TRFCQIN~6 -> DISTINCT_KEYS increased from 4 to 400

Rem Dec.  4th 2008 | Set BLEVEL of all TRFCQOUT indexes to 2 (before: values between 2 and 4)

Rem Jan.  2nd 2009 | /SAPAPO/ORDADM_I.ANCHOR

Rem Jan. 16th 2009 | SMOFCDBHD, SMOFCMPDAT, SMOFCMPHD, SMOFCMPOBJ

Rem Feb. 12th 2009 | SMOEJOBID, SMOHJOBQ, SMOHMSGQ, SMOHSITEQ

Rem Feb. 18th 2009 | TRFCQOUT: BLOCKS increased from 13.000 to 100.000 (to avoid full table scans)

Rem Feb. 20th 2009 | TRFCQIN: BLOCKS and LEAF_BLOCKS increased by factor 10 (to avoid segment scans)

Rem June 12th 2009 | SMFCMPDAT.TABNAME: NUM_DISTINCT >= 2

Rem Aug. 24th 2009 | SXMSPHIST, SXMSPHIST2

Rem Dec.  7th 2009 | More flexible execution of script via "/ AS SYSDBA" rather than SAP user

Rem Feb. 12th 2010 | AUFK.PSPEL, AFPO.PROJN, AFVC.PROJN: NUM_DISTINCT >= 10

Rem Mar.  1st 2010 | HOPCOUNT column for SXMSPHIST, SXMSPHIST2

Rem Apr. 19th 2010 | LTAK~Q: DISTINCT_KEYS >= 200

Rem Apr. 28th 2010 | SXMSPMAST.PARENTMSG, SXMSPMAST2.PARENTMSG: NUM_DISTINCT increased from 10 to 1000

Rem                | SXMSPMAST~PAR, SXMSPMAST2~PAR: DISTINCT_KEYS increased from 1 to 1000

Rem May  26th 2010 | /SAPAPO/MATLOC.LOCID, SAPAPO/MATLOC.SATID: NUM_DISTINCT increased from at least 5 to at least 20

Rem                | /SAPAPO/MATLOC~SAT: DISTINCT_KEYS increased from at least 25 to at least 400

Rem                | /SAPAPO/MATLOC~LID: DISTINCT_KEYS increased from at least 5 to at least 20

Rem June 21st 2010 | SXMSPMAST, SXMSPMAST2: NUM_DISTINCT of MSGTYPE increased from 3 to 15

Rem July  6th 2010 | SMOFCMPDAT: AVG_COL_LEN of SEG_DATA reduced from 3103 to 300

Rem                |             AVG_ROW_LEN reduced from 3213 to 400

Rem July  7th 2010 | SMOHSITEQEX, SMOHSITEQRD

Rem                | SMOHMSGQ: NUM_DISTINCT of QUEUENAME increased from 2 to 10

Rem Feb.  2nd 2011 | AFKO: NUM_DISTINCT of PRONR increased to at least 100

Rem                |       DISTINCT_KEYS of AFKO~3 increased to at least 100

Rem Apr.  5th 2011 | PAYR: DISTINCT_KEYS of PAYR~P increased to at least 10

Rem May   5th 2011 | TESTDATRNRPART0

Rem May  12th 2011 | TBTCO: NUM_DISTINCT of STATUS increased from 10 to 20

Rem Jul.  8th 2011 | AFKO: NUM_DISTINCT of MAUFNR increased to at least 100

Rem                |       DISTINCT_KEYS of AFKO~5 increased to at least 100

Rem Jul. 14th 2011 | BDCP2: NUM_DISTINCT of PROCESS increased to at least 10

Rem Jul. 20th 2011 | AUSP: Column group statistics for MANDT, KLART, ATINN

Rem Aug. 17th 2011 | BBP_PDHGP: NUM_DISTINCT of ACTIVE header increased to at least 100

Rem                |            DISTINCT_KEYS of BBP_PDHGP~VER increased to at least 100

Rem Sep.  3rd 2011 | BKPF: Column group statistics for MANDT, BUKRS, BSTAT

Rem Nov. 29th 2011 | MSEG: Column group statistics for MANDT, MBLNR, MJAHR

Rem Dec.  8th 2011 | SMOHMSGQRE

Rem Dec. 29th 2011 | VEPO: NUM_DISTINCT of UNVEL increased to at least 20

Rem                |       DISTINCT_KEYS of VEPO~A increased to at least 20

Rem Jan.  3rd 2012 | CATSDB: NUM_DISTINCT of STATUS increased to at least 20

Rem Jan.  6th 2012 | DRAW: NUM_DISTINCT of PRENR increased to at least 10

Rem                |       DISTINCT_KEYS OF DRAW~1 increased to at least 10

Rem Feb. 7 th 2012 | DFKKOP: NUM_DISTINCT of WHGRP increased to at least 2

Rem Feb. 16th 2012 | SWWWIHEAD: NUM_DISTINCT of CHECK_STAT increased to at least 10

Rem Feb. 27th 2012 | XI_AF_SVC_ID_MAP (JAVA schema)

Rem Mar.  8th 2012 | RSBATCHDATA: NUM_DISTINCT of RELID increased to at least 10

Rem Mar. 23rd 2012 | MSEG: Column group statistics for MANDT, WERKS, LGORT

Rem Mar. 29th 2012 | MSEG: Column group statistics for MANDT, MATNR, WERKS, LGORT

Rem

Rem Installation procedure:

Rem

Rem 1. Implement BRCONNECT with at least version 7.10 (25) - see SAP Note 12741.

Rem

Rem 2. Copy this script statistics.txt to a directory on the database server

Rem

Rem 3. Go to the directory with the script

Rem

Rem 4. Make sure that the indexes of the relevant tables follow the "~"

Rem    naming convention. Replace <sapuser> with the name of the SAP user

Rem    (SAPR3 / SAPSR3 / SAP<sid> / SAP<xyz>):

Rem

Rem    sqlplus / as sysdba

/*

       SELECT

         INDEX_NAME 

       FROM

         DBA_INDEXES

       WHERE

         OWNER = '<sapuser>' AND

         TABLE_NAME IN

         ( 'ARFCRSTATE'

         ) AND

         INDEX_NAME NOT LIKE '%~%' AND

         INDEX_TYPE != 'LOB';

*/

Rem    If indexes are returned (e.g. containing '_' or '^' as separator),

Rem    they have to be renamed to the "~" naming convention, e.g.:

Rem

Rem    ALTER INDEX "<sapuser>"."TATAF__0" RENAME TO "TATAF~0";

Rem    ALTER INDEX "<sapuser>"."SXMSPMAST^0" RENAME TO "SXMSPMAST~0";

Rem

Rem    This change of the index name is transparent to the SAP application.

Rem

Rem    Exit sqlplus via "exit".

Rem

Rem 5. Check if you have already made some customer specific settings for the

Rem    involved tables in DBSTATC. This script here will remove all previously

Rem    existing settings for the tables with delivered CBO statistics and so you

Rem    have to maintain them again appropriately after having run the script.

Rem

Rem 6. Execute the script statistics.txt. Replace <sapuser> with the name of

Rem    the SAP user (SAPR3 / SAPSR3 / SAP<sid> / SAP<xyz>):

Rem

Rem    sqlplus /nolog @statistics_BRCONNECT_710_25.txt <sapuser>

Rem

Rem 7. Check the spool file statistics.out for errors.

Rem

Rem 8. If errors are returned, consider the following:

Rem

Rem    PLS-00302: component 'LOCK_TABLE_STATS' must be declared

Rem    PLS-00302: component 'UNLOCK_TABLE_STATS' must be declared

Rem

Rem    -> These errors are returned if the script is run in an Oracle database with

Rem       release <= 9i because the locking functionality doesn't exist with these

Rem       releases. These errors are not critical, but be aware that this script

Rem       here is mainly intended for databases >= 10g.

Rem

Rem    ORA-00904: "DBMS_STATS"."CREATE_EXTENDED_STATS": invalid identifier

Rem

Rem    -> Extended statistics only available as of Oracle 11g, with earlier releases the

Rem       above ORA-00904 is thrown which can be ignored

Rem

Rem    ORA-00942: table or view does not exist

Rem

Rem    -> This error usually indicates that the script is run with the wrong user.

Rem       Make sure that you connect to SQLPLUS with the SAP schema owner.

Rem 

Rem    ORA-01403: no data found

Rem

Rem    -> This error indicates that the concerned table doesn't exist in the system.

Rem       This happens if a table doesn't exist for a specific SAP release and can be

Rem       ignored.

Rem

Rem    ORA-20000: INDEX "<owner>"."<index_name>" does not exist or insufficient privileges

Rem

Rem    -> This error can occur when the index name doesn't follow the "~" naming

Rem       convention. See step 4 and rename the index if necessary.

Rem    -> It can also happen if the index doesn't exist in the system. In this case it can

Rem       be ignored.

Rem

Rem    ORA-20000: TABLE "<owner>"."<table_name>" does not exist or insufficient privileges

Rem

Rem    -> This error is displayed if the table doesn't exist in the system. This can happen

Rem       because this script is generic while SAP systems often only contain a sub set of

Rem       the tables. In this case the error can be ignored.

Rem

Rem    ORA-20000: Unable to set values for column <column_name>: does not exist or

Rem               insufficient privileges

Rem

Rem    -> If columns are introduced with newer SAP releases, they are included in this

Rem       script. If an older SAP release without the column is used, updating the

Rem       statistics for this column will fail with ORA-20000 because the column doesn't

Rem       exist. This is not critical and can be ignored.

Rem

Rem    ORA-20005: object statistics are locked (stattype = ALL)

Rem

Rem    -> This error appears if the statistics of a table are already locked.

Rem       This situation should not occur because before performing the changes

Rem       the UNLOCK_TABLE_STATS functionality is executed.

Rem 

Rem    ORA-06550: line <line>, column <column>

Rem    ORA-06512: at line 

Rem

Rem    -> These error codes are secondary error codes (note 636475) and can be

Rem       ignored.

Rem

Rem    ORA-20007: extension (...) already exists in the table

Rem

Rem    -> Happens if required extended statistics are already defined and can be ignored

Rem

Rem 9. If you have created customer specific indexes or columns you have to take

Rem    into account that SAP can't deliver good statistics for them and so

Rem    you might have to adapt their statistics on your own in order to synchronize

Rem    them with the delivered statistics. This may only be necessary for tables that

Rem    receive a full set of statistics in this script (->

Rem    DBMS_STATS.SET_TABLE_STATS, DBMS_STATS.SET_INDEX_STATS and

Rem    DBMS_STATS.SET_COLUMN_STATS are used for these tables). In this case you

Rem    can manually execute the DBMS_STATS commands for these indexes and columns.

Rem

Rem 10. If you have identified customer specific DBSTATC settings in step 5 above, you have

Rem     to maintain them now again if required.

Rem

Rem 11. Some of the changes only take effect after the next BRCONNECT statistic run. If

Rem     required, schedule a BRCONNECT statistic run manually:

Rem   

Rem     brconnect -u / -c -f stats -t all

Rem ###########################################################################################

CONNECT / AS SYSDBA

SPOOL statistics.out

VARIABLE OWNER VARCHAR2(30)

EXECUTE :OWNER := '&&1'

ALTER SESSION SET CURRENT_SCHEMA = &&1;

-- ############# COMPLETE STATISTIC DELIVERY ###################

-- ARFCRSTATE --

EXECUTE DBMS_STATS.UNLOCK_TABLE_STATS(:OWNER, '"ARFCRSTATE"');

EXECUTE DBMS_STATS.SET_TABLE_STATS(:OWNER, '"ARFCRSTATE"', NUMROWS=>3390, NUMBLKS=>200, AVGRLEN=>260, NO_INVALIDATE=>FALSE);

EXECUTE DBMS_STATS.SET_INDEX_STATS(:OWNER, '"ARFCRSTATE~1"', NUMROWS=>3390, NUMLBLKS=>30, NUMDIST=>2432, AVGLBLK=>1, AVGDBLK=>1, CLSTFCT=>227, INDLEVEL=>1, NO_INVALIDATE=>FALSE);

EXECUTE DBMS_STATS.SET_INDEX_STATS(:OWNER, '"ARFCRSTATE~0"', NUMROWS=>3390, NUMLBLKS=>60, NUMDIST=>3390, AVGLBLK=>1, AVGDBLK=>1, CLSTFCT=>600, INDLEVEL=>1, NO_INVALIDATE=>FALSE);

EXECUTE DBMS_STATS.SET_COLUMN_STATS(:OWNER, '"ARFCRSTATE"', '"ARFCIPID"', DISTCNT=>4, DENSITY=>.25, NULLCNT=>0, AVGCLEN=>9, NO_INVALIDATE=>FALSE);

EXECUTE DBMS_STATS.SET_COLUMN_STATS(:OWNER, '"ARFCRSTATE"', '"ARFCPID"', DISTCNT=>104, DENSITY=>.009615, NULLCNT=>0, AVGCLEN=>5, NO_INVALIDATE=>FALSE);

EXECUTE DBMS_STATS.SET_COLUMN_STATS(:OWNER, '"ARFCRSTATE"', '"ARFCTIME"', DISTCNT=>3310, DENSITY=>.00030211, NULLCNT=>0, AVGCLEN=>9, NO_INVALIDATE=>FALSE);

EXECUTE DBMS_STATS.SET_COLUMN_STATS(:OWNER, '"ARFCRSTATE"', '"ARFCTIDCNT"', DISTCNT=>1507, DENSITY=>.000667, NULLCNT=>0, AVGCLEN=>5, NO_INVALIDATE=>FALSE);

EXECUTE DBMS_STATS.SET_COLUMN_STATS(:OWNER, '"ARFCRSTATE"', '"ARFCDEST"', DISTCNT=>5, DENSITY=>.2, NULLCNT=>0, AVGCLEN=>16, NO_INVALIDATE=>FALSE);

EXECUTE DBMS_STATS.SET_COLUMN_STATS(:OWNER, '"ARFCRSTATE"', '"ARFCLUWCNT"', DISTCNT=>200, DENSITY=>0.005, NULLCNT=>0, AVGCLEN=>9, NO_INVALIDATE=>FALSE);

EXECUTE DBMS_STATS.SET_COLUMN_STATS(:OWNER, '"ARFCRSTATE"', '"ARFCSTATE"', DISTCNT=>100, DENSITY=>.01, NULLCNT=>0, AVGCLEN=>9, NO_INVALIDATE=>FALSE);

EXECUTE DBMS_STATS.SET_COLUMN_STATS(:OWNER, '"ARFCRSTATE"', '"ARFCFNAM"', DISTCNT=>5, DENSITY=>.2, NULLCNT=>0, AVGCLEN=>18, NO_INVALIDATE=>FALSE);

EXECUTE DBMS_STATS.SET_COLUMN_STATS(:OWNER, '"ARFCRSTATE"', '"ARFCRETURN"', DISTCNT=>4, DENSITY=>.25, NULLCNT=>0, AVGCLEN=>2, NO_INVALIDATE=>FALSE);

EXECUTE DBMS_STATS.SET_COLUMN_STATS(:OWNER, '"ARFCRSTATE"', '"ARFCUZEIT"', DISTCNT=>2000, DENSITY=>.0005, NULLCNT=>0, AVGCLEN=>7, NO_INVALIDATE=>FALSE);

EXECUTE DBMS_STATS.SET_COLUMN_STATS(:OWNER, '"ARFCRSTATE"', '"ARFCDATUM"', DISTCNT=>50, DENSITY=>.02, NULLCNT=>0, AVGCLEN=>9, NO_INVALIDATE=>FALSE);

EXECUTE DBMS_STATS.SET_COLUMN_STATS(:OWNER, '"ARFCRSTATE"', '"ARFCUSER"', DISTCNT=>20, DENSITY=>.05, NULLCNT=>0, AVGCLEN=>10, NO_INVALIDATE=>FALSE);

EXECUTE DBMS_STATS.SET_COLUMN_STATS(:OWNER, '"ARFCRSTATE"', '"ARFCRETRYS"', DISTCNT=>8, DENSITY=>0.125, NULLCNT=>0, AVGCLEN=>5, NO_INVALIDATE=>FALSE);

EXECUTE DBMS_STATS.SET_COLUMN_STATS(:OWNER, '"ARFCRSTATE"', '"ARFCTCODE"', DISTCNT=>12, DENSITY=>.083333333333333, NULLCNT=>0, AVGCLEN=>4, NO_INVALIDATE=>FALSE);

EXECUTE DBMS_STATS.SET_COLUMN_STATS(:OWNER, '"ARFCRSTATE"', '"ARFCRHOST"', DISTCNT=>5, DENSITY=>.2, NULLCNT=>0, AVGCLEN=>9, NO_INVALIDATE=>FALSE);

EXECUTE DBMS_STATS.SET_COLUMN_STATS(:OWNER, '"ARFCRSTATE"', '"ARFCMSG"', DISTCNT=>4, DENSITY=>.25, NULLCNT=>0, AVGCLEN=>3, NO_INVALIDATE=>FALSE);

EXECUTE DBMS_STATS.SET_COLUMN_STATS(:OWNER, '"ARFCRSTATE"', '"ARFCRESERV"', DISTCNT=>9, DENSITY=>.111111111111111, NULLCNT=>0, AVGCLEN=>234, NO_INVALIDATE=>FALSE);

EXECUTE DBMS_STATS.SET_COLUMN_STATS(:OWNER, '"ARFCRSTATE"', '"HASH"', DISTCNT=>5, DENSITY=>.2, NULLCNT=>0, AVGCLEN=>11, NO_INVALIDATE=>FALSE);

DELETE FROM "DBSTATC" WHERE DBOBJ = 'ARFCRSTATE' AND DBTYP IN (' ', 'ORACLE');

INSERT INTO "DBSTATC"

  (DBOBJ, DOTYP, OBJOW, DBTYP, VWTYP, ACTIV, OBJEC, AEDAT, SIGNI, AMETH, OPTIO, TOBDO, HISTO, TDDAT, DURAT, PLAND) VALUES

  ('ARFCRSTATE', '01', ' ', 'ORACLE', 'O', 'I', ' ', ' ', '0', ' ', ' ', ' ', ' ', ' ', '000000', ' ');

EXECUTE DBMS_STATS.LOCK_TABLE_STATS(:OWNER, '"ARFCRSTATE"');

COMMIT;

SPOOL OFF

Thanks a lot .Let me know if m wrong

Regards

Ram

Former Member
0 Kudos

Hi Bharatram,

You have run CBO stats I guess. Please try running space stats as well. Please check the OSS note 554031 in detail and then run space stats accordingly and check if there is any performance improvement.

Regards.

Ruchit Khushu