Auto DOP ! 11g Feature…

May 12, 2015 Leave a comment

Recently, during one of the Performance POC on an Oracle Engineered System, I got to implement Auto DOP feature for one of their critical Batch Job. This Batch job is a a set of multiple processes. Some of the processes run as a Serial Process, some these are just one process, but the query is hinted to use Oracle Parallelism and some of these are parallel application threads.

For AUTO DOP to work, there are two mandatory settings. These are IO Calibration and setting of parallel_degree_policy (which defaults to MANUAL, means no AUTO DOP). I started with AUTO settings as LIMITED requires changing DEGREE for all (or critical huge) tables. AUTO setting means, Optimizer will calculate and come out with the Degree of Parallelism. Further, this setting also enables two features: Parallel_Statement_Queuing and In-Memory Parallel Execution. In-Memory Parallel execution might disable Exadata Smart Scan, therefore, I modified _parallel_cluster_cache_policy to ADAPTIVE i.e no In-Memory Parallel Execution. IO Calibration was done, so that, optimizer can take the advantage of various values (as can be seen from DBA_RSRC_IO_CALIBRATION) to come out with Degree of Parallelism.

My initial test run was not that successful. While investigating this, I came across a very informative article from Gwen Shapira. As per this article, for AUTO DOP considers MAX_PMBPS value only and this can be set manually to 200. In my case, since the calibration process was executed, the value for MAX_PMBPS was around 3000+. As I understand, this value depicts the throughput and therefore, larger the value, lower the DOP will be. I followed the advice in the article and deleted the rows from resource_io_calibrate$.

delete from resource_io_calibrate$;
insert into resource_io_calibrate$ values(current_timestamp, current_timestamp, 0, 0, 200, 0, 0);
commit;

Post setting this value manually, the database needs a restart. This change did the trick. The performance of many of the processes improved drastically. For some of the queries, we could also see Statement Queuing working effectively i.e. waiting for the resources to be available and taking up the required DOP, rather than downgrading the parallel degree. However, the customer was bit apprehensive about AUTO word and therefore, decided to Implement the optimizer calculations of DOP manually into each of these queries and disabling this feature. This change worked as well.

My observations from this nice feature (and I may be wrong as I did not get much time to investigate or work on this), to name a few are :

  • Any Query that takes more than 10 Seconds (Optimizer Estimation) will be subject to Auto DOP
  • A Query with parallel hint will also go through serial execution run time evaluation. This means, if optimizer estimates the runtime of serial execution to be less than 10 seconds, parallel hint will be ignored.

    • In my opinion, AUTO DOP is one of the best innovation.

    Filed under Exadata, Performance Tagged with , , , , , , ,

    Exadata v/s Solid State Disk ! Performance & Scalability Comparison.

    February 21, 2012 9 Comments

    During one of my Exadata Presentation, one of the attendees asked about the Performance & Scalability Difference between EXADATA and SSD. He mentioned that the Performance POC for one of their critical application on SSD and Exadata gave similar results. Before replying to his query, I asked him “What was the Average CPU Utilization during both the POC’s ?”. While the answer to my question was interesting, thought of writing a short blog on this debate and at the end will be the response by the attendee to my question.

    Lot has been written on the comparison between the two. For example, visit asktom.oracle.com and Kevin Closson’s blog. I will just write on my reply with a brief explanation.

    EXADATA is one of the biggest breakthrough in achieving fast computing and therefore, clearly overpowers other technologies around it. While other technologies might give you a similar performance, but investing only for performance is not the only Organization Goal. The primary reason, which I feel, Exadata scores over others is the Scalability Factor. With that said, while Exadata gives Extreme Performance, it improves the Scalabiity as well. SSD and any other technologies are Hardware components, whereas, Exadata is a combination of Hardware and Intelligent Software and this combination makes it Faster and Scalable.

    In an Oracle Database or an Application deployed on top of Oracle, Latches are considered to be a Performance and Scalability Inhibitor. Every Block access (into the cache or from the cache), requires Latch and if these latch gets are in access, these excessive visits to the cache will burn the CPU power thus impacting the performance and scalability. Exadata has changed this approach, by introducing a faster hardware that scans the required blocks and an Intelligent Software on top of it, to filter only the required column data directly to the Private Memory Area (PGA) of the User Process. This Intelligence bypasses the buffer cache thus avoiding a costlier step of Latching. This has a huge impact on the CPU Cycles and therefore, if the Database Server has enough CPU Power available, this additional CPU Power can be translated to Scalability.

    Usually, in an Oracle Database on a traditional (non-exadata) storage, the blocks are read from the Disk to the Buffer Cache and then the required column data is filtered and fetched to the Private Memory of the User Process. Reading a Block from Disk to Cache, filtering the required data from Cache involves CPU. All of these consume CPU Cycles on the Database Server. As mentioned, in an Exadata Storage, the combination of Hardware and Software works together to eliminate the costlier from the Database Server. From this explanation, it is quite evident that Exadata is not only improves performance, but also provides scalability. All of this is achieved without making any changes in the application. However, further optimization in an Exadata is also possible.

    Now to the reply on my Question “What was the Average CPU Utilization during both the POC’s ?”. The reply was that through out the POC, the CPU Utilization on an Exadata Machine was always far below than the POC on SSD. Solid State Disk’s would provided faster access to the blocks that are read from the disk, but will not eliminate the costlier steps of latching the blocks from the Database Server. Therefore, savings in CPU Cycles is not expected. Infact, it may worsen the performance.

    In one of the paragraph above, I mentioned about the steps as a part of row fetching that an Oracle Database, on an non-exadata storage, performs on behalf of a User. This is, a block is read from Disk to the Cache and then the required columns and row data is fetched to the Private Memory area, which is to the PGA, of the User Process. This contradicts to the steps that I mentioned in one of my previous blog on Consistent Gets. This blog rectifies the error in my assumption on the steps performed and mentioned in the said previous blog.

    Filed under Exadata, General, Performance Tagged with ,