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

oracle database-performance oracle-enterprise-manager
Prostak picture Prostak · Mar 19, 2015 · Viewed 18.4k times · Source

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?

Answer

Jon Heller picture Jon Heller · Mar 20, 2015

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.

Related questions

How do NULL values affect performance in a database search?

In our product we have a generic search engine, and trying to optimze the search performance. A lot of the tables used in the queries allow null values. Should we redesign our table to disallow null values for optimization or …

Read More
Sequence cache and performance

I could see the DBA team advises to set the sequence cache to a higher value at the time of performance optimization. To increase the value from 20 to 1000 or 5000.The oracle docs, says the the cache value, Specify how many …

Read More
Why is Oracle ignoring index with ORDER BY?

My intention is to obtain a paginated resultset of customers. I am using this algorithm, from Tom: select * from ( select /*+ FIRST_ROWS(20) */ FIRST_NAME, ROW_NUMBER() over (order by FIRST_NAME) RN from CUSTOMER C ) where RN between 1 and 20 order …

Read More