Hi guys,
It's Oracle 12c R2 on Linux 64-bit and I am testing the real-time monitoring feature.
When I monitor a session using the DBMS_SQL_MONITOR, the quick queries do not appear in v$sql_monitor, but long one do appear, although I enabled the FORCED_TRACKING.
Here's what I've done:
In the client session:
SQL> SELECT SID, SERIAL# FROM V$SESSION WHERE AUDSID = SYS_CONTEXT('USERENV', 'SESSIONID');
SID SERIAL#
---------- ----------
48 63502
In the admin session:
VARIABLE OP_ID NUMBER;
BEGIN
:OP_ID := DBMS_SQL_MONITOR.BEGIN_OPERATION (
DBOP_NAME => 'ORA.SALES.TOPCUSTOMERS4',
SESSION_ID=> 48,
SESSION_SERIAL=> 63502 ,
FORCED_TRACKING => DBMS_SQL_MONITOR.FORCE_TRACKING );
END;
/
Then back in client session, I ran the following queries:
SET TIMING ON
# Query 1: this is a very quick query, just to check if it will be monitored or not
SELECT SYSDATE FROM DUAL;
# Query 2: The query retrieves the top customers in the company history, another quick query
SELECT count(*)
FROM ORDERS, CUSTOMERS, EMP
WHERE CUSTOMERS.CUSTOMER_ID = ORDERS.CUSTOMER_ID AND EMP_NO = ACCOUNT_MGR_ID;
# Query 3: a very time consuming query for demonstration
SELECT /*+ USE_NL(A B) */ COUNT(*) FROM CUSTOMERS A, CUSTOMERS B;
In the admin session, I receive only two rows in v$sql_monitor for the operation, one represents the operation and one represents the long query (query # 3). Query numbers 1 and 2 do not appear.
SQL> select 'DBOP_NAME: ' || dbop_name ||chr(10) || 'IN_DBOP_NAME: ' || IN_DBOP_NAME || chr(10) || 'SQL_TEXT: ' || sql_text
from v$sql_monitor
where (dbop_name='ORA.SALES.TOPCUSTOMERS4' OR IN_DBOP_NAME='ORA.SALES.TOPCUSTOMERS4')
; 2 3 4
'DBOP_NAME:'||DBOP_NAME||CHR(10)||'IN_DBOP_NAME:'||IN_DBOP_NAME||CHR(10)||'SQL_T
--------------------------------------------------------------------------------
DBOP_NAME: ORA.SALES.TOPCUSTOMERS4
IN_DBOP_NAME:
SQL_TEXT:
DBOP_NAME:
IN_DBOP_NAME: ORA.SALES.TOPCUSTOMERS4
SQL_TEXT: SELECT /*+ USE_NL(A B) */ COUNT(*) FROM CUSTOMERS A, CUSTOMERS B