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

  • Follow
  • 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