Re: Is thjere a way to find all sql satements run by a session

On 24 Mar 2006 14:12:39 -0800, dbaplusplus_at_hotmail.com wrote:

>Thanks. I know of the various methods you suggest.
>Sorry, I did not explain why I was asking this question. My system is a
>production system where thousands of sol sessions run. I am not in a
>position to turn on tracing for a session or turn on logon trigger.
>What I am looking for is to look at V$sql views and find various types
>of sql statements executed, how long they take etc.
>
>Also some of my sql statement are longer than 1000 characters, they get
>chopped in statspack output.
>
>I want to find sql statements which take most cpu time etc. All this
>information is available in v$ views, but when I started with
>V4session, I was surprised to find it only shows me address of one sql
>statement, that's why I am posting this question.

statspack also gives you the hash or sql_address you can look into v$sqltext for full statement. you can look into v$open_cursor for all sql, but it's not guaranteed to be there.
also, turning on tracing for 1 or 2 sessions shouldn't be a big deal as long as you know what you're doing.
.......
We run Oracle 9.2.0.6 on RH4 AMD
remove NSPAM to email Received on Tue Mar 28 2006 - 10:36:45 CST