Re: Oracle Server is 100% busy, what should I do?

Peizhong Wu wrote:

> Hi, I am running Oracle 8.0.5 in a Sun Ultra Sparc I and Oracle
> application server 4.0.7 in another Ultra Sparc I. The stored procedure
> will generate dynamic html files for the web server.
> During peak time, ( 30hits/sec?), the server is extramly slow.
> The following is the result of 'top'
> **********************************************************************
> last pid: 15852; load averages: 12.36, 7.38, 4.12
> 14:06:46
> 86 processes: 66 sleeping, 19 running, 1 on cpu
> CPU states: 0.0% idle, 90.2% user, 9.8% kernel, 0.0% iowait, 0.0%
> swap
> Memory: 128M real, 2448K free, 129M swap in use, 338M swap free
> PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU COMMAND
> 15272 onesky 1 36 0 27M 18M run 3:45 5.73% oracle

Are you running the Oracle8 as Multi-Threaded (MTS option)? This would save a lot of memory and MTS is a good fit for the quick in-and-out nature of web invocations (you'd avoid repeated useless setup and takedown of DB processes). Conversely you might need to increase SGA under MTS, but still save overall.

Also in OWAS (I'm using OWAS3.0/Solaris), you can set the Minimum # of Instances and Maximum # of Instances of the pl/sql cartridge in the web admin page titled
'Configure Web Request Broker parameters for PL/SQL ' whose url is http://<your-host>/ows-abin/ctadmin?FORM_CODE=CARTRIDGE_EXIST_FORM&Cartridge=PLSQL&SUBMIT_BUTTON=Modify

Make sure the MIN is around your avg concurrent hits. Increase MAX to somewhere near peak load range. Factors include whether you want to optimize response-time vs. memory usage and do you have other consumers on machine to worry about.

>
> There are two options:
> 1). add more memory in the machine. how much is enough?
> 2). replace the machine with a 'big' one. where is the end? Sun company
> suggest E450 or E3500,
> Does anyone have any idea?

Your top cmd (for your DB server, I assume) does show only 2-3Mb free real memory. That seems pretty low to me. If you're running MTS already, installing more RAM and more memory allocated to the SGA will help and is the easiest h/w step to try. I'm in process of migrating from an Ultra-2 to a E450 but not for immediate performance reasons. I can't say whether E450 is your next best db server. Your OWAS may be fine on the Ultra for now.

>
> By the way, anyone know if it good to ask DB server to generate html(
> using PLSQL). It will sure increase the load of server.

Note that in using PL/SQL on the Oracle Web App Server we're really NOT using the OWAS as an app server(!)
because the apps really run inside the Oracle8 DB server. You save on DB-login overhead but you're not offloading much off the DB server. Is this good? I assume PL/SQL access to Oracle data is about as efficient as you can get. PL/SQL/web runs slower than most of my
PERL/cgi apps especially when the pl/sql s.p. is being brought into Shared Memory Pool
for the first time (Hello World and showing Oracle time can take 2-5 seconds the 1st time). My opinion is that PL/SQL is not a particularly speedy or efficient web environment. On the plus side, PL/SQL is an easy, well structured and db-friendly language.

Personally, more from the viewpoint of a web developer, I'm not keen on PL/SQL for web because you're running in a hermetically sealed environment inside the db. A lot of web programming draws on being able to do
a variety of dynamic things via Unix (e.g. send email, read a unix file, parse an html template, do a system cmd) which is awkward at best in PL/SQL (unlike Perl, say). Notice that in your case, PLSQL UTIL_FILE can access unix files on the DB server but your html is on the OWAS box! Using
any other cartridge type (e.g. Perl or C or Java) solves this and you'd be using the OWAS more truly as an app server.

• If all else fails ... ****************** __ __ __ ______ Howell Lee h.lee_at_umassp.edu / /\/ /\ / /\ / ____/\

Univ Info. Systems, Univ of Massachusetts / / / / / / / / / /___.\/

100 Venture Way, 2nd fl.  Voice: (413) 587-2128   / / / / / / / / /___  /\
Hadley, MA 01035-9682       FAX: (413) 587-2148  / /_/ / / / / / ____/ / /
http://hpl.dpc.umassp.edu                       /_____/ / /_/ / /_____/ /
***********   ...lower your standards. ******** \_____\/  \_\/  \_____\/