4 Replies Latest reply: Apr 21, 2014 11:52 AM by Kasey.Parker RSS

    Enable DOP on ExaData


      I need step to enable Degree of parallel on Exadata, i research in oracle support but i did not find documnet.


      the current database using for OLTP and there are two tables having a lot of data, and lot of report generated by using query dependent in this tables.


      i need best practice of parallel, what is best is to using Auto or Limited for this two table, and what is step to do this

        • 1. Re: Enable DOP on ExaData

          Hi friend


          Two things DOP works on Oracle Exadata :


          1. Create table ... as select statement automatically uses parallelism

          2. Statement like INSERT...SELECT parallelism to be enabled manually

              ----> alter session enable parallel dml


          You can set with PARALLEL hint also


          Hope it helps




          • 2. Re: Enable DOP on ExaData
            Marc Fielding

            The Oracle VLDB and partitioning guide has a excellent introduction to parallel execution, with suggestions for DOP and the rest:

            Using Parallel Execution



            • 3. Re: Enable DOP on ExaData
              Lothar Flatz

              Let me add: DOP on Exadata is not different from DOP on any other server.

              • 4. Re: Enable DOP on ExaData

                I've spent a lot of time working with and testing parallelism on Exadata. The best Oracle documentation on parallelism is the VLDB and partitioning guide Marc already mentioned. But a few other things to help guide you with using parallelism specifically with Exadata:

                • Parallel execution, at the database level, works the same on or off Exadata. However, even without a DOP on the database, Exadata inherently uses parallelism, when properly configured, through the way ASM stripes data across all grid and cell disks on each of the Exadata storage cells allowing the cell CPUs and disks to all work together – splitting the load across DB and Cell CPUs. This allows lower degree of parallelism on Exadata to achieve optimal performance.
                • I would recommend being very cautious with using Auto DOP (parallel_degree_policy) set to Auto or Limited. I've had very mixed results with it in testing and prefer to leave it at the default manual setting and enable DOPs, where needed, manually. I've only tested Auto DOP with 11gR2 though, not yet with 12c; so it may work better on 12c. You can enable parallel statement queuing, via the _parallel_statement_queuing hidden parameter, even without setting Auto DOP; and I do like this feature. If you're going to use this study up on it, test it and learn how to control it with the parallel_server_target and parallel_max_servers parameters.
                • Parallelism can be a great performance boost - especially on Exadata - because it can help drive smart scans. But it can also overwhelm any system, including Exadata, if left unchecked. I recommend the following to enable and control it:
                  • For larger tables you feel would benefit from parallelism on all queries, set the DOP on the table itself, i.e. ALTER TABLE [TABLE NAME] PARALLEL [DOP];
                    • Parallelism on small tables will hurt performance so only enable on larger tables.
                    • Test to determine the best parallel degree - you'll eventually get diminishing returns as you go up in DOP
                  • If you don't want all queries against a table to be parallel - then use the parallel hint in the queries you do want.
                    • Be careful when using the hint... specify the table name(s) in the hint if the query has joins to make sure you only parallelize the larger tables that need it and not the smaller tables
                  • Control parallelism using DBRM resource plans by setting parallel degree limits and max % targets - this is very important to not let parallel queries overwhelm a system.
                  • Set the parallel_max_servers and parallel_min_servers appropriately.
                    • Use MOS note 1274318.1 for Exadata best practices on these parameters
                    • Set parallel_min_servers to a high daily average of your concurrent parallel processes, as this will reduce overhead in constant spawning of new parallel processes.
                  • Test out parallel statement queuing; see point on this above. This can deliver more consistent parallel performance as it can help not kick off to many parallel processes but still by queuing for short time can often perform much better than serializing the statement
                  • Test, test , test!
                    • Monitor using Grid Control and SQL Monitor - you want to find the balance between not realizing the performance benefit by keeping things too throttled and overwhelming system resources and having performance degradation by allowing too many parallel processes