DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Parallel Query

Threaded View

  1. #1
    Join Date
    Dec 2001
    Posts
    203

    Parallel Query

    Questions to Experts

    Background

    I am inserting high volume of data into a table with parallel hint, degree 4.

    Query Below

    I was quering v$session_longops and here are results (results from 2 executions)

    Username QC/Slave Sl Sl OPERATION_NAME TARGET SOFAR TOTALWORK UNITS START_TIM TIMESTAMP QC SID QC INS
    -------- -------- -- -- ----------------- ------------------------- ---------- ---------- ------- --------- --------- ------ ------
    - p000 (Slave) 1 1 Rowid Range Scan GADMIN.OLD_ACC 44292 44292 Blocks 22-DEC-10 1058 1
    - p007 (Slave) 1 1 Rowid Range Scan GADMIN.OLD_ACC 23905 23905 Blocks 21-DEC-10 1058 1
    - p000 (Slave) 1 1 Rowid Range Scan GADMIN.OLD_ACC 45572 45572 Blocks 22-DEC-10 1058 1
    - p000 (Slave) 1 1 Rowid Range Scan GADMIN.OLD_ACC 25099 25099 Blocks 21-DEC-10 1058 1
    - p000 (Slave) 1 1 Rowid Range Scan GADMIN.OLD_ACC 83432 83432 Blocks 21-DEC-10 1058 1
    - p001 (Slave) 1 1 Rowid Range Scan GADMIN.OLD_ACC 41825 41825 Blocks 21-DEC-10 1058 1
    - p002 (Slave) 1 1 Rowid Range Scan GADMIN.OLD_ACC 19979 19979 Blocks 21-DEC-10 1058 1
    - p002 (Slave) 1 1 Rowid Range Scan GADMIN.OLD_ACC 37345 37345 Blocks 22-DEC-10 1058 1
    - p002 (Slave) 1 1 Rowid Range Scan GADMIN.OLD_ACC 36705 36705 Blocks 22-DEC-10 1058 1
    - p002 (Slave) 1 1 Rowid Range Scan GADMIN.OLD_ACC 41095 41095 Blocks 22-DEC-10 1058 1
    - p002 (Slave) 1 1 Rowid Range Scan GADMIN.OLD_ACC 39265 39265 Blocks 22-DEC-10 1058 1
    - p003 (Slave) 1 1 Rowid Range Scan GADMIN.OLD_ACC 166 46945 Blocks 22-DEC-10 1058 1
    - p003 (Slave) 1 1 Rowid Range Scan GADMIN.OLD_ACC 17419 17419 Blocks 21-DEC-10 1058 1
    - p003 (Slave) 1 1 Rowid Range Scan GADMIN.OLD_ACC 52072 52072 Blocks 21-DEC-10 1058 1
    - p003 (Slave) 1 1 Rowid Range Scan GADMIN.OLD_ACC 43105 43105 Blocks 22-DEC-10 1058 1
    - p003 (Slave) 1 1 Rowid Range Scan GADMIN.OLD_ACC 16779 16779 Blocks 21-DEC-10 1058 1
    - p004 (Slave) 1 1 Rowid Range Scan GADMIN.OLD_ACC 43012 43012 Blocks 21-DEC-10 1058 1
    - p004 (Slave) 1 1 Rowid Range Scan GADMIN.OLD_ACC 44932 44932 Blocks 22-DEC-10 1058 1
    - p005 (Slave) 1 1 Rowid Range Scan GADMIN.OLD_ACC 30859 30859 Blocks 21-DEC-10 1058 1
    - p005 (Slave) 1 1 Rowid Range Scan GADMIN.OLD_ACC 23815 23815 Blocks 22-DEC-10 1058 1
    - p006 (Slave) 1 1 Rowid Range Scan GADMIN.OLD_ACC 43652 43652 Blocks 22-DEC-10 1058 1
    - p006 (Slave) 1 1 Rowid Range Scan GADMIN.OLD_ACC 35432 35432 Blocks 21-DEC-10 1058 1
    - p006 (Slave) 1 1 Rowid Range Scan GADMIN.OLD_ACC 28295 28295 Blocks 21-DEC-10 1058 1
    - p006 (Slave) 1 1 Rowid Range Scan GADMIN.OLD_ACC 14215 14215 Blocks 22-DEC-10 1058 1
    - p007 (Slave) 1 1 Rowid Range Scan GADMIN.OLD_ACC 30212 30212 Blocks 22-DEC-10 1058 1
    - p000 (Slave) 1 1 Rowid Range Scan GADMIN.OLD_ACC 35335 35335 Blocks 21-DEC-10 1058 1

    26 rows selected.

    SQL> /

    Username QC/Slave Sl Sl OPERATION_NAME TARGET SOFAR TOTALWORK UNITS START_TIM TIMESTAMP QC SID QC INS
    -------- -------- -- -- ----------------- ------------------------- ---------- ---------- ------- --------- --------- ------ ------
    - p000 (Slave) 1 1 Rowid Range Scan GADMIN.OLD_ACC 83432 83432 Blocks 21-DEC-10 1058 1
    - p007 (Slave) 1 1 Rowid Range Scan GADMIN.OLD_ACC 30212 30212 Blocks 22-DEC-10 1058 1
    - p000 (Slave) 1 1 Rowid Range Scan GADMIN.OLD_ACC 35335 35335 Blocks 21-DEC-10 1058 1
    - p000 (Slave) 1 1 Rowid Range Scan GADMIN.OLD_ACC 25099 25099 Blocks 21-DEC-10 1058 1
    - p000 (Slave) 1 1 Rowid Range Scan GADMIN.OLD_ACC 44292 44292 Blocks 22-DEC-10 1058 1
    - p001 (Slave) 1 1 Rowid Range Scan GADMIN.OLD_ACC 7 28932 Blocks 22-DEC-10 1058 1 - p001 (Slave) 1 1 Rowid Range Scan GADMIN.OLD_ACC 41825 41825 Blocks 21-DEC-10 1058 1
    - p002 (Slave) 1 1 Rowid Range Scan GADMIN.OLD_ACC 36705 36705 Blocks 22-DEC-10 1058 1
    - p002 (Slave) 1 1 Rowid Range Scan GADMIN.OLD_ACC 41095 41095 Blocks 22-DEC-10 1058 1
    - p002 (Slave) 1 1 Rowid Range Scan GADMIN.OLD_ACC 39265 39265 Blocks 22-DEC-10 1058 1
    - p002 (Slave) 1 1 Rowid Range Scan GADMIN.OLD_ACC 19979 19979 Blocks 21-DEC-10 1058 1
    - p002 (Slave) 1 1 Rowid Range Scan GADMIN.OLD_ACC 37345 37345 Blocks 22-DEC-10 1058 1
    - p003 (Slave) 1 1 Rowid Range Scan GADMIN.OLD_ACC 16779 16779 Blocks 21-DEC-10 1058 1
    - p003 (Slave) 1 1 Rowid Range Scan GADMIN.OLD_ACC 52072 52072 Blocks 21-DEC-10 1058 1
    - p003 (Slave) 1 1 Rowid Range Scan GADMIN.OLD_ACC 17419 17419 Blocks 21-DEC-10 1058 1
    - p003 (Slave) 1 1 Rowid Range Scan GADMIN.OLD_ACC 43105 43105 Blocks 22-DEC-10 1058 1
    - p003 (Slave) 1 1 Rowid Range Scan GADMIN.OLD_ACC 180 46945 Blocks 22-DEC-10 1058 1
    - p004 (Slave) 1 1 Rowid Range Scan GADMIN.OLD_ACC 43012 43012 Blocks 21-DEC-10 1058 1
    - p004 (Slave) 1 1 Rowid Range Scan GADMIN.OLD_ACC 44932 44932 Blocks 22-DEC-10 1058 1
    - p005 (Slave) 1 1 Rowid Range Scan GADMIN.OLD_ACC 30859 30859 Blocks 21-DEC-10 1058 1
    - p005 (Slave) 1 1 Rowid Range Scan GADMIN.OLD_ACC 23815 23815 Blocks 22-DEC-10 1058 1
    - p006 (Slave) 1 1 Rowid Range Scan GADMIN.OLD_ACC 35432 35432 Blocks 21-DEC-10 1058 1
    - p006 (Slave) 1 1 Rowid Range Scan GADMIN.OLD_ACC 28295 28295 Blocks 21-DEC-10 1058 1
    - p006 (Slave) 1 1 Rowid Range Scan GADMIN.OLD_ACC 43652 43652 Blocks 22-DEC-10 1058 1
    - p006 (Slave) 1 1 Rowid Range Scan GADMIN.OLD_ACC 14215 14215 Blocks 22-DEC-10 1058 1
    - p007 (Slave) 1 1 Rowid Range Scan GADMIN.OLD_ACC 23905 23905 Blocks 21-DEC-10 1058 1
    - p000 (Slave) 1 1 Rowid Range Scan GADMIN.OLD_ACC 45572 45572 Blocks 22-DEC-10 1058 1

    Questions:

    1. I was expecting 4 parallel threads, why there are so many?
    2. Why thread P000 or P003 or P002 has 5 parallel connections in the result above, Where as P007 has only 2? How Oracle is deciding the number of threads?
    3. I was in belief that if SOFAR = TOTALWORKS the task is complete. Is that right? Looking at SOFAR I used to calculate time remaining.
    4. If I am right at question# 3 above, why I could see the thread P001.SOFAR = totalwork already, has started again where totalwork=28932?

    Regards
    Sumit
    Last edited by sumit; 12-22-2010 at 06:03 AM.
    sumit

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