5 Replies Latest reply: Jan 22, 2013 2:24 PM by Karan RSS

    parallel process in select query

    516481
      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
          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
            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
              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
                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
                  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