Kevin Mc

Are there any Oracle guru's on the board who can point me in the right direction for a document / url that gives an explanantion of trace files - i.e. how to interpret the trace file (don't mean tkprof). Have a locking issue that is trashing the CPU on a UNIX box and am trying to find out session info etc...

Have looked on Metalink to no avail. Also have "Oracle 8i DBA Handbook" though only has a very general half a page on trace files and the alert log...

Thanks in anticipation.

Kevin Mc

orbv

There is an oracle error messages guide (eg here) that may help out. If you really stuck drop them in an email to me and will have a look on Tuesday.

YorkshireSimon

Kevin

The trace files you speak of are, lets just say, Oracle technical staff, and usually developers only type reading material.

Sometimes, you might get lucky in extracting some of the numbers and error codes (non Oracle standard ones ie. ORA-), and find a match for them on Metalink.

So, trace file analysis is going to be difficult.

When you say locking that is thrashing the CPU, are you talking about session level locks, or internal locks (latches and enqueues)? If you are referring to sessions causing record locks and then other sessions waiting on these locks, you can query the v$lock, v$locked_objects views and link these back to v$session to identify the culprit session. Then issue the usuall 'alter system kill session' command.

Hope thios helps, give me a yell if you want to discuss further.

Cheers

Simon

Kevin Mc

Thanks for the info chaps! I'll take a look at that web site.

I've managed to determine from the trace file that the process causing the problem has a Shared lock (S), but needs an exclusive lock (X). There is more info on "waiting session" - I was wondering if this related to a session id in v$session.

Think it will be a case of waiting for the problem to occur again and query the lock views and sqlarea.

Thanks again.

popeye

Dunno if you already know this, but if you have a session which cannot acquire a lock, you can determine the object it's attempting to lock by looking at the 'id1' column in v$lock. You should then see another session which has managed to acquire a lock on that object, and that's your blocking session.

Cheers.

Kevin Mc

Cheers for that Popeye - I'll look at that too when the problem next occurs.