1

I cannot see all queries with v$sql_monitor or gv$sql_monitor. I can see some queries, none of which have /*+ MONITOR */. I have tested a query with MONITOR and it still did not show. I thought it might just be a big explain problem, so I adjusted my session with:

ALTER SESSION SET "_SQLMON_MAX_PLAN"=4020;
ALTER SESSION SET "_SQLMON_MAX_PLANLINES"=4000;

select first_refresh_time, last_refresh_time, 
status, sql_text 
from v$sql_monitor 
order by last_refresh_time desc;

, but it didn't help. What I am trying to do is see query submitted via an application. The query works, so I know it is hitting the database. I simply cannot see the query in Oracle.

What am I doing wrong? I am using SQL Developer if that matters.

1

Oracle explains the V$SQL_MONITOR view in its official documentation.

Looks like it only keeps the record there for a minute or two, or until it needs the space to show the other queries.

Disclaimer
The original answer was posted as a comment by @MguerraTorres

2

SQL Monitoring will be triggered if one of the following conditions is present:

  • query is taking more than 5 seconds of CPU time or
  • query is taking more than 5 seconds waiting on I/O or
  • query is running in parallel or
  • /*+ MONITOR */ hint was present in the query

In other words, if your query is a very fast one, doesn't run in parallel and doesn't have MONITOR hint, it won't be monitored.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.