| how to find out no.of CPUs allocated to a specific Database ? [message #518040] |
Mon, 01 August 2011 00:56  |
kesavansundaram
Messages: 183
Registered: October 2007
Location: MUMBAI
|
Senior Member |
|
|
Dear Sir/Madam,
when we ran SELECT statement against CUBE, we got below wait event: resmgr: cpu quantum.
Further, we checked below 2 parameters :
NAME VALUE
------------------------- --------------------------------------------------
resource_limit TRUE
resource_manager_plan SCHEDULER[0x12B943C]:DEFAULT_MAINTENANCE_PLAN
2 rows selected.
DUKEDMTS03DB02:SYS@DEDW2> select * from V$RSRC_PLAN;
ID NAME IS_TO CPU
---------- -------------------------------- ----- ---
19633108 DEFAULT_MAINTENANCE_PLAN TRUE ON
19633106 ORA$AUTOTASK_SUB_PLAN FALSE ON
19633107 ORA$AUTOTASK_HIGH_SUB_PLAN FALSE ON
select plan,cpu_method,comments,status,mandatory from DBA_RSRC_PLANS
where plan = 'DEFAULT_MAINTENANCE_PLAN';
PLAN CPU_METHOD COMMENTS STATUS MAN
------------------------------ --------------- --------------------------------------------------------------------------- ------ ---
DEFAULT_MAINTENANCE_PLAN EMPHASIS Default plan for maintenance windows that prioritizes SYS_GROUP operations YES
and allocates the remaining 5% to diagnostic operations and 25% to automate
d maintenance operations.
1 row selected.
select plan,type,CPU_P1,CPU_P2,CPU_P3,CPU_P4,CPU_P5,CPU_P6,CPU_P7,CPU_P8,MAX_IDLE_TIME,MAX_IDLE_BLOCKER_TIME,MAX_UTILIZATION_LIMIT from
DBA_RSRC_PLAN_DIRECTIVES where plan = 'DEFAULT_MAINTENANCE_PLAN';
PLAN TYPE CPU_P1 CPU_P2 CPU_P3 CPU_P4 CPU_P5 CPU_P6 CPU_P7 CPU_P8 MAX_IDLE_TIME MAX_IDLE_BLOCKER_TIME
MAX_UTILIZATION_LIMIT
------------------------------ -------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------- ---------------------
---------------------
DEFAULT_MAINTENANCE_PLAN CONSUMER_GROUP 75 0 0 0 0 0 0 0
DEFAULT_MAINTENANCE_PLAN CONSUMER_GROUP 0 70 0 0 0 0 0 0
DEFAULT_MAINTENANCE_PLAN PLAN 0 25 0 0 0 0 0 0
DEFAULT_MAINTENANCE_PLAN CONSUMER_GROUP 0 5 0 0 0 0 0 0
4 rows selected.
It has been found that these sessions did not get enough CPU to process the request.
Please guide me how to find out how many CPU has been allocated to this database ?
> uname -a
Linux dukedmts03db02.corp.cox.com 2.6.18-128.1.16.0.1.el5 #1 SMP Tue Jun 30 16:48:30 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux
Also, guide me how to analyze how much % of CPU being utilized for a specific session ?
Thank you,
Kesavan
[Updated on: Mon, 01 August 2011 00:59] Report message to a moderator |
|
|
|
|
|
| Re: how to find out no.of CPUs allocated to a specific Database ? [message #518047 is a reply to message #518044] |
Mon, 01 August 2011 01:35  |
kesavansundaram
Messages: 183
Registered: October 2007
Location: MUMBAI
|
Senior Member |
|
|
Hi Michel,
Thank you for your prompt response.
Further i checked CPU allocated to my database. It is 2.
NAME VALUE
---------- ----------
cpu_count 2
> cat /proc/cpuinfo | grep processor
processor : 0
processor : 1
processor : 2
processor : 3
processor : 4
processor : 5
processor : 6
processor : 7
processor : 8
processor : 9
processor : 10
processor : 11
processor : 12
processor : 13
processor : 14
processor : 15
I have one clarification further. yesterday when SELECT query was slow, I checked CPU utilization for corresponding SPID from TOP comamnd. it was showing 99.9 %. So it means, that session was using 99.9 % of that 2 CPU ? or 99.9 % of total 16 CPUs ?
do we have any SQL query to link SID to get CPU usage for that session ? or for corresponding SPID ? ( yes, we have TOP command to see the CPU utilization for it )
Thank you for your time and clarification.
Regards,
Kesavan
|
|
|
|
|
|
| Re: how to find out no.of CPUs allocated to a specific Database ? [message #518134 is a reply to message #518040] |
Mon, 01 August 2011 09:38 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
It looks as though you have implemented instance caging, which was the subject of my post last week:
http://www.orafaq.com/forum/mv/msg/173397/517432/0/#msg_517432
My tests have shown that it does seem to work. You have cpu_count set to 2, so that one instance will restrict its use to only 2 CPUs at once. Presumably this was deliberate, in order to leave 14 CPUs free for other work.
Note that while instance caging should restrict the CPU usage of background and server processes, if you are running user processes (such as SQL*Plus) on the server, they will run outside the cage.
I am open to correction, but I believe the resource manager controls CPU usage through a cooperative multi-tasking algorithm layered on top of the operating system's pre-emptive multi-tasking algorithm. This means that Oracle processes will voluntarily relinquish the CPU before being pre-emptively context switched off it.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
