-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|