DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2001

    interpreting v$session_longops

    Problem Description:
    I am running parallel insert statement querying data from other tables. The performance is very slow.

    The record count is almost 900K

    No intermin commit is in place

    Parallel Max Server is set to 24


    I tried to look into v$session_longops and here is the query and the output which I had received within 5 mnts from the time I started running the insert statement. But the statement is still running.

    select * from (select opname, target, sofar, totalwork, units, elapsed_seconds, message from v$session_longops
    where sid in(select sid from v$session where username ='Goozy') order by start_time desc)
    where rownum <=1;

    SQL> /

    -------------------- ---------- ---------- ---------- ---------- --------------- --------------------------------------------------
    Table Scan Goozy 17512 17512 Blocks 7 Table Scan: Goozy.REQUEST: 17
    512 out of 17512 Blocks done

    I am condused with the result of sofar and total work. If within 5 mnts it shows 17512 sofar is done for total work of 17512, then why the script os still running?

    Or am I missing anything?


  2. #2
    Join Date
    Mar 2007
    Ft. Lauderdale, FL
    Not everything shows up on v$session_longops, there are some restrictions to it.

    When quering v$session_longops use both SID and SERIAL# otherwise you may be looking at an "old" long operation coming from the same SID.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.