* SQL Tuning Aid in SAP
* ---------------------
* To tune SQLs effectively one must know relative row counts of tables
in the program.
* Also primary Keys & all indexes of all the selected tables are shown
all in 1 place.
* Then the ABAP programmer has to change navigation and logic to suit
indexes.
* The large tables are likely to be the "hot spots".
* As a last resort it may be necessary to add a new Index to SAP or Z
tables.
* Try with BSEG MSEG A004 RFBLG KAPOL MSEG VBFA
* The Code is given below for SAP with Oracle RDBMS. Should be easy to
adapt to SQLServer Informix DB2,
* if you know a bit of the DBA side of things.
REPORT ZSQLTUNE.
TYPE-POOLS: slis. "ALV Global types
***Table Declaration
TABLES: dd02l.
***Internal Tables Declaration
TYPES: BEGIN OF t_statsora,
num_rows TYPE i,
avg_row_len TYPE i,
last_analyzed TYPE ekbe-budat,
END OF t_statsora.
TYPES: BEGIN OF t_stats,
tabname TYPE dd02t-tabname,
tabclass TYPE dd02v-tabclass,
num_rows TYPE i,
avg_row_len TYPE i,
last_analyzed TYPE ekbe-budat,
ddtext TYPE dd02t-ddtext,
index0(80) TYPE c, "DD03L
index1(80) TYPE c, "1-6 from DD17S
index2(80) TYPE c,
index3(80) TYPE c,
index4(80) TYPE c,
index5(80) TYPE c,
index6(80) TYPE c,
END OF t_stats.
DATA: i_stats TYPE STANDARD TABLE OF t_stats,
r_stats TYPE t_stats,
r_statsora TYPE t_statsora,
l_kount TYPE i.
DATA: secs(2) TYPE n,
rndnum TYPE i,
iscreated TYPE i.
CONSTANTS: allmychoices(44) TYPE c VALUE
'ProgFuncBAdIFormSmrtObjtTcodWbObTblsHelpWhlp'.
DATA: schema(30) TYPE c,
idxnum(1) TYPE n,
windexname(30) TYPE c,
posnum TYPE dd03l-position,
wfieldname(30) TYPE c,
fldname TYPE string.
FIELD-SYMBOLS:
*&---------------------------------------------------------------------*
* SELECTION-SCREEN DESIGN
*
*&---------------------------------------------------------------------*
SELECTION-SCREEN: BEGIN OF BLOCK b1sels WITH FRAME TITLE text-001.
SELECT-OPTIONS: stabname FOR dd02l-tabname. "Abap
table
SELECTION-SCREEN: END OF BLOCK b1sels.
*&---------------------------------------------------------------------*
* INITIALIZATION EVENT
*
*&---------------------------------------------------------------------*
INITIALIZATION.
*&---------------------------------------------------------------------*
* AT SELECTION-SCREEN VALUE-REQUEST EVENT
*
*&---------------------------------------------------------------------*
*&---------------------------------------------------------------------*
* AT SELECTION-SCREEN EVENT
*
*&---------------------------------------------------------------------*
*&---------------------------------------------------------------------*
* START-OF-SELECTION EVENT
*
*&---------------------------------------------------------------------*
START-OF-SELECTION.
PERFORM f_validation.
PERFORM f_retrieve_data.
PERFORM f_process_data.
PERFORM f_display_data.
*&---------------------------------------------------------------------*
*& Form F_VALIDATION
*&---------------------------------------------------------------------*
FORM f_validation.
ENDFORM. " F_VALIDATION
*&---------------------------------------------------------------------*
*& Form F_RETRIEVE_DATA
*&---------------------------------------------------------------------*
FORM f_retrieve_data .
SELECT dd02v~tabname "ABAP TableBName
dd02v~tabclass
dd02t~ddtext
INTO CORRESPONDING FIELDS OF TABLE i_stats
FROM dd02v INNER JOIN dd02t
ON dd02v~tabname = dd02t~tabname
AND dd02v~ddlanguage = dd02t~ddlanguage
AND dd02t~ddlanguage = sy-langu
WHERE dd02t~tabname IN stabname.
SELECT sqltab AS tabname "ABAP TableBName
sqlclass AS tabclass
ddtext
APPENDING CORRESPONDING FIELDS OF TABLE i_stats
FROM dd06v
WHERE ddlanguage = sy-langu
AND sqltab IN stabname.
ENDFORM. " F_RETRIEVE_DATA
*&---------------------------------------------------------------------*
*& Form F_PROCESS_DATA
*&---------------------------------------------------------------------*
FORM f_process_data .
LOOP AT i_stats INTO r_stats.
MOVE 0 TO l_kount.
EXEC SQL.
open c1 for
select a.num_rows,
a.avg_row_len,
TO_CHAR(a.last_analyzed,'YYYYMMDD') As last_analyzed
from USER_tables a
where a.table_name = :r_stats-tabname
ENDEXEC.
DO.
EXEC SQL.
fetch next c1 INTO :R_STATSORA
ENDEXEC.
IF sy-subrc <> 0.
EXIT.
ENDIF.
MOVE-CORRESPONDING r_statsora TO r_stats.
EXIT.
ENDDO.
EXEC SQL.
close c1
ENDEXEC.
r_stats-index0 = 'PK('.
SELECT fieldname
position
INTO (wfieldname, posnum)
FROM dd03l
WHERE tabname = r_stats-tabname
AND keyflag = 'X'
ORDER BY position.
IF r_stats-index0 = 'PK('.
CONCATENATE r_stats-index0 wfieldname INTO
r_stats-index0.
ELSE.
CONCATENATE r_stats-index0 ',' wfieldname INTO
r_stats-index0.
ENDIF.
ENDSELECT.
CONCATENATE r_stats-index0 ')' INTO r_stats-index0.
idxnum = 0.
SELECT indexname
fieldname
position
INTO (windexname, wfieldname, posnum)
FROM dd17s
WHERE sqltab = r_stats-tabname
ORDER BY indexname position.
IF posnum = 1.
IF idxnum <> 0.
CONCATENATE
ENDIF.
ADD 1 TO idxnum.
IF idxnum > 7.
CONCATENATE r_stats-index6 ' more!!!' INTO
r_stats-index6 .
EXIT.
ENDIF.
CONCATENATE 'R_STATS-INDEX' idxnum INTO fldname.
ASSIGN (fldname) TO
CONCATENATE windexname '(' wfieldname INTO
ELSE.
CONCATENATE
ENDIF.
ENDSELECT.
IF idxnum <> 0.
CONCATENATE
ENDIF.
MODIFY i_stats FROM r_stats.
ENDLOOP.
ENDFORM. " F_PROCESS_DATA
*&---------------------------------------------------------------------*
*& Form F_DISPLAY_DATA
*&---------------------------------------------------------------------*
FORM f_display_data.
* Macro definition
DEFINE m_fieldcat.
ls_fieldcat-fieldname = &1.
ls_fieldcat-tabname = &2.
ls_fieldcat-ref_fieldname = &3.
ls_fieldcat-ref_tabname = &4.
ls_fieldcat-seltext_l = &7.
ls_fieldcat-seltext_m = &7.
ls_fieldcat-seltext_s = &7.
ls_fieldcat-reptext_ddic = &7.
ls_fieldcat-hotspot = &5.
ls_fieldcat-fix_column = &6.
append ls_fieldcat to lt_fieldcat.
END-OF-DEFINITION.
DEFINE m_sort.
ls_sort-tabname = &1.
ls_sort-fieldname = &2.
ls_sort-up = 'X'.
append ls_sort to lt_sort.
END-OF-DEFINITION.
DATA:
ls_fieldcat TYPE slis_fieldcat_alv,
lt_fieldcat TYPE slis_t_fieldcat_alv," Field catalog
ls_sort TYPE slis_sortinfo_alv,
lt_sort TYPE slis_t_sortinfo_alv," Sort table
ls_keyinfo TYPE slis_keyinfo_alv,
ls_layout TYPE slis_layout_alv.
ls_layout-box_tabname = 'I_STATS'.
ls_layout-min_linesize = 240.
ls_layout-window_titlebar = 'Index Info & Oracle Statistics'..
ls_layout-colwidth_optimize = 'X'.
m_fieldcat 'TABNAME' 'I_STATS' 'TABNAME' 'DD02T' '
' 'X' 'Table Name'.
m_fieldcat 'TABCLASS' 'I_STATS' 'TABCLASS' 'DD02V' '
' ' ' 'Class'.
m_fieldcat 'NUM_ROWS' 'I_STATS' 'STYLE' 'ABDEMONODE' '
' ' ' 'Num Rows'.
m_fieldcat 'AVG_ROW_LEN' 'I_STATS' 'STYLE' 'ABDEMONODE' '
' ' ' 'Avg.RowLen'.
m_fieldcat 'LAST_ANALYZED' 'I_STATS' 'BUDAT' 'EKBE' '
' ' ' 'LastAnalyzed'.
m_fieldcat 'DDTEXT' 'I_STATS' 'DDTEXT' 'DD02T' '
' ' ' 'Description'.
m_fieldcat 'INDEX0' 'I_STATS' 'MATKX' 'MAKT' '
' ' ' 'PrmKey'.
m_fieldcat 'INDEX1' 'I_STATS' 'MATKX' 'MAKT' '
' ' ' 'Index1'.
m_fieldcat 'INDEX2' 'I_STATS' 'MATKX' 'MAKT' '
' ' ' 'Index2'.
m_fieldcat 'INDEX3' 'I_STATS' 'MATKX' 'MAKT' '
' ' ' 'Index3'.
m_fieldcat 'INDEX4' 'I_STATS' 'MATKX' 'MAKT' '
' ' ' 'Index4'.
m_fieldcat 'INDEX5' 'I_STATS' 'MATKX' 'MAKT' '
' ' ' 'Index5'.
m_fieldcat 'INDEX6' 'I_STATS' 'MATKX' 'MAKT' '
' ' ' 'Index6'.
CALL FUNCTION 'REUSE_ALV_LIST_DISPLAY'
EXPORTING
is_layout = ls_layout
it_fieldcat = lt_fieldcat
TABLES
t_outtab = i_stats.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.
ENDFORM. " F_DISPLAY_DATA
SAP SQL Tuning Aid with Oracle RDBMS Statistics
SAP Tablespace sizes in large databases
-----Original Message-----
Subject: BASIS: Tablespace sizes in large databases -Reply [5]
From: mark kochanski
Robert and others following the thread,
First a little background on extents in our production system. We created the instance about 4 months prior to going live. As man of you know, getting down time during the last few months is nearly impossible, so we saw and let extents grow. In fact, by the time we went live, we had 2 objects over 450, 5 objects over 300, about 50 objects (tables and indices) that were over 100 extents, and we had hundreds of objects over 10 extents.
I agree to doing both planning for growth and monitoring growth. And the earlier in your SAP implementation you do this the better - which is something we did not do until after we created our productive instance.
We were very concerned about this situation and spoke to 3 or 4 different SAP consultants. We got the same answer from each - objects in high extents will have little or no performance impact. Like Sanjay mentioned, the consultants had no specific reason for this.
I do not believe you will find an SAP employed person who will say you should keep extents below a specific value. Also, I cannot definitively give that advice either.
Over the months we have all our objects below 100 extents. We have not seen a significant change in database response time. Our goal is to have all objects below 20 extents - which is a corporate standard.
But we will not ask for extra down time to reach this goal.
Good luck trying to keep objects below 10 extents. While data is "pumped" into the system during the weeks before going live, whatch the extents, they will take off. This also occurs after performing a SAP version upgrade.
Mark A. Kochanski
-----Reply Message-----
Subject: Re: BASIS: Tablespace sizes in large databases -Reply [3]
From: "Robert A. Simard"
Gentleman,
Why not do both? Planning for growth is critical. Monitoring daily can be automated via CCMS can it not? With proper alert thresholds, a system freeze can be thwarted long before extents reach 300 (max extents in my version of Oracle).
My question to you both is, how many extents are to many? I have heard from consultants that SAP says that, for performance reasons 10 is the limit. I do not understand the logic in this. Unless There is alot of fragmentation throughout the tables, why not 50 or 100? I just completed a Client Copy and have 4 tables in the BTABD tablespace that are over 17
extents. Is this to many? and should I lose the uptime for a reorg for 17 versus 10 extents?
I guess what I am asking is, since both of you seem to have put some thought into this, is there a hard-and fast number when in comes to an acceptable amount of extents? SAP seems to be overly conservative most of the time - was wondering if anyone has good numbers?
Thanks and have a great day.
~Bob
----------------------------------------------------------------------------
Robert A Simard SAP-Basis Support, NT Sys. Admin.
"Whoever is first in the field and awaits the coming of the enemy, will
be fresh for the fight; whoever is second in the field and has to hasten
to battle will arrive exhausted."
Sun Tzu - The Art of War
----------------------------------------------------------------------------
-----Reply Message-----
Subject: Re: BASIS: Tablespace sizes in large databases -Reply [3] -Reply
From: Sanjay Shastri
Good suggestion and well received ;-)
Now to try and answer your question, looking at just Oracle ( or any DB ), you would think that too many extents would cause problems with your performance ( and that is quite true in most cases ). However, I believe I read on this list that SAP ignores the extent growth ( no explanation provided ) and that it 'really does not matter how large the number gets'.....
We have several tables that are over 150 extents and don't see too much of a performance glitch ( on an overall level ) but in practice, I do not let any table go beyond 100 extents in an SAP environment. Letting indices grow too much seems to have a much greater impact.
Any comments / insights?
- Sanjay
-----Reply Message-----
Subject: BASIS: Tablespace sizes in large databases -Reply -Reply
From: Sanjay Shastri
Mark,
would you be willing to risk a system freeze, even if it happens once? I happen to believe in the saying 'prevention is better than cure' !
You are correct that keeping up with extent growth and increasing the size of the next extent via SAPDBA controls the extent problem but, resizing the tables offers one advantage in that you plan better for growth and you are not bogged down by too much of an maintenance effort. System availability IS critical and minimizing downtime doesn't hurt... ;-)
- Sanjay
-----Reply Message-----
Subject: BASIS: Tablespace sizes in large databases -Reply -Reply
From: Mark Kochanski
Sanjay,
Tablespaces will grow and you can add space as needed but if you run out of extents on tables....tough luck!
Why Tough luck? Sure, if a table or index reaches max extents your system will freeze or go down, or certain transactions will have errors. .....
-----End of Reply Message-----
Copying table entries from client 000
I need to copy table entries from client 000.
I have identified which entries I need to copy through running RPULCP00 but I don't know how to move the entries.
The simplest way is to go into the table through SM31
Then in your top row of buttons there should be one called 'utilities' from here select 'adjust',
Then select the client that you want to compare/copy from (you need to have an RFC destination set up).
This will then show you the contents of the table in both clients and identify the status of each record, they will fall into the following categories:
ML Differences, logon client entry
MR Differences, comparison client entry
L Entry only exists in logon client
R Entry only exists in comparison client
Identical entries
(M) Differences only in hidden fields
You should be able to scroll down the table, select the entries that you want to import, then hit the 'adjust' button, then hit the 'copy all' button, then back out with the green arrow, and save your table.
That should do the job.
Transport Tables between Clients
Use report RSCLCCOP to transport user master records, profiles and authorizatons between clients in an R/3 system.
Start RSCLCCOP from the target client which the users and authorizations should be copied.
Do not use this report if the target client contains some users and authorizations you want to preserve.
Finding any of the SAP tables that have been changed
During the production run of the SAP system, additional fields might have been added and you might have lost tracks of the SAP tables changes.
Transaction code SPDD have been created to help you to find all the SAP tables that have been modified.
Other ABAP Dictionary objects such as lock objects, matchcodes, and views, for which modification would not result in data loss, are not processed during the upgrade with transaction SPDD, but only after the upgrade is complete with transaction SPAU.
Basis - Edit, create, delete or adjust your database table
Edit, create, delete or adjust your database table
The database utility is the interface between the ABAP Dictionary and the relational database underlying the R/3 System.
This tools allows you to delete all the data in the tables.
You can call the database utility from the initial screen of the ABAP Dictionary with
Utilities -> Database utility (Transaction SE14).
You can use the database utility to edit all the database objects that are generated from objects of the ABAP Dictionary.
These are database tables that are generated from transparent tables or physical table pools or table clusters, indexes, database views and matchcode pooled tables or matchcode views.
If you want to use the database utility, you need authorization for authorization object
S_DDIC_OBJ, e. g. S_DDIC_ALL.
How to activate the IMG Change Log?
SCU3 transaction is used to see the IMG change logs for modified objects.
If your table change log is not active, it will gives you a message that :-
...table logging is switched off...
This particular setting can be changed in the following path :-
IMG--> Basis Components --> System Administration --> Tables changes recording
You can log changes made to the following tables:
- Control tables (system logic control)
- Customizing tables
What is recorded is always in the form of complete "before" images, that is, all entries as they appear before the changes.
The recorded data is compressed without buffering, and this is not an appropriate method for recording and managing large amounts of data. Activating logging impacts on performance as it entails twice as many database updates as would otherwise be the case, and the database storage load is also increased substantially.
It is recommend that you use logging for your production clients and Customizing clients so that you can see exactly where Customizing tables have been changed. Other than the reasons above, it is not recommended that you use this tool for application tables.
Two conditions have to be met for a table to be logged:
1. The table has to be selected for logging in the Dictionary (see Dictionary -> Table maintenance -> Technical configuration).
2. Logging also has to be set in the system profile
Set the rec/client (note the use of lowercase characters) profile parameter to one of the following values :-
- OFF: no logging at all (effectively a central system switch)
- nnn: logs client-specific tables in client nnn only
- mmm,nnn,ppp,...: logs client-specific tables in the named clients
- ALL: logs all client-specific tables in all clients.
Caution: Only in exceptional circumstances is it appropriate to use the 'ALL' setting. If, for example, the profile parameter is set to 'ALL' when you upgrade all test clients (including 000, the SAP client), these changes are recorded in the system log file. This reduces performance and requires a lot of database space.
The default setting is OFF (no changes are logged).
If logging is set in the ABAP Dictionary, changes to client-indepedent tables are always logged unless rec/client is set to 'OFF'
Use the ABAP programs RSTBHIST or RSVTPROT to analyze table changes. RSVTPROT allows you to analyze change logs both at table level and with reference to Customizing objects. To access the program, select an executable Customizing activity in IMG and choose Goto -> Change log.
How to Increase the Tablespace free space?
our tablespace PSAPBTABD is 92% used. How to increase the free space?
SAPDBA V3.1I - SAP Database Administration
____________________________________________________________________________
ORACLE version : 7.3.4.3.0
ORACLE_SID : ALD
ORACLE_HOME : /oracle/ALD
DATABASE : open
SAPR3 : 31H, 23 times connected
a - Startup/Shutdown instance h - Backup database
b - Instance information i - Backup archive logs
c - Tablespace administration j - Check (and repair) database
d - Reorganization k - Restore/Recovery
e - Export/import l - Show/Cleanup
f - Archive mode m - User and Security
g - Additional functions n - Help
q - Quit
Please select ==> c
____________________________________________________________________________
Tablespace administration
____________________________________________________________________________
Current value
a - Tablespace:
b - Log checks: no
c - Free space and fragmentation of all tablespaces
d - Check free space of objects in all tablespaces
e - Check free space of objects in tablespace
f - Alter tablespace Add Datafile
g - Create/drop tablespace
h - Display all tablespaces and datafiles
i - Display raw device drivers in /dev
q - Return
Please select ==> a
Enter tablespace name [%] ==> psapbtabd
____________________________________________________________________________
Tablespace administration
____________________________________________________________________________
Current value
a - Tablespace: PSAPBTABD
b - Log checks: no
c - Free space and fragmentation of all tablespaces
d - Check free space of objects in all tablespaces
e - Check free space of objects in tablespace PSAPBTABD
f - Alter tablespace PSAPBTABD Add Datafile
g - Drop tablespace PSAPBTABD
h - Display all tablespaces and datafiles
i - Display raw device drivers in /dev
q - Return
Please select ==> f
____________________________________________________________________________
Alter tablespace PSAPBTABD add datafile
____________________________________________________________________________
Current value
a - File system or raw device: file system
b - File size: 2088960 K (larger than free space)
c - Select path: /oracle/ALD/sapdata5
d - Alter suggested path: /oracle/ALD/sapdata5
free space: 3608 K
e - Display current files: 10 files, total (in DB): 77
s - Start (Add datafile)
q - Return
Please select ==> s
Reorganization of Single Object
You have ARCHIVED the BSIS table (because of old data etc) and now this BSIS table has "holes" in physical structure and it's quite a large table.
Here are the steps to reorg. a single object:
1. Take a backup of your Oracle database.
2. Shutdown SAP
3. Start Oracle Database with Rollback Segment PSAPROLLBIG (all other smaller segments not started). Remember, the size of PSAPROLLBIG should be equal or larger than your BSIS table.
4. Ensure sapreorg directory has enough space for taking BSIS Oracle Export dump. The Oracle Export utility compresses the file to some extent.
5. Fire your SAPDBA from Unix level.
6. Go into SAPDBA "reorganisation" menu.
7. Select "Reorganize Single Table / Index"
8. Give BSIS as table name.
9. Start.
10. Don't Forget, first upgrade your SAPDBA tools first from OSS/SAPNet.
These include: 1. sapdba 2. brtools 3. brarchive 4. brbackup 5. brrestore 6. brconnec
