Re: Sql Trace to detect concurrency issues

"Sudhir" <sudhir.jorwekar_at_gmail.com> wrote in message news:1137963129.009538.326880_at_g14g2000cwa.googlegroups.com...

>
> For this I want to take the sql trace of various queries that are
> encountered by server along with transaction id (or session id) and
> time at which the query is executed. (The time and transaction id will
> help finding conflicts between transactions.)
>

If all you want is the queries and the point in time at which there were executed, you could try to take advantage of fine grained auditing. (And in 10g this will capture inserts, updates, and deletes as well).

By setting an audit policy on every table, you can capture every SQL statement executed, who executed it, and the SCN at which it ran. You can then convert the SCN to a time with one of the utility packages (but I can't remember the name at present).

Be careful - this can add a lot of overhead to the run-time resource requirements, and it dumps the audit into a table called fga_log$ in the SYS schema, so you need to keep an eye on free space in the system tablespace.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle: Fundamentals

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 23rd Jan 2006