RE: Does Oracle keep a history of which blocks are/were loaded into the buffer cache?
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>
- To: Job Miller <jobmiller@xxxxxxxxx>, "sacrophyte@xxxxxxxxx" <sacrophyte@xxxxxxxxx>
- Date: Tue, 14 Dec 2010 22:31:52 -0500
Cool idea, Job. I have zero experience, and clearly far too little insight,
into DBMS_FGA. Just another item to add to the list of stuff to learn next
year… ☺
-Mark
From: Job Miller [mailto:jobmiller@xxxxxxxxx]
Sent: Tuesday, December 14, 2010 9:44 PM
To: Bobak, Mark; sacrophyte@xxxxxxxxx
Cc: ORACLE-L
Subject: Re: Does Oracle keep a history of which blocks are/were loaded into
the buffer cache?
Charles,
You can use DBMS_FGA to audit any sql that actually saw "old data" as defined
by an audit condition on a particular column for a date older than a particular
value.
It will capture full sql+ binds when data was queried where your audit
condition is met and it can call a handler that records it for counter purposes.
So you set up a few of these policies that act as counters, for different date
ranges.
and monitor the results.
Job
--- On Tue, 12/14/10, Charles Schultz <sacrophyte@xxxxxxxxx> wrote:
From: Charles Schultz <sacrophyte@xxxxxxxxx>
Subject: Re: Does Oracle keep a history of which blocks are/were loaded into
the buffer cache?
To: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>
Cc: "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
Date: Tuesday, December 14, 2010, 8:31 PM
Mark,
Yeah, I realized my goof with logminer as soon as I sent it. =) Oh well.
Fortunately, I am not overly worried about the overhead associated with
SQL_TRACE; that is an interesting idea. I am not so sure I could sell that on a
Production database, though. v$segment_stats sounds like the best way to at
least get some idea. As I mentioned to another lister, partitioning is an
option on the table but I really want to justify it first before I blindly
offer it as a solution. *grin* The table is not currently partitioned, hence my
analysis.
Thanks for the ideas.
On Tue, Dec 14, 2010 at 17:31, Bobak, Mark
<Mark.Bobak@xxxxxxxxxxxx<http://us.mc539.mail.yahoo.com/mc/compose?to=Mark.Bobak@xxxxxxxxxxxx>>
wrote:
Charles,
I’m pretty sure you’re out of luck. Consider that blocks are loaded from disk
to satisfy queries, as well as DML, and logminer will have nothing to say about
that. You can enable SQL_TRACE, and it will capture file#/block# data, but at
great overhead and expense.
Oracle just doesn’t track to that level of detail, as far as I know. You can
look at V$SEGMENT_STATISTICS, to get an idea of which segments have more
physical I/O happening on them, but no detail about which set of blocks in a
table were most recently loaded from disk. If the table is partitioned by
date, and the partitions closely align w/ the level of granularity that you
require for your analysis, that may allow you to use V$SEGMENT_STATISTICS and
periodic snapshots to determine if newer data is read from disk more recently
than old data.
Just some thoughts….hope that helps,
-Mark
From:
oracle-l-bounce@xxxxxxxxxxxxx<http://us.mc539.mail.yahoo.com/mc/compose?to=oracle-l-bounce@xxxxxxxxxxxxx>
[mailto:oracle-l-bounce@xxxxxxxxxxxxx<http://us.mc539.mail.yahoo.com/mc/compose?to=oracle-l-bounce@xxxxxxxxxxxxx>]
On Behalf Of Charles Schultz
Sent: Tuesday, December 14, 2010 5:47 PM
To: ORACLE-L
Subject: Does Oracle keep a history of which blocks are/were loaded into the
buffer cache?
Good day,
I am trying to analyze a given table in an effort to determine how often the
older records are used compared to the newer records. I know I can go against
the AWR and determine the age of some statements by searching for a date column
in the table and grabbing the associated bind variable, but this obviously
misses any queries that limit data via a join (ie, no bind variable). Is there
a way to determine when a particular range of datafile blocks have been last
loaded from disk, or how often? I did some digging in x$bh but that has no
history (that I know of). In the meantime, I'll start up a LogMiner session and
scrape all interested ROWIDs, but this is not a pretty solution. Maybe the only
solution?
--
Charles Schultz
--
Charles Schultz
Other related posts:
