Re: 11gR2 DB Flash Cache

From: Franky Weber Faust <>
Date: Tue, 07 Feb 2017 21:36:01 +0000
Message-ID: <>

Hey Jack,

I'm not sure but I think you can query the column name from v$sysstat where name like '%flash%cache%'.
I'm out of office now, so I'm not able to test it, but I'm almostly sure there's some stats there.

And for the objects you can query v$bh column status like '%flash%' then you get block number and check what is the object in dba_objects. I think that'll work.


Franky Weber Faust

On Tue, 7 Feb 2017 at 18:27 Jack Applewhite <> wrote:

> We recently moved our busiest two 11gR2 EE databases (about 1TB each) from
> an X4 ODA to an X5. It was via switchover to physical standbys, so we
> hadn't been using - didn't really know about (no Flash Cache SSDs in X4s) -
> Database Smart Flash Cache. Last weekend we were able to configure each DB
> (one per node on the X5 ODA) to have 192GB Flash Cache (out of 512GB total
> available in the ODA).
> Well, it's been better than Christmas! We've been marveling at the HUGE
> overall performance improvement we see. The main App hitting one of the DBs
> is a COTS Student Information System, hit by all of our 12,000 or so
> teachers and staff. There were a bunch of Selects in the App's code that
> would drive load averages to 200 - 400% periodically during the day. Now we
> rarely see load averages approaching 100%, usually staying around 25%.
> StatsPac reports show the dramatic differences in waits, as well as the
> SQLs found to be hogs.
> My question ... Is there a way to get visibility into the flash cache to
> see what's in there? Since this is 11g, the 12c-only V$FlashFileStat view
> is not available and V$Sess_IO only has the session level
> Optimized_Physical_Reads column. None of the SGA-related views that I've
> found have info. about the flash cache. Mr. Google and MOS haven't helped
> either.
> Any way to see what's going on in the Flash Cache?
> Thanks.
> --
> Jack C. Applewhite - Database Administrator
> Austin I.S.D. - MIS Department
> 512.414.9250 (wk)
> I can't help about the shape I'm in,
> I can't sing, I ain't pretty and my legs are thin.
> But ... -- "Oh Well" F.M.
> Confidentiality Notice: This email message, including all attachments, is
> for the sole use of the intended recipient(s) and may contain confidential
> student and/or employee information. Unauthorized use of disclosure is
> prohibited under the federal Family Educational Rights & Privacy Act (20
> U.S.C. §1232g, 34 CFR Part 99, 19 TAC 247.2, Gov’t Code 552.023, Educ. Code
> 21.355, 29 CFR 1630.14(b)(c)). If you are not the intended recipient, you
> may not use, disclose, copy or disseminate this information. Please call
> the sender immediately or reply by email and destroy all copies of the
> original message, including attachments.


Franky Weber Faust
Oracle DBA
Skype: franky.faust

Received on Tue Feb 07 2017 - 22:36:01 CET

Original text of this message