0

I'm playing around with the data from GV$SQL_MONITOR and I found something that I don't understand. The oracle documentation says that SQL_EXEC_START, SQL_ID, and SQL_EXEC_ID combine to "uniquely identify one execution of the SQL statement". However, I see the below data in the table. Many records have the same SQL_EXEC_START, SQL_ID, and SQL_EXEC_ID, but a unique SID.

Does anyone here have insight into why the documentation and my observations contradict each other?

Possibly relevant: I observed that all of these queries finish at the same time.

SQL_EXEC_START        SQL_ID        SID SQL_EXEC_ID ELAPSED_TIME STATUS              LAST_REFRESH_TIME   
--------------------- ------------- --- ----------- ------------ ------------------- ---------------------
2017-09-20 14:27:56   gncss37ygprra 196    16777218      1847766 EXECUTING           2017-09-20 14:27:58   
2017-09-20 14:27:56   gncss37ygprra 3148    16777218       498598 EXECUTING           2017-09-20 14:28:00   
2017-09-20 14:27:56   gncss37ygprra 3329    16777218        96863 EXECUTING           2017-09-20 14:28:00   
2017-09-20 14:27:56   gncss37ygprra 480    16777218      1849199 EXECUTING           2017-09-20 14:27:58   
2017-09-20 14:27:56   gncss37ygprra 3727    16777218      1848618 EXECUTING           2017-09-20 14:27:58   
2017-09-20 14:27:56   gncss37ygprra 498    16777218       513823 EXECUTING           2017-09-20 14:28:00   
2017-09-20 14:27:56   gncss37ygprra 2578    16777218      1955707 EXECUTING           2017-09-20 14:28:00   
2017-09-20 14:27:56   gncss37ygprra 3541    16777218      1848522 EXECUTING           2017-09-20 14:27:58  
1
  • select SQL_EXEC_START start_tm, sid, SQL_ID, max(last_refresh_time) end_tm from gv$sql_monitor ... should help. I hazard a guess that this seems a PX SQL?
    – Raj
    Commented Sep 20, 2017 at 21:38

1 Answer 1

1

Let's create the below function so we can have a long running parallel query and have time to query SQL monitoring data:

create or replace function f1 return number parallel_enable as
begin
  dbms_lock.sleep(1800);
  return 1;
end;
/

Then this table:

create table t1 as select * from dual connect by level <= 4;

And:

alter session force parallel query parallel 4;

And start this query:

select f1 from t1;

If you query GV$SQL_MONITOR, with some additional columns, you can see it is a parallel query, and the sessions it's spawned, and also find the session that originally started it (QCSID):

select sql_exec_start, sql_id, sql_exec_id, sid, px_qcinst_id, px_qcsid from gv$sql_monitor
where status = 'EXECUTING';

SQL_EXEC_START      SQL_ID        SQL_EXEC_ID        SID PX_QCINST_ID   PX_QCSID
------------------- ------------- ----------- ---------- ------------ ----------
2017-09-26 22:20:55 25kj6ftn2vhrn    16777216        270            1        262
2017-09-26 22:20:55 25kj6ftn2vhrn    16777216         20            1        262
2017-09-26 22:20:55 25kj6ftn2vhrn    16777216        262                        
2017-09-26 22:20:55 25kj6ftn2vhrn    16777216        400            1        262
2017-09-26 22:20:55 25kj6ftn2vhrn    16777216        134            1        262

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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