Skip to Content

sap system slow after upgrade

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • Best Answer
    avatar image
    Former Member
    Jun 12, 2012 at 02:37 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member bharatram ramakrishnan

      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

      RFC_STAT.txt.zip (3.7 kB)
  • Jun 13, 2012 at 11:47 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jun 12, 2012 at 02:20 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Jun 13, 2012 at 11:47 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Jun 13, 2012 at 11:47 AM

    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

    Add comment
    10|10000 characters needed characters exceeded