Oracle Resource Manager Is Not Allocating Cpu to Query
Our Application Is Executing Many Queries in the Same Session but Somehow It Is Always Getting Stuck at One Particular Query from Today(Previously It Was...
Oracle Resource Manager is not allocating CPU to Query. Our application is executing many queries in the same session but somehow it is always getting stuck at one particular query from today(Previously it was running fine from more than 3 years) and on checking with DBA it is found that it is waiting for event resmgr:cpu quantum
Although when I ran the same query from my SQL developer it is running without any issue. I have checked there are no locks and load on CPU is also minimal but I believe it doesn't matter as query is not even getting CPU to execute.
Below are my Oracle Version details
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production,
PL/SQL Release 12.1.0.2.0 - Production , "CORE 12.1.0.2.0 Production"
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production
Can someone please guide what can be the cause that Resource manager is not allocating CPU for this particular query only. There are no hints mentioned in the query
2 Answers
To effectively monitor and tune the Database Resource Manager, you must design a representative environment. The Database Resource Manager works best in large production environments in which system utilization is high. If a test places insufficient load on the system, measured CPU allocations can be very different from the allocations specified in the active resource plan.
to do this
- Create a job which will perform some statistics.
- Create a plan
- Create Consumer Groups
- Create plan directives
- Validate and submit the plan
- Group switching
- Assign the user 'CRC' of the database a default initial consumer group at connection time
this is because DEFAULT_MAINTENANCE_PLAN was active check these links:
David Miller brings 15 years of experience in global economics, personal finance strategy, and market dynamics. He specializes in turning complex economic trends into actionable insights for everyday readers.
