oracle-databasedatabase-performanceoracle-enterprise-manager

What does Active Sessions and User I/O mean in Oracle Enterprise Manager?

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?

Solution

  • 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.