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

Thread: Parallel Query

  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

  2. #2
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    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,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  3. #3
    Join Date
    Dec 2001
    Posts
    203
    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
    sumit

  4. #4
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    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,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  5. #5
    Join Date
    Dec 2001
    Posts
    203
    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.
    sumit

  6. #6
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    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,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  7. #7
    Join Date
    Dec 2001
    Posts
    203
    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
    sumit

  8. #8
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    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,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

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