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

[dbapl...@hotmail.com]

I am using Oracle 9.2.0.5 on HP UNIX 11. I am using following query:
select sid, sql_address from v$session order by sid;

I only get one sql_address for each session id. I am assuming this is
the sql address of current or last sql statement run by this
session (assmuning sql satements have not been flushed from SGA).

How can I find all the sql's run by a session?

Thanks.

[kric...@juno.com]

sql tracing

You can enable tracing for your own session,

alter session set sql_trace=true;

...or, if you have execute on sys.dbms_system.set_ev, then for another
existing session:

exec sys.dbms_system.set_ev(sid,serial#,10046,1,'')

In both cases, you will capture the sql from the time tracing was
enabled going forward (but not prior to that time).

You can also set tracing it for all future sessions (which could be too
much if you are doing it on a busy database):

init.ora
sql_trace=true

..or, if you use after logon trigger, for some future sessions (you
decide which ones in the logic of the trigger), e.g.

http://www.oracle.com/technology/oramag/code/tips2003/042703.html

Kirill

[dbapl...@hotmail.com]

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.

[Mladen Gogala]

On Fri, 24 Mar 2006 13:09:33 -0800, dbaplusplus wrote:

>
> How can I find all the sql's run by a session?

Auditing would be a good start.

--
http://www.mgogala.com

[Mladen Gogala]

On Fri, 24 Mar 2006 14:12:39 -0800, dbaplusplus wrote:

> 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.

V$SQL doesn't support that. As soon as there is need for space in shared
pool, old SQL is purged out of V$SQL. You can use DBA_HIST views in 10g,
only you have to pay for it. In 9.2 there is no way to do that.

--
http://www.mgogala.com

[Mark D Powell]

On version 9.2 the Oracle audit feature does not capture the actual SQL
statement executed in the audit trail so while auditing can be used to
see all objects a session touched and the type of SQL statement issued
it will not capture the level of detail requested in the OP's post.

Setting trace on from another session will only trace SQL statements
ran after the trace is set on so I think the only practical solution is
to set trace on when the session of interest starts. If the actual
user task cannot be modified to set trace on then perhaps a database
logon event trigger could be used.

HTH -- Mark D Powell --

[Mladen Gogala]

On Sat, 25 Mar 2006 10:29:49 -0800, Mark D Powell wrote:

> Setting trace on from another session will only trace SQL statements
> ran after the trace is set on so I think the only practical solution is
> to set trace on when the session of interest starts. If the actual
> user task cannot be modified to set trace on then perhaps a database
> logon event trigger could be used.

Mark, the question here is what do you want to see. Usually, the auditing
requirements are to preserve all information pertaining to modifying
business critical data and executing certain privileged operations on the
database. That is covered with auditing. Fine grained auditing (DBMS_FGA)
does preserve SQL and can be triggered by select statement. If, on the
other hand, you want to see how many times did somebody go to football and
NCAA pools, you're out of luck.

--
http://www.mgogala.com

[Mark D Powell]

Yes, the question is what do you want to see (and why). The OP wants
>> to find sql statements which take most cpu time etc. << so as this is really a performance issue I think trace is a more practical and appropriate solution.

[dbapl...@hotmail.com]

Thanks a lot for various responses. What I wanted can rougly be found
from v$open_cursors insted of v$session.. To find all the satements,
one needs login triggers etc which is quite hard to implement in
production system,

[NetComrade]

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

[dbapl...@hotmail.com]

I was succesful in writing a script using v$sql, v$sqltext, v$sql_plan
to get all the statements in shared pool at that point in time
which exceed some criterion (like elapsed_time > 0.5 mins). Now I can
see entire statements (without getting chopped - spme of my statements
are 500 lines long - were dynamical genearted) as well see
sql based on elapshed time (somehing which statspack or OEM does not
show).