on 06-12-2012 3:12 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Peter,
owner used:sapr3, and used below sql command too.
sqlplus / as sysdba
SELECT INDEX_NAME FROM DBA_INDEXES WHERE OWNER = 'SAPR3' AND TABLE_NAME IN ( 'ARFCRSTATE') AND INDEX_NAME NOT LIKE '%~%' AND INDEX_TYPE != 'LOB';
no rows selected
sqlplus /nolog @statistics1.txt SAPR3
output was few sql procedure was completed and 1 row deleted and 1 row inserted.
I checked in DB 20 and active flag as I in DBSTATC setting
do i need to run BRCONNECT statistic run for all tables?
Regards
Ram
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
83 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.