Is there any way that I can trace a session from start to finish,
recording each and every SQL, along with the duration, CPU consumption
and, possibly, the number of I/O requests performed while executing every
SQL?



--
http://mgogala.byethost5.com

Mladen Gogala wrote:
You can't trace CPU consumption and I/O in PostgreSQL.

Here's what you can do:

ALTER USER ... SET log_min_duration_statement = 0;
ALTER USER ... SET log_min_error_statement='ERROR'; (or better)
ALTER USER ... SET log_min_messages='ERROR'; (or better)

That will take care of the other things for the user in question.

You can also do something about CPU consumption and maybe
about I/O, but you'll have to resort to the operating system.
For that, you'll first have to find out the process id of
the backend process.

The following is for Linux (which have access to here):
- there's a "-x <pid>" flag for sar that reports, among others,
user and system CPU time per process.
- on kernel 2.6.20 and later, there is I/O accounting per
process. You can find it in /proc/<pid>/io

What you certainly can't do is trace these things per query.

You can always look into pg_statio_all_tables and
pg_statio_all_indexes to find cumulative statistics for
blocks read, buffer hits and disk reads per object.

This cannot be used for tracing, but you can figure out how
much I/O a statement causes by quering these tables before and
after the statement. This is useful for later analysis of
questionable queries.

Yours,
Laurenz Albe

On Thu, 18 Mar 2010 10:54:42 +0100, Laurenz Albe wrote:

I was looking for something like SQL_TRACE which, in its most primitive
form, is able to tell me which statement is running for the longest time.
Ideally, I should be able to do something like

alter session set events='10046 trace name context forever, level 12'

or the equivalent using DBMS_MONITOR, but there is no wait event
interface. Also, log files do not contain execution plans.



--
http://mgogala.byethost5.com

Mladen Gogala wrote:
If you want something akin to an AWR report, check out
pgFouine (http://pgfouine.projects.postgresql.org/).
It's a pretty damn good log file analyzer that will help you
find statements that use lots of time because a) they take long
or b) they run frequently.

If you want explain plans for long running statements in the
log file, take a look at the auto_explain contrib module.

Yours,
Laurenz Albe