Showing results for 
Search instead for 
Did you mean: 

Oracle performance issue

Go to solution

Oracle performance issue

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?
Honored Contributor

Re: Oracle performance issue

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
There is no substitute to HARDWORK
Stefan Farrelly
Honored Contributor

Re: Oracle performance issue

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.

Im from Palmerston North, New Zealand, but somehow ended up in London...
Honored Contributor

Re: Oracle performance issue

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...
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Hein van den Heuvel
Honored Contributor

Re: Oracle performance issue

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,

[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
Exalted Contributor

Re: Oracle performance issue

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.

Introduction to Performance Tuning
DocId: UPERFKBAN00000726 Updated: 20031008

Steven E Protter
Owner of ISN Corporation
Valued Contributor

Re: Oracle performance issue


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.

Honored Contributor

Re: Oracle performance issue

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

Manoj Srivastava

Re: Oracle performance issue

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.