Parallel Query

[sumit]

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

[vnktummala]

Hi Sumit,

You are querying the entire view which stores the historical data also. Use the below query to see the current sessions running more than last 5 seconds.

select sid,username,time_remaining, message from v$session_longops where (totalwork - sofar) > 5;

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?

1. Because you are querying the whole view with out any condition.
2. Depends on the parallel servers available Oracle invoke the required servers.
3. SOFAR = elapsed time so far. For the sessions already completed obviously SOFAR = TOTALWORK
4. You are wrong in question # 3

Thanks,

[sumit]

Thanks Vijay.

So initally I had only one thread of P001

p001 (Slave) 1 1 Rowid Range Scan GENEVA_ADMIN.OLD_ACCOUNT 41825 41825 Blocks 21-DEC-10 1058 1

where the totaltime = sofar. What I understood is that parallel session is completed. right?

but later I saw another session popped up

- p001 (Slave) 1 1 Rowid Range Scan GENEVA_ADMIN.OLD_ACCOUNT 7 28932 Blocks 22-DEC-10 1058 1 - p001 (Slave) 1 1 Rowid Range Scan GENEVA_ADMIN.OLD_ACCOUNT

I was thinking # of parallel session is static in this view. If P001 session is completed already, I was not expecting another session of P001. Otherwise how could someone estimate the remaining time of processes?

regards
Sumit

[vnktummala]

This is because Oracle allocate the work part by part to the parallel servers not at one stretch.

For example, a table with 1000 blocks and a query with 4 parallel servers, Oracle will divide the table into 4 parts i.e 250 blocks each. Out of 250 blocks first it allocate 100 blocks then another 100 then remaining 50. In this case you will see each parallel session 3 times.

Hope this clears.

Thanks,

[sumit]

Alright, then it is really difficult to estimate the remaning execution time in case parallel query - is not that correct?

or do you suggest any other query or logic or any doc link ... I want to estimate the remaining execution time for my parallel queries.

Thanks once again.

[vnktummala]

yes, it is difficult to estimate the time for parallel queries by looking into v$session_longops. However, If the stats are updated for the respective tables / indexes then you can estimate the time by generating the explain plan for the query.

Thanks,

[sumit]

Thanks!

Let me clarify something more here ...

In my database I can see 8 parallel processes being fired for the migration scripts. Normally this can be controlled by the DEFAULT parallel hint being reduced to a specified parallelism. Having a 4 CPU machine, I reduced this to 4, still I see 8 sessions being fired. I changed the Parallel_threads_per_cpu to 1.

Any ideas what might prevent the parallel hints being used to control the width of parallelism?

Regards

[vnktummala]

PARALLEL_MAX_SERVERS parameter controls the number of parallel servers.

Also, please check if the tables have object level parallelism enabled (DEGREE column). If the parallel hint in the query is 2 and DEGREE is 2 for the table used in the query then you will see 4 parallel servers running.

Thanks,