IFTTT is a free web-based service that people use to create chains of simple conditional statements, called applets. An applet is triggered by changes that occur within other web services such as G...
tutorial on how to use powtoon
Abaqus2MatlabFull description
Tutorial to use Easy68K simulatorFull description
sap abap bapi
How to use ADX
Choice Words, Phrases, Sentences, And Paragraphs for Every Situation, Revised EditionDescripción completa
talismanDescripción completa
talisman
How to Make and Use Primitive Weapons
talisman
piano practiceFull description
How to understand SAP ST04 information and use it for performance analysis Posted on April 27, 2013
My previous posts give an introduction on SAP database performance monitor and talk about how to run SAP transaction ST04 and navigate to frequent accessed ST04 screens This post would choose several frequent referenced ST04 screens and talk about ! "ow to under understan stand d the infor informatio mation n in ST04 ST04 screens screens # "ow to use ST04 ST04 informatio information n from those those screens screens to do perform performance ance tuning tuning This post is based based on e$periences e$periences on the SAP system % am working with with &efore % go down to the details' % would like to highlight that it is important to get yourself familiar with the normal status of database performance and establish database performance baselines which can be used for comparison for future performance issue %n general' database performance can be improved via application(S)* code tuning' database itself tuning and +S,level tuning +ften' system and application performance can be improved via application(S)* code tuning Poor database performance is normally an outcome of inappropriate application(S)* design other than database con-guration and Server setting
1!2 %nderstand t"e information in ST04 database performance oeriew screen
Table 1 &'planation of ST04 database oeriew screen
%t is a memory area to store database data(blocks so future access on the same data can be ful-lled from memory Accessing data in memory is much faster than accessing data in the diskoal of data bu/er is to reduce physical %(+ as much as possible 1ield Si(e displays con-gured memory si2e )uality indicates how often would a requested data is found in memory without disk access )uality 3 number of memory reads(reads $ !00 )uality is also known as hit ratio
.ata bu/er
*eads displays the sum of number of disk access and memory access since start of database server P"ysical reads+ Total number of disk access u-er usy wait 5 Total number of bu/er busy wait This wait happens when a session wants to access a database block in memory but it cannot because the bu/er is busy This is related to concurrent accessing on the same database table
%t is a memory area to store e$ecutable S)* version of S)* codes and .atabase ob6ectsoal of Shared Pool is to promote reusing of parsed S)* and avoid disc access for database ob6ects
Shared Pool *og bu/er
../ac"e )uality indicates how often would a requested data ob6ect is found in cache The .ata .ictionary cache stores information on ob6ects in the +racle database' such as their names' de-nition and access to them S) Area et*atio and Pin*atio %ndicates how often parsed S)* statements are found in S)* Area of the shared pool The Shared S)* Area 7also called as Shared 8ursor 8ache9 stores the parsed S)* statements 7parsing means processing S)* statements to derive an access path to the data within the database9
%t is a circular memory area to store every database change before they are written to log -les :very database change would create one ;redo< entry in log bu/er8hanges in log bu/er can be written to a redo log -le before the corresponding transaction is committed %f the redo log bu/er -lls' or another transaction commits' all entries in the log bu/er would be written to a redo log -le by +racle *=> process' even though some redo records may not be committed %f necessary' the database can roll back these changes
The log bu/er is ?ushed to persistent storage based on +racle design5 o every @ seconds o every commit o when !(@ full o when ! meg full Si(e -eld shows memory si2e for the log bu/er &ntries shows total number of redo entry since database was started
Allocation *etries Allocation fault rate show the number of failed attempts to allocate free space in the redo log bu/er and the ratio *edo o$ wait o$ les display wait situation and number of log -les used for recovery log -le sync waits are a wait on lgwr process to complete a write to disk
.isplay important statistics on database operation !
8all
alls displays the total number of user calls in the database since it was started ommits displays the total number of committed transactions since the database was started *ollbac5s displays the number of rolled back transactions since the database was started *ecursie calls displays the number of recursive calls *ecursie calls6user calls display total number of recursive calls and user calls SAP mentions total number of recursive calls should be smaller than the number of user calls by a factor of in a productive environment Parses displays the total number of parsed S)* statements SAP mentions the rate of Parses to Bser 8alls should be under # *eads 6 %ser calls displays the number of +racle blocks read on average from the data bu/er to answer user queries %f this number is greater than @0' this points to ;e$pensive< S)* statements
.isplay summary of database wait situation =ait itself would not consume resources but a result of resource contention =ait situation should be reviewed and mitigated whenever possible
Time Statistic s >edo *ogging Table scans C 1etches
usy wait time shows the cumulative time that is used up' because the database system had to wait for a resource that was not available at the time of the request %f this value is high' you must perform a detailed analysis via +racle wait event analysis P% time+ total 8PB time used since database was started P% usa$e+ average 8PB usage since database was started
%nformation here is related to redo log output performance .isplay summary of sequential read as well as total number related to chained row(continued row or read via inde$1rom performance point view' we need to avoid sequential read on long tables S"ort tables -eld displays the total number of sequential read operations on small tables 7 tables with less than +racle data blocks 9 since database was started . on$ tables -eld displays the number of sequential read operations on large tables 7 tables with or more +racle data blocks 9 since database was started
Fetch By rowid
-elds displays total number of table rows chosen by inde$ or by a unique %. 7row %.' >+=%.9 in the S)* statement since database was started ontinued *ow displays the number of chained >ows fetched since database was started .
.isplays the total number of sort operations performed in the main memory and on the disk . Sorts section displays the total number of sort operations performed in the main memory and on the disk emory displays the number of sorts in the main memory These sorting processes are normally faster than sorting processes on the disk .is5 displays the number of sorting processes temporarily written to the tablespace PSAPT:MP The
Sorts
u-er busy wait+ Dou can refer to +racle online document for more information on bu/er
busy wait %t mentions three main causes for bu/er busy wait5 accessing a data block which is under changesE insert into the same block at the same timeE concurrent reads on the same physical block *ecursie call+ following is e$cerpt from +racle document on >ecursive calls Sometimes, to execute a SQL statement issued by a user, the Oracle Server must issue additional statements. Such statements are called recursive calls or recursive SQL statements. For example, if you insert a row into a table that does not have enough space to hold that row, the Oracle Server makes recursive calls to allocate the space dynamically if dictionary managed table spaces are being used. ecursive calls are also generated!
"hen data dictionary information is not available in the data dictionary cache and must be retrieved from disk
#n the $ring of database triggers
#n the execution of %%L statements
#n the execution of SQL statements within stored procedures, functions, packages and anonymous &L'SQL blocks
#n the enforcement of referential integrity constraints
%n a standard SAP environment' >ecursive calls if there is any are transparent to SAP developers in my view
%nformation from ST04 performance overview screen cannot lead to a conclusion or solution and is servicing a hint(direction for further investigation in most cases
%t is generally recommended that bu/er quality(hit ratio should be greater than F To improve the hit ratio' you need to analy2e S)* cache to tune e$pensive S)*s and(or increase data bu/er"owever high ratio could be in?ated by a very frequent e$ecuted S)* which get data from bu/er 1or e$ample' .atabase has @ tables which is the same si2e but bu/er can hold one table at one time %f one table is accessed !00 times' the remaining two tables are accessed ! time' the hit ratio might be much higher than each table is accessed @4 times %f the number of wait situations is over of reads' you must perform an e$tensive analysis of the databaseE wait situations may also occur due to a data bu/er that is too small so if catch hit rate is low at the same time' you might need to increase the data bu/er -rst
Shared Pool
.ictionary cache quality above FG in a production system The value S)* Area get(pinratio should be above FF in a production system%f not' then you should consider to increase Shared Pool si2e
*og bu/er
%f number of failed attempts is higher' you need to review the *+H&B11:>H=A%T event further Possible mitigate action is to increase redo log bu/er and(or tune %+ system
8alls
Sap mentions that the number of recursive calls should be smaller than the number of user calls by a factor of in a productive environment The rate of Parses to Bser 8alls should be under #The value >eads ( Bser calls displays the number of +racle blocks read on average from the data bu/er to answer user queries %f this number is greater than @0' this points to ; e$pensive< S)* statementsDou need to analy2e S)* cache to tune e$pensive S)*
Time Statistic s >edo *ogging
&usy wait time shows the cumulative time that is used up' because the database system had to wait for a resource that was not available at the time of the request %f this value is high' you must perform a detailed analysis using wait event analysis and 8PB load analysis on database server 8PB usage #0 or @0 itself is not high "owever usage from #0 to @0 represent a 0 changes' this could be signi-cant .etail investigation is needed to see what leads to this ;dramatic< change and possible impact This area might be related to log output performance =hether there is a performance concern' we need to refer to database 7+racle9 wait event analysis
Table scans C 1etches
Sort
%f fetches by continued row is high' then you need to analy2e inde$(tables fragmentation situation and consider inde$(table reorgani2ation 1or short table' sequential read might be better than inde$ read 1or long table' reading via inde$ read is generally better than sequential read %f huge number of records 7like #09 is needed' then sequential read might be better bet from performance point view 1urther analysis on sequential read needed to be done via S)* cache analysis %deally' sort should be in memory from performance point view SAP mentions that the ratio of disk to main memory should be under %f this is not the case' increase the +racle parameter sortHareaHsi2e
Dou can reference to SAP document for some threshold values mentioned in table # Dou can use reset point to see how statistics are changed after reset in comparison with information since database was started 2 SAP ST04 8 S) ac"e onitor screen 2!1 SAP ST04 8 S) cac"e monitor screen
#i$ure 2 ST04 S) cac"e monitor screen part 1
#i$ure 3 ST04 S) cac"e monitor screen part 2
#i$ure 4 ST04 S) e'ecution plan
1ollowing is sample of S)* analysis report
#i$ure 9 ST04 S) analysis report
2!2 %nderstand t"e information in ST04 S) cac"e monitor screens
All information in ST04 S)* cache monitor screen is a snapshot taken at the time of running S)* cache monitor since database was started or latest reset
Table 3 &'planation of ST04 S) cac"e monitor screen
#ield
:$ecutio ns
&'planation
Total number of times which a S)* statement is e$ecuted
.isk reads
Iumber of disc blocks reads
>eads(:$ ec
Average number of disc reads per S)* e$ecution
&u/er gets
Total number of bu/er read
#ield
&'planation
:lapsed Time
Accumulated runtime for all e$ecution of a S)*
:lapse Time(:$ec
Average run time per S)* e$ecution
8PB Time
Total 8PB time consumed for all e$ecution of a S)*
8PB time(:$ecuti on
Average 8PB time per S)* e$ecution
&gets(:$ ec
Proc >ows >proc(:$ ec
Average number of bu/er read per S)* e$ecution
Total number of rows processed(returned Average number of rows per S)* e$ecution
=ait time
Total wait time occurs during the S)* e$ecution
S)* statement
S)* statement in the cache S)* statement in lower case is related to system operation
Program name
Program name who issues a S)* statement
There are more screen -elds than what listed in table @ like -elds related to memory usage etc
1igure 4 S)* e$ecution plan screen tells you the access strategy used by database to ful-ll the S)* statement 1ollowing are corresponding -eld e$planation in 1igure 4
Table 4 &'planation of ST04 S) e'ecution plan screen
#ield
:stimate d 8osts
:stimate d >ows
&'planation
This number is an estimate of the number of disk %(+s and the amount of 8PB and memory used in e$ecuting the S)* "igher the number is' more e$pensive the access is
Pro6ection on number of rows returned by the S)*
Sequence The sequence of operation number chosen by oracle for the !' # etc S)*
#ield
&'planation
Search 8olumn
Iumber of -eld used in accessing inde$
Access Predicat es
A list of -elds used in accessing inde$
1ilter Predicat es
A list of -elds used in -lter records
+racle 8&+ always choose a plan with lowest cost based on calculation "owever the cost calculation can be in?uenced by many factors +ne of factors is table statistics so obsoleted table statistics can lead to unreliable cost which leads to improper S)* e$ecution plan
% have not mentioned column selectivity and inde$ cluster factor screen which can be brought up if you double click on the inde$ in e$ecution plan Dou might need to review this information
Dou can refer to table @ to understand the information in S)* analysis report screen 7-gure 9%n some cases' load distribution can help to understand the performance change of a transaction or business application
Most of database performance issues are related to application design 7code and table(inde$ design9' which needs application owner to -$ it %f issue cannot be -$ed via code(application design change' then it might need database level changes like statistics updating and table(inde$ reorgani2ation which is normally owned by &asis(database guy %t seldom happens that we change a database con-guration design to -$ a performance issue .atabase con-guration changes can include %+ design like table distribution' data striping etc to mitigate hotspot for concurrent accesses ST0J can be used to identify hot disc based on utili2ation SAP ST04 cache screen contains a lot of statistics data like number of e$ecution' disc read etc as you have already seen Sorting on those statistics in descending' most e$pensive S)* statement would be at top of the list so you can review those top S)* statements to identify tuning opportunity Tuning e$pensive S)* code can improve underlying application performance' sometimes' tuning e$pensive S)* can have big improvement on overall database performance if total physical read and logical reads is reduced 1or e$ample' reducing physical read can reduce demand on bu/er so database would needs less bu/er space to e$ecute a S)* statement' this means less e$isting data is moved out of bu/er
2!3!1 S) AH& A:A;S
S)* cache analysis is a critical performance tuning area for database and application performance 1ollowing is a list of performance analysis based on S)* cache review
1ocus on top o/enders based on ;disc read< and disc ;*eads6e'ec;+b6ective is
.isc %+ can be reduced via appropriate inde$' reducing S)* e$ecution' using alternative table'
Tune S)* memory read
Tune S)* respons e time
Tune S)* 8PB time
Tune S)* >ow process ed
to reduce S)* disc read
changing S)* where, clause' or changing table(inde$ etc 8ombining similar 6obs can help reduce .isc %+ read
1ocus on top o/enders based on ;bu-er $ets < and ;$ets6&'ec< is to reduce bu/er read
Similar to disc read 8ombining similar 6ob steps would help more to reduce bu/er read
1ocus on top o/enders based on ;&lapsed time< and ;&lapsed time6&'ec;+b6ective is to make S)* run faster when possible
>esponse time can be reduced via appropriate inde$' reducing S)* e$ecution' using alternative table' changing S)* where, clause' changing table(inde$ etc Moving operation like ;sort< to application server and using parallel solution etc can help as well if applicable
1ocus on top o/enders based on ;P% time< and ;P% time6&'ec;+b6ective is to reduce 8PB load especially when contention
8PB time can be reduced via appropriate inde$' reducing S)* e$ecution' using alternative table or changing S)* where, clause etc moving operation like ;sort< to application server can help as well
1ocus on top o/enders based on ;Proc! *ows; and;*proc6&'ec ; +b6ective is to reduce rows traKc between database server and application server
>ow processed can be reduced via reducing S)* e$ecution' consolidating similar S)* statements' using alternative table' do aggregation operation on database server side and adding more -lters to the selection etc
Approach in table mentioned is mainly from application solution point view There might be database level changes which can be done to improve performance like inde$(table reorgani2ation' table statistics updating7including histogram9 and inde$(table sorting' table(inde$ compression' other oracle memory(storage parameter changes' disc stripping etc which might be needed based on storage analysis and wait event analysis
There are overlapped among di/erent categories 1or e$ample' one S)* can show up as Top e$pensive S)* under response time and Top e$pensive S)* under .isc %+ as well "ere' % mentioned to combine similar 6obs and(or 6ob steps to reduce disc %+' bu/er gets "owever there are other factors we need to consider before we consolidate 6obs or 6ob steps 1or e$ample' memory utili2ation' business performance goal as well as relation between program performance and volume S)* tuning is focusing on ;select< S)* statement other than ;insert< statement in most cases There is really no way for you to reduce number of rows and number of insertion or control which table should be inserted into in a SAP system &ut for ;select< statement' this is tricky since the same information usually shows up in di/erent SAP tables' many records can meet one selection criteria as well as S)* where,clause design can in?uence oracleLs table access strategy =hen there is a database performance issue' you can use reset point in S)* cache monitor to see what is most e$pensive S)* for a problematic period after reset' you might -nd this helpful sometimes
2!3!2 S) &=&%T<>: PA: *&?<&@
S)* is either getting data from base table or from the inde$ table =hen it read data from table' it either access table via inde$ or without inde$ =hen you analy2e S)* e$ecution plan' please pay attention to following points5
1or small tables or occasional access' full table is better than inde$ read %nde$ is limited resources and has additional cost
8heck inde$ used against with what is speci-ed in S)* where,clause5 %f no inde$ -eld is referred in S)* where,clause' then it is not correct for the system to use the inde$ to read the table %n this case' a ;1ull< table scan is more eKcient than inde$ read Dou also need to check whether there is a better inde$ for the S)* statement this can happens when -elds in where,clause show up in several inde$es of a table %f +racle chooses a wrong inde$ for a S)* e$ecution' you can correct this via statistics updating or +racle hints +racle hints needs to change related program code
8heck to see whether all available -elds from where,clause are used to access inde$
8heck whether -elds from where,clause are correctly used to -lter data read from table
&ased on my e$perience with my system' most often seen 6oint operation is ;nested, loop< % did see ;hash 6oint< %n my N yearLs performance work' % encountered ! or # cases where 6oint operation was the concern %f you would like to know more on 6oint operation' please refer to +racle document Bnderstanding Ooins ST04 S)* cache analysis would not tell you about duplicated selection To get such information' you need to run ST0(ST!# trace on e$ecution of related program Then you can analy2e the S)* trace %f you wonder how to do that' you can refer to my post SAP ST!# trace S)* performance analysis % did encounter performance issue due to -elds in ;Search 8olumn< Those error are +racle 8&+ error could be due to +racle bug or table(system statistics status The solution to -$ this is normally statistics updating or +racle patches or solution change %t is important to review table cluster factor and inde$ column selectivity as well Dou can -nd such information by double clicking inde$ name in S)* e$ecution plan screen *ower selectivity and high cluster factor can be a reason for poor performance which can be addressed via table(inde$(program changes7 the former 9 and table(inde$ reorgani2ation7 the latter 9
S)* analysis report is used to identify load changes over time
3 SAP ST04 .atabase session monitor
Dou can use ST04 session monitor to understand current activities of +racle session This helps to understand the performance of system and applications
3!1 SAP ST04 database session monitor screen
#i$ure C ST04 database session monitor screen
3!2 %nderstand information in SAP ST04 session monitor screen
1ollowing are some of -eld(column e$planation in ST04 session monitor
Table C &'planation of ST04 session monitor screen
#ield
&'planation
#ield
&'planation
S%.
+racle Session %.
*ogical >eads
Total number of physical read and memory read occurs under the session
+p Sys P%.
+perating system %. related to the +racle session
Physical >eads
Physical reads
8lient system
>elated system where client process is running
&lock 8hange
Iumber of block changed by the session
8lient Proc
8orresponding client process for the +racle session +ne 8lient process can create more than one oracle sessions
PAHBS:.HM: M
8urrent used PA memory by the oracle session
PAHMAHM:M
Ma$ memory used by the oracle session
:vent
:vent where current session is waiting for
Bser
>elated SAP user,id which is related to +racle session
>efresh oracle session monitor to see whether any number under logical read' physical read' block changes and consistent changes -elds are changed %f no number changes between two refresh' then the oracle session is stuck such as corresponding business 6ob or processSo this information can be used to answer whether a 6ob or program is stuck when SM0 show ;database operation< action as well how e$pensive the S)* is
Dou can check corresponding S)* e$ecution plan for an ;active< S)* to understand why a process in 8heck S)* SM0 spends long time on ;sequential read< or e$ecution plan ;direct read< Dou can compare S)* source code and with S)* code in the cache' this might help you to corresponding understand whether a performance issue can be code addressed by e$ecuting S)* using di/erent data
Memory utili2ation
Dou can sort the PAHBS:.HM:M or PAHMAHM:M: to see which +racle session is using most of memory when needs arise but % have not used this so far in my work
Dou can sort on the event column to see whether many processes are stuck on the same event and their corresponding 6obs(processes This can be :vent analysis used to 6ob scheduling or user training
4 SAP ST04 .atabase loc5 monitor 4!1 SAP ST04 database loc5 monitor screen
1ollowing is a truncated ST04 lock monitor screen to help you to know what the screen looks like
#i$ure 7 ST04 oc5 onitor
4!2 %nderstand information in ST04 database loc5 monitor screen
Session %. and client id has been covered in session monitor The lock type(>equest mode and hold mode is standard +racle term =hat we need to pay attention to normally is ;status< column(-eld The row with ;"+*.< value in Status column is the process which has placed locking while other process which has ;H=A%T< value in ;status< column are waiting for
.atabase performance monitor can be used to identify current lock contention SAP transaction .&0! can be used to monitor database lock as well &ut ST04 lock monitor screens would show root lock directly %f process A is hold a lock which process & is waiting for At the same time' & process is holding a lock which process 8 is waiting for .&0! and ST04 lock monitor would report lock contention like5
Table D .isplay di-erence between SAP ST04 loc5 monitor and .01
.&0! display Parent
St04 database lock monitor 8hild
A
Parent
8hild
A &
&
& 8
8
=hen many processes are locking each other and you would like to -gure out which one is root lockerQ SAP ST04 lock monitor can provide a straightforward answer
A=> report allows you to compare oracle workload between two periods this might be helpful if there is an issue with database and you would like to -gure out what causes the performance di/erence A..M report would generate database tuning proposal
Those are standard reports Dou can refer to P.1 document from +racle company website to understand how to use those reports for performance analysis C #urt"er clarication
%n addition to this post' % have other two posts related to database performance monitor SAP database performance monitor introduction and how to run SAP database performance monitor ST04 My focus is mainly on how to tune S)* from application performance point view and general understanding database performance monitor %t is not my intention or within my abilities to cover all areas of database tuning There is a ton of documents on database tuning already =ait event analysis is critical for database performance tuning' you can refer to online +racle documentAutomatic Performance Statistics for further reading Dou can go to click here to understand more in details on database performance tuning
This entry was posted in SAP transaction and tagged How to understand SAP ST04 screensEHow to use SAP st04 for performance improementE "ow to use SAP ST04 for
performance improementE SAP database S) cac"e performance analysisE SAP ST04 database sess by eric.
