Re: How to trace sql comes from middleware in production environment?

"wangbin" <wangbin_at_start.com.au> wrote in message news:2d15bd69.0204291828.719f8ba8_at_posting.google.com...
> Our application has N-tie structure: Browser+corba server + DB. Many
> times I am asked what happens in the db when clicking this button.
> It's quite easy to identify a active query, if it lasts for a while. I
> have the problem if it finishes in a second. I don't want to trace the
> whole database. I can trace one session, however, corba server set up
> about 70 sessions to DB. God knows which one is used by this click. I
> use the following way in the test environment.
>
> 1.restart DB and application;
> 2.run the following query before the click;
> SELECT q.hash_value,
> q.sql_text,
> q.first_load_time
> FROM v$sqlarea q
> order by q.first_load_time
> /
> 3.click the button;
> 4.rerun the query, compare the result.
>
> It works in test environment, but I cannot use it in production. Is it
> any better way?
>
> Regards,
> Bin

assuming 8i or higher you can create an after logon trigger on the affected schema to put the session in trace mode automatically for you.

Hth

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address