OraFAQ Forum: Server Administration » how to find out no.of CPUs allocated to a specific Database ?

Home » RDBMS Server » Server Administration » how to find out no.of CPUs allocated to a specific Database ? (oracle 11.2.0.1.0 linux 2.6)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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 #518044 is a reply to message #518040]

Mon, 01 August 2011 01:18

Michel Cadot
Messages: 68644
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

Please guide me how to find out how many CPU has been allocated to this database ?

show parameter cpu_count

Quote:

Also, guide me how to analyze how much % of CPU being utilized for a specific session ?

Not directly possible with Oracle and meaningless: the percentage of CPU used by a session changes
every second, so which figure do you want?
Use an OS tool, see How do I Find Out Linux CPU Utilization?

Regards
Michel

[Updated on: Mon, 01 August 2011 01:19]

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

and at o/s level, i checked below command. It seems 16 CPU has been allocated at server level.

> 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

Report message to a moderator

Re: how to find out no.of CPUs allocated to a specific Database ? [message #518052 is a reply to message #518047]

Mon, 01 August 2011 01:58

Michel Cadot
Messages: 68644
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

So it means, that session was using 99.9 % of that 2 CPU ? or 99.9 % of total 16 CPUs ?

The OS command (the same that gives you the 99.9%) will give you the answer.

Quote:

do we have any SQL query to link SID to get CPU usage for that session ?

The statistics "CPU used by this session" will give the cpu used by the session (when it is alive).
Query v$sesstat join to v$statname.

And please, align the column in the result you post.

Regards
Michel

Report message to a moderator

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: 8931
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.

Report message to a moderator

Previous Topic:	OLAP DML error "ORA-35571: The maximum number of load errors has occured." while executing
Next Topic:	Needs bit Info about RAC

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 05:27:33 CDT 2024