This discussion is archived
5 Replies Latest reply: Jan 22, 2013 12:24 PM by karan RSS

parallel process in select query

516481 Newbie
Currently Being Moderated
Hi Experts
its production
stats are good
indexes are good

issue :
a select query is taking time 20min , the indexes exists on the where clause columns, I tried the query using parallel hint it gave the results iin 20 sec.
the DOP of the table set to 10, but when I run the query it picks old plan

constraints : I cannot modify the query, it needs to be tuned without modifying it


if DOP is >1 then select is expected to use parallellism in explain plan..is my understanding correct?

if I am wrong please advice how can i force the query to use the parallellism

Please advice and guide asap


THanks in advance
Ajay Kumar
  • 1. Re: parallel process in select query
    VenkatB Guru
    Currently Being Moderated
    Hi Ajay


    You can enable parallelism at session level as well

    Alter session force parallel query parallel 10;

    Now, run the query

    Regards
    Venkat
  • 2. Re: parallel process in select query
    516481 Newbie
    Currently Being Moderated
    Venkat ,Thanks for the quick response

    the options that you have provided will work but my limitation is the query cannot be modified and it fires using application, as it fires using application so i cannot set anything at session level.

    so I decided to to set the DOP to 10 but no luck.

    I am still with the same issue...

    Thanks
    Ajay Kumar

    Edited by: user513478 on Jan 22, 2013 6:59 AM
  • 3. Re: parallel process in select query
    VenkatB Guru
    Currently Being Moderated
    Hi Ajay

    Using parallel servers depends on the resources availability, what action that CBO thinks relevant to satisfy the query. Even if your table has DOP set, if the query is only going to use an index to do a range scan and then use table access by index rowid, it won't even use parallel processes. If your query demands a full table scan, it may use parallel servers.

    For ex, in a million record table, if you have an unique index on ID column and if you are querying SELECT * FROM TABLE where ID=:id; even though this table may have DOP set, it may not use parallel servers at all as it will be a single index scan followed by table access by ROWID

    If you can show the query, original explain plan and DOP of table(s), indexe(s) involved, it may help better

    Regards
    Venkat
  • 4. Re: parallel process in select query
    riedelme Expert
    Currently Being Moderated
    user513478 wrote:
    Hi Experts
    its production
    stats are good
    indexes are good

    issue :
    a select query is taking time 20min , the indexes exists on the where clause columns, I tried the query using parallel hint it gave the results iin 20 sec.
    the DOP of the table set to 10, but when I run the query it picks old plan

    constraints : I cannot modify the query, it needs to be tuned without modifying it
    What version of Oracle?

    Tuning SQL without modifying the SQL can be very hard but you may have options.

    Could you use a materialized view with automatic query rewrite?

    Can you use a SQL profile to affect performance?

    Have you looked very+ carefully at the indexes to see if other indexes might be more efficient?

    20 minutes to 20 seconds sounds too good to be true. Are you sure you aren't reading cached data and that the 2nd, faster run really is running in parallel?
  • 5. Re: parallel process in select query
    karan Pro
    Currently Being Moderated
    Like you said optimizer is using the old plan it could be your statistics are accurate for the corresponding objects, If you have set object level parallelism there is no guarantee that that the number of parallel processes would be used. If auto DOP isnt there then the query may or may not have used degree of table parallelism depending on the optimizer, parallel you did try and it forced it use it, the third option for you is to alter session force parallel query ; which you said is not possible in your case. Even if the auto DOP was used and the estimated elapse time for the statement was less than PARALLEL_MIN_TIME_THRESHOLD by default 10 seconds then the statement will run serial. hope it helps. The point is you need to understand optimizer and parallelism concepts.

    Regards
    Karan

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points