Toad World® Forums

Tr : Re : Re : Identify a oracle process consumes most of the CPU resources


#1

SEE ATTACHMENT

image001.png

image002.png


#2

Morning,

OK but I have a RAM memory problem on oracle server : On my linux
server (ubuntu)
Ubuntu is not a supported Linux for use with Oracle.

I have 4 Go RAM at starting oracle server it consums already 2,5 Go
(1,5 Go free)
Yes. Many of the Oracle initialisation parameters tell it how much RAM
to allocate for shared pool, Java pool, sort areas etc. These can be
either explicit, or left to Oracle to work out, depending on your
setting for WORKAREA_SIZE_POLICY. You also had RAM set aside for your
buffer cache.

and when web application works the RAM to decrease to has 3,9 Go (100
Mo free).
That’s because your web server is running on the same server as the
database? If it is Apache, then there will be a number of processes, all
consuming RAM, that get started up when Apache starts. There are
parameters to control this as well. (Off hand, sorry, I can’t remember -
something like minservers and so on.)

When web application is stopped, there are several oracle (ps -aef |
grep oracle) which

continues to work and consumes RAM memory.
These are most likely the Oracle background processes. As long as Oracle
is running, some or all of these will show up.

I must restart oracle server to get 1,5 Go RAM free ?
No, you need to find out where what these processes are. If they are
Oracle processes, then a “ps” command will show them and show you their
process id. (The second column in the “ps” output.). Here is one from my
little test Linux “server”:

$ ps -ef|grep -i oracle
oracle 18198 1 0 07:53 ? 00:00:02 ora_j000_perf10g
dba 19567 22693 0 11:41 pts/3 00:00:00 grep -i oracle
oracle 23865 1 0 Jun02 ? 00:00:01 oracleperf10g (LOCAL=NO)
oracle 23867 1 0 Jun02 ? 00:00:00 oracleperf10g (LOCAL=NO)
oracle 24074 1 0 Jun02 ? 00:00:00 oracleperf10g (LOCAL=NO)
oracle 30308 1 0 Feb10 ? 00:00:00
/u00/app/oracle/product/10.2.0.3/bin/tnslsnr LISTENER -inherit
oracle 30314 1 0 Feb10 ? 00:00:00 ora_pmon_perf10g
oracle 30316 1 0 Feb10 ? 00:00:01 ora_psp0_perf10g
oracle 30318 1 0 Feb10 ? 00:02:12 ora_mman_perf10g
oracle 30320 1 0 Feb10 ? 00:02:00 ora_dbw0_perf10g
oracle 30322 1 0 Feb10 ? 00:01:17 ora_lgwr_perf10g
oracle 30324 1 0 Feb10 ? 00:00:17 ora_ckpt_perf10g
oracle 30326 1 0 Feb10 ? 00:03:30 ora_smon_perf10g
oracle 30328 1 0 Feb10 ? 00:00:00 ora_reco_perf10g
oracle 30330 1 0 Feb10 ? 00:04:01 ora_cjq0_perf10g
oracle 30332 1 0 Feb10 ? 00:00:25 ora_mmon_perf10g
oracle 30334 1 0 Feb10 ? 00:00:00 ora_mmnl_perf10g
oracle 30336 1 0 Feb10 ? 00:00:00 ora_d000_perf10g
oracle 30338 1 0 Feb10 ? 00:00:00 ora_s000_perf10g
oracle 30342 1 0 Feb10 ? 00:00:00 ora_qmnc_perf10g
oracle 30366 1 0 Feb10 ? 00:00:00 ora_q000_perf10g
oracle 30368 1 0 Feb10 ? 00:00:00 ora_q001_perf10g

You can see that most of these are background processes, there are these
which look to be user/server processes though:

oracle 23865 1 0 Jun02 ? 00:00:01 oracleperf10g (LOCAL=NO)
oracle 23867 1 0 Jun02 ? 00:00:00 oracleperf10g (LOCAL=NO)
oracle 24074 1 0 Jun02 ? 00:00:00 oracleperf10g (LOCAL=NO)

Looking at the pid column, I can interrogate the database to find out
who or what they are:

SELECT s.SID,s.serial#,s.username,s.sql_hash_value,s.program,P.spid,
b.NAME
FROM v$session s, v$process P, v$bgprocess b
WHERE P.spid IN (23865,23867,24074)
AND s.paddr = P.addr
AND b.paddr(+) = P.addr;

My own test output shows that these are foreground processes because the
background name column is NULL:

SID SERIAL# USERNAME SQL_HASH_VALUE PROGRAM SPID NAME
140 1286 SYSTEM 0 Toad.exe 23865 null
149 22939 SYSTEM 0 Toad.exe 23867 null
135 41048 SYSTEM 0 Toad.exe 24074 null

So, these are my three separate threads in my Toad connection. You may
get similar returns, but at least you will know what is still connected
to the database. It could be that your web server (is theare a middle
tier as well, like WebLogic?) is holding open a connection pool - and
these are what you are seeing.

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk


#3

Hi;

Thank’s

Sorry its SLES 10SP2 ! There 2 serveurs web (websphere + ihs) and 1 server DB
(oracle 10g).

How verify the buffer cache ?


#4

Afternoon Djamel Boussebha,

Thank’s
Welcome.

Sorry its SLES 10SP2 ! There 2 serveurs web (websphere +
ihs) and 1 server DB (oracle 10g).
No worries. SLES is supported.

How verify the buffer cache ?
You need to look at the settings for various Oracle initialisation
parameters.

DB_%_CACHE%
DB_BLOCK_BUFFERS
Etc.

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk


#5

On Fri, 2010-06-04 at 12:06 +0100, Dunbar, Norman wrote:

djamel boussebha wrote:

On my linux server (ubuntu)
Ubuntu is not a supported Linux for use with Oracle.

Technically Oracle “approves” of installing Oracle XE on debian or
ubuntu(1), but since no os is “supported” for XE (they don’t provide
“support”) and since I don’t think that’s what the OP was using anyway,
you’re basically correct.

(1)
http://www.oracle.com/technology/tech/linux/install/xe-on-kubuntu.html

Joseph Charpak
josephcharpak@comcast.net


#6

Hi Joseph,

Ubuntu is not a supported Linux for use with Oracle.

Technically Oracle “approves” of installing Oracle XE on debian or

Yoo are absolutely correct. I hadn’t noticed that the OP was referring
to Oracle XE. My bad.

Thanks for the correction.

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk


#7

On Fri, 2010-06-04 at 15:21 +0100, Dunbar, Norman wrote:

Ubuntu is not a supported Linux for use with Oracle.

Technically Oracle “approves” of installing Oracle XE on debian or

Yoo are absolutely correct. I hadn’t noticed that the OP was referring
to Oracle XE. My bad.

Actually I was just being pedantic, I have no idea what the OP is using.

Joseph Charpak
josephcharpak@comcast.net