Oracle’s GV$SQL_MONITOR – Uniquely identify execution of a SQL statement

oracleoracle-12c

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

Best Answer

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

Related Solutions

Sql-server – How to find equivalent of Oracle’s privileges in SQL Server

In SQL Server the right to "CONNECT" is given at the instance level. You'll find this right assigned to the login. The ability to view the database catalog is given at the database level by assigning the "VIEW DEFINITION" right to the database or the schema.

Update Statement in SQL

You need to avoid catching and reraising errors with a user defined error number. That will only make it harder to see where the error is happening. You should also avoid using explicit cursors and try using PL/SQL collections. As long as you are using 9i or above PL/SQL collections are the better way to go. You should probably have an index on transdetail as follows:

CREATE UNIQUE INDEX transdetail_idx1 
  ON transdetail ( transtype, transdate, sr_cd, quantity);

This will greatly reduce the number of rows that Oracle needs to look at. It will do a range scan of the transdate column for each of the transtype values that you are searching for, plus the index has all of the columns that you want. Hence Oracle won't need to look at the index, then look at the table.

-- Let unhandled exceptions remain unhandled
-- EXCEPTION
-- This is a terrible example of error handling, don't use it
-- WHEN OTHERS THEN
-- raise_application_error(-20001,'An error was encountered - '||SQLCODE||' 
-- -ERROR- '||SQLERRM);
-- END;
-- /


CREATE OR REPLACE PROCEDURE TEST_RECPT_0705
AS
   TYPE transdetail_rec IS RECORD (
      sr_cd     transdetail.sr_cd%TYPE,
      transdate transdetail.transdate%TYPE,
      quantity  transdetail.quantity%TYPE );

   TYPE transdetail_type IS TABLE OF transdetail_rec
      INDEX BY PLS_INTEGER;

   transdetail_tab transdetail_type;
BEGIN
   SELECT sr_cd, to_char(trunc(transdate,'mm'),'YYYYMM') Month, sum(quantity)Receipt 
     BULK COLLECT INTO transdetail_tab
     FROM transdetail
    WHERE transtype IN('STKREC','RP','RECADJ','SRET') 
      AND trunc(transdate,'mm') BETWEEN '01-April-2013' AND '31-March-2014'
    GROUP BY trunc(transdate,'mm'),sr_cd;

   FORALL i IN transdetail_tab.FIRST..transdetail_tab.LAST
   UPDATE transdetail
      SET receipt = transdetail_tab(i).RECEIPT
    WHERE sr_cd   = transdetail_tab(i).SR_CD
      AND yyyymm  = transdetail_tab(i).MONTH;

    SELECT sr_cd, to_char(trunc(transdate,'mm'),'YYYYMM') Month,  sum(quantity) Issue
      FROM transdetail
     WHERE transtype IN('SINV','IP','STA','ISSADJ','PURRET')
       AND trunc(transdate,'mm') BETWEEN '01-April-2013' AND '31-March-2014'
     GROUP BY trunc(transdate,'mm'), sr_cd;

   FORALL i IN transdetail_tab.FIRST..transdetail_tab.LAST
   UPDATE transdetail
      SET receipt = transdetail_tab(i).RECEIPT
    WHERE sr_cd   = transdetail_tab(i).SR_CD
      AND yyyymm  = transdetail_tab(i).MONTH;

   COMMIT;
END;
/

Best Answer

Related Solutions

Sql-server – How to find equivalent of Oracle’s privileges in SQL Server

Update Statement in SQL

Related Question