Reputation: 11
I guys I am looking for a tool which can analyze the SQL queries at runtime. I want to capture all the queries that's executed during an invocation of a function in an web application during runtime. It would be great if you can help me with this.
Regards
Upvotes: 0
Views: 544
Reputation: 146349
"I want to capture all the queries that's executed during an invocation of a function..."
This is the easy bit: the 10046 event will write all the SQL executed by a session to a trace file which can be mined for information using the standard tkprof utility, or other tools such as Trace Analyzer (if you have an Oracle Support contract). Tim Hall has written a good intro: Read it here.
"...in an web application during runtime."
This is harder. "web application" usually means connection pooling, which makes it darned hard to establish which database session is linked to which front-end activity. However, later versions of Oracle can help out there, with tools like DBMS_MONITOR. Find out more.
Upvotes: 2
Reputation: 7932
Not sure about any tool but select * from v$sql;
can give a whole lot of information.
It will show you the SQL Queries that are running, the Application type that is running the query, no. of rows processed, optimizer cost, user i/o wait time, application wait time, buffered gets, concurrency wait time, direct writes, disk reads etc. etc. (and a whole bunch of other information)
Upvotes: 1