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

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.
makk2010Asked:
Who is Participating?
 
it-rexConnect With a Mentor Commented:
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
 
it-rexCommented:
now after setting resource_manager_plan=' '
and this wait event  is gone;
what exactly do you have in statspack?
0
All Courses

From novice to tech pro — start learning today.