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.
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.