Fixing System Slowness Caused by Excessive Inactive Sessions

Recently, I faced an interesting performance issue in one of our Oracle 10g databases running on IBM AIX. The system had become noticeably slow, and user operations were taking longer than usual.

When I started investigating, I discovered that the database was holding a large number of inactive (idle) sessions — most of them left open by the application layer. These idle sessions were quietly consuming system resources, leading to overall slowness.

---

⚙️ The Immediate Fix

As a temporary measure, I manually killed the inactive sessions to free up resources. This immediately improved system responsiveness, but it was only a short-term solution.

I needed a more permanent fix to ensure that idle sessions wouldn’t keep piling up in the future.

---

🧠 The Long-Term Solution — Profile Configuration

To automate the management of idle sessions, I decided to create a new Oracle profile with an idle time limit of 30 minutes.

🏗️ Step 1: Create a New Profile

CREATE PROFILE IDLE_30MIN LIMIT IDLE_TIME 30;

This profile automatically disconnects any session that remains idle for more than 30 minutes.

---

👤 Step 2: Assign the Profile to Relevant Users

Next, I applied this new profile to the most frequent inactive users (original usernames replaced for privacy):

ALTER USER APPUSER1 PROFILE IDLE_30MIN;
ALTER USER APPUSER2 PROFILE IDLE_30MIN;
ALTER USER APPUSER3 PROFILE IDLE_30MIN;

---

🔍 Step 3: Verify Profile Assignment

To confirm that the new profile was properly assigned and the idle time setting was active:

SELECT username, profile FROM dba_users;
SELECT * FROM dba_profiles WHERE resource_name='IDLE_TIME';

Both queries confirmed that the idle time restriction was correctly applied.

---

🧭 Step 4: Monitor Idle Sessions

I also used the following query to monitor currently inactive sessions and their idle durations:

SELECT sid, serial#, username, status, last_call_et/60 AS idle_minutes
FROM v$session
WHERE status='INACTIVE';

And to make sure Oracle enforces the resource limits:

ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;

---

✅ The Outcome

After applying the new profile and enabling resource limits, idle sessions were automatically disconnected after 30 minutes, and system performance returned to normal.

This simple configuration helped maintain database stability and ensured that inactive sessions would no longer accumulate and degrade performance.

---

🧩 Takeaway

Even in older environments like Oracle 10g on AIX, proactive session management can make a huge difference. Sometimes, all it takes is a well-defined profile policy to keep your system healthy and responsive.