Oracle performance issue

[Naoyuki]

Hi there.

I have HP-UX 11.11 machine with oracle 8.1.6 installed.
When I checked the cpu usage with top, one process was very high cpu usage and it seems this process is a bottleneck.

oracle 28834 1 19 11:07:16 ? 0:00 oracleORACLE_SID (DESCRIPTION=(LOCAL=yes)(ADDRESS=(PROTOCOL=BEQ))

It seems this process is launched when user is connected.
I saw only one process and this process was using cpu almost 100% but only one cpu because this is only one process.
Our machine has 4 cpus and it looks like other cpu power is wasted.
No oracle background process was busy at all.

Is there any way to launch multiple processes per user or reduce cpu usage of this process like changing parameters?

[RAC_1]

Oracle is very memory hungry.

Seems that the SQL query for this process needs to be checked.

From system side, you can control max process, system wide processes.

KErnel para. nproc
maxuprc

[Stefan Farrelly]

We see these sort of runaway user processes all the time. Usually caused by a users' PC crashing or them not disconnecting properly - this leaves the process running on the HP server until a db shutdown or someone kills it. Trying to identify it isnt too hard, use lsof to identify the IP address of this connection then you can find the user and see what they are doing thats different from a normal user.

If we find these processes running for long periods we simply kill them.

You can use PRM (Process Resource Manager) to limit cpu usage per process/app - this allows you to set a limit for user processes so if they do runaway like this they wont consume all available cpu on the server, but this is a purchaseable product.

[Yogeeraj_1]

hi,
-
Running this SQL statement should enable you locate the user doing the query, program, etc...
--------------------------------------------
select b.sid SID,b.serial# "Serial#", c.spid "srvPID", b.osuser, b.username
, b.status, b.client_info from v$session b, v$process c where b.paddr = c.addr
and c.sPID = '&SPID'
--------------------------------------------
e.g. As an input to the above SQL, the SPID, as in your case above, would be 28834
-
It would be preferable that you tune your application first...
-
regards
Yogeeraj

[Hein van den Heuvel · Accepted solution]

In order to use more than 1 cpu for oracle slave processing from a single user query you will need: "Oracle Parallel Query"(formerly Oracle Parallel Query Option or PQO) allows one to break-up a given SQL statement so that its parts can run simultaneously on different processors
in a multi-processor machine. Typical operations that can run in parallel are: full table scans, sorts, sub-queries, data loading etc.

Parallel Query can improve performance of certain types of operations dramatically and is commonly used in Decision Support and Data Warehousing applications.

Google for "Oracle Parallel Query" and /or " +oracle +degree".

I concur with the others that you first need to undertand what the heavy query is doing.
Get a trace, use statspack, whatever tickles you best.

Personally i tend to use little scripts like the one below.
Good luck,
Hein.

[change the 1000000 to a number fitting with your usage. Maybe look for cpu_time]

set pages 9999
select buffer_gets, executions, cpu_time
from v$sql where BUFFER_GETS >1000000
order by BUFFER_GETS;

or when I need the full text...

select t.sql_text
from v$sql s, v$sqltext t
where BUFFER_GETS > 1000000
and t.hash_value = s.hash_value
order by t.hash_value, piece;

[Steven E. Protter]

Intro to performance tuning, written by one of HP's top oracle performance guys.

A good read, and I know the guy, he's brilliant.

http://www2.itrc.hp.com/service/cki/search.do?category=c0&docType=Security&docType=Patch&docType=EngineerNotes&docType=BugReports&docType=Hardware&docType=ReferenceMaterials&docType=ThirdParty&searchString=UPERFKBAN00000726&search.y=8&search.x=28&mode=id&admit=-1335382922+1067355421585+28353475&searchCrit=allwords

Introduction to Performance Tuning
DocId: UPERFKBAN00000726 Updated: 20031008

SEP

[Stan_17]

Hi,

As yogeeraj pointed out, first look into sql and see if you could tune it. But if they are number crunching sqls(from batch jobs) or well tuned, then look into parallel execution where you can make use of available 4cpus to execute the query much quicker. If not familiar with parallel execution, then look into parallel_automatic_tuning parameter.

-
Stan

[MANOJ SRIVASTAVA]

Are you using the 32 bit implementation or the 64 bit implementation of Oracle.


Manoj Srivastava

[Naoyuki]

Thanks for all your replies.
I think the solution of this is parallel query.
I tested parallel query and it spreaded on several cpus but didn't improve the performance. I don't know why but I'll check what SQL is causing this problem anyway.