In Microsoft SQL, we can trace currently which SQL query takes execution time and load of each SQL query in SQL Profiler. In Oracle, what should we do? It's not that simple like Microsoft SQL. Open TOAD and login as SYSTEM authentication. Then type following statement into TOAD SQL.

view plain print about
1<!---
2MYDSN is simple database of mine.
3Put your database name instead of MYDSN.
4--->
5
6SELECT * FROM V$SESSION WHERE STATUS = 'ACTIVE' AND USERNAME = 'MYDSN'

Run this query. After that, all active query will be displayed and note which query execution time takes so long.

As our application's performance, we scheduled that some reports in query is needed to run at Mid-night. But some stupid users run those reports in day-time. In these cases, we need to kill the loaded query from Oracle rendering. If not, which will make JRun to be crushed and our application will be hanging.

How to kill SQL query in TOAD? It's just simple.

view plain print about
1<!---
2Need to replace actual SID and SERIAL
3of above query result.
4--->
5ALTER SYSTEM KILL SESSION 'SID, SERIAL'

This entry was posted on January 4, 2011 at 7:35 PM and has received 1785 views. There are currently 0 comments. Print this entry.