4 Replies Latest reply: Dec 10, 2012 2:19 AM by Aman.... RSS

    Parallel Statement Queueing

    user154136
      I know that parallel statement queueing is enabled when you use automatic degree of parallelism. I am trying to identify if it is possible to use parallel statement queueing without using auto DOP. My current environment does not use auto DOP and I was looking to use queueing to solve a performance variance issue I am seeing. I will submit a request to support as well but I wanted to see if anyone had any experience with doing this or if it was even possible. Thanks for your time.
        • 1. Re: Parallel Statement Queueing
          L-MachineGun
          user12191713 wrote:
          ... I am trying to identify if it is possible to use parallel statement queueing without using auto DOP. My current environment does not use auto DOP
          and I was looking to use queueing to solve a performance variance issue I am seeing.
          Queueing?
          I don't fully understand your question or requirements, but have you considered using the "PARALLEL" hint?
          :p
          • 2. Re: Parallel Statement Queueing
            user154136
            There is a feature in 11g where you can place parallel statements in a queue if there are not sufficient parallel servers available. The statements are then run once sufficent parallel servers are available. I am looking to use this if possible to prevent downgraded statements from being run when there are not sufficient parallel servers. I would rather wait and use full parallelism then be downgraded and have a longer statement run time.
            • 3. Re: Parallel Statement Queueing
              Max Seleznev
              According to Oracle documentation advanced features of parallel execution such as automatic DOP, statement queuing and in-memory parallel execution depend on the value of PARALLEL_DEGREE_POLICY parameter. Statement queuing is only available if the parameter set to AUTO.

              Here is the [url http://docs.oracle.com/cd/E11882_01/server.112/e25523/parallel002.htm#CIHEFJGC]document.

              Since automatic DOP itself depends on many other factors to work (such as DEFAULT degree set for the objects) it's theoretically possible to use queuing and still explicitly specify DOP for majority of the objects through either hints of parallel degree property.

              Hope it helps.
              • 4. Re: Parallel Statement Queueing
                Aman....
                IMO, the answer would be a no as the feature was introduced in 11.2 Auto DOP only (as Max mentioned already) . Please read,
                http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-parallel-execution-fundamentals-133639.pdf

                HTH
                Aman....