This discussion is archived
5 Replies Latest reply: Jul 31, 2011 1:31 PM by Kerry.Osborne RSS

Flash Cache(KEEP)- No Change in execution time

765115 Newbie
Currently Being Moderated
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 Oracle ACE Director
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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
    501277 Newbie
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points