Re: extproc using too much CPU time

On Mon, 10 Dec 2001 11:32:44 -0500, pmatt69 wrote:

> We have recently noticed that the extproc process on our DB server is
> using around 75% of the CPU time. It has been this way for several days.
> There is very little additional activity in our database. Has anyone
> ever seen this before? Is it possible to fix this without restarting the
> instance?
>
> Our environment is Oracle 8.1.6 on Solaris 8.
>
> Thanks,
>
> Matt
>

Well, that process executes your routines. Have you ever tried them standalone? Even if CPU usage of a single invocation is very small, iterated in a loop through all the rows of a table which has a few million rows can make it ver expensive. As a general rule:

1. Floating point is expensive. Expensive to the extreme.
2. Avoid computations within loops.
3. Subroutine calls consume CPU and can flush CPU cache frequently, thus slowing it to the crawl.
4. Read Kewin Dowd's book named "High Performance Computing" and try using optimization techniques on your routines.

Extproc is where oracle meets the traditional high performance computing and techniques like loop unrolling, loop peeling, parallelizing execution, worrying about the instruction mix and alike. In particluar, if you plan doing image processing as a set of external procedures, better be prepared to spend some major money for the iron.

-- 
Mladen Gogala