Tom at 11:53, 31 March 2016

2016-03-31T11:53:13Z

Tom: Created page with "You can use following query to see how many bytes in your session were optimized: SELECT name, VALUE FROM v$mystat JOIN v$statname USING (statistic#) WHE..."

2016-03-31T11:52:44Z

Created page with "You can use following query to see how many bytes in your session were optimized: <source lang="sql"> SELECT name, VALUE FROM v$mystat JOIN v$statname USING (statistic#) WHE..."

New page

You can use following query to see how many bytes in your session were optimized:
<source lang="sql">
SELECT name, VALUE
FROM v$mystat JOIN v$statname USING (statistic#)
WHERE name IN ('cell flash cache read hits',
'physical read requests optimized',
'physical read total IO requests');
</source>

You should either reconnect to reset the counter, or run it before/after the query and calculate the differences yourself.
'physical read total IO requests' is how many times a request was sent to the storage (which could be anywhere between 1 and 128 blocks)
'physical read requests optimized' is how many read requests were satisfied by either flash cache or storage indexes
'cell flash cache read hits' is how many read requests were satisfied by flash cache hits

The difference between ‘physical read requests optimized' - 'cell flash cache read hits' is how many requests were satisfied by the storage indexes. This means that the storage index decided that some blocks did not have to be read from the storage because there was no data in it that could satisfy the where clause.

To have similar results in bytes you can use 'physical read total bytes', ‘cell physical IO bytes saved by storage index’ and ‘physical read total bytes optimized’.
<source lang="sql">
SELECT name, VALUE
FROM v$mystat JOIN v$statname USING (statistic#)
WHERE name IN ('physical read total bytes',
'cell physical IO bytes saved by storage index',
'physical read total bytes optimized');
</source>

@@ Line 3: / Line 3: @@
 SELECT name, VALUE
    FROM v$mystat JOIN v$statname USING (statistic#)
-WHERE name IN ('cell flash cache read hits',
+ WHERE name IN ('cell flash cache read hits',
-               'physical read requests optimized',
+                'physical read requests optimized',
-               'physical read total IO requests');
+                'physical read total IO requests');
 </source>
@@ Line 19: / Line 19: @@
 SELECT name, VALUE
    FROM v$mystat JOIN v$statname USING (statistic#)
-WHERE name IN ('physical read total bytes',
+ WHERE name IN ('physical read total bytes',
-               'cell physical IO bytes saved by storage index',
+                'cell physical IO bytes saved by storage index',
-               'physical read total bytes optimized');
+                'physical read total bytes optimized');
 </source>

Optimized reads - Revision history

Tom at 11:53, 31 March 2016

Tom: Created page with "You can use following query to see how many bytes in your session were optimized: SELECT name, VALUE FROM v$mystat JOIN v$statname USING (statistic#) WHE..."