Re: DB server has a rest cyclically

"Ko mi jung" <gorgeous_at_ccs.sogang.ac.kr> wrote in message news:<bqpb7l$q1$1_at_ccsun2.sogang.ac.kr>...
> We have problem that DB server has a rest cyclically when connected by many
> concurrent users.
> We need help of someone who has a similar experience.
> Please see below.
>
> * Configuration of WEB server : SUN Fire 6800, CPU : 12,
> Memory : 24GB,
> OS : SUN OS 5.8 Web Server : Apache
> 1.3.26, Application Program : CGI, HTML
>
> * Configuration of DB server : SUN Fire 6800 CPU : 4
> Memory : 8GB
> OS : SUN OS 5.8 DBS : ORACLE 9.2.0.4
>
> * Environment of DB server : ORACLE 9.2.0.4 Server = DEDICATED
> Process = 1024
> session = 1131
>
> System construction is that if users request services in Web browser, users
> receive answer
> that comes from WEB server through DB server.( We don't use Web Application
> Server )
> We have Web server and DB server inner same firewall
>
> When 1000users send requests from WEB server to DB server at same times, DB
> server has a rest
> at regular intervals ( ex. 10 ~ 20 seconds ) after DB server processes in
> some requests ( ex. 200requests )
> and then processes and has a rest ..... repeatedly.
>
> We monitored system when DB server has a rest. The result is that used part
> of CPU is less then 10%,
> and there is no request processing.
> And at this time we saw 'established service = 200' at DB server command <
> lsnrctl services >.
> The result is that 200 requests is established and the rest requests don't
> still pass Listener.
> Alert log and Listener log of DB server have no error
>
> We are very curious about the reason that DB server doesn't service
> continuously.
> We don't konw the problem is DB server , Network, or Firewall at all.
> We are waiting for reply anxiously.
> ( E-mail : albert07_at_sogang.ac.kr albert_at_ccs.sogang.ac.kr
> gorgeous_at_ccs.sogang.ac.kr )
> Thanks for reading.
>
> [ P.S. The result of test ]
> ----------------------------------------------------------------------------
> test sql
> ----------------------------------------------------------------------------
> sqlplus sguser/xxx_at_ORA9<<EOF
> SELECT COUNT(*) FROM sguser.temp_test_log where cheorija = 'test1';
> INSERT INTO temp_test_log (nyeondo, hakgi, test_num, cheori_ilja, cheorija)
> SELECT '2003','2',COUNT(*),SYSDATE,'test1' FROM HAKB_SUGANG;
> COMMIT;
> EOF
> ----------------------------------------------------------------------------
> case 1 : We run 500 test sqls at the same time ( WEB server --> DB server :
> remote )
> Result) process and then rest repeatedly. Process well 350, the
> rest died (time out)
> case 2 : We run 500 test sqls ( DB server --> DB server : local )
> Result) process all
> case 3 : We run 500 test sqls in order ( WEB server --> DB server : remote )
> Result) process all

Sybrand likely identified your primary problem, which you could likely confirm by examining the Oracle wait interface (if you search for this along with level 10046 trace, you'll find lots of info available).

tracing a specific session (waits=>true), or just examining the wait events during the "system rest" episodes should provide you with enough information to determine the cause of the issue, although you may have several issues besides the possible single table freelist contention issue.

also,
- are you pooling connections at the app server tier? - are you using persistent connections, or is a dedicated server process being created and destroyed for each (pseudo) transaction? - can you provide any info as to the network connection between tiers? (gigabit, trunked fast ethernet, switched or point to point) - consider installing the Oracle statspack utility. you can read about this in $ORACLE_HOME/rdbms/admin/spdoc.txt.

Pd Received on Sun Dec 07 2003 - 14:33:17 CST