RE: Does Oracle keep a history of which blocks are/were loaded into the buffer cache?

Would setting up partitioning and querying v$segment_statistics work?
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] On Behalf 
Of Bobak, Mark [Mark.Bobak@xxxxxxxxxxxx]
Sent: Tuesday, December 14, 2010 7:31 PM
To: Job Miller; sacrophyte@xxxxxxxxx
Cc: ORACLE-L
Subject: RE: Does Oracle keep a history of which blocks are/were loaded into 
the buffer cache?

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: