?
Solved

how to reduce 'resmgr:waiting in check' wait event in Oracle 9i Database

Posted on 2010-01-11
2
Medium Priority
?
1,268 Views
Last Modified: 2012-05-08
We have in our Oracle 9.2.0.8 Database a user which opens upto 10 sessions at a time
from a certain Application and each session have same SQL script. The scripts finishes
fast but the thing is that the sessions are always running on the DB server and consuming CPU resources.

There are many 'resmgr:waiting in check' active wait events(from this user) on the Database most of the times, eventhough this particular DB user has unlimited
 CPU_PER_SESSION, CPU_PER_CALL and SESSIONS_PER_USER set for his profile.

I changed the resource_manager_plan,
> alter system set resource_manager_plan=' ' ; scope=both;

the waiting event does not appear now but the load is still high on the Database Server.

So how do I change the resource plan to allocate more resources to this user
in order to reduce this wait event.
0
Comment
Question by:makk2010
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
2 Comments
 
LVL 11

Accepted Solution

by:
it-rex earned 2000 total points
ID: 26293729
you have hit a very well know bug of 9i:

Known Issues When Resource Manager Is Activated
===============================================

The Oracle database or a single user session can hang if RESOURCE_MANAGER_PLAN is set to system in the init.ora.  For rediscovery, users can look in V$SESSION_WAIT and 'resmgr:waiting in end wait' should be listed as the top wait event.
The solution is to set RESOURCE_MANAGER_PLAN to the default value or comment it out in the init.ora if
not in use, and restart the database.
"resmgr:waiting in end wait" is a resource manager wait event introduced in 8.1.5.
If a statspack report lists "resmgr:waiting in end wait" or resmgr waiting in end wait2" as a top
wait event, then resource manager is the culprit.  To resolve this, resource_manger_plan needs to
be removed from the init.ora.

makk2010 few of these bugs are

Bug 2932269  Sessions hang on "resmgr:waiting in end wait" while session tries to connect over DBLINK.
Bug:2791258 Database hang when setting RESOURCE_MANAGER_PLAN if EXTPROC or HS is used.
0
 
LVL 11

Expert Comment

by:it-rex
ID: 26293751
now after setting resource_manager_plan=' '
and this wait event  is gone;
what exactly do you have in statspack?
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question