monitor progress of long running job with dbms_application_info.set_session_longops

Investigating a major performance issue with a PL/SQL loop running for more then 12 hours. Added dbms_application_info.set_session_longops to have entry in v$session_longnops.

DECLARE

	-- @what: display elapsed msecs
	-- @link: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2389164927483
	n_starttime NUMBER;
	n_endtime   NUMBER;
	n_elapsed   NUMBER;
	
	d_date			DATE   := SYSDATE;

	-- @what: dbms_application_info.set_session_longops
	-- @link: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1099233454171#18173358903352
	l_nohint 		NUMBER DEFAULT dbms_application_info.set_session_longops_nohint;
	l_rindex 		NUMBER DEFAULT l_nohint;

  n_slno      NUMBER;
  n_totalwork NUMBER := 847; -- init total work, number of records in loop recordset is 847
  n_sofar     NUMBER := 0;


BEGIN

  FOR i IN
  (
          SELECT  *********.DBKey  FloorDBKey,
                  *********.Value3 StoreVersionID
          FROM    *********,
                  *********,
                  *********
          WHERE   *********.DBParentStoreKey = *********.DBKey
          AND     *********.DBKey = *********.DBParentFloorplanKey
          AND     *********.DBStatus = 1
          AND     *********.DBStatus = 1
  --         AND       *********.DBKey = 121281
          ORDER BY *********.DBKey
  )
  LOOP
    n_sofar := n_sofar + 1;
		sys.dbms_application_info.set_session_longops (
		  rindex 				=> l_rindex,
		  slno   				=> n_slno,
		  op_name 			=> 'csg_Store_Fixture_OneStore',
		  target  			=> 123,
		  context 			=> 123,
		  sofar   			=> n_sofar,
		  totalwork 		=> n_totalwork,
		  target_desc 	=> '********* loop',
		  units					=> 'PL/SQL Loop iterations'
		);

  	n_starttime := dbms_utility.get_time+power(2,32);

  	ems_ikb.csg_Store_Fixture_OneStore( d_date, i.FloorDBKey, i.StoreVersionID);

  	n_endtime := dbms_utility.get_time+power(2,32);
  	n_elapsed := n_endtime - n_starttime;
  	dbms_output.put_line( 'Store ' || i.FloorDBKey || ' took ' || n_elapsed || ' msecs');

  END LOOP;
END;
/


select to_char(sysdate, 'DD-MON-YY hh:mi:ss AM') sdate from dual
/

Monitor progress in v$session_longops. (Output was from a testrun after I added an index and reducing runtime from over twelve hours to under 15 minutes.)

SQL> /
  SID OP                         SOFAR TOTALWORK  PERC UNITS                  START_TIME      ELAPSED_SECONDS
----- ------------------------ ------- --------- ----- ---------------------- --------------- ---------------
  107 csg_Store_Fixture_OneSto     301       847    36 PL/SQL Loop iterations 16-OCT 12:30:20             243
                               
SQL> /                         
  SID OP                         SOFAR TOTALWORK  PERC UNITS                  START_TIME      ELAPSED_SECONDS
----- ------------------------ ------- --------- ----- ---------------------- --------------- ---------------
  107 csg_Store_Fixture_OneSto     422       847    50 PL/SQL Loop iterations 16-OCT 12:30:20             343
                               
SQL> /                         
  SID OP                         SOFAR TOTALWORK  PERC UNITS                  START_TIME      ELAPSED_SECONDS
----- ------------------------ ------- --------- ----- ---------------------- --------------- ---------------
  107 csg_Store_Fixture_OneSto     550       847    65 PL/SQL Loop iterations 16-OCT 12:30:20             424
                               
SQL> /                         
  SID OP                         SOFAR TOTALWORK  PERC UNITS                  START_TIME      ELAPSED_SECONDS
----- ------------------------ ------- --------- ----- ---------------------- --------------- ---------------
  107 csg_Store_Fixture_OneSto     740       847    87 PL/SQL Loop iterations 16-OCT 12:30:20             574
                               
SQL> /                         
  SID OP                         SOFAR TOTALWORK  PERC UNITS                  START_TIME      ELAPSED_SECONDS
----- ------------------------ ------- --------- ----- ---------------------- --------------- ---------------
  107 csg_Store_Fixture_OneSto     845       847   100 PL/SQL Loop iterations 16-OCT 12:30:20             663
                               
        

Links:

Leave a Reply

Helpful? - leave your note below so I can brag

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>