Re: problem connecting to db.

On Jan 6, 2:52�am, "Mr. X." <no_spam_please_at_nospam_please.com> wrote:
> I don't know if this is relevant, but :
> A specific program's memory-use is getting larger and larger
> every day, (I don't have the program's code) .
> So every day at night, I do :
> rerun the service with above problem, and kill its oracle-session
> (There is no other choice, since the process is black-box,
> and I have to kill its session, because there will be more sever problems if
> not).
>
> The code :
> alter system kill session ...
> (That's the only thing I do for killing that session, in VB 6.0)
>
> Do you know a better way of killing the session ?

Unfortunately, I'm mostly on unix and don't generally look at Windows - Charles sure seems to have a good handle on that! But anyways, on unix systems, you can either kill with the alter session command (which is what management software does too), or kill the process. The alter session command gets cleaned up by SMON, which may never get there -hence, you see some very old stuff. Killing the process gets cleaned up by PMON, which tends to be right on top of things. On Windows, something like orakill. I'm wondering if Note:1020720.102 has PMON/SMON backwards or maybe I'm just getting senile.

So do you see the memory being released after you kill? Do you know which program has the memory leak? There are some memory leaks in Oracle, you can find them searching on metalink.

> (By the ctime is saw on v$lock, �there are about 20 rows,
> and some of them are 20 days long, and may be irrelevant ...
> also one of the rows seems to be at the exact time since killing the last
> session yesterday -
> I think that ctime count the time in seconds from time the process was
> running).

Well, see metalink Note:1020008.6 for one old way to display useable information from locks. Note:102925.1 is a pretty good description of what can be going on.

>
> I can do some code for tracing (once for 5 minutes) the v$lock,
> but what do you advice me to do first ?

I was thinking of two possible errors - something that would prevent you from logging on, which would look like a hang, and something that would keep the app from accessing a specific row or table. Oracle normally only uses row locks, but sometimes the row could be a hot or locked block if your app does something like try to look at a header row, or more obscurely, could be doing bizarro things trying to get undo information as it rolls back the killed session info, that has a lock from when _that_ session started. So if you could post the stuff like lock type we might be able to help figure out what is going on.

>
> I didn't see any rows in v$lock that occurs at the same time I cannot
> connect to the database
> (the first problem I've described).
> Does v$lock is related to the first problem I have described (failed to
> connect to db) ?
>
> Any clue ?

You might also look at at the Lock Manager part of OEM.

Also, have you checked the alert log for any errors? Are you using SESSION_CACHED_CURSORS? I ask because sometimes some software doesn't really use cursors correctly, causing lots of thrashing in the shared code area of the SGA. If severe, this will show errors in the alert log, but if not so severe can simply cause random waits while code is swapped in. So it is important to check what exactly you are waiting on. OEM (or some snappy DBA work) can show how many open cursors you have - it may surprise you, especially all the possible implicit cursors. Middle 9.2.x versions changed how cursors were handled, some people don't realize SESSION_CACHED_CURSORS can help.

I suspect you have 2 or more problems.

jg

--
@home.com is bogus.
"I'm not dead yet!" - Monty Python
http://www.networkworld.com/news/2008/010708-carr-it-dead.html