1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to check cursor stats during Runtime ??

Discussion in 'General' started by ankit_203, Sep 24, 2011.

  1. ankit_203

    ankit_203 Guest

    Hi
    I have a stored procedure running in which there is a cursor which fetches around 1500000 records and then query another table using the fetched record values.
    I cannot modify the procedure as its on production.
    I want to know which cursor record is currently being processed by the procedure, and how many are still remaining ?
    please provide guidance on how to check the cursor stats at runtime. I want to check upto which record the cursor has been fetched and how many are still remaining.
    I have cursor name.
    Is there some dynamic view to check cursor stats at runtime ??

    thanks.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    No. You could check the progress through the v$session_longops view if the select is considered by Oracle as a long operation. If it isn't visible then you're only choice is to modify the procedure to either:

    1) Write records to a progress table
    2) Instrument the procedure to write to V$SESSION_LONGOPS

    An example of how to do the latter using DBMS_APPLICATION_INFO is shown below:

    Code (SQL):
    [FONT=Courier]DECLARE
     rindex  BINARY_INTEGER;
     slno    BINARY_INTEGER;
     sofar   NUMBER(6,2);
     target  BINARY_INTEGER;
     totwork NUMBER := 100;
    BEGIN
      rindex := [COLOR=#0000ff]dbms_application_info.set_session_longops_nohint[/COLOR];

      SELECT object_id
      INTO target
      FROM all_objects
      WHERE object_name = 'TEST';

      FOR i IN 1 .. totwork
      LOOP
        sofar := i;
        [COLOR=#0000ff]dbms_application_info.set_session_longops[/COLOR](rindex, slno,
        'PSOUG', target, 0, sofar, 100, 'Pct Complete');

        INSERT INTO test VALUES (i);

        dbms_lock.sleep(0.25);
      END LOOP;
      COMMIT;
    END;
    /[/FONT]

     

    Notice the two calls to dbms_application_info; the function call sets the index of the line to insert/modify in teh V$SESSION_LONGOPS view and the second inserts/updates the row to reflect progress. This example is found at http://psoug.org/reference/dbms_applic_info.html where you'll also find commands to show the progress of the script from another database session.
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO

    How does this apply to the original thread? This needs to be a new topic. Please repost this as a new thread.