interpreting v$session_longops
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: interpreting v$session_longops

Hybrid View

  1. #1
    Join Date
    Dec 2001
    Posts
    203

    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

    Investigation:

    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> /

    OPNAME TARGET SOFAR TOTALWORK UNITS ELAPSED_SECONDS MESSAGE
    -------------------- ---------- ---------- ---------- ---------- --------------- --------------------------------------------------
    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?

    Regards
    sumit

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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