5 Replies Latest reply: Jul 31, 2011 3:31 PM by Kerry.Osborne RSS

    Flash Cache(KEEP)- No Change in execution time

    765115
      I have a PL/SQL procedure, which accesses many of tables for read. A specific bunch of these tables are setup/configuration based and hence are accessed repeatedly in the procedure. I have pinned these setup tables to the Flash Cache using the KEEP option. On re-running the procedure, I did not see any difference in the execution time.

      I ran this query before and after running the PL/SQL without pinning any table to Flash Cache and repeated the same after pinning the setup tables to Flash Cache. From the results, I noticed a significant decrease in the number of Physical Reads and increase in the number of Flash Cache Reads.

      However I don't understand why this does not bring down the execution time?

      Query to find Physical & Flash Cache Reads
      select name,value from v$mystat s, v$statname n
      where s.statistic#=n.statistic#
      and name in ('cell flash cache read hits','physical read total IO requests');
        • 1. Re: Flash Cache(KEEP)- No Change in execution time
          Kerry.Osborne
          Flash_cache keep is most useful for enabling Smart Scans to read from Disk and Flash Cache simultaneously. If you are doing Smart Scans on the tables you mentioned, you should see a significant improvement in response time for the statements doing those Smart Scans. If your tables are small look up type tables they should be cached in the buffer cache, not flash cache. At this point you should probably trace your process and see where it's spending it's time.
          • 2. Re: Flash Cache(KEEP)- No Change in execution time
            603349
            museshad wrote:
            However I don't understand why this does not bring down the execution time?
            If you are not seeing the execution time decrease even after pinning segments in the cell flash cache and you verified that are cell flash cash IOs hits, then maybe the speed of IO isn't significant to your application performance. e.g. IO time is not a significant portion of the performance profile....

            I'd recommend that you profile your code and find out where the time is being spent.
            Suggested readings:
            http://oreilly.com/catalog/9780596005276
            http://method-r.com/downloads/doc_download/72-mastering-performance-with-extended-sql-trace

            --
            Regards,
            Greg Rahn
            http://structureddata.org
            • 3. Re: Flash Cache(KEEP)- No Change in execution time
              Jagjeet Singh
              Hi - Kerry

              Do you mean smart scan can utilized Flash Cache as well, I had expression that smart scan will be kicked out for Direct read ( directly from datafiles ). so I thought it will be direct from Disk.

              Regards,
              Js

              Edited by: Jagjeet Singh on Jul 31, 2011 6:26 PM
              • 4. Re: Flash Cache(KEEP)- No Change in execution time
                603349
                Jagjeet Singh wrote:
                Do you mean smart scan can utilized Flash Cache as well, I had expression that smart scan will be kicked out for Direct read ( directly from datafiles ). so I thought it will be direct from Disk.
                Exadata can read data from disk, flash, or both at the same time (blended read). That behavior is for smart scan reads and non-smart scan reads.

                --
                Regards,
                Greg Rahn
                http://structureddata.org
                • 5. Re: Flash Cache(KEEP)- No Change in execution time
                  Kerry.Osborne
                  Yes, as Greg said, Smart Scans are capable of using both disk and Flash Cache. This behavior is only enabled for segments that are designated for "aggressive" caching using the STORAGE (CELL_FLASH_CACHE KEEP) syntax. For objects with this setting, Smart Scans will attempt to use both disk and flash cache. This can significantly improve Smart Scan performance in certain situations. Of course it may also cause problems with other objects that are not cached as aggressively.