Capturing all the SQLs ran yesterday

[rsuri]

Hi,
One of my customers is complaining that their Cold-Fusion application sometimes gets really really slow and they have some idea that it's because of the users running some long, exhaustive queries. He wants to know if I can capture the SQLs for him. The SQLs are generated dynamically so there is no way of knowing it at the application level.

I know v$sql will show the SQLs in the Shared SQL area. Explain_Plan will also show similar information but this type of information is available only when the SQL is running not a few hours later when customer calls and complains about performance.

Is there a way to log all the SQLs generated with the user information? This needs to be done at the database level ?
I know this will generate a *lot* of information but I want to give it a shot.

Thanks,

[gpsingh]

enable sql_trace=true in init.ora
OR match OS pid of long running connection with Oracle sid and username and find the sql.
thanks
GP

[jdorlon]

A way to do it that might not generate such a large trace file :

1) find the users's session in V$SESSION, get the SID and serial#

2) execute the following :
exec dbms_system.set_sql_trace_in_session(sid, serial#, TRUE);

3) to stop tracing - its the same command except with FALSE as the 3rd parameter.

no matter which way you trace, you might want to set the MAX_DUMP_FILE_SIZE parameter. This parameter determines how big the dump file is allowed to grow...

[rsuri]

Thanks guys
The problem with sql_trace is that --
1. It does not capture the time when the sql ran and the user who ran it.
2. It also generates too much information while my customer wants only the SQLs so that he can decipher the culprits.

I heard that DBArtisan is a good tool for this so got an Evaluation copy and am still trying it. So far it seems that it captures the SQLs DBArtisan sends to Oracle to generate the actual SQLs and statistics but it does not Log the actual SQLs.

For example when you select a process it shows you the actual SQL on the screen (select col1, col2 from customer where col1 > 5) but in the Log file it captures --

SELECT T.SQL_TEXT FROM V$SQLTEXT T, V$SESSION S WHERE S.SID = 23 AND S.SQL_ADDRESS=T.ADDRESS ORDER BY T.PIECE

I think that defeats the purpose of logging because what I wanted to log was the actual SQL.

I will appreciate if anybody has more suggestions.

Thanks,

[tamilselvan]

When the system is running slow, run the following the query to capture the SQL statement and then do EXPLAIN PLAN.

Replace SCOTT with your user name.

select sql_text from sys.v$sqltext_with_newlines
where (address, hash_value ) in
( select sql_address, sql_hash_value
from v$session where username in ('SCOTT')
)
order by address, piece;