9 Replies Latest reply: Jun 30, 2014 9:53 AM by Kasey.Parker RSS

    Reg: Exadata and /*+ FULL */ hint -

    ranit B

      Hi Experts,

       

      Recently, our database got migrated to Exadata environment, and a DBA told me that using the /*+ FULL */ hint in the query increases the query performance.

       

      Doubt -

      1) Does it actually enhance performance?

      2) I read some articles and got some information that Exadata does some kind of "Smart Scan" and "Cell Offloading" which makes the query efficient. But how does FULL hint contribute here?

       

      This links talks something about this, but not sure if correct - Some Hints for Exadata SQL Tuning - Part III - SQL Optimizer for Oracle - SQL Optimizer for Oracle - Toad World

       

      Please share your thoughts and advise.

       

      Thanks and Regards,

      -- Ranit

      ( on Oracle 11.2.0.3.0 - Exadata )

        • 1. Re: Reg: Exadata and /*+ FULL */ hint -
          Marc Fielding

          Hello Ranit,

           

           

           

          If all it took was 12-character hint to any SQL statement would always increase query performance, wouldn't Oracle have made it the default?

           

           

           

          Naturally the answer is more nuanced:  it depends on data volumes, concurrency, physical data placement, partition layout, caching, and a host of other factors.

           

           

           

          I suspect that your DBA mentioned the /*+FULL*/ hint to attempt to avoid single-block reads in favor of full scans, making it more likely that Exadata smart scans and cell offloading happen.  But it's not the same as increasing performance;  it can actually caused reduced performance, sometimes by a large factor.

           

           

           

          There has been a lot of good material written about Oracle performance, with and without Exadata.  One place I'd suggest to start would be Cary Millsap's excellent paper Thinking Clearly About Performance.

           

           

           

          HTH!

           

           

           

          Marc

          • 2. Re: Reg: Exadata and /*+ FULL */ hint -
            Franck Pachot

            Hi,

            If query is faster when forcing a full table scan, then you should investigate why the optimizer did not choose that. Did you 'gather' system statistics with dbms_stats.gather_system_stats('EXADATA'); ?

            Regards,

            Franck.

            • 3. Re: Reg: Exadata and /*+ FULL */ hint -
              Mohamed Houri

              Ranit,

               

              You should know that the golden point of exadata is smart scan and predicate offloading. You should also know that there is a fundamental pre-requisite for the smart scan to take place which is that your segment (table or index) should be read via mutli-block read (full table scan or index fast full scan) and that this multi-block read should bypass the buffer cache. It means it should be done via direct path read.

               

              So generally, to take advantage from an exadata machine you should have physical read done via a full table scan using direct path read. And this is why probably your DBA told you to use the full hint which in passing is not striclty sufficient to have smart scan and predicate offloading happen.

               

              Best regards

              Mohamed Houri

              • 4. Re: Reg: Exadata and /*+ FULL */ hint -
                Kasey.Parker

                Ranit -

                 

                Lots of good advice given by others. A little more to add to the comments already made...

                 

                Using a full hint as a general tuning rule on Exadata would not be a good idea, just like the sometimes proposed notion of dropping all indexes on Exadata to performance is not a good idea. As Mohamed mentions, a key performance optimization for Exadata are the smart scans, which do require direct path reads. Pushing for smart scans is what drives these types of ideas; because, other than the index fast full scan, index scans will not smart scan. However, smart scanning isn't always faster. OLTP type queries that are looking for one or two rows out of many are still usually faster with an index even on Exadata. If you find using a hint like FULL does improve a query's performance, then just as with using hints in general, it's better to determine why the optimizer is not picking the better execution plan, a full table scan in this case, in the first place; and resolve the underlying issue.

                 

                What you will probably find is you are over-indexed on Exadata. If you have control of the indexes in your environment, test by making certain indexes invisible and seeing if that helps performance. Indexes that were created to eliminate a percentage, even a large percentage, of rows, but not almost all rows for queries are candidates to be dropped. You definitely want to tune for direct path reads.

                 

                This is done by doing index evaluations as described; making sure your stats are accurate and up-to-date; as mentioned by Franck, be sure to gather the Exadata system stats - as this is the only thing that helps the optimizer be Exadata aware. And also, especially if you are running a data warehouse workload, you can look into using parallelism. Running queries in parallel, often even with a degree as little as 2, will help prompt the optimizer to favor direct path reads. Parallelism does need to be kept in check. Look into using the DBRM to help control parallelism - possibly even enabling parallel statement queuing.

                 

                Hopefully these will give you some ideas of things to look at as you enter the realm of SQL Tuning on Exadata.

                 

                Good luck!

                -Kasey

                • 5. Re: Reg: Exadata and /*+ FULL */ hint -
                  ranit B

                  If all it took was 12-character hint to any SQL statement would always increase query performance, wouldn't Oracle have made it the default?

                  Exactly, even I thought the same and got confused.

                  But it's not the same as increasing performance;  it can actually caused reduced performance, sometimes by a large factor.

                  Why? If something is working for good, it should increase the performance as well, right?

                  Why does it reduce performance?

                  • 6. Re: Reg: Exadata and /*+ FULL */ hint -
                    ranit B

                    Hi  Franck,

                     

                    Always good to have you in the discussions.

                    Franck Pachot wrote:

                     

                    Hi,

                    If query is faster when forcing a full table scan, then you should investigate why the optimizer did not choose that. Did you 'gather' system statistics with dbms_stats.gather_system_stats('EXADATA'); ?

                    Regards,

                    Franck.

                    I didn't know about the significance of gathering system stats. Will have a look at that?

                     

                    Btw, how to know when was the last stats were gathered? Anything like LAST_ANALYZED from ALL_INDEXES?

                    • 7. Re: Reg: Exadata and /*+ FULL */ hint -
                      ranit B

                      Hi Mohamed,

                      You should know that the golden point of exadata is smart scan and predicate offloading. You should also know that there is a fundamental pre-requisite for the smart scan to take place which is that your segment (table or index) should be read via mutli-block read (full table scan or index fast full scan) and that this multi-block read should bypass the buffer cache. It means it should be done via direct path read.

                       

                      So generally, to take advantage from an exadata machine you should have physical read done via a full table scan using direct path read. And this is why probably your DBA told you to use the full hint which in passing is not striclty sufficient to have smart scan and predicate offloading happen.

                      Thanks for the wonderful inputs.  This is my first interaction with Exadata ever. So, now I know something and will read further into these concepts.

                       

                      Much appreciated.

                      • 8. Re: Reg: Exadata and /*+ FULL */ hint -
                        ranit B

                        Hi Kasey,

                        Hopefully these will give you some ideas of things to look at as you enter the realm of SQL Tuning on Exadata.

                        It was indeed a great piece of advise and definitely helpful for SQL Tuning on Exadata. Thanks!!!

                        • 9. Re: Reg: Exadata and /*+ FULL */ hint -
                          Kasey.Parker

                          Ranit -

                           

                          System stats can be queried from sys.aux_stats$ to see settings and when system stats were last gathered - not just for Exadata but for all system stats. Specific to the Exadata system stats, this sets the PVAL1 to a not null value, e.g. 128, where PNAME = MBRC. The date/time the stats were last gathered can be seen in PVAL2 where PNAME = DSTOP. Here's an example of a query to check this:

                           

                          select pname, PVAL1, PVAL2 from sys.aux_stats$ where pname in ('MBRC','DSTOP');

                           

                          - Kasey