SQL Monitoring
Submitted by dave on Sun, 01/15/2012 - 05:19
Bind Variables
Tanel Poder mentions looking at v$sql_monitor.bind_xml for currently running (or recently run) bind variables for a SQL statement.
Querying SQL Monitoring Info
Summary
-- Status, Duration, SQL_ID, User, Parallel, Database Time, IO Requests, Start, Ended, SQL Text -- http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/dynviews_3048.htm SELECT Status, TO_CHAR(SQL_EXEC_START,'HH24:MI') as STARTTime, CASE WHEN Status<>'EXECUTING' THEN TO_CHAR(SQL_EXEC_START + (ELAPSED_TIME/1000000/24/60/60),'HH24:MI') WHEN Status IS NULL THEN NULL END as SQL_EXEC_END, ROUND(ELAPSED_TIME/1000000)||'s' as Duration, USERNAME, SID, SQL_ID, SQL_PLAN_HASH_VALUE as PlanHashVal, SQL_TEXT, KEY FROM v$sql_monitor mon ORDER BY SUBSTR(STATUS,1,4) DESC, SQL_EXEC_START DESC;
Detail
Using "Key" field from above Summary query:
select * From v$sql_plan_monitor where key = :key