Here is a quick table comparison of different types of metrics views The first line of the table is the classic wait event and statistic views. The following lines are the metric views. The metric views were introduced in Oracle 10g. Why Metrics are good Metric views compute deltas and rates which hugely simplifying the ability to answer simple questions like “what is the I/O rate on my databases right now?” This question, before 10g, was surprisingly tedious to answer. To answer the question one would have to query v$sysstat for example: Select value from v$sysstat where name=’physical reads’; but…
-
-
If one is seeing waits for enq: TX – row lock contention then there could be a lot of reasons. One distinguishing factor is the lock mode. If the lock mode is exclusive (mode 6) then it’s most likely a classic row lock where two sessions are trying to modify the same row. On the other hand if the lock mode is share (mode 4) it’s typically going to be Primary/Unique Key: inserting a unique key when someone else has already inserted that key but not committed Foreign Key: Inserting a foreign when then parent value has been inserted but not…
-
Oracle has a crazy mix of units of time in various v$ views seconds centi-seconds milliseconds microseconds Some are straight forward such as time_waited_micro, but what unit is “TIME_WAITED” or “WAIT_TIME” in? For example v$session WAIT_TIME – centi SECONDS_IN_WAIT – seconds v$session_wait WAIT_TIME – centi SECONDS_IN_WAIT – seconds v$system_event TIME_WAITED – centi AVERAGE_WAIT – centi TIME_WAITED_MICRO – micro v$system_wait_class TIME_WAITED – centi v$eventmetric TIME_WAITED – centi v$waitclassmetric DBTIME_IN_WAIT – “percentage of the measured wait time that was actually in foregrounds and therefore part of DB time” * TIME_WAITED – centi v$waitclassmetric_history DBTIME_IN_WAIT – “percentage of the measured wait time that…