Problem with some ORACLE.EXE (SHAD) processes

Discussion:

David Ramírez Reyes

2013-09-25 18:07:03 UTC

Hi All,
I have an Oracle 11g R2 over a windows server 2008 (am the first of hating
Windows, but that's what I have...); it has 10 GB of SGA, 500 process limit
-400 processes avg-, db on archive mode.

The server has 16 GB of physical memory and 8 cpu cores.

Now the problem is this:

On the last days, the cpu usage has been growing up about every 12-16
hours, it starts at 8-10 avg, then goes to 28-35, then 65-75, 90-95 and
then 100 pick, once it goes to 100% the db hungs (and the server itself
hungs) and there's no other solution than restart the instance.

All this cpu is used by oracle.exe so, I dowloaded a tool to monitor the
specific processes cpu usage at OS Level (MS Process Explorer) and I notice
that the heavy load of cpu usage is on 6 processes which are the following:

SYSTEM 190 ORACLE.EXE (SHAD)
SYSTEM 25 ORACLE.EXE (SHAD)
SYSTEM 5 ORACLE.EXE (SHAD)
SYSTEM 211 ORACLE.EXE (SHAD)
SYSTEM 167 ORACLE.EXE (SHAD)
SYSTEM 2 ORACLE.EXE (SHAD)

This is something strange because I have 3 similar instances on another
server with exactly the same characteristics and I have no problem, even
cpu usage goes between 10-20%.

Does anybody has an idea of how to know which Oracle processes are related
to these (SHAD) processes?, I even stop the EMC for some time without any
difference.

Thanks in advance.

David Ramírez Reyes
DBA

--
http://www.freelists.org/webpage/oracle-l

David Ramírez Reyes

2013-09-25 18:12:29 UTC

Permalink

By the way, there are no errors on the error log for the DB or listener.

Regards

David Ramírez Reyes
Profesión: Padre de Familia

Post by David RamÃrez Reyes
Hi All,
I have an Oracle 11g R2 over a windows server 2008 (am the first of hating
Windows, but that's what I have...); it has 10 GB of SGA, 500 process limit
-400 processes avg-, db on archive mode.
The server has 16 GB of physical memory and 8 cpu cores.
On the last days, the cpu usage has been growing up about every 12-16
hours, it starts at 8-10 avg, then goes to 28-35, then 65-75, 90-95 and
then 100 pick, once it goes to 100% the db hungs (and the server itself
hungs) and there's no other solution than restart the instance.
All this cpu is used by oracle.exe so, I dowloaded a tool to monitor the
specific processes cpu usage at OS Level (MS Process Explorer) and I notice
SYSTEM 190 ORACLE.EXE (SHAD)
SYSTEM 25 ORACLE.EXE (SHAD)
SYSTEM 5 ORACLE.EXE (SHAD)
SYSTEM 211 ORACLE.EXE (SHAD)
SYSTEM 167 ORACLE.EXE (SHAD)
SYSTEM 2 ORACLE.EXE (SHAD)
This is something strange because I have 3 similar instances on another
server with exactly the same characteristics and I have no problem, even
cpu usage goes between 10-20%.
Does anybody has an idea of how to know which Oracle processes are related
to these (SHAD) processes?, I even stop the EMC for some time without any
difference.
Thanks in advance.
David Ramírez Reyes
DBA

--
http://www.freelists.org/webpage/oracle-l

v***@public.gmane.org

2013-09-25 18:18:39 UTC

Permalink

Can you check what all threads are associated with this process, since its windows.
Sent on my BlackBerry® from Vodafone

-----Original Message-----
From: David Ramírez Reyes <dramirezr-***@public.gmane.org>
Sender: oracle-l-bounce-***@public.gmane.org
Date: Wed, 25 Sep 2013 13:12:29
To: 'oracle-l-***@public.gmane.org' (oracle-l-***@public.gmane.org)<oracle-***@freelists.org>
Reply-To: dramirezr-***@public.gmane.org
Subject: Re: Problem with some ORACLE.EXE (SHAD) processes

By the way, there are no errors on the error log for the DB or listener.

Regards

David Ramírez Reyes
Profesión: Padre de Familia

--
http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l

David Ramírez Reyes

2013-09-25 19:15:43 UTC

Permalink

This is a new environment that was migrated from a very old 8i version just
a week ago; the problems started 2 days after the Go Live...
The EMC advisor says there's a big Virtual Memory paging (I changed the sga
max memory from 6.5 GB to 10 GB 5 days ago without any difference on cpu
usage), but I have other db's (also migrated from 8i) with much more
Virtual Memory usage without any cpu problems (for more than a year) with
the same hardware capabilities.

Performance in general is very good, the problem comes every 24-36 hours
when the cpu goes to the 100% pick and the service hungs; it has failed at
11 am, 5 pm, 7 am, etc, there's no pattern on the hours..

David Ramírez Reyes
Profesión: Padre de Familia

I am curious about the wait events you observe (or not) on the instance.
My subsequent question would be (and you might have already asked): What
changed in your environment in the last few days?

Post by David RamÃrez Reyes
By the way, there are no errors on the error log for the DB or listener.
Regards
David Ramírez Reyes
Profesión: Padre de Familia

Post by David RamÃrez Reyes
Hi All,
I have an Oracle 11g R2 over a windows server 2008 (am the first of

hating

Post by David RamÃrez Reyes
Windows, but that's what I have...); it has 10 GB of SGA, 500 process

limit

Post by David RamÃrez Reyes
-400 processes avg-, db on archive mode.
The server has 16 GB of physical memory and 8 cpu cores.
On the last days, the cpu usage has been growing up about every 12-16
hours, it starts at 8-10 avg, then goes to 28-35, then 65-75, 90-95 and
then 100 pick, once it goes to 100% the db hungs (and the server itself
hungs) and there's no other solution than restart the instance.
All this cpu is used by oracle.exe so, I dowloaded a tool to monitor the
specific processes cpu usage at OS Level (MS Process Explorer) and I

notice

Post by David RamÃrez Reyes
that the heavy load of cpu usage is on 6 processes which are the
SYSTEM 190 ORACLE.EXE (SHAD)
SYSTEM 25 ORACLE.EXE (SHAD)
SYSTEM 5 ORACLE.EXE (SHAD)
SYSTEM 211 ORACLE.EXE (SHAD)
SYSTEM 167 ORACLE.EXE (SHAD)
SYSTEM 2 ORACLE.EXE (SHAD)
This is something strange because I have 3 similar instances on another
server with exactly the same characteristics and I have no problem, even
cpu usage goes between 10-20%.
Does anybody has an idea of how to know which Oracle processes are

Post by David RamÃrez Reyes
to these (SHAD) processes?, I even stop the EMC for some time without

any

Post by David RamÃrez Reyes
difference.
Thanks in advance.
David Ramírez Reyes
DBA

--
http://www.freelists.org/webpage/oracle-l

--
A life yet to be lived...

--
http://www.freelists.org/webpage/oracle-l

David Ramírez Reyes

2013-09-26 16:03:47 UTC

Permalink

Thanks for your comments Maaz, however we haven't had any problem with
connections right now (even considering that there are more then 100
automatic machines that save data every 5 mins), we also have Windows 2008
server R1 (standard version).
The numbe of connections was something that were causing problems on the 8i
server (many of them stayed hung), but now we haven't even passed the 405
(which is good now).

The db now is getting 36 hours up, with a cpu avg of 65-70% (still high
compared with another similar servers we have, but completely acceptable),
but the same processes are consuming the majority of this resources, we are
now making changes on all clients (the sqlnet.ora parameter) and being
completely sure that all machines have at least the 10g client.

David Ramírez Reyes
Profesión: Padre de Familia

--
http://www.freelists.org/webpage/oracle-l

Andy Klock

2013-09-26 16:23:05 UTC

Permalink

Have you tried determining what those processes that you found with MS
Process Explorer are actually doing? I would start with Tanel's sw.sql:

On Thu, Sep 26, 2013 at 12:03 PM, David Ramírez Reyes

Post by David RamÃrez Reyes
The db now is getting 36 hours up, with a cpu avg of 65-70% (still high
compared with another similar servers we have, but completely acceptable),
but the same processes are consuming the majority of this resources, we are
now making changes on all clients (the sqlnet.ora parameter) and being
completely sure that all machines have at least the 10g client.

--
http://www.freelists.org/webpage/oracle-l

Andy Klock

2013-09-26 16:26:42 UTC

Permalink

Some days, my fingers can't distinguish CTRL-S from CTRL-V...
As I was saying...

Tanel's sw.sql is very slick:

http://blog.tanelpoder.com/files/scripts/sw.sql

and the info in here:
http://blog.tanelpoder.com/2008/08/07/the-simplest-query-for-checking-whats-happening-in-a-database/

and Snapper too. http://blog.tanelpoder.com/files/scripts/snapper.sql once
you know the SIDs in question.

Good luck.

Post by Andy Klock
Have you tried determining what those processes that you found with MS

--
http://www.freelists.org/webpage/oracle-l

David Ramírez Reyes

2013-09-30 15:59:41 UTC

Permalink

Thanks to all (specially Ingrid) for your suggestions and help, the problem
were caused by some machines that had the 8 client (there was one with even
7 client); now cpu average is 1.5 - 2.5% on pic hours.
Regards

David Ramírez Reyes
Profesión: Padre de Familia

Post by Andy Klock
Some days, my fingers can't distinguish CTRL-S from CTRL-V...
As I was saying...
http://blog.tanelpoder.com/files/scripts/sw.sql
http://blog.tanelpoder.com/2008/08/07/the-simplest-query-for-checking-whats-happening-in-a-database/
and Snapper too. http://blog.tanelpoder.com/files/scripts/snapper.sql once
you know the SIDs in question.
Good luck.

Post by Andy Klock
Have you tried determining what those processes that you found with MS

--
http://www.freelists.org/webpage/oracle-l

Ingrid Voigt

2013-09-25 19:01:44 UTC

Permalink

Hi,

I have seen the exact same symptoms when an older client (Forms 6i)
was trying to connect with SQLNET.AUTHENTICATION_SERVICES
set to (NTS). Every attempted connection blocked a CPU core.
The problem went away when using (NONE).

(We never did proper troubleshooting to find the underlying cause,
just compared with a working client.)

Best regards
Ingrid Voigt

.
--
http://www.freelists.org/webpage/oracle-l

Ingrid Voigt

2013-09-25 19:29:25 UTC

Permalink

That was the sqlnet.ora on the client.
And btw, our system was Oracle 11.2.0.3 on Windows 2008 R2, too.

Regards
Ingrid

Where did you configure that?, on the client or the listener?
David Ramírez Reyes
Profesión: Padre de Familia
Hi,
I have seen the exact same symptoms when an older client (Forms 6i)
was trying to connect with SQLNET.AUTHENTICATION_SERVICES
set to (NTS). Every attempted connection blocked a CPU core.
The problem went away when using (NONE).
(We never did proper troubleshooting to find the underlying cause,
just compared with a working client.)
Best regards
Ingrid Voigt
Hi All,
I have an Oracle 11g R2 over a windows server 2008 (am the first
of hating
Windows, but that's what I have...); it has 10 GB of SGA, 500
process limit
-400 processes avg-, db on archive mode.
The server has 16 GB of physical memory and 8 cpu cores.
On the last days, the cpu usage has been growing up about every
12-16
hours, it starts at 8-10 avg, then goes to 28-35, then 65-75,
90-95 and
then 100 pick, once it goes to 100% the db hungs (and the
server itself
hungs) and there's no other solution than restart the instance.
that the heavy load of cpu usage is on 6 processes which are the
SYSTEM 190 ORACLE.EXE (SHAD)
SYSTEM 25 ORACLE.EXE (SHAD)
SYSTEM 5 ORACLE.EXE (SHAD)
SYSTEM 211 ORACLE.EXE (SHAD)
SYSTEM 167 ORACLE.EXE (SHAD)
SYSTEM 2 ORACLE.EXE (SHAD)

(cut a bit to avoid overquoting)

.
--
http://www.freelists.org/webpage/oracle-l