How do you come to know that currently the host is taking more cpu cycles
to do the same amount of work?
On Wed, Sep 29, 2021 at 11:47 PM Pap <oracle.developer35@xxxxxxxxx> wrote:
Thank you. When I check the AWR for both periods it shows the same
NUM_CPUs and cores, sockets. No difference in that. I am not sure how I can
check the resource manager limits or if somebody played with the actual cpu
turned on on this exadata box, but in that case won't it be impacting all
the hosts/compute nodes in that exadata machine? Currently It's just one
of the host/compute nodes(and its related database) which is showing the
cpu spike issue but other compute nodes in this full rack are not showing
such symptoms.
One thing i checked, as picked few sys stats from dba_hist_sysstat view
to see the trend for say like 'cpu used by this session', 'cpu used when
call started', 'physical read total bytes optimized', ' logical read bytes
from cache' etc. It's clearly showing a spike on the first two stats and
the significant drop in later two statistics. So it is mostly pointing
towards the fact that it's now taking more cpu cycles to do the same amount
of work which it used to do with less cpu cycles in the past. So something
is wrong with one of these host/compute nodes. This host is 64 cpus(32
core, 2socket), linux X-86, 64bit machine with ~251Gb memory.
Is there any way I can fetch some past performance stats for this host ,
which will show if there are any hardware issues in the host itself?
On Wed, Sep 29, 2021 at 9:24 PM Clay Jackson (cjackson) <
Clay.Jackson@xxxxxxxxx> wrote:
First things I’d look for would be someone fiddling with resource manager
(CPU limits) and/or the number of CPUs actually “turned on” in the Exadata.
Clay Jackson
*From:* oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> *On
Behalf Of *Pap
*Sent:* Wednesday, September 29, 2021 8:13 AM
*To:* Lok P <loknath.73@xxxxxxxxx>
*Cc:* Oracle L <oracle-l@xxxxxxxxxxxxx>
*Subject:* Re: High CPU on Host
*CAUTION:* This email originated from outside of the organization. Do
not follow guidance, click links, or open attachments unless you recognize
the sender and know the content is safe.
I checked that it has been increased for all schemas/users. Even i see
the "insert into table... values() " type of queries which were loading
data in batches are having an increase in run duration(~10times) and its
mainly CPU time observed. What must be the reason?
On Wed, Sep 29, 2021 at 7:26 PM Lok P <loknath.73@xxxxxxxxx> wrote:
From dba_hist_sqlstat you can fetch the sum(cpu_time_delta) order by
parsing_schema_name to see if any specific user is standing out with
respect to the overall cpu time consumption for good day vs bad day. If
it's equally increased for all the users then mostly the issue is outside
the database or perhaps need to see host related things then.
On Wed, Sep 29, 2021 at 6:39 PM Pap <oracle.developer35@xxxxxxxxx> wrote:
Hello Listers, This is an exadata(X3 full rack) machine. One of
the hosts/compute nodes hosting two database nodes with version 19.9.0.0.0
and 11.2.0.4 respectively. We are encountering a situation in which
suddenly the query execution time has been increased by almost ~50% for
many queries in this database without any change in plan and change in data
volume. Checking the host cpu utilization trend, it's almost doubled from
around that exact time when queries ran longer. The waits for those queries
are all DB CPU. Not sure what the cause and what is the effect here. If
something wrong on the host/hardware is impacting queries or something
wrong with the database/queries itself(but then it looks odd that many
queries are running longer which were running fine since the past).
We checked with the infra team regarding any change that went in for that
host but got an answer as NO. And we didn't see any new
queries/functionalities introduced which might cause this sudden spike in
CPU. So I wanted to understand from experts, how can we get to the
bottom of this issue in such a situation?
