4 Replies Latest reply: Sep 29, 2011 9:13 AM by UweHesse RSS

    Exadata smart scan AWR

    User13512691-Oracle
      Hi,
      We have 2 node RAC running on X2-2 Full Rack system.

      I wanted to know- how can we confirm that our database is efficiently using Smart scan or storage index. Could this be checked in AWR report?

      Note - We don't have access to Exadata storage servers since is being managed by other party.
        • 1. Re: Exadata smart scan AWR
          256937
          Hi,
          I wanted to know- how can we confirm that our database is efficiently using Smart scan or storage index. Could this be checked in AWR report?
          Check stats like :

          cell flash cache read hits
          cell physical IO bytes saved by storage index
          cell physical IO interconnect bytes returned by smart scan


          You can find it on v$sysstat or on AWR report pn "Instance Activity Stats"


          Regards,
          Cerreia
          • 2. Re: Exadata smart scan AWR
            Kerry.Osborne
            AWR is probably not the best tool for gauging whether you are getting the benefit of Exadata's optimizations or not. The stats will show you whether you are using those features or not but won't give you much of a feel for how often they are being used. One of the things that I do when I look at a system is to try to get a feel for the percentage of "long running" statements that are getting some benefit from offloading (i.e. smart scans). To that end I sometimes use a script to count the number of "big" statements, either qualified by elapsed time (or better yet, the amount of blocks accessed), in the shared pool(s) and then calculate a percentage that have received some benefit from smart scans. I discussed the script in the Expert Oracle Exadata book (and it is available online at expertoracleexadata.com - I think it's called offload_percent.sql). Even this approach is somewhat flawed since statements can have a very small benefit from one full table scan while most of the time is spent elsewhere in a complex plan, but at least it will give you an idea of how many statements are getting smart scans. Focusing in on individual statements that are not performing as expected is probably a more productive activity and SQL Monitor is the tool of choice for that activity.

            Edited by: Kerry.Osborne on Sep 28, 2011 6:28 PM
            • 3. Re: Exadata smart scan AWR
              256937
              Hi Kerry,

              Agreed with your explanation, however not 100%.

              The question wasn't which is the best or the worst option, but if he can or cannot confirm if his database is efficiently using Smart scan or storage index. I do believe that with stats he'll be able to know that.

              I mean, if his database has zero or low stats, it means that it's not efficiently using it ( that is my understanding ). Specially if there is no prompt on cells.


              Kind regards,

              Cerreia
              • 4. Re: Exadata smart scan AWR
                UweHesse
                An easy way to determine that you actually benefit from Smart Scans & Storage Indexes are statistics in v$sysstat on the Database Layer like

                cell physical IO interconnect bytes
                cell physical IO interconnect bytes returned by smart scan
                cell physical IO bytes saved by storage index

                in comparison to
                physical read total bytes

                See here an example:
                http://uhesse.wordpress.com/2011/07/06/important-statistics-wait-events-on-exadata/

                You can of course break down these statistics to a certain time period with a STATSPACK/AWR report.

                Kind regards
                Uwe Hesse

                http://uhesse.wordpress.com