So, if I have a single session running for 2 minutes (120 seconds) and from those 2 minutes only 45 seconds are spend in database calls, the “% Activity” for the session would be 45 / 120 = 0.375 or in percents 0.375 * 100 = 37.5 % of DB time. (sessions with status=ACTIVE in v$session view).Īverage activity of a single session = the ratio of active time to total wall clock time (elapsed time). Understanding database time also involves two main crucial parts that we need to understand first, those are “Active Session” and “Average Active Session (% Activity)”.Īctive session = session currently running (spending time) in a database call. Wait events in Oracle are grouped into wait event classes, there are total of 13 Wait Event Classes (Administrative, Application, Cluster, Commit, Concurrency, Configuration, Idle, Network, Other, Queue, Scheduler, System I/O, User I/O). From this we can conclude that database requests are composed from CPU (service time, performing some work) and wait time (session is waiting for resources). I/O wait event is included into Non-idle wait events, but I’ve put it as separate unit just for clarification. In other words DB Time = CPU time + I/O time + Non-idle wait time The goal for tuning process should be to reduce to minimum CPU time and Wait time so that more transactions can be processed.ĭatabase time is total time spent by user processes either actively working or actively waiting in a database call. ![]() ![]() Some main database performance measurement techniques are dependent on database time, such as Active Session History (known as ASH) or Average Session Activity (% Activity) which we see in the Enterprise Manager charts. ![]() Oracle came up with database time (DB time) definition to measure time spent by all active sessions in the database. Someone might find this information useful to understand the basis for performance tuning. With this post I’ll try to give explanation for database time, elapsed time and active session history. We all know that time is important and every DBA goal should be to reduce to a minimum end-user response time and resources used by each request.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |