3

enter image description here

What does "Active Session" mean? I need to analyze database performance by using this graph taken from Oracle Enterprise Manager (OEM), but I don't understand what is "Active Session" and why there are 22 of them of User I/O? Could someone please explain me with simple language this basics: "Active Session" and "User I/O" in OEM? And what is the relationship between them?

Prostak
  • 3,565
  • 7
  • 35
  • 46

1 Answers1

3

Active Sessions are sessions waiting for the database to do something. It is a good way to measure how busy the database is. There may have been a thousand users connected at 2PM but only 12 of them were running something and would even notice if the database was slow. If parallelism is used then a single user may have multiple active sessions.

User I/O is usually a wait on disk operations performed directly for an active session. For example, reading data from disk for a full table scan or an index range scan.

To tune this workload you'll need to drill down and learn a lot more about your environment:

  1. What are you trying to tune and why? (Too many people overlook this question. If you don't have a concrete goal in mind tuning is usually a waste of time.)
  2. What do the users expect to be running at this time? Is this too busy and slowing things down, or is it not busy enough and not using enough resources for a large batch job.
  3. What statements are responsible for the I/O and CPU? That information should be available on the same OEM page, underneath the chart.
  4. Are those statements running efficiently? This is the tough part, and there's no simple checklist to solve this; it takes years of experience.

If I were to take a wild guess (based on the relatively "smooth" chart and the ratio of I/O to CPU) you are running a large parallel statement that is performing a full table scan. If that's the case, and there's a single SQL statement responsible for most of the activity, you should be able to click on it and bring up the SQL Monitoring Report to drill down some more.

When you find out what is slow that may be a separate question.

Jon Heller
  • 34,999
  • 6
  • 74
  • 132
  • Thanks, @Jon! A few questions: 1) Did I understand you correctly and graph shows number of CPU on the side of the graph, not "Active Sessions"? In that case I was mislead by "Active Session" label on that graph which is deceivingly is also vertical to these numbers ... 2) How do I see the number of sessions for those peaks (before I thought 20 was the session number, not CPU)? 3) Does these graph wave represent User I/O wait times or Active session numbers? – Prostak Mar 20 '15 at 19:01
  • Active Sessions is the total height of the chart. It's then broken down into what each active session is waiting for. For example, there may be 12 active sessions, 10 waiting for I/I and 2 waiting for CPU. – Jon Heller Mar 23 '15 at 00:32
  • 22 active sessions seems very small.... to simplify: does one request to website by a user generate one active session to backend? does it mean that database handles only 22 users simultaneously? seems to me very low number... – Prostak Mar 24 '15 at 02:09
  • No, the number of connections or even the number of requests may not be directly related to the number of active sessions. For example, I manage some databases with many thousands of connections but usually only a few active sessions. – Jon Heller Mar 25 '15 at 04:03
  • How does a connection relate to a active session? What is the correlation between them? 1000 connections = 1 active session? – Prostak Mar 26 '15 at 05:34
  • There's no simple correlation between them. An active session is a session that is waiting on something from the database (not including network time to transfer data). For example, if 100 users were connected via SQL*Plus, but just sitting at the prompt doing nothing, there would be 0 active sessions. – Jon Heller Mar 26 '15 at 13:05
  • But every single request to backend is waiting on something from database. Login for a example. If 100 users try to login at the same time, why it doesn't cause 100 active sessions? Because they all would be waiting on something from database therefore by your difinition of active session, it would constituate 100 active sessions? Where am I wrong? – Prostak Mar 27 '15 at 20:35