I have, in the past, demonstrated DBMS_APPLICATION_INFO here and here.
Also, I have demonstrated how V$SESSION_LONGOPS can be used (and misunderstood !!)
Here is a demo of how you can use both features to enable monitoring of a (batch ?) job run.
The job is modified to include calls to DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS.
The DBA can then monitor the job from V$SESSION_LONGOPS.
The job is to update a PRODUCTS table with new PRODUCT_IDs, after some validation. Since, this can take some time to run, the DBA wants to monitor it via V$SESSION_LONGOPS.
Here is the PL/SQL code for the batch (job) run :(look up the documentaion on DBMS_APPLICATION_INFO in the PL/SQL Developers Guide for our version -- I know that is available since at least 10.2, although this demonstation below is in 19c)
DECLARE
rindex BINARY_INTEGER;
slno BINARY_INTEGER;
target_count number;
updated_count number;
product_row products%rowtype;
check_flag varchar2(8);
BEGIN
rindex := dbms_application_info.set_session_longops_nohint;
updated_count := 0;
select num_rows into target_count --- this is an approximation based on last updated statistics on the table
from user_tables
where table_name = 'PRODUCTS';
-- begin the updates here
for product_row in (select product_id, product_name from products)
loop
-- validate_for_update is a function that checks if this product should get a new PRODUCT_ID
select validate_for_update(product_row.product_id, product_row.product_name) into check_flag from dual ;
if check_flag='OK' then
update products set product_id = product_id+10000 where product_id=product_row.product_id;
insert into update_run_log values (product_row.product_id,systimestamp);
updated_count := updated_count+1;
dbms_application_info.set_session_longops(rindex,
slno,
op_name=>'New_Product_IDs',
target=>0, -- default, not used by me
context=>0, -- default, not used by me
sofar=>updated_count,
totalwork=>target_count,
target_desc=>'Table : PRODUCTS',
units=>'rows');
else
null;
end if;
end loop;
END;
/
commit;
And here is the DBA monitoring the job as it is running :
23:22:15 SQL> l 1 select sid, opname, target_desc, sofar, totalwork, units, start_time, elapsed_seconds, time_remaining 2 from v$session_longops 3* where username = 'HEMANT' 23:22:15 SQL> / no rows selected 23:22:16 SQL> 23:22:21 SQL> / SID OPNAME TARGET_DESC SOFAR TOTALWORK UNITS START_TIME ELAPSED_SECONDS TIME_REMAINING ----- ------------------ ---------------- ---------- ---------- ------ ------------------ --------------- -------------- 18 New_Product_IDs Table : PRODUCTS 3 500 rows 03-APR-21 23:22:18 2 331 23:22:22 SQL> 23:22:30 SQL> / SID OPNAME TARGET_DESC SOFAR TOTALWORK UNITS START_TIME ELAPSED_SECONDS TIME_REMAINING ----- ------------------ ---------------- ---------- ---------- ------ ------------------ --------------- -------------- 18 New_Product_IDs Table : PRODUCTS 12 500 rows 03-APR-21 23:22:18 12 488 23:22:31 SQL> 23:22:44 SQL> / SID OPNAME TARGET_DESC SOFAR TOTALWORK UNITS START_TIME ELAPSED_SECONDS TIME_REMAINING ----- ------------------ ---------------- ---------- ---------- ------ ------------------ --------------- -------------- 18 New_Product_IDs Table : PRODUCTS 26 500 rows 03-APR-21 23:22:18 26 474 23:22:45 SQL> 23:23:26 SQL> / SID OPNAME TARGET_DESC SOFAR TOTALWORK UNITS START_TIME ELAPSED_SECONDS TIME_REMAINING ----- ------------------ ---------------- ---------- ---------- ------ ------------------ --------------- -------------- 18 New_Product_IDs Table : PRODUCTS 67 500 rows 03-APR-21 23:22:18 69 446 23:23:27 SQL> 23:26:28 SQL> / SID OPNAME TARGET_DESC SOFAR TOTALWORK UNITS START_TIME ELAPSED_SECONDS TIME_REMAINING ----- ------------------ ---------------- ---------- ---------- ------ ------------------ --------------- -------------- 18 New_Product_IDs Table : PRODUCTS 245 500 rows 03-APR-21 23:22:18 251 261 23:26:30 SQL> 23:28:31 SQL> / SID OPNAME TARGET_DESC SOFAR TOTALWORK UNITS START_TIME ELAPSED_SECONDS TIME_REMAINING ----- ------------------ ---------------- ---------- ---------- ------ ------------------ --------------- -------------- 18 New_Product_IDs Table : PRODUCTS 364 500 rows 03-APR-21 23:22:18 373 139 23:28:32 SQL> 23:29:24 SQL> / SID OPNAME TARGET_DESC SOFAR TOTALWORK UNITS START_TIME ELAPSED_SECONDS TIME_REMAINING ----- ------------------ ---------------- ---------- ---------- ------ ------------------ --------------- -------------- 18 New_Product_IDs Table : PRODUCTS 416 500 rows 03-APR-21 23:22:18 426 86 23:29:25 SQL> 23:30:16 SQL> / SID OPNAME TARGET_DESC SOFAR TOTALWORK UNITS START_TIME ELAPSED_SECONDS TIME_REMAINING ----- ------------------ ---------------- ---------- ---------- ------ ------------------ --------------- -------------- 18 New_Product_IDs Table : PRODUCTS 467 500 rows 03-APR-21 23:22:18 478 34 23:30:17 SQL> 23:30:43 SQL> / SID OPNAME TARGET_DESC SOFAR TOTALWORK UNITS START_TIME ELAPSED_SECONDS TIME_REMAINING ----- ------------------ ---------------- ---------- ---------- ------ ------------------ --------------- -------------- 18 New_Product_IDs Table : PRODUCTS 493 500 rows 03-APR-21 23:22:18 505 7 23:30:44 SQL> 23:30:48 SQL> / SID OPNAME TARGET_DESC SOFAR TOTALWORK UNITS START_TIME ELAPSED_SECONDS TIME_REMAINING ----- ------------------ ---------------- ---------- ---------- ------ ------------------ --------------- -------------- 18 New_Product_IDs Table : PRODUCTS 497 500 rows 03-APR-21 23:22:18 509 3 23:30:49 SQL> 23:30:50 SQL> / SID OPNAME TARGET_DESC SOFAR TOTALWORK UNITS START_TIME ELAPSED_SECONDS TIME_REMAINING ----- ------------------ ---------------- ---------- ---------- ------ ------------------ --------------- -------------- 18 New_Product_IDs Table : PRODUCTS 500 500 rows 03-APR-21 23:22:18 512 0 23:30:51 SQL> 23:31:04 SQL> / SID OPNAME TARGET_DESC SOFAR TOTALWORK UNITS START_TIME ELAPSED_SECONDS TIME_REMAINING ----- ------------------ ---------------- ---------- ---------- ------ ------------------ --------------- -------------- 18 New_Product_IDs Table : PRODUCTS 500 500 rows 03-APR-21 23:22:18 512 0 23:31:05 SQL>
(In this demo, all 500 rows actually do pass the "VALIDATE_FOR_UPDATE" check)
Normally, we expect V$SESSION_LONGOPS to be populated by Long Running Operations (I specify Operations because of the "misinterpretation" that I demonstrate here). Operations that are Full Table Scans or Parallel Execution or RMAN Operations --- all of these are cases where the view is populated "automatically" by Oracle.
However, as I have demonstrated above, you can use your own code to populate this view so that it can be used to monitor Long Running "Operations" that you have defined (or, rather, worked with the Developers to define if you are the DBA).
No comments:
Post a Comment