10g
Is there a way/view to find out from WITHIN Oracle (i.e. No access to
OS)
How many CPUs on the server ?

thx

klabu wrote:
> 10g
> Is there a way/view to find out from WITHIN Oracle (i.e. No access to
> OS)
> How many CPUs on the server ?
>
> thx

V$OSSTAT:

SELECT
MAX(DECODE(STAT_NAME,'NUM_CPUS',VALUE,1)) CPUS,
MAX(DECODE(STAT_NAME,'NUM_CPU_CORES',VALUE,1)) CPU_CORES,
MAX(DECODE(STAT_NAME,'BUSY_TIME',VALUE,0)) BUSY_TIME,
MAX(DECODE(STAT_NAME,'IDLE_TIME',VALUE,0)) IDLE_TIME,

(MAX(DECODE(STAT_NAME,'BUSY_TIME',VALUE,0))/MAX(DECODE(STAT_NAME,'NUM_CPUS',VALUE,1)))/24/
3600/100 BUSY_DAYS,

(MAX(DECODE(STAT_NAME,'IDLE_TIME',VALUE,0))/MAX(DECODE(STAT_NAME,'NUM_CPUS',VALUE,1)))/24/
3600/100 IDLE_DAYS
FROM
V$OSSTAT;

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.

Charles Hooper wrote:
> klabu wrote:
>> 10g
>> Is there a way/view to find out from WITHIN Oracle (i.e. No access to
>> OS)
>> How many CPUs on the server ?
>>
>> thx
>
> V$OSSTAT:
>
> SELECT
> MAX(DECODE(STAT_NAME,'NUM_CPUS',VALUE,1)) CPUS,
> MAX(DECODE(STAT_NAME,'NUM_CPU_CORES',VALUE,1)) CPU_CORES,
> MAX(DECODE(STAT_NAME,'BUSY_TIME',VALUE,0)) BUSY_TIME,
> MAX(DECODE(STAT_NAME,'IDLE_TIME',VALUE,0)) IDLE_TIME,
>
> (MAX(DECODE(STAT_NAME,'BUSY_TIME',VALUE,0))/MAX(DECODE(STAT_NAME,'NUM_CPUS',VALUE,1)))/24/
> 3600/100 BUSY_DAYS,
>
> (MAX(DECODE(STAT_NAME,'IDLE_TIME',VALUE,0))/MAX(DECODE(STAT_NAME,'NUM_CPUS',VALUE,1)))/24/
> 3600/100 IDLE_DAYS
> FROM
> V$OSSTAT;
>
> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.
>

Wouldn't
SELECT value
FROM gv$osstat
WHERE stat_name = 'NUM_CPUS';

Be a bit more direct?
--
Daniel Morgan
University of Washington
Puget Sound Oracle Users Group

DA Morgan wrote:
> Charles Hooper wrote:
> > klabu wrote:
> >> 10g
> >> Is there a way/view to find out from WITHIN Oracle (i.e. No access to
> >> OS)
> >> How many CPUs on the server ?
> >>
> >> thx
> >
> > V$OSSTAT:
> >
> > SELECT
> > MAX(DECODE(STAT_NAME,'NUM_CPUS',VALUE,1)) CPUS,
> > MAX(DECODE(STAT_NAME,'NUM_CPU_CORES',VALUE,1)) CPU_CORES,
> > MAX(DECODE(STAT_NAME,'BUSY_TIME',VALUE,0)) BUSY_TIME,
> > MAX(DECODE(STAT_NAME,'IDLE_TIME',VALUE,0)) IDLE_TIME,
> >
> > (MAX(DECODE(STAT_NAME,'BUSY_TIME',VALUE,0))/MAX(DECODE(STAT_NAME,'NUM_CPUS',VALUE,1)))/24/
> > 3600/100 BUSY_DAYS,
> >
> > (MAX(DECODE(STAT_NAME,'IDLE_TIME',VALUE,0))/MAX(DECODE(STAT_NAME,'NUM_CPUS',VALUE,1)))/24/
> > 3600/100 IDLE_DAYS
> > FROM
> > V$OSSTAT;
> >
> > Charles Hooper
> > PC Support Specialist
> > K&M Machine-Fabricating, Inc.
> >
>
> Wouldn't
> SELECT value
> FROM gv$osstat
> WHERE stat_name = 'NUM_CPUS';
>
> Be a bit more direct?
> --
> Daniel Morgan
> University of Washington
> Puget Sound Oracle Users Group

There is a reason why I posted the code as I did (aside from already
having written the SQL code). NUM_CPUS returns the number of virtual
CPUs, including in the count hyperthreaded CPUs in addition to the
actual core count. NUM_CPU_CORES returns the number of actual CPU
cores reported by the operating system. The OP did not indicate which
was of interest, so both results will be returned on a single row,
along with a rough calculation of how busy the CPUs have been.

You probably are correct that selecting from GV$OSSTAT is more direct
than selecting from V$OSSTAT. My memory is a bit fuzzy. Isn't there
another layer of views that the database needs to drill through to
reach the data when accessing through V$OSSTAT, rather than GV$OSSTAT?

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.

Charles Hooper wrote:
> V$OSSTAT:

Thanks for the code.
So I get the follwing:

CPUS CPU_CORES BUSY_TIME IDLE_TIME BUSY_DAYS IDLE_DAYS
---------- ---------- ---------- ---------- ---------- ----------
4 1 2453880159 1929529093 71.0034768 55.8312816


I also did this:
SQL> show parameter cpu

NAME VALUE
-------------------------- ------------------------------
cpu_count 4
parallel_threads_per_cpu 2


So does that mean if I ask the DBA or Solaris admin : "How many
(physical) processors on this Oracle server ?"
- answer SHOULD be "ONE" ?

On 29 Sep 2006 10:41:49 -0700, "Charles Hooper"
<hooperc2000@yahoo.com> wrote:

>My memory is a bit fuzzy. Isn't there
>another layer of views that the database needs to drill through to
>reach the data when accessing through V$OSSTAT, rather than GV$OSSTAT?


Apart from that this is a 10g specific answer.
9i and 8i customers need to check the init.ora parameter cpu_count.

--
Sybrand Bakker, Senior Oracle DBA

On 29 Sep 2006 11:35:09 -0700, "klabu" <klabu76@gmail.com> wrote:

>Charles Hooper wrote:
>> V$OSSTAT:
>
>Thanks for the code.
>So I get the follwing:
>
> CPUS CPU_CORES BUSY_TIME IDLE_TIME BUSY_DAYS IDLE_DAYS
>---------- ---------- ---------- ---------- ---------- ----------
> 4 1 2453880159 1929529093 71.0034768 55.8312816
>
>
>I also did this:
>SQL> show parameter cpu
>
>NAME VALUE
>-------------------------- ------------------------------
>cpu_count 4
>parallel_threads_per_cpu 2
>
>
>So does that mean if I ask the DBA or Solaris admin : "How many
>(physical) processors on this Oracle server ?"
>- answer SHOULD be "ONE" ?

Your Oracle Rep will say you have 4 cpu's and charge you accordingly.

--
Sybrand Bakker, Senior Oracle DBA

Sybrand Bakker wrote:
> On 29 Sep 2006 10:41:49 -0700, "Charles Hooper"
> <hooperc2000@yahoo.com> wrote:
>
> >My memory is a bit fuzzy. Isn't there
> >another layer of views that the database needs to drill through to
> >reach the data when accessing through V$OSSTAT, rather than GV$OSSTAT?
>
>
> Apart from that this is a 10g specific answer.
> 9i and 8i customers need to check the init.ora parameter cpu_count.
>
> --
> Sybrand Bakker, Senior Oracle DBA

Thanks for the additional input. So, in 9i and 8i Oracle believes the
DBA, while in 10g Oracle believes the OS? Would it be possible to
determine the number of CPUs in an 8i or 9i database based on some of
the other statistics recorded in the database? For example: 2 CPUs = 2
CPU seconds for every 1 clock second, 4 CPUs = 4 CPU seconds for every
1 clock second.

10g: Would it be more efficient, although more difficult to read, to
select directly from SYS.X$KSUCPUSTAT? This would eliminate a couple
synonym look ups, view definition look ups, and a UNION:
SELECT
MAX(DECODE(KSUCPUSTATNAME,'NUM_CPUS',KSUCPUSTATVAL UE,1)) CPUS,
MAX(DECODE(KSUCPUSTATNAME,'NUM_CPU_CORES',KSUCPUST ATVALUE,1))
CPU_CORES,
MAX(DECODE(KSUCPUSTATNAME,'BUSY_TIME',KSUCPUSTATVA LUE,0)) BUSY_TIME,
MAX(DECODE(KSUCPUSTATNAME,'IDLE_TIME',KSUCPUSTATVA LUE,0)) IDLE_TIME,

(MAX(DECODE(KSUCPUSTATNAME,'BUSY_TIME',KSUCPUSTATV ALUE,0))/MAX(DECODE(KSUCPUSTATNAME,'NUM_CPUS',KSUCPUSTATVAL UE,1)))/24/
3600/100 BUSY_DAYS,

(MAX(DECODE(KSUCPUSTATNAME,'IDLE_TIME',KSUCPUSTATV ALUE,0))/MAX(DECODE(KSUCPUSTATNAME,'NUM_CPUS',KSUCPUSTATVAL UE,1)))/24/
3600/100 IDLE_DAYS
FROM
SYS.X$KSUCPUSTAT;

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.

Comments embedded.
Charles Hooper wrote:
> Sybrand Bakker wrote:
> > On 29 Sep 2006 10:41:49 -0700, "Charles Hooper"
> > <hooperc2000@yahoo.com> wrote:
> >
> > >My memory is a bit fuzzy. Isn't there
> > >another layer of views that the database needs to drill through to
> > >reach the data when accessing through V$OSSTAT, rather than GV$OSSTAT?
> >
> >
> > Apart from that this is a 10g specific answer.
> > 9i and 8i customers need to check the init.ora parameter cpu_count.
> >
> > --
> > Sybrand Bakker, Senior Oracle DBA
>
> Thanks for the additional input. So, in 9i and 8i Oracle believes the
> DBA, while in 10g Oracle believes the OS?

No. Oracle still retrieves this information from the O/S, however in
10g it retrieves more of it as the CPUs are more complex than they were
at the time 8i and 9i were written. The DBA should never set the
CPU_COUNT in the init.ora parameters, and the associated documentation
states this.

> Would it be possible to
> determine the number of CPUs in an 8i or 9i database based on some of
> the other statistics recorded in the database? For example: 2 CPUs = 2
> CPU seconds for every 1 clock second, 4 CPUs = 4 CPU seconds for every
> 1 clock second.

That isn't necessary as Oracle does query the O/S for the cpu count,
even in 8i.

>
> 10g: Would it be more efficient, although more difficult to read, to
> select directly from SYS.X$KSUCPUSTAT? This would eliminate a couple
> synonym look ups, view definition look ups, and a UNION:
> SELECT
> MAX(DECODE(KSUCPUSTATNAME,'NUM_CPUS',KSUCPUSTATVAL UE,1)) CPUS,
> MAX(DECODE(KSUCPUSTATNAME,'NUM_CPU_CORES',KSUCPUST ATVALUE,1))
> CPU_CORES,
> MAX(DECODE(KSUCPUSTATNAME,'BUSY_TIME',KSUCPUSTATVA LUE,0)) BUSY_TIME,
> MAX(DECODE(KSUCPUSTATNAME,'IDLE_TIME',KSUCPUSTATVA LUE,0)) IDLE_TIME,
>
> (MAX(DECODE(KSUCPUSTATNAME,'BUSY_TIME',KSUCPUSTATV ALUE,0))/MAX(DECODE(KSUCPUSTATNAME,'NUM_CPUS',KSUCPUSTATVAL UE,1)))/24/
> 3600/100 BUSY_DAYS,
>
> (MAX(DECODE(KSUCPUSTATNAME,'IDLE_TIME',KSUCPUSTATV ALUE,0))/MAX(DECODE(KSUCPUSTATNAME,'NUM_CPUS',KSUCPUSTATVAL UE,1)))/24/
> 3600/100 IDLE_DAYS
> FROM
> SYS.X$KSUCPUSTAT;
>

I believe Oracle would prefer you use the views supplied for that
purpose.

> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.


David Fitzjarrell

fitzjarrell@cox.net wrote:
> Comments embedded.
> Charles Hooper wrote:
> > Sybrand Bakker wrote:
> > > On 29 Sep 2006 10:41:49 -0700, "Charles Hooper"
> > > <hooperc2000@yahoo.com> wrote:
> > >
> > > >My memory is a bit fuzzy. Isn't there
> > > >another layer of views that the database needs to drill through to
> > > >reach the data when accessing through V$OSSTAT, rather than GV$OSSTAT?
> > >
> > >
> > > Apart from that this is a 10g specific answer.
> > > 9i and 8i customers need to check the init.ora parameter cpu_count.
> > >
> > > --
> > > Sybrand Bakker, Senior Oracle DBA
> >
> > Thanks for the additional input. So, in 9i and 8i Oracle believes the
> > DBA, while in 10g Oracle believes the OS?
>
> No. Oracle still retrieves this information from the O/S, however in
> 10g it retrieves more of it as the CPUs are more complex than they were
> at the time 8i and 9i were written. The DBA should never set the
> CPU_COUNT in the init.ora parameters, and the associated documentation
> states this.
>
> > Would it be possible to
> > determine the number of CPUs in an 8i or 9i database based on some of
> > the other statistics recorded in the database? For example: 2 CPUs = 2
> > CPU seconds for every 1 clock second, 4 CPUs = 4 CPU seconds for every
> > 1 clock second.
>
> That isn't necessary as Oracle does query the O/S for the cpu count,
> even in 8i.
>
> >
> > 10g: Would it be more efficient, although more difficult to read, to
> > select directly from SYS.X$KSUCPUSTAT? This would eliminate a couple
> > synonym look ups, view definition look ups, and a UNION:
> > SELECT
> > MAX(DECODE(KSUCPUSTATNAME,'NUM_CPUS',KSUCPUSTATVAL UE,1)) CPUS,
> > MAX(DECODE(KSUCPUSTATNAME,'NUM_CPU_CORES',KSUCPUST ATVALUE,1))
> > CPU_CORES,
> > MAX(DECODE(KSUCPUSTATNAME,'BUSY_TIME',KSUCPUSTATVA LUE,0)) BUSY_TIME,
> > MAX(DECODE(KSUCPUSTATNAME,'IDLE_TIME',KSUCPUSTATVA LUE,0)) IDLE_TIME,
> >
> > (MAX(DECODE(KSUCPUSTATNAME,'BUSY_TIME',KSUCPUSTATV ALUE,0))/MAX(DECODE(KSUCPUSTATNAME,'NUM_CPUS',KSUCPUSTATVAL UE,1)))/24/
> > 3600/100 BUSY_DAYS,
> >
> > (MAX(DECODE(KSUCPUSTATNAME,'IDLE_TIME',KSUCPUSTATV ALUE,0))/MAX(DECODE(KSUCPUSTATNAME,'NUM_CPUS',KSUCPUSTATVAL UE,1)))/24/
> > 3600/100 IDLE_DAYS
> > FROM
> > SYS.X$KSUCPUSTAT;
> >
>
> I believe Oracle would prefer you use the views supplied for that
> purpose.
>

For a good time, search "CPU_COUNT WRONG" on the bug database.

jg
--
@home.com is bogus.
"Dear Jack: I am so glad you warned me about contacting heads of
state as I was just about to write to Castro." - Rose Kennedy
(November, 1962)