SEO

How to log all the queries that takes more than n seconds .. like
mysql show query log

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 Gogala
http://mgogala.freehostia.com

On May 11, 2:29*pm, Mladen Gogala <mgog...@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 --