Smart Flash Cache and Smart Flash Logging - Oracle Exadata Recipes: A Problem-Solution Approach - page 532

To perform this same type of analysis using Oracle's AWR repository, query DBA_HIST_SQLSTAT on the
OPTIMIZED_PHYSICAL_READS column and compare against your total reads. Listing 18-6 provides an example.
Listing 18-6. lst18-06-sfc-sqlawr.sql
SQL> select sql_id,prr,oprr,100*(oprr/prr) orate from (
select sql_id,sum(physical_read_requests_total) prr,
sum(optimized_physical_reads_total) oprr
from dba_hist_sqlstat
where physical_read_requests_total is not null
group by sql_Id
order by 2 desc)
where rownum < &&num_rows + 1 ;
Enter value for num_rows: 5
SQL_ID PhysReads OptPhysReads Flash%
------------- ---------- ------------ -------
1cq3qr774cu45 507077882 507067843 100.00
934ur8r7tqbjx 42566664 42566387 100.00
6m2ckkhmmqctb 36371819 36367782 99.99
bpmt7gm1bbd4u 20814431 20814241 100.00
9ybhpsssm2zhn 16379002 15273896 93.25
Measuring Smart Flash Cache Hits per Object
To measure your Smart Flash Cache hit counts per object, interrogate the cell server's flashcachecontent object.
Following, we are listing flash cache hits where the number of hits per object exceeded 1,000:
[oracle@cm01dbm01 ~]$ dcli -g ./cell_group "cellcli -e list flashcachecontent \
> attributes dbUniqueName,hitCount,missCount,cachedSize,objectNumber \
> where hitCount \> 1000"
cm01cel01: VISX 1397 573 20979712 3321189
cm01cel01: VISY 490628 2572 851968 4294967294
cm01cel01: EDW 74968 2617 163840 4294967294
cm01cel01: DWPRD 1470 2 147456 4294967294
cm01cel01: VISX 24629 345 892928 4294967294
... Output omitted for brevity
[oracle@cm01dbm01 ~]$
Once you have this information, you can map objectNumber to your object names using steps presented in
Recipe 18-2.
You can also query V$SEGSTAT or its AWR counterpart, DBA_HIST_SEG_STAT , to measure the number of optimized
reads per segment. Use the script in Listing 18-7 to display your current and historical physical reads, optimized
physical reads, and a flash read hit percentage.
Listing 18-7. lst18-07-sfc-seg.sql
SQL> @lst18-07-sfc-seg.sql
Current segment statistics
SQL> select obj.owner, obj.object_name,
segstat.pr prd, segstat.opr oprd,
100*(segstat.opr/segstat.pr) pct