Ansicht
Dokumentation
/SDF/DB_TIME_HIST_WITH_OBJ - Function Module DB_TIME_HIST_WITH_OBJ
rdisp/max_wprun_time - Maximum work process run time General Data in Customer MasterThis documentation is copyright by SAP AG.
Functionality
Shows for what DB time is spent
- Wait events
- CPU
- Network (heuristic)
In addition the statement maps the segment statistics to the wait events to show POTENTIAL candidates that may be
responsible for the wait time on the mentioned wait event. The following mapping is done
Event Segment statistic
db file sequential read => physical reads (quite reliable)
db file scattered read => table scans (quite reliable)
buffer busy waits => buffer busy waits (counts waits on buffer busy waits and read by other session)
read by other session => buffer busy waits (counts waits on buffer busy waits and read by other session)
CPU => logical reads (assuming CPU is mainly used for buffer gets)
enq: TX - row lock cont.=> row lock waits (BE CAREFUL; segment statistics count waits not time; objects with few
but long waits will not be shown)
log file sync => db block changes (assuming the objects with many changes have also many commits; BE CAREFUL!)
direct path write => physical writes direct (quite reliable)
direct path read => physical reads direct (quite reliable)
gc buffer busy => gc buffer busy (quite reliable)
Variables
specify here
- a concrete instance id OR
- -1 for the current instance id
- -2 for an aggregation of all instances (instance id shown will be 0)
- -3 for a list of each instance separately
SNAPSHOT,
DAY or
HOUR_OF_DAY
can be specified
For some events (e.g. enqueues) Oracle does not wait longer than a specific time; if this time is over and there is still the neccessity to wait oracle starts a NEW wait; this falsifies the average wait time for this event; by default this monitor therefore calculates the avg=time/(waits-timeouts)
direct path operations have often much lower averages than techically possible
E.g. if on a system a db file sequential read last 20 ms and a direct path read
takes 1 ms it is quite likely that Oracles measurement is wrong and the time
waited for direct path reads is underrepresented. Therefore it is possible
to specify a - personally estimated - minimum value. This value is of course a guess
but better a good guess than a definitely wrong measurement. Specify a guess
in ms to get a better than the default time distribution
If you do not specify anything a minimum avg duration of 1 ms is assumed (optimistic9
here you can enter an average network time/usercall DURATION in microseconds
to consider network time in an overall DB time calculation
The default of 300 microseconds is quite optimistic if there are application
servers not located on the DB machine. The default is a COMPLETELY VIRTUAL number
To get a better, system specific number measure the network time with NIPING
- several times
- at different points in time
- on different application servers
Even then this value is more an estimation than a measurement. Anyway it gives a direction
and is better than not considering network time at all.
can be used to filter specific wait events a client process does not directly suffer from and/or
to exclude weekends
This parameter can be used to speed up the runtime of the statement significantly (especially if the
system is RAC, has a high snapshot retention and/or frequency
Oracle collects per snapshot in the history of the wait events not the deltas but the absolute time.
Therefore for each snapshot and wait event the delta calculation has to be done to the former snapshot
to get the delta time consumed between the two snapshots. This delta calculation is expensive and the number of
rows to be further processed can be immense. Usually only those wait events are of interest responsible
for a high time. Anyway there are many, many wait events that consumed since DB start a few ms and therefore
appear again and again in each delta calculation for each snapshot. Those can be filtered out for all snapshots
for which the absolute time spent since startup does not exceed <MIN_TIME_WAITED_SEC [600]> (by default 10 minutes)
This reduces the number of delta calculations and finally the runtime of this query by factors. The maximum
error per wait event are ONCE
so that it is not visible.
If you don´t feel comfortable do a simple test by running this query twice: once with 0 seconds and once with 3600 seconds.
Compare the charts. You will not see a significant difference or a difference at all.
if an event has in an aggregation interval less impact than this percentage it is counted as OTHER
if an object has less impact than the specified percentage on a segment statistic in an aggregation
intrerval it is not listed. Maximum top 5 Objects are shown. If the output is to long it is cut off.
Default [19900307000000]>
Default [SY-DATUM SY-UZEIT]>
Allows to restrict the selection.
If nothing is specified max to date will be current time.
Example
Notes
Further information
Parameters
AGGREGATE_BYCONSIDER_TIMEOUTS
CONTEXT
DBCON_NAME
DB_TIME_HIST_WITH_OBJ
DOWNLOAD_RANGE
EVENT_HOURLY_LIMIT
EXCLUDE_ADMINISTRATIVE
EXCLUDE_SYSTEM
EXCLUDE_WEEKENDS
FROM_DATE
INSTANCE_NUMBER
IT_MESSAGE
IT_RESULT
MIN_AVG_DP_TIME
MIN_TIME_WAITED_SEC
OBJECT_HOURLY_LIMIT
RTT_MICRO
TO_DATE
Exceptions
Function Group
/SAPLRI_ORACLERFUMSV00 - Advance Return for Tax on Sales/Purchases CPI1466 during Backup
This documentation is copyright by SAP AG.
Length: 8250 Date: 20240328 Time: 131525 sap01-206 ( 96 ms )