sql prompt hanging

[muthamilan]

dear friends

my client using oracle 8.0.5 on hp-ux,frontend is D2k.If more then 15 users login frontend then sql prompt hanging.this time usr process takes more then 80%.but OS working properly that is we can telnet,ftp and do all other activity on OS side but sql user gets hanging.kindly suggest me some solution.

Thanks& Regards
S.Muthu

[Ravi_8]

Hi

what's the nproc,maxdsiz,maxdsiz_64bit values?

issue the command
#kmtune
and grep for nproc,maxdsiz

[Sanjay Kumar Suri]

Can you share the parameter file?

Also look for the value PROCESSES in the parameter file.

sks

[Yogeeraj_1]

hi,

you should consider running a statspack report (15 minutes interval) and analyze too.

does the sql prompt freeze when you run a query or is it another thing?

regards
Yogeeraj

[Brian Crabtree]

What happens when it hangs? Does it hang trying to run a query, or just trying to login to the system in general?

Brian

[muthamilan]

friends,
nprocs=1000
PROCESS on init****.ora is 100.

when users are working(all user's having same sql user login) sudeenly gets hanging,but sql sys&system user can login.

[ericfjchen]

Suggest you to enlarge process to 200 on init.ora.

[Hein van den Heuvel]

Instead of gambling on which resource might be exhausted in Oracle, why not ask oracle?

set pages 9999
column NAME format A25
column INIT format A10
column LIMIT format A10
SELECT RESOURCE_NAME "Name", CURRENT_UTILIZATION "Current",
MAX_UTILIZATION "Max", INITIAL_ALLOCATION "Init", LIMIT_VALUE "Limit"
FROM V$RESOURCE_LIMIT;



In stead of wondering what Oracle might be waiting for, why not ask Oracle?
Google for v$session_wait

For example:
http://examples.oreilly.com/oracle2/Chap11/objwait.sql


Or here in the forum:

http://forums1.itrc.hp.com/service/forums/questionanswer.do?threadId=657038

Or our friends at ixora:

http://www.ixora.com.au/scripts/waits.htm

Of course it could be an w2k/hpux interaction thingie, maybe with the network, or with control0c polling or whatever...

Hein.

[muthamilan]

thanks friends
I de-installed & installed oracle with ur suggestions.system is working fine.

Thanks&Regards
S.Muthamilan