Re: A couple of questions

"Noons" <wizofoz2k_at_yahoo.com.au> wrote in message news:<3f4f75b0$1$14560$afc38c87_at_news.optusnet.com.au>...
> "MK" <MK_at_foo.com> wrote in message news:bingco$92uqo$1_at_ID-174077.news.uni-berlin.de...
> > 1) when I export with indexes=y, and then import the resulting
> > dmp file into another instance (also with indexes=y), are
> > indexes imported, or rebuilt from ground-up?
>
> indexes are always re-created in an import.
>
>
> >
> > 2) how can I throttle CPU usage for oracle.exe? During
> > the night I run various DML jobs (mostly DELETE statements)
> > and those jobs run oracle.exe up to 100%. This suffocates
> > other processes on the machine and I get all kinds of
> > warning messages. I'd like to put a limit, for example
> > 80%, on oracle.exe's CPU usage - would this be possible?
> >
> > Oracle8i on W2K.
>
> Upgrade to 9i. There is a nice resource manager in 9i
> that will allow you to smoothly run the system. It exists in
> a rudimentary form in 8i, so I don't know if it will help.

the premise of throttling an OS process just so that it does not appear to be using 100% CPU seems backwards to me. In w2k-land, we run nothing else except oracle instances on our oracle servers.
I'd say, move the other processes off of the box. Don't run things on the server console. Just because a foreground app on the desktop does not seem to be responding, it doesn't mean that the background processes aren't cranking along nicely. Put apps like OEM on management workstations, run sqlplus.exe on the server for administration.

Now, if oracle.exe (a single process, for this example) has no bottleneck with respect to I/O (say that a query is running nested loops with loss of IN subqueries)- why wouldn't the oracle.exe process use 100% CPU?

If you have a server sith a single disk, chances are you'll never hit 100% cpu utilization, as you'll have a bottleneck on physical I/O.

The OS kernel is responsible for scheduling which process is running. If there are competing processes, (say multiple oracle.exe processes) then the OS should handle those processes being context switched on and off the CPU(s).

Resource Manager should handle what threads within the oracle.exe process are executed (win32 speak) and their resource utilitization.

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/c10rsmgr.htm#24594 "The effect of the Database Resource Manager is noticeable only in busy environments with high system utilization."

"Performance Overhead

• Database Resource Manager does not switch process as often as any operating system's fair-share scheduler does.
• Database Resource Manager runs fewer processes concurrently and never context-switches a process that is holding a latch."

"Interaction with Operating-System Resource Control

Oracle9i expects a static configuration and allocates internal resources, such as latches, from available resources detected at database startup. The database might not perform optimally and can become unstable if resource configuration changes very frequently. Therefore, operating-system resource control should be used with Oracle databases judiciously, according to the following guidelines:

1. Operating-system resource control should not be used concurrently with the Database Resource Manager, because neither of them are aware of each other's existence. As a result, both the operating system and Database Resource Manager try to control resource allocation in a manner that causes unpredictable behavior and instability of Oracle databases.
   • If you want to control resource distribution within an instance, use the Database Resource Manager and turn off operating-system resource control.
     • If you have multiple instances on a node and you want to distribute resources among them, use operating-system resource control, not the Database Resource Manager."

hth.

Paul Received on Sat Aug 30 2003 - 09:28:20 CDT