Re: Slow log

On May 11, 2:29 pm, Mladen Gogala <mgog..._at_yahoo.com> wrote:
> On Sat, 10 May 2008 18:15:00 -0700, iavian wrote:
> > How to log all the queries that takes more than n seconds .. like mysql
> > show query log
>
> FGA (fine-grained auditing) might be helpful. Also, you may write
> a script that will query V$SQL and extract SQL by the elapsed time.
> However, querying that table hits the library cache latch every time.
> Don't do it too often.
>
> --
> Mladen Gogalahttp://mgogala.freehostia.com

Statspack will catpure heavy hitter SQL depending on the selected level of the snapshots you take. With 10g the AWR processes also capture heavy hitter SQL though these two options may not capture all SQL you want it is probably from a practical point of view good enough.

You can use the trace facility to trace specific tasks of interest. While possible I would not set it on to trace every session connected to the database as this will generate way too much data most of which you will not care about.

HTH -- Mark D Powell -- Received on Sun May 11 2008 - 15:17:24 CDT