Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
cancel
Showing results for 
Search instead for 
Did you mean: 

sql prompt hanging

muthamilan
Frequent Advisor

sql prompt hanging

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
8 REPLIES
Ravi_8
Honored Contributor

Re: sql prompt hanging

Hi

what's the nproc,maxdsiz,maxdsiz_64bit values?

issue the command
#kmtune
and grep for nproc,maxdsiz
never give up
Sanjay Kumar Suri
Honored Contributor

Re: sql prompt hanging

Can you share the parameter file?

Also look for the value PROCESSES in the parameter file.

sks
A rigid mind is very sure, but often wrong. A flexible mind is generally unsure, but often right.
Yogeeraj_1
Honored Contributor

Re: sql prompt hanging

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Brian Crabtree
Honored Contributor

Re: sql prompt hanging

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
Frequent Advisor

Re: sql prompt hanging

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
Regular Advisor

Re: sql prompt hanging

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

Hein van den Heuvel
Honored Contributor

Re: sql prompt hanging

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
Frequent Advisor

Re: sql prompt hanging

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

Thanks&Regards
S.Muthamilan